Setting up an external Oracle database for OpenPages

Before you begin

Oracle RAC

IBM OpenPages is Oracle RAC tolerant but not Oracle RAC aware. This means that OpenPages will work within an Oracle RAC environment, but OpenPages does not support any of the Oracle RAC high availability features. If a node failure occurs, then any in-process transactions will fail, and you will need to rerun them. This also means that the OpenPages product documentation is primarily geared toward single instance Oracle deployments. Further, all internal testing is performed with a single Oracle instance. You might need to make configuration changes to use OpenPages within an Oracle RAC environment, and it is up to you to determine and implement those changes.

Follow the checklist to set up the Oracle database server: Checklist for setting up the Oracle database server for IBM OpenPages

Configure SSL support for database connections. For configuring TLS on Oracle, see Configure Transport Layer Security on the Server

Procedure

Complete the following tasks to set up the external database:

1. Preparing the database
2. Preparing the files
3. Running the steps that require DBA privileges
4. Preparing to run the non-DBA database scripts
5. Restoring the OpenPages database schemas (Oracle)
6. Retrieving the certificate from the database server
7. Setting up a vault to store the database credentials
8. Creating a secret in OpenShift that references the database secrets
9. Verifying the installation

Preparing the database

1. Determine the version number of the external database package you need to download based on the version of IBM Software Hub that you are using. Use the following list for guidance.
   • 5.2.x 9.1.0.1-82
2. Download the external database setup files and transfer them to the database server.

   For example, you can use the following commands to get the files from the IBM Entitled Registry:

   The following command extracts the OpenPages database backup file from the image:

   docker cp $(docker create --rm cp.icr.io/cp/openpages-cpd-provisioner-oracle:<db_package_version>):/app-root/backup .

   Replace <db_package_version> with the database package version number you chose in step 1. For example, the command will be the following if you're using IBM Software Hub version 5.2:

   docker cp $(docker create --rm cp.icr.io/cp/openpages-cpd-provisioner-oracle:9.1.0.1-82):/app-root/backup .

   The following command extracts the setup scripts from the image:

   docker cp $(docker create --rm cp.icr.io/cp/openpages-cpd-provisioner-oracle:<db_package_version>):/opt/ibm/OpenPages/ORACLE/

   Note: The image name (cp.icr.io/cp/openpages-cpd-provisioner-oracle:<db_package_version>) might be different if you mirrored the images to a local container registry.

Preparing the files

The sql-wrapper.sql file contains information that is used by the database scripts. You need to enter the values for your environment, such as the name of the database instance for OpenPages.

When you install OpenPages, use the same values that you set in the sql-wrapper.sql file.

1. Log on to the Oracle database server computer as a user with administrative privileges.
2. Go to the $oracle-dir/ORACLE/<version>/INSTALL_SCRIPTS directory.
3. Verify that you have write permission on the sql-wrapper.sql file. If not, change the permission on the file by using the chmod command.
4. Edit the sql-wrapper.sql file.
   Restriction: Change only the parameters that are described in this step.

   These parameters are used by the scripts that your DBA will run.

   Table 1. Parameters in the sql-wrapper.sql file for Oracle databases

   Property	Description
   opx_datafile_storage_dir	Defines the physical locations of the datafiles that are associated with the tablespaces that are created. This should be set to a value that is appropriate for your environment
   opx_dflt_sid	The TNS alias of the Oracle database for OpenPages.
   opx_db_owner	The database owner, also the name of the schema
   opx_op_admin_name	The OpenPages administrator user name
   opx_op_admin_pwd	The OpenPages administrator password
   opx_dflt_stor_srv_root	The path to the OpenPages storage directory. Example: define opx_dflt_stor_srv_root='/home/opuser/ OP/OpenPages/openpages-storage'

Running the steps that require DBA privileges

You can run a script to do the database schema creation steps that require DBA privileges. Do this procedure if you do not want to enter DBA credentials when you install IBM OpenPages or if your organization's security policies require the separation of DBA and non-DBA tasks.

Run the following script: op-database-dba-install.sh|.bat. The script uses the properties that are defined in the sql-wrapper.sql file.

1. Log on to the Oracle database server computer as the Oracle database administrator (DBA).
2. Locate the scripts that are required.

   If you are a database administrator, get the scripts from your OpenPages team.

   Or, you can get the scripts from the /OP_<version>_Main/OP_<version>_Configuration/Database/ORACLE/INSTALL_SCRIPTS directory.

3. Verify that you have execute permission on the scripts.
4. Open the sql-wrapper.sql file. Verify that the values are suitable for your environment.
   • Enter the DBA user name in the opx_oracle_dba_user parameter. The user must be DBA that does not have SYSDBA privileges.
     For example:

     define opx_oracle_dba_user='DBA'

   • If you want to customize the table space names, see Table space names and other customizations.
   • If you want to use custom locations for the table space data files, see Customize table space data file locations.
   • If you use Oracle Automatic Storage Management (ASM), see Changing the database script when you use Oracle ASM.
   • If you want to use Oracle TDE, see Oracle Transparent Data Encryption (TDE) for fresh installations.
   • Set the flag_install_oracle_text parameter to N.

     define flag_install_oracle_text='N'

     You can enable Oracle Text after you install OpenPages. See Enabling Oracle Text.

   Note: Do not modify the parameters in the section that is used only for upgrades.
