Creating users for Oracle databases

You must create the users for Oracle databases before you install IBM® Business Process Manager. Create the cell-scoped user, the deployment environment-level user, the Process Server user, and the Performance Data Warehouse user.

Note: If you are using a custom installation and configuration path, follow the instructions in the topic Creating users for Oracle databases in a network deployment environment on Windows.

Before you begin

Before you create any users for Oracle databases, see the topic Configuring XA transactions for Oracle in a network environment on Windows.

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.

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.

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.

Procedure

Use one of the following methods to create each database user that you require:

Create and run the createUser.sql file as described in the following substeps:

Save the following SQL statements into a file named createUser.sql:

-- create a new user
CREATE USER @DB_USER@ IDENTIFIED BY @DB_PASSWD@;

-- allow the user to connect to the database
grant connect to @DB_USER@;

-- provide quota on all tablespaces with BPM tables
grant unlimited tablespace to @DB_USER@;

-- grant privileges to create database objects:
grant resource to @DB_USER@;
grant create view to @DB_USER@;

-- grant access rights to resolve lock issues
grant execute on dbms_lock to @DB_USER@;

-- grant access rights to resolve XA related issues:
grant select on pending_trans$ to @DB_USER@;
grant select on dba_2pc_pending to @DB_USER@;
grant select on dba_pending_transactions to @DB_USER@;
-- If using Oracle 10.2.0.3 or lower JDBC driver, un-comment the following statement:
-- grant execute on dbms_system to @DB_USER@;
-- If not using Oracle 10.2.0.4 or higher JDBC driver, comment the following statement:
grant execute on dbms_xa to @DB_USER@;

For information about recovering Oracle database transactions, see the topic Configuring XA transactions for Oracle in a network environment on Windows.

In the SQL statements, replace @DB_USER@ with the user name that you want to use for the database and replace @DB_PASSWD@ with the password for that user.

Run the following command to create the database user:

sqlplus oracle_user_ID/oracle_password@db_name @createUser.sql

Run SQL statements in a command editor as described in the following substeps:

Copy the following SQL statements into a command editor:

-- create a new user
CREATE USER @DB_USER@ IDENTIFIED BY @DB_PASSWD@;

-- allow the user to connect to the database
grant connect to @DB_USER@;

-- provide quota on all tablespaces with BPM tables
grant unlimited tablespace to @DB_USER@;

-- grant privileges to create database objects:
grant resource to @DB_USER@;
grant create view to @DB_USER@;

-- grant access rights to resolve lock issues
grant execute on dbms_lock to @DB_USER@;

-- grant access rights to resolve XA related issues:
grant select on pending_trans$ to @DB_USER@;
grant select on dba_2pc_pending to @DB_USER@;
grant select on dba_pending_transactions to @DB_USER@;
-- If using Oracle 10.2.0.3 or lower JDBC driver, un-comment the following statement:
-- grant execute on dbms_system to @DB_USER@;
-- If not using Oracle 10.2.0.4 or higher JDBC driver, comment the following statement:
grant execute on dbms_xa to @DB_USER@;

In the SQL statements, replace @DB_USER@ with the user name that you want to use for the database and replace @DB_PASSWD@ with the password for that user.
Run the SQL statements in the command editor.