Oracle Export and Import

<< Click to Display Table of Contents >>

Navigation:  Bizagi Engine > Bizagi system administration > Maintenance and administration > Backing up Bizagi > Database backups >

Oracle Export and Import

Overview

As part of common maintenance and administration tasks, it is of critical importance to always be able to rely on backups for the possibility to restore your project to a previous state, should an unexpected event occur.

Restoring your project to a previous state when having a database backup is a really simple procedure for a Bizagi System, given that Bizagi is data-driven.

For more information, refer to Maintenance and administration.

 

Important notes

Backups in such types of tasks are mainly created as a contingency measure.

 

note_pin

Restoring a database backup should only be considered as a contingency measure to restore a given environment to a previous state, or to move an environment's database to a new location.

This means that a development environment backup should only be restored in the same development environment, and a production environment backup should only be restored in the same production environment.

To create environments, Bizagi offers its Deployment options.

 

When using an Oracle database instance as the database engine for a Bizagi project database, the backup-and-restore operation is done (and referred to) through Oracle's Export and Import utility.

If you are using a Microsoft SQL Server database as Bizagi's database instead, refer to SQL Server Backup and Restore.

 

note_pin

Keep in mind that case attachments are not stored within the backup itself.

 

 

Prerequisites

To use the export and import utilities in an Oracle database for a Bizagi project, it is required:

 

1. To have the Oracle client installed where the export and import will be carried out.

 

Notice that the installed client version will most likely differ according to the bit version (32-bit or 36-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. Take into account that Bizagi supports Oracle 10g R2 as the project’s database.

 

2. To have either the same character set, or a compatible set configured for your Oracle database instances (at the servers), as well as for the Oracle clients involved in the export and import.

 

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).

 

3. To have no more than 1 active Oracle home definition.

 

This means that 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.

 

 

Additional Considerations

If you are moving a Development project to a new server, and wish to keep your existing cases (Process instances), take into account that file attachments are not stored in the database but on Bizagi Engine server (by default), or a file server or ECM, if configured.

 

If this is the case and you are using Bizagi Engine server as the path for the cases' uploaded files, moving the attachment files would be necessary as well.

 

Also, it is highly recommended that an experienced user (having an advanced knowledge in Oracle) carries out the export and import utilities' commands (such as a DBA). 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 known to a DBA).

 

2. Managing an Oracle database of a Bizagi project will require at some point (for a specific instruction in the import), having at hand the credentials to connect as a user with 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, 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).

 

 

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.

 

 

Using DataPump Export (expdp)

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

We will illustrate how to use the Datapump export utility to backup all the information in a Bizagi project from into a dmp file.

 

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

 

1. Preparting the backup directory

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

 

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

 

Once connected, 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.

 

2. Opening a command prompt

A command prompt is used to execute the export 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

 

3. Using the DataPump export utility

The characteristics of the import operation are determined by the export parameters you specify.

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

 

expdp USERID=BizAgiAdmon/%SCHEMA_USER_PASSWORD% SCHEMAS=%SCHEMA_USER_TO_EXPORT% DIRECTORY=%BIZAGI_BACKUP_PATH% DUMPFILE=%EXPORT_FILE% LOGFILE=%EXPORT_LOG%

 

 

In this command line, consider that:

 

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

%SCHEMA_USER_TO_EXPORT% is the name of the Bizagi project.

%BIZAGI_BACKUP_PATH% should be "BizAgiBackupPath" by default if no customization was done, according to step #1 (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).

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

 

 

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.

 

 

4. Reviewing the DataPump export log

After the export execution it is necessary to check for and solve any possible unexpected errors.

 

To do this, go through the log recorded in the previous step once the export has finished.

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

 

note_pin

Take into account that the export 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 those involved when using the import utility.

 

 

 

Using DataPump Import (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 file name in which the exported information was saved (the .dmp created when running the export).

%IMPORT_LOG% is the path and file name 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 file name 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.

 

Click for more information about this option in the Management Console.