Install Operational Reporting for Oracle 11g and Oracle 12c (non-PDB)

Before you install Content Pack:

  • If you want to install content pack 2.0 or later versions in Oracle 12c, you need to disable the Oracle parameter _ADD_COL_OPTIM_ENABLED.

    To disable it, run the following with sysdba privilege in PPM schema:

    ALTER SYSTEM set _ADD_COL_OPTIM_ENABLED = FALSE

  • When you install content pack 2.0 or later versions, you should set the parameter streams_pool_size to a value equal to or greater than 512 MB.

    To do this:

    1. Run the following with sysdba privilege in PPM database:

      alter system set streams_pool_size=512M scope=spfile;

      alter system set job_queue_processes=1000 scope=spfile;

      alter system set "_use_realfree_heap"=false; (This is required only when your Oracle version is 11.2.0.3 or lower)

    2. Restart Oracle database.

To install Content Pack:

  1. Caution: If the REMOTE_ADMIN_REQUIRE_AUTH parameter is set to true, users running kStop.sh to shut down the PPM Server must supply a valid PPM user name and password. If the parameter is set to false, any user with access to the kStop.sh script can shut down the server. For information about the REMOTE_ADMIN_REQUIRE_AUTH parameter, see the Installation and Administration Guide.

    To stop a PPM Server:

    1. Navigate to the <PPM_Home>/bin directory.

    2. Run the kStop.sh script as follows:

      sh ./kStop.sh -now -user <User_Name>

      Make sure that you type a valid user name for a user who has Administrator privileges.

    • Connect to PPM database as SYSDBA and run the SQL commands as follows:

      grant select_catalog_role to <PPM_SCHEMA>;
grant execute_catalog_role to <PPM_SCHEMA>;
grant create job to <PPM_SCHEMA>;
grant create materialized view to <PPM_SCHEMA>;
grant dba to <PPM_SCHEMA>;
grant become user to <PPM_SCHEMA>;
grant create view to <PPM_SCHEMA>; 
grant create sequence to <PPM_SCHEMA>;

    • Run the sample_setup_ppm_sys.sh script:

      1. Log on to the machine that can connect to PPM database and Reporting database, navigate to the
        <Op_Report_Home>/install/sample directory, and open the sample_setup_ppm_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

        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

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

        Full tnsnames.ora entry to PPM schema

        Full tnasame.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 tnsname.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)))"

      3. Run the sample_setup_ppm_sys.sh script.

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

        PPM database server SYS user password

      5. Navigate to the <Op_Report_Home>/install/log directory and check the setup_ppm_sys.log file for errors.

    • 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.sh script:

      1. Navigate to the <Op_Report_Home>\install\sample directory, and open the sample_setup_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

        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

        Important: 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

        Important: 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

        Important: 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

        Important: 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.sh script.

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

        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.

  4. Run the sample_setup_cdc_streams.sh script.

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

      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.

      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

      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 tnsnames.ora entry for the PPM Center database schema.

      • For HOST, specify the IP address of the PPM Center database server
      • For PORT, specify the PPM Center database port
      • For SERVICE_NAME, specify the SID in tnsnames.ora file for the PPM Center 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_cdc_streams.sh script.
    4. Navigate to the <Op_Report_Home>\install\log directory and check the setup_reporting_sys.log file for errors.

    1. Navigate 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 as an 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

      Important: 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

      Important: 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

      Important: 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

      Important: 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

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

      PPM DB data_tablespace_name

      PPM database data tablespace name

      Note: 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.

      Note: 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

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

      PPM DB index_tablespace_name

      PPM database index tablespace name.

      Note: 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

      Important: 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 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

      Important: 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. 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.

  6. Connect to PPM database as SYSDBA and run the SQL commands as follows:

    revoke dba from <PPM_SCHEMA>;
revoke become user from <PPM_SCHEMA>;

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

    1. Navigate 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 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.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 5 and then run the load script.

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

    • STOP MANAGER
    • START MANAGER