Restore backups in Oracle

<< Click to Display Table of Contents >>

Navigation:  Bizagi Studio > How To´s > Useful how-to's > How to restore a backup of your Bizagi database >

Restore backups in Oracle

Overview

To restore a Oracle backup file (dump) of your Bizagi database follow the steps described below.

For doubts on this approach, and details on what information is stored at the backup file, refer to How to restore a backup of your Bizagi database.

 

 

Prerequisites

When using Oracle as the database for your Bizagi project, creating backups and restoring them is done (and referred to) through Oracle's Export and Import utilities.

For this you will need to consider in the machine creating the export:

 
1. Matching client versions in export and import.

Notice that the installed client version will most likely differ according to the bit version (32-bit or 64-bit) supported by the database server system. It is required that the Oracle client's version used in the export matches the client's version used in the import.

 

2. Compliant character sets

Ensuring that these character sets are the same one or a compatible one, is a requisite for Oracle’s export and import utilities to maintain the integrity of the information (otherwise, any of this information contained in the database can result altered).

These need to be compatible between the database instances.

 

3. Oracle home setting.

If you have a unique Oracle home environment variable, you may disregard this consideration.

On the other hand, if you have more than 1 Oracle client installed where you will use the export and import utilities, you will need to ensure that the ORACLE_HOME environment variable is properly set to the Oracle client used by Bizagi.

 

 

note_pin

It is required that the BizagiAdmon user has already been created in the given Oracle database instance. This is so, because a Bizagi project creation requires that the BizagiAdmon user is previously created.

 

 

Recommendations

It is highly recommended that an experienced user (i.e, a DBA or a person having an advanced knowledge on Oracle), carries out the export and import  commands. This is so, due to 3 main reasons:

 

1. The export and import utilities are run in a DOS command with use of command line parameters, which are familiar to a DBA.

You may choose to use a parameters file if considered useful.

Note that there is more than one way to use Export or Import capabilities, such as the use of RMAN, however this article illustrates the way as recommended by Bizagi. You may use RMAN as long as you are an experienced user with the capability of guaranteeing that the complete information will be considered for backup and restore purposes.

 

2. Managing an Oracle database of a Bizagi project will require at some point to have at hand, authorized credentials to connect as a user with system privileges (for example, using the BizagiAdmon user).

 

3. After performing both the export or import of an Oracle database, it is imperative to review the execution logs, so that any possible errors and warnings thrown in the export and import are handled and solved, or verified as "OK".

For example regarding errors, an issue that can show up and that needs immediate resolution, is when a tablespace in Oracle runs out of space (when these cannot be extended) in an import. This type of error will not stop the import, but can leave the information incomplete (without integrity).

For example, warnings which may be ignored are those stating:

ORA-31684: Object type USER:"%USER_NAME%" already exists

And on the other hand, some warnings which require resolution actions are those stating:

ORA-39082: Object type ALTER_PROCEDURE:"%PROCEDURE_NAME%" created with compilation warnings

 

Restoring a database backup (using datapump impdp)

The Data Pump utility provides a mechanism for transferring data objects between Oracle databases.

We will illustrate how to use the Datapump import utility to restore all the information in a Bizagi project from a .dmp export file, meeting one of the 2 following conditions:

 

This .dmp file was previously created through the DataPump export (manually using the expdp command).

This .dmp file was created by Bizagi. This is done automatically as a previous step when launching a project upgrade (to a newer version of Bizagi) or a deployment.

 

Backup files created by Bizagi automatically will be found at the "backup path" specified when configuring an Oracle instance to work with Bizagi (prerequisite of Bizagi project creation in Oracle databases).

 

note_pin

Take into account that the Datapump import (impdp command) presented in this section will not restore dmp backup files created through the traditional export utility (exp command). Such dmp backup files not considered by this approach are those manually created when using the exp command. Importing a dmp file created with the traditional export utility, needs to be done with the traditional import utility (imp command) described in the section above.

 

To use Oracle’s DataPump import utility for a Bizagi project, the following steps are carried out:

 

1. Ensuring there are no active connections

In the DataPump import operation carried out to restore information, it is necessary that the schema user is not in use (with no active connections).

Take into account, that Bizagi Scheduler's service will certainly have active connections if it is in a started status. If this is so, you will need to first stop the Scheduler service.

 

 

note_pin

You need to take proper cautions when ensuring that there are no active connections. This means that you may check for active connections by using BizagiAdmon, through this command:

 

SELECT s.sid, s.serial#, s.username FROM   gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND' and s.username = '[your_user_schema]'

 

And, you may as well force to kill active connections (by using the command below), but under your responsibility and controlling which specific sessions you are ending. Additionally, you should validate and guarantee that there are no users working at that moment in the implied Bizagi project (user schema).

 

