Setting up an external AWS RDS Oracle database for OpenPages

• Oracle Enterprise Edition (EE) 19c (19.3 and later 19.x)
• Oracle Standard Edition 2 (SE2) 19c (19.3 and later 19.x)

1. Preparing the database
2. Preparing the files
3. Configuring the RDS parameter group
4. Running the steps that require DBA privileges
5. Preparing to run the non-DAB database scripts
6. Restoring the OpenPages database schemas (Oracle)
   1. Importing the OpenPages database schema
   2. Copying the Dump File from S3 to RDS
   3. Importing OpenPages database with impdp command
7. Retrieving the certificate from the database server
8. Setting up a vault to store the database credentials
9. Creating a secret in OpenShift that references the database secrets
10. Verifying the installation

• The Oracle RDS database is provisioned
• The sql-wrapper.sql is configured for your environment
• Run the following script: op-database-dba-install.sh|.bat. The script uses the properties that are defined in the sql-wrapper.sql file

• You can specify a custom name for the OpenPages schema
• If you are a database administrator, get the scripts from your OpenPages team.
• Or, you can get the scripts from the $oracle-dir/ORACLE/INSTALL_SCRIPTS directory.

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

./op-validate-dba-install.sh '<op_schema_owner_password>'

Importing the OpenPages database schema

Create an S3 bucket and move the openpages_dump_file to the S3 bucket. Ensure that the AWS CLI is configured with the appropriate credentials (aws configure).

aws s3 cp /home/ec2-user/cpd-or/backup/opdb-oracle.dmp s3://cp4d-oracle-rds-import-bucket

Note: The opdb-oracle.dmp backup file is obtained from the docker command in step 3 under Preparing the database:

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

Check IAM role association:

  aws rds describe-db-instances \
   --db-instance-identifier <your-db-id> \
   --query "DBInstances[0].AssociatedRoles"

Sample output:

[
    {
        "RoleArn": "arn:aws:iam::230957186347:role/rds-s3-import-role",
        "FeatureName": "S3_INTEGRATION",
        "Status": "ACTIVE"
    }
]

If S3_INTEGRATION is Not Active, then do the following:

1. Create an IAM Policy. Name the policy as RDS-S3ImportAccessPolicy. The following is the policy in JSON:

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Effect": "Allow",
               "Action": [
                   "s3:GetObject",
                   "s3:ListBucket"
               ],
               "Resource": [
                   "arn:aws:s3:::cp4d-oracle-rds-import-bucket",
                   "arn:aws:s3:::cp4d-oracle-rds-import-bucket/*"
               ]
           }
       ]
   }
   

2. Create an IAM Role. Name the role as rds-s3-import-role. Attach the RDS-S3ImportAccessPolicy policy. The following is the Trust Relationship JSON:

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Effect": "Allow",
               "Principal": {
                   "Service": "rds.amazonaws.com"
               },
               "Action": "sts:AssumeRole"
           }
       ]
   }
   

3. Create an Option Group for S3 Integration. Name the option group as cpd-rds-s3-og. Add the option S3_INTEGRATION.
4. Bind the Role to RDS:

   aws rds add-role-to-db-instance \
      --db-instance-identifier cpd-rds-oracle \
      --role-arn arn:aws:iam::230957186347:role/rds-s3-import-role \
      --feature-name S3_INTEGRATION

5. Update the Option Group in RDS. Associate the newly created option group with your RDS instance.

Copying the Dump File from S3 to RDS

On your EC2 instance, create a file named s3_download.sql with the following contents:

set pages 0;
set head off;
set feedback off;
SET VERIFY OFF
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;

SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
      p_bucket_name    => '&1', 
      p_s3_prefix      => '&2', 
      p_directory_name => 'DATA_PUMP_DIR'
   ) AS TASK_ID 
FROM DUAL;

exit;

Run the following SQLPlus command:

sqlplus -s admin/"<admin_password>"@cpd-rds-oracle.<instance_id>.amazonaws.com:1521/ORCL
      @s3_download.sql  cp4d-oracle-rds-import-bucket opdb-oracle.dmp

Check the S3 Download Task Status in Oracle RDS:

 SELECT text 
 FROM table(rdsadmin.rds_file_util.read_text_file(
   'BDUMP', 
   'dbtask-<ID_from above command>.log'
 ));

