Creating users for Oracle databases

You can create the users for Oracle databases either before or after you create the profiles and the deployment environment. Create the cell-scoped user, the deployment environment-level user, the Process Server user, and the Performance Data Warehouse user. The Process Server user and the Performance Data Warehouse user are not needed for an Advanced-only deployment environment.

When you use the Deployment Environment wizard to create the deployment environment, you can use the Create Tables option to specify whether database tables are created when the deployment environment is created:

If the Create Tables option is selected, database tables are automatically created at the same time as the deployment environment. Therefore, the database users must exist before you run the Deployment Environment wizard.
If the Create Tables option is not selected, database table creation is deferred when you create the deployment environment. Therefore, you can create the database users either before or after you run the Deployment Environment wizard. You might find it useful to create the database users after running the wizard because you can use the set of populated scripts, which the wizard generates, to create the database users and database tables at a time that you choose.

Before you begin

Before you install your system in Oracle database, ensure that the minimum initial settings in the following table are met for IBM BPM database schema users:

Table 1. Minimum initial settings for BPM database schema users
Tuning item	Minimum memory for initial settings (MB)
Buffer cache	2048
Shared pool size	1024

Note: You might want to enlarge the memory for the preceding settings depending on the capacity of your BPM system. For more tuning details, consult your database administrator.

Recommendations for database tablespace settings:

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 new IBM BPM installations, create tablespaces with the AUTOALLOCATE option.

For migrations, 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.

You can use a single instance of Oracle for configuring IBM Business Process Manager. The Oracle instance must exist and be available for access. Consult the Oracle documentation to create an Oracle instance. If you use a single Oracle instance, make sure that you use different user IDs for the three different IBM Business Process Manager databases.

About this task

The default database names are BPMDB for the Process database, PDWDB for the Performance Data Warehouse database, and CMNDB for the Common database. In the case of an Advanced or Advanced-only deployment environment, the Common database has two parts: one is scoped to the cell and the other is scoped to the deployment environment. Both parts can be defined to use CMNDB (which is the default) or they can use separate databases.

For more information about creating databases, see the following sections:

Creating users for the databases before creating profiles or the deployment environment
Creating users for the databases after creating the profiles and the deployment environment

Creating users for the databases before creating profiles or the deployment environment

To create the users for the databases before you create the profiles or before you use the Deployment Environment wizard to create your deployment environment, you can use the createUser.sql template that is provided with your IBM Business Process Manager installation.

Procedure

Complete the following steps for each database user that you want to create:

Navigate to the BPM_HOME/BPM/dbscripts/Oracle/Create directory and make a copy of the createUser.sql file.
In the copied file, replace @DB_USER@ with the user name that you want to use for the database and replace @DB_PASSWD@ with the user password. Save the file.
Create the database user by running the following command on your local or remote database server:
```
sqlplus oracle_user_ID/oracle_password@db_name @createUser.sql
```

Creating users for the databases after creating the profiles and the deployment environment

After you create the profiles, you can use the Deployment Environment wizard to create the deployment environment and generate the database scripts. The scripts are populated with the configuration values that you specified in the wizard. You can use some of these scripts to create the users for the databases if you chose to defer the creation of the database tables.

Before you begin

You must have already used the Profile Management Tool, the BPMConfig command, or the manageprofiles utility to create and augment the profiles. You must also have used the Deployment Environment wizard to configure the deployment environment.

Procedure

On the computer where you created the deployment manager profile, navigate to one or more of the following default subdirectories where the SQL database scripts were generated:
- dmgr_profile_root/dbscripts/cell_name/Oracle/oracle_instance_name/cell_user
- dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/CMN_user
- dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/PS_user
- dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/PDW_user
These directories contain the createUser.sql scripts that you can use to create the users for the databases.

The number of subdirectories that are generated is dependent on the deployment environment type and the number of database users that were configured in the Deployment Environment wizard.
For each createUser.sql file that was generated, run the following command on your local or remote database server:
```
  sqlplus oracle_user_ID/oracle_password@oracle_instance_name @createUser.sql schema_user_password
```
Note: Although you can specify the schema user password shown in the command syntax, you can alternatively edit the createUser.sql script and replace the parameter &1 with the schema user password and then run the script without specifying any parameter.