Install Operational Reporting on Amazon RDS for Oracle 19c (non-CDB/PDB)

This topic describes how to install Operational Reporting on Amazon RDS for Oracle 19c (non-CDB/PDB).

Create an Amazon RDS DB instance for Oracle 19c (non-CDB/PDB)

You must create an Amazon RDS DB instance for Oracle 19c before installing the Operational Reporting.

Follow the guidelines below when creating a RDS DB instance for Oracle 19c:

  • When creating the RDS DB instance, do not use the automatically generated master (admin user) password for the DB instance. You should enter your own password. Otherwise, the installation of the Operational Reporting would fail.
  • In the Architecture settings section, leave the Use multitenant architecture option unselected.
  • Create a new parameter group for the RDS DB instance and set the enable_goldengate_replication parameter value to true.

For further details about how to create and configure an Amazon DB instance, see the Amazon RDS documentation.

Back to top

Install Operational Reporting

This section provides detailed steps on how to install Operation Reporting Content Pack.

To install Operational Reporting:

  1. Install Oracle GoldenGate (OGG) in Oracle server.

  2. Run the ./ggsci command in the <ogg_home> directory to start the OGG Manager.

  3. Create an empty reporting schema and grant necessary privileges to it by running the sample_setup_rds_reporting_sys.sh script:

    1. Go to the <Op_Report_Home>\install\sample directory, and open the sample_setup_rds_reporting_sys.sh file in a text editor.

    2. Uncomment the parameters listed in the following table, replace the placeholders with valid values, and then save and close the file.

      Parameter

      Value

      RDS admin user of Reporting DB

      RDS admin user name for the Operational Reporting database. This refers to the master username of the RDS DB instance.

      Example value: admin

      Reporting DB Schema Name

      Operational Reporting database schema name.

      Example value: RPT_SCHEMA

      Note: The PPM Center database schema name must be in all capital letters.

      Reporting DB data_tablespace_name

      Operational Reporting database to store data.

      Example value: RPT_DATA_TS

      Note: The Operational Reporting database tablespace name must be in all capital letters.

      Reporting DB temp_tablespace_name

      Operational Reporting database temp tablespace.

      Example value: RPT_TEMP_TS

      Note: The Operational Reporting database temp tablespace name must be in all capital letters.

      Reporting DB index_tablespace_name

      Operational Reporting database to store index.

      Example value: RPT_INDEX_TS

      Note: The Operational Reporting database index tablespace name must be in all capital letters.

      Reporting DB TNS Name

      Identifies the Oracle instance that runs the Operational Reporting database schema. TNS name is configured in the tnsnames.ora file.

      Example value: RPT

      Reporting DB DATA_NOLOGGING_TABLESPACE

      Separate tablespace that requires no redo log for the Operational Reporting database to store data.

      Example value: RPT_DATA_TS_NL

      Reporting DB INDEX_NOLOGGING_TABLESPACE

      Separate tablespace that requires no redo log for the Operational Reporting database to store indexes.

      Example value: RPT_INDEX_TS_NL

    3. Run the sample_setup_rds_reporting_sys.sh script.

    4. During the script run, provide the RDS admin user password when prompted.

      Tip: The RDS admin user password is the master password of the RDS DB instance. For this password, do not use the automatically generated password. Otherwise, the installation of the Operational Reporting would fail.

    5. Go to the <Op_Report_Home>\install\log directory and check the setup_rds_reporting_sys.log file for errors.

    Note: Running the sample_setup_rds_reporting_sys.sh script drops the old reporting schema and creates a new reporting schema. The new reporting schema is reusable, therefore, you do not need to run this script if you want to re-install the Operational Reporting package.

  4. Run the sample_setup_ppm_rds_root_ogg.sh script:

    1. Go to the <Op_Report_Home>\install\sample directory, and open the sample_setup_ppm_rds_root_ogg.sh file in a text editor.

    2. Uncomment the parameters listed in the following table, replace the placeholders with valid values, and then save and close the file.

      Parameter

      Value

      RDS admin user

      RDS admin user name for the PPM Center database.

      Example value: admin

      OGG OWNER

      Name of the OGG owner.

      For PDB, it should begin with c##. Example value: C##OGGOWNER.

      For non-CDB/PDB, it cannot begin with C##. Example value: OGGOWNER.

      Data Table Space of OGG Owner

      Data tablespace of the OGG owner.

      Recommended value: SYSTEM
      Temp Table Space of Ogg Owner

      Temp dataspace of the OGG owner.

      Recommended value: TEMP
      DB TNS Name of PPM DB

      TNS name of DB where PPM database exists.
    3. Run the sample_setup_ppm_rds_root_ogg.sh script.
    4. Go to the <Op_Report_Home>\install\log directory and check the setup_ppm_rds_root_ogg.log file for errors.

  5. Follow the steps below to set up Oracle GoldenGate on Amazon RDS:

    1. Run the following to turn on the minimum database-level supplemental logging on the database:

      Copy code 
      EXEC rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD')
    2. Set the ENABLE_GOLDENGATE_REPLICATION initialization parameter to true to allow database services to support logical replication.

    3. Set the log retention period on the source database. For example, set the retention period for archived redo logs to 24 hours:

      Copy code 
      EXEC rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24)

    4. Execute the following to create the RDS directories to the online and archived redo log files:

      Copy code 
      EXEC rdsadmin.rdsadmin_master_util.create_archivelog_dir;
      EXEC rdsadmin.rdsadmin_master_util.create_onlinelog_dir;

    5. Grant the privileges needed by a user account to be an OGG owner. The package that you use to perform the grant, rdsadmin_dbms_goldengate_auth or dbms_goldengate_auth, depends on the Oracle DB engine version.

      • For Oracle DB versions that are later than or equal to Oracle Database 12c Release 2 (12.2), which requires patch level 12.2.0.1.ru-2019-04.rur-2019-04.r1 or later, run the following PL/SQL program:

        Copy code 
        EXEC rdsadmin.rdsadmin_dbms_goldengate_auth.grant_admin_privilege (
            grantee                 => 'OGGOWNER',
            privilege_type          => 'capture',
            grant_select_privileges => true, 
            do_grants               => TRUE);

      • For Oracle database versions that are earlier than Oracle Database 12c Release 2 (12.2), run the following PL/SQL program:

        Copy code 
        EXEC dbms_goldengate_auth.grant_admin_privilege (
            grantee                 => 'OGGOWNER',
            privilege_type          => 'capture',
            grant_select_privileges => true,
            do_grants               => TRUE);

      6. For more details, see the Using Oracle GoldenGate with Amazon RDS for Oracle topic in the Amazon RDS documentation.

  6. Run the following command to create userid alias for the OGG owner in database:

    ALTER CREDENTIALSTORE ADD USER <OGG_OWNER>@<RDS_NAME> ALIAS <ALIAS_IN_RDS>

  7. Run the sample_setup_ppm_rds_ogg.sh script.

    1. Go to the <Op_Report_Home>\install\sample directory, and open the sample_setup_ppm_rds_ogg.sh file in a text editor.

    2. Uncomment the parameters listed in the following table, replace the placeholders with valid values, and then save and close the file.

      Parameter

      Value

      RDS admin user of PPM DB

      RDS admin user name for the PPM Center database. This refers to the Master username of the Amazon RDS Oracle DB instance.

      Example value: admin

      PPM DB Schema Name

      This value should exist in the Oracle tnsnames.ora entry.

      Example value: PPM_SCHEMA

      Note: The PPM Center database schema name must be in all capital letters.

      OGG OWNER

      		Name of the OGG owner.
      Full tnsnames.ora entry to PPM schema

      Full tnsnames.ora entry for the PPM database schema.

      • For HOST, specify the IP address of the PPM database server.

      • For PORT, specify the PPM database port.

      • For SERVICE_NAME, specify the SID in the tnsnames.ora file for the PPM database.

      Example value:

      "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=16.189.27.11) (PORT=1521)) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=ORCL)))"

    3. Run the sample_setup_ppm_rds_ogg.sh script.
    4. Go to the <Op_Report_Home>\install\log directory and check the setup_ppm_rds_ogg.log file for errors.

  8. Run the sample_setup_rds_cdc_ogg.sh script:

    1. Go to the <Op_Report_Home>\install\sample directory, and open the sample_setup_rds_cdc_ogg.sh file in a text editor.

    2. Uncomment the parameters listed in the following table, replace the placeholders with valid values, and then save and close the file.

      Parameter

      Value

      PPM DB Schema Name

      This value should exist in the Oracle tnsnames.ora entry.

      Example value: PPM_SCHEMA

      Important: The PPM Center database schema name must be in all capital letters.
      PPM DB data_tablespace_name

      PPM Center database tablespace name.

      This refers to the existing data tablespace in PPM Center database schema. PPM Center schema stores this in the KINS_TABLESPACES table.

      Example value: PPM_DATA_TS

      Important: PPM Center database data tablespace name must be in all capital letters.
      PPM DB temp_tablespace_name

      PPM Center database temp tablespace name.

      This refers to the existing temp tablespace in PPM Center database schema. PPM Center schema stores this in the KINS_TABLESPACES table.

      Example value: PPM_TEMP_TS

      Important: PPM Center database temp tablespace name must be in all capital letters.
      PPM DB index_tablespace_name

      PPM Center database index tablespace name.

      This refers to the existing index tablespace in PPM Center database schema. PPM Center schema stores this in the KINS_TABLESPACES table.

      Example value: PPM_INDEX_TS

      Important: PPM Center database index tablespace name must be in all capital letters.
      Full tnsnames.ora entry to PPM schema

      Full tnsnames.ora entry for the PPM database schema.

      • For HOST, specify the IP address of the PPM database server.

      • For PORT, specify the PPM database port.

      • For SERVICE_NAME, specify the SID in the tnsnames.ora file for the PPM database

      Example value:

      "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=16.89.27.63) (PORT=1522)) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=MDB1106A)))"

      Ogg OWNER

      		Name of the OGG owner.
      USERIDALIAS of Ogg Owner

      The userid alias you created for the OGG owner in database.
      USERIDALIAS of Ogg Owner

      The userid alias you created for the OGG owner in database.
      Container name

      Name of DB where PPM database exists.

      It must be NULL.

      extract name

      Name of the extract job.

      Extract name cannot be longer than 8 characters.
      data pump name

      Name of the data pump job.

      Data pump name cannot be longer than 8 characters.
      replicat name

      Name of the replicat job.

      Replicat name cannot be longer than 8 characters.
      SID of CDB of PPM DB

      SID of the DB where PPM database exists.
      ogg port

      OGG port number.

      Example value: 7809
      File Name of Local Trail

      Name of the local trail that stores the changes captured by the Extract process.

      The file name should have and only have two characters.
      File Name of Remote Trail

      Name of the remote trail.

      The file name should have and only have two characters.
    3. Run the sample_setup_rds_cdc_ogg.sh script.
    4. Go to the <Op_Report_Home>\install\log directory and check the setup_rds_cdc_ogg.sh.log file for errors.
  9. Copy the *.prm files from the <Op_Report_Home>\DB\install\ogg directory to the <ogg_home>\dirprm directory.
  10. Copy the ggsci_input.txt file from the <Op_Report_Home>\DB\install\ogg directory to the <ogg_home> directory, and run the ./ggsci < ggsci_input.txt command.

  11. In the <ogg_home> directory, run the ./ggsci > info all command to see whether the extract, data dump, and replicat jobs are running.

  12. Run the sample_setup_all.sh script:

    1. Go to the <Op_Report_Home>/install/sample directory, and open the sample_setup_all.sh file in a text editor.

      Note: Make sure that you make the file executable.
      For example: chmod +x sample_setup_all.sh

    2. Uncomment the parameters listed in the following table, replace the placeholders with valid values, and then save and close the file.

      Parameter

      Description

      Reporting DB Schema Name

      Operational Reporting database schema name.

      Example value: RPT_SCHEMA

      The Operational Reporting database schema name must be in all capital letters.

      Reporting DB data_tablespace_name

      Name of the data tablespace for the Operational Reporting database.

      Example value: RPT_DATA_TS

      The Operational Reporting database data tablespace name must be in all capital letters.

      Reporting DB temp_tablespace_name

      Name of the temp tablespace for the Operational Reporting database.

      Example value: RPT_TEMP_TS

      The Operational Reporting database temp tablespace name must be in all capital letters.

      Reporting DB index_tablespace_name

      Name of the index tablespace for the Operational Reporting database.

      Example value: RPT_INDEX_TS

      The Operational Reporting database index tablespace name must be in all capital letters.

      Reporting DB TNS Name

      Identifies the Oracle instance that runs the Operational Reporting database schema. TNS name is configured in the tnsnames.ora file.

      Example value: RPT

      PPM DB Schema Name

      PPM database schema name.

      This value should exist in the Oracle tnsnames.ora entry.

      Example value: PPM_SCHEMA

      The PPM database schema name must be in all capital letters.

      PPM DB data_tablespace_name

      PPM database data tablespace name.

      This refers to the existing data tablespace in the PPM database schema. The PPM schema stores this in KINS_TABLESPACES table.

      Example value: PPM_DATA_TS

      Important: The PPM database data tablespace name must be in all capital letters.

      PPM DB temp_tablespace_name

      PPM database temp tablespace name.

      This refers to the existing temp tablespace in the PPM database schema. The PPM schema stores this in KINS_TABLESPACES table.

      Example value: PPM_TEMP_TS

      The PPM database temp tablespace name must be in all capital letters.

      PPM DB index_tablespace_name

      PPM database index tablespace name.

      This refers to the existing index tablespace in the PPM database schema. The PPM schema stores this in KINS_TABLESPACES table.

      Example value: PPM_INDEX_TS

      The PPM database index tablespace name must be in all capital letters.

      Full tnsnames.ora entry to PPM schema

      Full tnsnames.ora entry for the PPM database schema.

      • For HOST, specify the IP address of the PPM database server.

      • For PORT, specify the PPM database port.

      • For SERVICE_NAME, specify the SID in the tnsnames.ora file for the PPM database

      Example value:

      "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=16.89.27.63) (PORT=1522)) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=MDB1106A)))"

      DB_LINK_NAME to PPM

      Name of the link to the PPM database.

      This value is generated in the Operational Reporting database schema.

      Example value: PPM_DB_LINK

      The name of the link to the PPM database must be in all capital letters.

      Reporting DB DATA_NOLOGGING_TABLESPACE

      Separate tablespace that requires no redo log for the Operational Reporting database to store data.

      Example value: PPM_DATA_TS_NL

      Reporting DB INDEX_NOLOGGING_TABLESPACE

      Separate tablespace that requires no redo log for the Operational Reporting database to store indexes.

      Example value: RPT_INDEX_TS_NL
      INSTALLATION_TYPE

      What is used to install the Content Pack, Oracle GoldenGate or Oracle Streams.

      Default value: OGG

      If you use Oracle Streams, set the value to STREAMS.

    3. Run the sample_setup_all.sh script.

    4. During the script run, provide the following passwords when prompted:

      • PPM database server schema password

      • Operational Reporting database server schema password

    5. The script performs a sanity check on PPM database:

      • If the sanity check fails, an error message pops up. You must fix the errors following the suggestions in the error message.

      • If the sanity check passes, continue with step f.

    6. The script run creates a log file in the <Op_Report_Home>/install/log directory. Check the setup_all.log file to make sure that no errors occurred. If the setup_all.log file indicates that compilation errors occurred, run the following command:

      Select * from user_objects where status = 'INVALID'

      If no rows are returned, you can safely ignore the warning.

  13. Run the sample_load_data.sh script to import the PPM data into the Operational Reporting database.

    1. Go to the <Op_Report_Home>/install/sample directory, and open the sample_load_data.sh file in a text editor.

    2. Uncomment the parameters listed in the following table, replace the placeholders with valid values, and then save and close the file.

      Parameter

      Description

      Reporting DB Schema Name

      Operational Reporting database schema name.

      Example value: RPT_SCHEMA

      The Operational Reporting database schema name must be in all capital letters.

      Reporting DB TNS Name

      Identifies the Oracle instance running the Operational Reporting database schema. the TNS name is configured in the tnsnames.ora file.

      Example value: RPT

      Reporting DB index_tablespace_name

      Name of the index tablespace for the Operational Reporting database.

      Example value: RPT_INDEX_TS

      The Operational Reporting database index tablespace name must be in all capital letters.

      DB_LINK_NAME to PPM

      Name of the link to the PPM Center database. This link is created automatically during the setup_all script run.

      Example value: PPM_DB_LINK

      ETL start date (mm-dd-yyyy)

      Start date (in mm-dd-yyyy format) for the PPM Center data to load into the Operational Reporting database schema.

      Example value: 01/01/2009

      ETL end date (mm-dd-yyyy)

      End date (in mm-dd-yyyy format) for the PPM Center data to load into the Operational Reporting database schema.

      Example value: 01/01/2011

      Note: The ETL end date you specify is converted based on the fiscal year. For details, see the Installation and Administration Guide.

      Reporting DB data_tablespace_name

      Name of the data tablespace for the Operational Reporting database.

      Example value: RPT_DATA_TS

      The Operational Reporting database data tablespace name must be in all capital letters.

      Request dimension ETL start date (mm-dd-yyyy)

      Start date (in mm-dd-yyyy format) for the PPM Center request data to load into the Operational Reporting database schema.

      Example value: 01/01/2009

      Note: If your PPM Center database contains data for old, but active requests, you can include that data without importing all data from that time period.

      Reporting DB DATA_NOLOGGING_TABLESPACE

      Separate tablespace that requires no redo log for the Operational Reporting database to store data.

      Example value: PPM_DATA_TS_NL

      Reporting DB INDEX_NOLOGGING_TABLESPACE

      Separate tablespace that requires no redo log for the Operational Reporting database to store indexes.

      Example value: PPM_INDEX_TS_NL
    3. Run the sample_load_data.sh script.

    4. During the load script run, provide Operational Reporting database schema password and the Operational Reporting admin user password, as prompted.

      Tip: The Operational Reporting admin user password is the master password of the RDS DB instance. For this password, do not use the automatically generated password. Otherwise, the installation of the Operational Reporting would fail.

    5. The script creates a load_data.log file in the <Op_Report_Home>/install/log directory. Check the log file to make sure that no errors occurred.

      Note: You can run the sample_load_data.sh only once. If you discover any incorrect data, you have to re-install the Operational Reporting package. To re-install the package, you start from step 6 and then run the load script.

  14. Run the following commands in the <ogg_home> directory to restart the OGG Manager:

    • STOP MANAGER

    • START MANAGER

Back to top