Upgrading Oracle databases

For Oracle, initialize new databases and upgrade your existing schemas and data so that your databases work with the new version of IBM® Business Process Manager.

Figure 1. Sample environment after existing schemas and data are updated. The source environment is not running and the databases are not in use. The databases contain updated schemas and data. The target is not running but contains a deployment environment.

The details of the diagram are provided in the figure caption.

Run the DBUpgrade command to modify some of your existing data for use with IBM Business Process Manager V8.5.6.

Before you begin

Ensure that you have shut down the source environment before you proceed with the migration.

Important: You must upgrade your Oracle database to a supported version. If your Oracle database is at 9i or 10g, upgrade it to 11g before migration.

Verify that the users that are configured to access your Oracle databases have the necessary privileges to upgrade the databases. The following database privileges are needed to modify existing Oracle database schemas and data for use with IBM Business Process Manager V8.5.6.

The CONNECT, RESOURCE, DBA_TABLES, and SELECT privileges are required on the database level.
The upgrade process accesses system views. Grant the SELECT privilege to the user who performs the upgrade. These privileges are already granted to the public group by default; it is not necessary to grant them again unless they were revoked.
```
all_indexes
all_tables
USER_TAB_COLUMNS
all_objects
all_constraints
all_tab_columns
```

Tip: If you use tablespaces with a UNIFORM SIZE less than 120K, create new tablespaces with the AUTOALLOCATE option and make it the default tablespace for IBM BPM database schema users. Otherwise, you will see an error similar to the following error when you upgrade the schema:

Executing upgrade step: Upgrade 8.5.0 schema to 8.5.5 for database ProcessServerDatabase.
Error executing SQL statement: ORA-60019: Creating initial extent of size 14 in tablespace of extent size 8

On Oracle, IBM Business Process Manager stores large objects (LOBs) with the SECUREFILE option. For SECUREFILE, it is recommended to use a tablespace with the AUTOALLOCATE option. If you use UNIFORM SIZE extents, ensure that the UNIFORM SIZE is big enough. Given the default block size of 8K, specify a UNIFORM SIZE of at least 120K. IBM BPM does not explicitly prescribe the tablespace options; it relies on the default Oracle settings (such as AUTOALLOCATE) to automatically manage extents.

For a list of supported database versions, refer to the system requirements.

IBM Business Process Manager Advanced system requirements

Procedure

For each deployment environment that you are creating, complete the following steps:

Copy the whole folder target_deployment_manager_profile/dbscripts/Upgrade/ to your database computer.
If you did not create a new messaging engine database and instead you plan to reuse your previous messaging engine database and schema, you must manually drop the existing messaging engine tables for each one.
Tip: The messaging engine table names use the SIB prefix.
On the database computer, upgrade all schemas. To see which schemas are upgraded, go to the directory where you copied the Upgrade folder and see the upgradeSchemaScriptsHelp_de_name.txt file.
Go to the directory where you copied the Upgrade folder and run the upgradeSchemaAll command. There is a different upgradeSchemaAll command for each deployment environment in the source.
```
upgradeSchemaAll_de_name.sh
```
You are prompted to enter the user name and password for each database connection. The command initializes the new database components and upgrades the schemas of all the existing databases. When you are asked if shared work items are enabled for Business Process Choreographer, refer to the note in the next step to see whether shared work items are enabled.
Alternatively, if you want to run the SQL scripts manually, use an SQL session and run the scripts in the sequence listed in the Upgrade_folder/upgradeSchemaScriptsHelp_de_name.txt file. The options are embedded in the SQL scripts. Additional options are not required.

Note: If your source environment is V7.0.0.3 or V7.0.0.4 or later minor versions, two versions of upgrade schema scripts are generated for the BPC database. If you create a new Business Process Choreographer configuration with a newly created database, shared work items are enabled and used automatically. If you migrated your Business Process Choreographer configuration, using shared work items is optional. If you have enabled the "Shared Work Items" feature that was introduced in V7.0.0.3, you must remove those generated scripts with "700" in the file name before you run upgradeSchema.bat. If that is not the case, you must remove the script with "7003" in the file name.
For detailed information about shared work items, refer to Shared work items.

You might see warning messages when you run the scripts to upgrade the Business Space database telling you that the result of a query is an empty table or that no row was found for FETCH, UPDATE, or DELETE. These messages can safely be ignored.

The result.log files are found in Upgrade_folder/cell_name or cell_name.de_name/database_type/database_name.schema_name.
Copy the sample migration.properties file and rename it to target_migration.properties. Update the file with the configuration information for the target environment. Check all the target properties and edit them if required, following the instructions in the sample file. The sample file is in install_root/util/migration/resources/migration.properties.
Ensure that all properties have been changed to the target (not source) environment. Ensure that the value of the target.config.property.file property is set to the full path of the configuration properties file that you used to create your target environment. You must also set the value of profile.name to the name of the new deployment manager profile.
To upgrade the topology data in the database to V8.5.6, run the DBUpgrade utility:
Important: Ensure that your deployment manager and all the managed nodes in the source environment have been stopped before running this utility.
```
install_root/bin/DBUpgrade.sh -propertiesFile target_migration_properties_file -backupFolder snapshot_folder
```
where:
- target_migration_properties_file is the full path to the migration properties file in which you specified the configuration information for the target environment.
- snapshot_folder is the directory that contains the information that was extracted from the source environment
For example:
```
install_root/bin/DBUpgrade.sh -propertiesFile /opt/BPM85/util/migration/resources/target_migration.properties -backupFolder /tmp/snapshot
```
The command displays each database upgrade action as it runs. After all the upgrades are finished, you see a message similar to the following message:
```
All upgrade steps have been completed successfully.
```
The log location is listed in the output. If there are errors or exceptions, they appear in the log.

What to do next

You might see warning messages similar to the following in the upgrade log: Couldn't load Resource META-INF*****. These messages can safely be ignored.