Grant access to an OpenPages user to DATA_PUMP_DIR:

 GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO < opx_db_owner>;
 GRANT EXECUTE ON rdsadmin.rds_file_util TO < opx_db_owner>;

 SELECT * 
 FROM table(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'));

 SELECT directory_name, directory_path 
 FROM dba_directories 
 WHERE directory_name = 'DATA_PUMP_DIR';

Importing OpenPages database with impdp command

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 by opening a shell window and running the following command:

   . /home/oracle/.bash_profile

6. Import the OpenPages database schema by running the following command:

   impdp <opx_db_owner >/"< op_schema_owner_password>"@<rds_instance_id>.<location>.rds.amazonaws.com:1521/ORCL \ 
     DIRECTORY=DATA_PUMP_DIR \ 
     DUMPFILE=<Dump_file_name>\ 
     SCHEMAS="OPENPAGE" \ 
     REMAP_SCHEMA=OPENPAGE:< opx_db_owner> \ 
     TRANSFORM=OID:N \  
     EXCLUDE=INDEX_STATISTICS \ 
     EXCLUDE=JOB \ 
     EXCLUDE=USER
   
   

   Execution should be completed with the output `Job "OPENPAGE_DEV"."SYS_IMPORT_SCHEMA_01" successfully completed`.

echo | openssl s_client -showcerts \
-connect <rds_instance_id>.<location>.rds.amazonaws.com:2484 \
-servername <rds_instance_id>.<location>.rds.amazonaws.com

• Root CA

  Subject CN: Amazon RDS
  
  Issuer CN: Amazon RDS us-west-1 Root CA 
  
  This is self-signed → Root CA for Amazon RDS in the us-west-1 region.
  
  Purpose: Trust anchor that verifies the intermediate CA.

• Intermediate CA

  Subject CN: Amazon RDS us-west-1 Subordinate CA RSA2048 G1.A.4
  
  Issuer CN: Amazon RDS us-west-1 Root CA 
  
  This is signed by the root CA → Intermediate CA.
  
  Purpose: Signs the actual RDS instance certificate.

• The DBA and database user secrets must be created by the same IBM Software Hub user.
• The DBA and database user credentials must be stored in separate secrets within the same vault.
• Each secret must be of the type username and password in the vault.
• The secrets must either be owned by the user who provisions OpenPages instances or shared with the owner of the OpenPages instances.
• OpenPages does not support storing the SSL certificate in a vault.

1. If you want to use an external vault, set up the vault. For more information, see Adding integrations to external vaults.

   In the vault, add two secrets: one for the DBA user credentials and another for the database user credentials. Make a note of the secret details. For example, if you're using HashiCorp, note the secret names and paths.

2. In IBM Software Hub, set up references to the DBA user secret and the database user secret.
   • If you're using an external vault, see Adding references to secrets in external vaults
   • If you're using the IBM Software Hub vault, see Adding secrets to the internal vault
   For each secret, use the following settings:

   1. For the information type, choose Username and password.
   2. Enter the database credentials.
   3. If the user who will provision OpenPages is different from the user who is creating the secret, share the secret with the user who provisions OpenPages instances.
3. In IBM Software Hub, click Administration > Configurations, click the Vaults and secrets tab, and then click Secrets. Make a note of the following information for the DBA user secret and the database user secret:
   • Name is the name of the secret.
   • Vault name
   • Created by is the owner of the secret.

kind: Secret
apiVersion: v1
metadata:
  name: openpages-external-db-info
  namespace: <cpd_namespace>
  labels:
    app.kubernetes.io/name: openpages
    app.kubernetes.io/version: "<version>"
    app.kubernetes.io/managed-by: ibm-openpages-cpd-operator
stringData:
  host: <rds_endpoint_url>
  port: "1521"                  # Standard Oracle listener port
  sslport: "2484"               # SSL-enabled port for RDS
  alias: ORCL                   
  vault: <vault_name>
  secretowner: <created_by>
  usercred: "<db_user_secret>"  
  dbacred: "<dba_secret>"     
  sysdbacred: "<sysdba_secret>"
  schemaname: "<schema_name>"
  opdb-cert.pem: |-
    -----BEGIN CERTIFICATE-----
     Root Certificate
    -----END CERTIFICATE-----
    -----BEGIN CERTIFICATE-----
     Intermediate Certificate
    -----END CERTIFICATE-----
type: Opaque

oc apply -f openpages-external-db-info

sqlplus openpage/<op_db_password>@<SID>