5. Run the op-database-dba-install.sh|.bat script from the command line.
   • On Windows:

     op-database-dba-install.bat "<dba_password>" "<op_schema_owner_password>"

   • On Linux®:

     ./op-database-dba-install.sh '<dba_password>' '<op_schema_owner_password>'

   Note: Quotation marks are required around a password only if the password contains special characters. See Special characters in passwords.
6. Verify that the return code is 0, indicating success.

   You can also check the log file, op-database-dba-install-<timestamp>.log.

What to do next: You can use the OpenPages installation program to complete the database setup. Or you can complete the database setup manually by using scripts.

Preparing to run the non-DBA database scripts

Edit the sql-wrapper.sql file to specify the values for your environment.

The sql-wrapper.sql file contains information that is used by the database scripts. You need to enter the values for your environment, such as the name of the database instance for OpenPages.

Restoring the OpenPages database schemas (Oracle)

If the schema names or table space names are different in the source and target environments, you must remap them during the import. See Remap schema and table space names (Oracle).

1. Log on to a server that has access to the database server and has SQL*Plus.
2. Set the NLS_LANG environment variable.

   Windows

   1. In the Windows search box, type environment variables, and then click Edit system environment variables.
   2. On the Advanced tab, click Environment variables.
   3. In the System Variables pane, click New.
   4. Add the NLS_LANG variable. For example: NLS_LANG=AMERICAN_AMERICA.AL32UTF8
   5. Click OK twice to exit.

      Alternatively, you can set the environment variable globally. See Microsoft Windows Server documentation.

   Linux

   1. Open a shell window.
   2. Open the user profile (for example, .profile) that is in the home directory of the user who is currently logged in.
   3. Set the NLS_LANG variable if it's missing from the file.
      For example:

      export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

   4. Save the file.
   5. Refresh the profile.

      For example, open a shell and run the following command:

      . /home/oracle/.bash_profile

3. Import the OpenPages database schema.
   Note: The Oracle Data Pump command IMPDP is used because the IMP command is not supported.
   For more information about Oracle Data Pump, see the IBM OpenPages Administrator's Guide.
   Run the following command to import the OpenPages database:

   impdp <op_db_user>/\"<op_db_password>\"@<SID> DIRECTORY=OP_DATAPUMP_DIRECTORY DUMPFILE=<openpages_dump_file> LOGFILE=openpages_import.log exclude=statistics

   Parameters and their descriptions: Importing the OpenPages database schema

   Parameter	Description
   <op_db_user>	The user name for accessing the OpenPages database.
   <op_db_password>	The password for accessing the OpenPages database.
   <SID>	The Oracle System Identifier (for example, OP). If you are using a pluggable database, use the service name of the pluggable database, not the container database.
   <openpages_dump_file>	The .dmp file name of the backed-up OpenPages database schema: openpage_<timestamp>.dmp. Important: Do not enter a path. Enter only the file name.
   DIRECTORY	Important: Do not enter an explicit path when you specify the DIRECTORY parameter. Use OP_DATAPUMP_DIRECTORY only.

If the import fails, review the log files carefully. A failure at the beginning of the process might cause a number of cascading failures that are a symptom of the root issue. After the import completes successfully, upgrade the OpenPages database.

Retrieving the certificate from the database server

If you are using your own certificate, get it from the database server. The certificate must be Root CA. You need the certificate when you create the database secret.

openssl s_client -servername <db_server> -connect <db_server>:<db_port> </dev/null 2>/dev/null | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > opdb_cert.pem

Replace db_port with the SSL port number of the Oracle server.

Setting up a vault to store the database credentials

The database credentials must be stored in a vault. You can use the vault that is provided in IBM Software Hub or you can use an external vault. For more information about vaults, see Managing secrets and vaults.

Creating a secret in OpenShift that references the database secrets

Create a secret in OpenShift®. The secret references your vault and the database secrets that are defined in IBM Software Hub.

If you're using a tethered project for OpenPages, create the secret in the tethered project.

OpenPages uses the secret to connect to the database. The following example shows the required format. This example creates a secret that is called openpages-external-db-info. This example references secrets in the IBM Software Hub vault.

kind: Secret
apiVersion: v1
metadata:
  name: openpages-external-db-info
  namespace: <openpages_namespace>
  labels:
    app.kubernetes.io/name: openpages
    app.kubernetes.io/version: "9.001.0"
    app.kubernetes.io/managed-by: ibm-openpages-cpd-operator
stringData:
  host: <db_server_url>
  sslport: "1521"
  alias: extdb
  vault: <vault_name>
  secretowner: <created_by>
  usercred: "<db_user_secret>"
  dbacred: "<dba_secret>"
  schemaname: "<schemaname>"
  opdb-cert.pem: |-
    -----BEGIN CERTIFICATE-----
    <SSL cert>
    -----END CERTIFICATE-----
type: Opaque

oc apply -f openpages-external-db-info

The DBA and database user credentials are ready to use.

When you provision OpenPages, you need to provide the name of the Kubernetes secret. In this example, the name is openpages-external-db-info.

Add the certificate you retrieved earlier to the OpenShift secret.

Verifying the installation

Review the log file, setup-extdb.<date_time_stamp>.log for any errors.

sqlplus openpage/<op_db_password>@<SID>