ALTER SYSTEM DISCONNECT SESSION '[sid],[serial#]' IMMEDIATE;

 

2. Deleting the schema user

When restoring into an existing Bizagi project, the current schema user representing this project is deleted.

 

To do this, first connect to your Oracle instance with the BizagiAdmon user by using a sqlplus:

 

sqlplus BizagiAdmon/%BIZAGIADMON_USER_PASSWORD%@%DATABASE_SERVER%:%SERVICE_PORT%/%SERVICE_NAME%

 

 

In this sqlplus command, consider that:

 

%BIZAGIADMON_USER_PASSWORD% is the password for the BizagiAdmon user.

%DATABASE_SERVER% is the name of the Oracle database server.

%SERVICE_PORT% is the port number in which the Oracle database services are listening.

%SERVICE_NAME% is the alias to your database instance.

 

Oracle_import00

 

Then, execute the following drop user command (with cascade):

 

drop user %SCHEMA_USER_TO_IMPORT% cascade;

 

Oracle_import01

 

Notice that %SCHEMA_USER_TO_IMPORT% is the name of the Bizagi project.

 

 

3. Creating again the schema user

The schema user to restore the Bizagi project is created again (as blank) through a Bizagi stored procedure.

 

To do this, in the same session (the already connected sqlplus window), execute Bizagi's "spBA_ORA_CreateAppUser" stored procedure to create the project user:

 

exec spBA_ORA_CreateAppUser('%SCHEMA_USER_TO_IMPORT%','%SCHEMA_USER_PASSWORD%');

 

 

Oracle_import01_createUser

 

 

4. Looking up the backup directory

To make use of the DataPump import, you will need to specify as a parameter the directory in which the .dmp backup is located.

 

Therefore, you may look up the created directories for your database instance in which you will also find Bizagi's backup path:

 

Oracle_importdp_00

 

If you wish to manually create a different directory to use another physical path for your backup and log, you may do so by running the sqlplus lines as shown below:

 

Oracle_importdp_01

 

Once you have at hand the name of the directory you will use, log off (disconnect) from your Oracle instance’s BizagiAdmon session.

 

5. Opening a command prompt

A command prompt is used to execute the import commands.

 

To do this, launch a DOS command prompt:

 

Oracle_export00

 

Then, browse to your Oracle client home path, and into its "bin" folder:

 

Oracle_export01

 

 

6. Using the DataPump import utility

The DataPump import execution is done with parameters specification. This parameters are completely different to those used by the traditional import utility.

 

To do this, input the following command line from the bin folder location:

 

impdp USERID=BizAgiAdmon/%SCHEMA_USER_PASSWORD% SCHEMAS=%SCHEMA_USER_TO_IMPORT% DIRECTORY=%BIZAGI_BACKUP_PATH% DUMPFILE=%EXPORT_FILE% LOGFILE=%IMPORT_LOG%

 

 

In this command line, consider that:

%SCHEMA_USER_PASSWORD% is the password for the user with system rights (BizAgiAdmon).

%SCHEMA_USER_TO_IMPORT% is the name of the Bizagi project.

%BIZAGI_BACKUP_PATH% should be "BizAgiBackupPath" by default if no customization was done, according to step #4 (this parameter is the name for the directory on which the backup is located).

%EXPORT_FILE% is the path and filename in which the exported information was saved (the .dmp created when running the export).

%IMPORT_LOG% is the path and filename in which the import log will be recorded. This file commonly uses the .log file extension.

 

 

Notice that this is executed by connecting as the schema user whose objects (Bizagi project) will be restored (the same user and password as specified in step #3).

 

 

Oracle_importdp_02

 

 

For more information about Oracle's DataPump import and export utility (available from Oracle 10g version databases), refer to external links from the source: http://www.orafaq.com/wiki/Data_Pump.

 

 

7. Reviewing the DataPump import log

After the DataPump import execution, it is necessary to check for and solve any possible unexpected errors (no errors should  be shown when performing an import in Bizagi).

 

To do this, go through the log recorded for this operation once the import has finished.

 

This log will be located by the path and filename specified as "%IMPORT_LOG%".

 

The import should finish correctly without warnings and having successfully enabled Bizagi's constraints.

 

note_pin

Take into account that the import utility will use the character set defined for your Oracle client.

Therefore, and as mentioned at the prerequisites section, this character set should be the same one (or a compatible one) to that one defined for the database server. In addition to this, the character set must also be the same one or a compatible one to that one involved when using the export utility.

 

After an import, it is also recommended to run the following query (with the BizagiAdmon user) to ensure that there are records returned by it which point to errors/issues:

SELECT * FROM ALL_OBJECTS WHERE OWNER = '%USER_NAME%' AND STATUS <> 'VALID';