Creating required databases in Oracle

Before you install, create Oracle databases for IBM Business Automation Workflow, Workstream Services, or both, as well as for Application Engine, IBM FileNet® Content Manager, and IBM Business Automation Navigator.

About this task

Restriction: You cannot use the same database user for IBM Business Automation Workflow and Workstream Services, or for other components, such as Business Automation Studio. You can use a shared database but you must use a different database user for each component.
Tip: If you need to know which database versions are supported for your installation, use this IBM Support page to generate a software compatibility report.

For more details of the database privileges that are required to run Business Automation Workflow, see the Process Server component in Oracle database privileges. The other components do not apply.

Procedure

  1. Prepare the Oracle database for Business Automation Workflow.
    1. Create a user to represent the schema, and grant the user privileges to create resources.
      For example, the user could be named BAW1USER.
      -- 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@;
      
      -- Note:
      -- 1. @DB_DIR@ is a folder in the PV.
      -- 2. You must specify the DATAFILE or TEMPFILE clause unless you have enabled Oracle Managed Files by setting a value for the DB_CREATE_FILE_DEST initialization parameter. 
      CREATE TABLESPACE @BAWDB_TBLSPACE@
           DATAFILE '@DB_DIR@/@DB_NAME@/@BAWDB_TBLSPACE@.dbf' SIZE 200M REUSE
           AUTOEXTEND ON NEXT 20M
           EXTENT MANAGEMENT LOCAL
           SEGMENT SPACE MANAGEMENT AUTO
           ONLINE
           PERMANENT
       ;
      CREATE TEMPORARY TABLESPACE @BAWDB_TBLSPACE@_TEMP
           TEMPFILE '@DB_DIR@/@DB_NAME@/@BAWDB_TBLSPACE@_TEMP.dbf' SIZE 200M REUSE
           AUTOEXTEND ON NEXT 20M
           EXTENT MANAGEMENT LOCAL
      ;
      
      ALTER USER @DB_USER@ QUOTA UNLIMITED ON @BAWDB_TBLSPACE@;
       
      ALTER USER @DB_USER@
           DEFAULT TABLESPACE @BAWDB_TBLSPACE@
           TEMPORARY TABLESPACE @BAWDB_TBLSPACE@_TEMP;
      
      -- grant privileges to create database objects:
      grant  CREATE TABLE to @DB_USER@;
      grant  CREATE PROCEDURE to @DB_USER@;
      grant  CREATE SEQUENCE 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, uncomment 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@;

      To modify the @DB_USER@ and @DB_PASSWD@ in the Business Automation Workflow database, you must specify the database user and password in the Business Automation Workflow Server database secret.

    2. Optional: If you want to create a secret to ensure that all communications between Business Automation Workflow and Oracle are encrypted, follow these steps:
      1. Import the database certified authority (CA) certificate to Business Automation Workflow and create a secret to store the certificate:
        oc create secret generic <baw_instance1-oracle_ssl_secret> --from-file=tls.crt=<path_to_oracle_certificate_file>
        Where path_to_oracle_certificate_file is the path to the Oracle database certificate file. Do not modify the secret key name tsl.crt from the --from-file=tls.crt= part of the command.
      2. Add the secret to the database configuration in the baw_configuration section of the custom resource file. For example, the name of the secret could be baw_instance1-oracle_ssl_secret.
          baw_configuration:
          - name: instance1
            ...
            database:
              enable_ssl: true
              db_cert_secret_name: "baw-instance1-oracle-ssl-secret"
              ...
  2. Prepare the Oracle database for Workstream Services.
    1. Create a user to represent the schema, and grant the user privileges to create resources. For example, the user could be named BAW2USER.
      -- 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@;
      
      -- Note:
      -- 1. @DB_DIR@ is a folder in the PV.
      -- 2. You must specify the DATAFILE or TEMPFILE clause unless you have enabled Oracle Managed Files by setting a value for the DB_CREATE_FILE_DEST initialization parameter. 
      CREATE TABLESPACE @BAWDB_TBLSPACE@
           DATAFILE '@DB_DIR@/@DB_NAME@/@BAWDB_TBLSPACE@.dbf' SIZE 200M REUSE
           AUTOEXTEND ON NEXT 20M
           EXTENT MANAGEMENT LOCAL
           SEGMENT SPACE MANAGEMENT AUTO
           ONLINE
           PERMANENT
       ;
      CREATE TEMPORARY TABLESPACE @BAWDB_TBLSPACE@_TEMP
           TEMPFILE '@DB_DIR@/@DB_NAME@/@BAWDB_TBLSPACE@_TEMP.dbf' SIZE 200M REUSE
           AUTOEXTEND ON NEXT 20M
           EXTENT MANAGEMENT LOCAL
      ;
      
      ALTER USER @DB_USER@ QUOTA UNLIMITED ON @BAWDB_TBLSPACE@;
       
      ALTER USER @DB_USER@
           DEFAULT TABLESPACE @BAWDB_TBLSPACE@
           TEMPORARY TABLESPACE @BAWDB_TBLSPACE@_TEMP;
      
      -- grant privileges to create database objects:
      grant  CREATE TABLE to @DB_USER@;
      grant  CREATE PROCEDURE to @DB_USER@;
      grant  CREATE SEQUENCE 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, uncomment 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@;

      To modify the @DB_USER@ and @DB_PASSWD@ in the Workstream Services database, you must specify the database user and password in the Workstream Services Server database secret.

    2. Optional: If you want to create a secret to ensure that all communication between Workstream Services and Oracle are encrypted, follow these steps:
      1. Import the database certified authority (CA) certificate to Workstream Services and create a secret to store the certificate:
        oc create secret generic <baw_instance2-oracle_ssl_secret> --from-file=tls.crt=<path_to_oracle_certificate_file>
        Where path_to_oracle_certificate_file is the path to the Oracle database certificate file. Do not modify the secret key name tsl.crt from the --from-file=tls.crt= part of the command.
      2. Add the secret to the database configuration in the baw_configuration section of the custom resource file. For example, the name of the secret could be baw_instance2-oracle_ssl_secret.
          baw_configuration:
          - name: instance2
            ...
            database:
              enable_ssl: true
              db_cert_secret_name: "baw-instance1-oracle-ss2-secret"
              ...
  3. Optional: To use your own JDBC driver, package your JDBC files into a compressed file and use the sc_drivers_url configuration parameter to download them from an accessible web server.
  4. To create the database for Application Engine, follow the instructions in Creating an Oracle database.
  5. To create the databases for IBM FileNet Content Manager, create a database for the Content Platform Engine global configuration database (GCD) and databases for the content stores. Follow the instructions in Preparing the databases.

    Business Automation Workflow needs four different object stores (DOCS, TOS, DOS, and AEOS). If you are installing the Workflow capability, you must run the instructions to create the Oracle table spaces four times. If you are installing only the Workstreams capability, you need two object stores (DOCS and AEOS).

  6. To create the database for Business Automation Navigator, follow the instructions in Creating the databases without running the provided scripts.

What to do next

To protect the configuration data you're going to enter, see Creating secrets to protect sensitive configuration data.