Share this page

# Install Operational Reporting for Oracle 12c/18c/19c (PDB) and Oracle 19c (non-CDB/PDB)

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

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

3. Configure Oracle server.

   1. Run the following command to switch to archive mode.

          SHUTDOWN;

          STARTUP MOUNT;

          ALTER DATABASE ARCHIVELOG;

          ALTER DATABASE OPEN;

   2. Run the following command to enable supplemental log.

          ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

4. Create an empty reporting schema and grant necessary privileges to it by one of the following methods:

   - Connect to Report database as SYSDBA and run the SQL commands as follows:

     1. Create a new schema:

            CREATE USER <report_schema_name>
            IDENTIFIED BY <report_shcema_password>
            DEFAULT TABLESPACE <data_table_space>
            TEMPORARY TABLESPACE <temp_table_space>
            QUOTA UNLIMITED ON <data_table_space>
            QUOTA UNLIMITED ON <index_table_space>
            QUOTA UNLIMITED ON <DATA_NOLOGGING_TABLESPACE>
            QUOTA UNLIMITED ON <INDEX_NOLOGGING_TABLESPACE>;

     2. Grant necessary privileges to the new schema:

            grant connect to <reporting_shcema_name>;
            grant create procedure to <reporting_shcema_name>;
            grant create session to <reporting_shcema_name>;
            grant create sequence to <reporting_shcema_name>;
            grant create synonym to <reporting_shcema_name>;
            grant create table to <reporting_shcema_name>;
            grant create view to <reporting_shcema_name>;
            grant create materialized view to <reporting_shcema_name>;
            grant create database link to <reporting_shcema_name>;
            grant alter session to <reporting_shcema_name>;
            grant analyze any to <reporting_shcema_name>;

            grant select on v_$parameter to <reporting_shcema_name>;
            grant create job to <reporting_shcema_name>;
            grant EXECUTE ANY PROGRAM to <reporting_shcema_name>;
            grant MANAGE SCHEDULER to <reporting_shcema_name>;
            grant select on dba_scheduler_programs to <reporting_shcema_name>;
            grant select on dba_scheduler_schedules to <reporting_shcema_name>;
            grant select on dba_scheduler_jobs to <reporting_shcema_name>;

            BEGIN

            DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ,
            '<reporting_shcema_name>');
            DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
            '<reporting_shcema_name>');
            DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_
            OBJ, '<reporting_shcema_name>');

            END;

   - Run the `sample_setup_reporting_sys.bat` script:

     1. Navigate to the `<Op_Report_Home>\install\sample` directory, and open the `sample_setup_reporting_sys.bat` 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
        --- | ---
        SYS user name of Reporting DB | SYS user name for the Operational Reporting database. Example value: sys
        Reporting DB Schema Name | Operational Reporting database schema name. Example value: RPT_SCHEMA 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 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 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 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_reporting_sys.bat` script.

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

        OpenText PPM database server SYS user password

     5. Navigate to the`<Op_Report_Home>\install\log` directory and check the `setup_reporting_sys.log` file for errors.

   Note: Running the `sample_setup_reporting_sys.bat` 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.

5. Run the `sample_setup_ppm_cdb_root_ogg.bat` script.

   1. Navigate to the `<Op_Report_Home>\install\sample` directory, and open the `sample_setup_ppm_cdb_root_ogg.bat` 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
      --- | ---
      SYS user name of PPM DB | SYS user name for the PPM Center database. Example value: sys
      OGG OWNER | Name of 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. For CDB/PDB, the data tablespace should exist in all DBs. Recommended value: SYSTEM
      Temp Table Space of Ogg Owner | Temp dataspace of the OGG owner. For CDB/PDB, the temp tablespace should exist in all DBs. Recommended value: TEMP
      DB TNS Name of CDB ROOT of PPM DB | TNS name of CDB where PPM database exists. For non-CDB/PDB, TNS name of DB where PPM database exists.

   3. Run the `sample_setup_ppm_cdb_root_ogg.bat` script.
   4. Navigate to the `<Op_Report_Home>\install\log` directory and check the `setup_ppm_cdb_root_ogg.log` file for errors.

6. Create alias for the OGG owner.

   - For PDB, create two aliases for the OGG owner:

     1. Run the following command to create userid alias for OGG owner in CDB:

            Example: ALTER CREDENTIALSTORE ADD USER <OGG_OWNER> ALIAS <ALIAS_IN_CDB>

     2. Run the following command to create userid alias for OGG owner in PDB:

            Example: ALTER CREDENTIALSTORE ADD USER <OGG_OWNER>@<PDB_NAME> ALIAS <ALIAS_IN_PDB>

   - For non-CDB/PDB, run the following command to create userid alias for OGG owner in database:

       Example: ALTER CREDENTIALSTORE ADD USER <OGG_OWNER> ALIAS <ALIAS_IN_CDB>

7. Run the `sample_setup_ppm_pdb_ogg.bat` script.

   1. Navigate to the `<Op_Report_Home>\install\sample` directory, and open the `sample_setup_ppm_pdb_ogg.bat` 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
      --- | ---
      SYS user name of PPM DB | SYS user name for the PPM Center database. Example value: sys
      PPM DB Schema Name | This value should exist in the Oracle `tnsnames.ora` entry. Example value: PPM_SCHEMA The PPM Center database schema name must be in all capital letters.
      OGG OWNER | Ogg owner name.
      Full tnasame.ora entry to PPM schema | Full `tnasame.ora` entry for the OpenText PPM database schema. For `HOST,` specify the IP address of the OpenText PPM database server For `PORT,` specify the OpenText PPM database port For `SERVICE_NAME,` specify the SID in `tnsname.ora` file for the OpenText PPM database Example value: `"(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=16.89.27.63) (PORT=1522)) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=MDB1106A)))"`

   3. Run the `sample_setup_ppm_pdb_ogg.bat` script.
   4. Navigate to the `<Op_Report_Home>\install\log` directory and check the `setup_ppm_pdb_ogg.log` file for errors.

8. Run the `sample_setup_cdc_ogg.bat` script.

   1. Navigate to the `<Op_Report_Home>\install\sample` directory, and open the `sample_setup_cdc_ogg.bat` 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. Note: This refers to the existing data tablespace in PPM Center database schema. PPM Center schema store 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. Note: This refers to the existing temp tablespace in PPM Center database schema. PPM Center schema store this in the KINS_TABLESPACES table. Example value: PPM_TEMP_TS PPM Center database temp tablespace name must be in all capital letters.
      PPM DB index_tablespace_name | PPM Center database index tablespace name. Note: This refers to the existing index tablespace in PPM Center database schema. PPM Center schema store this in the KINS_TABLESPACES table. Example value: PPM_INDEX_TS PPM Center database index tablespace name must be in all capital letters.
      Full tnasame.ora entry to PPM schema | Full `tnasame.ora` entry for the OpenText PPM database schema. For `HOST,` specify the IP address of the OpenText PPM database server For `PORT,` specify the OpenText PPM database port For `SERVICE_NAME,` specify the SID in `tnsname.ora` file for the OpenText PPM database Example value: `"(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=16.89.27.63) (PORT=1522)) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=MDB1106A)))"`
      Ogg OWNER | Ogg owner name.
      USERIDALIAS of Ogg Owner with CDB ROOT | The userid alias you created for OGG owner in CDB. For non-CDB/PDB, the userid alias you created for OGG owner in database.
      USERIDALIAS of Ogg Owner with PDB | The userid alias you created for OGG owner in PDB. For non-CDB/PDB, the userid alias you created for OGG owner in database.
      Container name | Name of PDB where PPM database exists. For non-CDB/PDB, 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 CDB where PPM database exists. For non-CDB/PDB, 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_cdc_ogg.bat` script.
   4. Navigate to the `<Op_Report_Home>\install\log` directory and check the `setup_cdc_ogg.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 ogg jobs are running.

12. Run the `sample_setup_all.bat` script.

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

       Note: Make sure that you make the file as an executable.
       For example: `chmod +x sample_setup_all.bat`

    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 | OpenText PPM database schema name. This value should exist in the Oracle `tnsnames.ora` entry. Example value: PPM_SCHEMA The OpenText PPM database schema name must be in all capital letters.
       PPM DB data_tablespace_name | OpenText PPM database data tablespace name. Note: This refers to the existing data tablespace in the OpenText PPM database schema. The OpenText PPM schema stores this in KINS_TABLESPACES table. Example value: PPM_DATA_TS Important: The OpenText PPM database data tablespace name must be in all capital letters.
       PPM DB temp_tablespace_name | OpenText PPM database temp tablespace name. Note: This refers to the existing temp tablespace in the OpenText PPM database schema. The OpenText PPM schema stores this in KINS_TABLESPACES table. Example value: PPM_TEMP_TS The OpenText PPM database temp tablespace name must be in all capital letters.
       PPM DB index_tablespace_name | OpenText PPM database index tablespace name. Note: This refers to the existing index tablespace in the OpenText PPM database schema. The OpenText PPM schema stores this in KINS_TABLESPACES table. Example value: PPM_INDEX_TS The OpenText PPM database index tablespace name must be in all capital letters.
       Full tnsnames.ora entry to PPM schema | Full `tnsnames.ora` entry for the OpenText PPM database schema. For `HOST,` specify the IP address of the OpenText PPM database server For `PORT,` specify the OpenText PPM database port For `SERVICE_NAME,` specify the SID in `tnsnames.ora` file for the OpenText 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 OpenText PPM database. This value is generated in the Operational Reporting database schema. Example value: PPM_DB_LINK The name of the link to the OpenText 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.bat` script.

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

       - OpenText PPM database server schema password

       - Operational Reporting database server schema password

    5. The script performs a sanity check on PPM database. Do one of the following:

       - If the sanity check fails, an error message pops up. You must fix the errors by the suggestions on 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 and 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:

       `Select * from user_objects where status = 'INVALID'`

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

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

    1. Navigate to the `<Op_Report_Home>/install/sample` directory, and open the `sample_load_data.bat` 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.bat` script.

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

    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.bat` 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 12 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`

https://admhelp.microfocus.com PPM 8942
Tips to improve your search results... OpenText™ Project and Portfolio Management Help Center PPM Help 26.2-26.3 All View Local Help Center View Online Help Center Version: