All product information in wiki.bizagi.com is only valid for Bizagi BPM Suite 9.1.X.
For newer Bizagi BPM Suite versions (10.X and up) please visit the User Guide.
 

Export and Import of a Bizagi database in Oracle

From Business Process Management, BPM and Workflow Automation Wiki | BizAgi BPMS

Jump to: navigation, search

<keywords content="keywords"> Oracle, import, export, dba, database backup, imp, exp, database restore, backup, restore, project upgrade, upgrade backup, failed deployment </keywords>

Applies to the ENTERPRISE EDITIONS

Contents

Export and Import of a Bizagi database in Oracle

Overview

Creating a backup of a Bizagi database, and having the possibility to eventually restore this backup, is a common requirement of the administrative tasks scheduled in a Bizagi project.

These backups are mainly created as a contingency measure, given that Bizagi is a data-driven BPM solution. By being a data-driven solution, Bizagi will provide the possibility for any of its project environments (Production, Test and Development) to be restored to a previous state (an application snapshot).

When using an Oracle database instance as the database engine for a Bizagi project database (applies for Bizagi Enterprise JEE and Bizagi Enterprise .Net editions), the backup and restore operations are done (and referred to) through Oracle's Export and Import utility.



Alert: Creating backups for an eventual restore (export/import) should only be considered as a contingency measure for a given environment. This approach is not adequate to deploy a project to a different environment (Bizagi offers for this a Deployment wizard to publish processes into Test and Production environments). Bizagi also supports a database configuration with fault tolerance clustering.


In this article, we will illustrate how to execute an export for Bizagi projects using Oracle (backing up the project database), and how to execute an import for these projects (restoring the project database). If you are using a Microsoft SQL Server database as Bizagi's database instead, refer to the Backup and Restore for SQL Server article.


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 bits 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 in its 9.1.x versions, support Oracle 10g R2 as the project’s database.

2. To have either the same character sets, or compatible ones 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 ones or compatible ones, 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

It is strongly 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 and with use of command line parameters (which are known for a dba).

2. Managing an Oracle database of a Bizagi project will require in some point (for a specific instruction in the import), having at hand the credentials to connect with an user with priviledges (for example, using the BizagiAdmon user).

3. After performing both the export or import of an Oracle database, it is completely required 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 an error shown when tablespaces run out of space (when these cannot be extended) in an import. These type of errors will not stop the import, but can leave the information incomplete (without integrity).



Note: It is assumed 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 (as part of the "configuring an Oracle instance to work with Bizagi" prerequisites.



Using Export (exp)

We will illustrate how to use the export utility to backup all the information in a Bizagi project, into a dmp file. This way, a file is created with all the information from a given "Oracle user schema".


What you need to do

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

1. Opening a command prompt

A command prompt is used to execute the export commands.

To do this, launch a DOS command prompt:


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


2. Using the export utility

The export execution is done with parameters especification.

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

export %SCHEMA_USER_TO_EXPORT%/%SCHEMA_USER_PASSWORD%@%SERVICE_NAME% OWNER=%SCHEMA_USER _TO_EXPORT% FILE=%EXPORT_FILE% LOG=%EXPORT_LOG% STATISTICS=none

In this command line, consider that:

  • %SCHEMA_USER_TO_EXPORT% is the name of the Bizagi project.
  • %SCHEMA_USER_PASSWORD% is the password for this schema user.
  • %SERVICE_NAME% is the alias to your database instance.
  • %EXPORT_FILE% is the path and filename in which the exported information will be saved. This file commonly uses the .dmp file extension.
  • %EXPORT_LOG% is the path and filename in which the export log will be recorded. This file commonly uses the .log file extension.



Notice that this is executed by connecting as the user from which its information will be backed-up, and that it is strictly required to specify STATISTICS=none.


3. Reviewing the export log

After the export execution it is necessary to check and solve for 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%".



Alert: Take into account that the export utility will use the character set defined for your Oracle client instance. 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 Import (imp)

We will illustrate how to use the import utility to restore all the information in a Bizagi project, from a previously created dmp export file through the export (exp command) presented in the section above.


Alert: Take into account that the import (imp command) presented in this section will not restore dmp backup files created through the Datapump export utility. Such dmp backup files not considered by this approach are those created when using the expdp command, or those created by Bizagi automatically previous to a deployment or a project upgrade. In the above cases, importing these files needs to be done with the impdp command described in a section below.


What you need to do

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

1. Ensuring there are no active connections

In the 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.


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.



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

drop user %SCHEMA_USER_TO_IMPORT% cascade;
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 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%');




Then, log off (disconnect) from your Oracle instance’s BizagiAdmon session.

4. Opening a command prompt

A command prompt is used to execute the import commands.

To do this, launch a DOS command prompt:


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


5. Using the import utility

The import execution is done with parameters especification.

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

import 
%SCHEMA_USER_TO_IMPORT%/%SCHEMA_USER_PASSWORD%@%SERVICE_NAME% FILE=%EXPORT_FILE% LOG=%IMPORT_LOG% STATISTICS=none FROMUSER=%SCHEMA_USER_TO_EXPORT% TOUSER=%SCHEMA_USER_TO_IMPORT%

In this command line, consider that:

  • %SCHEMA_USER_TO_EXPORT% is the name of the user involved when the export file was created.
  • %SCHEMA_USER_TO_IMPORT% is the name of the Bizagi project.
  • %SCHEMA_USER_PASSWORD% is the password for this schema user (Bizagi project).
  • %SERVICE_NAME% is the alias to your database instance.
  • %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 user to which its information will be restored (the same user and password as specified in step #3), and that it is strictly required including the STATISTICS=none parameter.




6. Reviewing the import log

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

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

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




Notice that the import should finish correctly without warnings and having successfully enabled Bizagi's constraints.


Alert: Take into account that the import utility will use the character set defined for your Oracle client instance. 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.



7. Checking metadata

To validate that existing objects and rows were properly imported by Oracle, it is recommended to run Bizagi's option to check its metadata.

To do this, open your restored project through Bizagi's Management Console, and run the "check metadata" option to validate the integrity of the information allocated in the restored database.

View information about the check metadata option.



Using DataPump Import (impdp)

We will illustrate how to use the Datapump import utility to restore all the information in a Bizagi project, from a dmp export file dmp 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).



Alert: 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.



What you need to do

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.


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.



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

drop user %SCHEMA_USER_TO_IMPORT% cascade;

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 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%');





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:




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:




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:




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




6. Using the DataPump import utility

The DataPump import execution is done with parameters especification. 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 %SCHEMA_USER_TO_IMPORT%/%SCHEMA_USER_PASSWORD%@%SERVICE_NAME% REMAP_SCHEMA=%SCHEMA_USER_TO_EXPORT%:%SCHEMA_USER_TO_IMPORT% DIRECTORY=%BIZAGI_BACKUP_PATH% DUMPFILE=%EXPORT_FILE% LOGFILE=%IMPORT_LOG%

In this command line, consider that:

  • %SCHEMA_USER_TO_EXPORT% is the name of the user involved when the export file was created.
  • %SCHEMA_USER_TO_IMPORT% is the name of the Bizagi project.
  • %SCHEMA_USER_PASSWORD% is the password for this schema user (Bizagi project).
  • %SERVICE_NAME% is the alias to your database instance.
  • %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 user to which its information will be restored (the same user and password as specified in step #3).




Further information about Oracle's DataPump import and export utility, available from Oracle 10g version databases, can be checked 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 and solve for any possible unexpected errors.

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

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


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


Alert: Take into account that the import utility will use the character set defined for your Oracle client instance. 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.



8. Checking metadata

To validate that existing objects and rows were properly imported by Oracle, it is recommended to run Bizagi's option to check its metadata.

To do this, open your restored project through Bizagi's Management Console, and run the "check metadata" option to validate the integrity of the information allocated in the restored database.

View information about the check metadata option.



Related Articles

Retrieved from "http://wiki.bizagi.com/en/index.php?title=Export_and_Import_of_a_Bizagi_database_in_Oracle"