Upgrade Source Application

This page describes how to upgrade your source applications to be compatible with Oracle Utilities Analytics Warehouse v2.8.0.1.0.

On this page:

Upgrade Source Applications

Notes:

  • Before proceeding with the prerequisite steps on the source database server, ensure you have a proper backup plan to revert the changes in case of any failure.
  • Upgrade the source applications to be compatible with Oracle Utilities Analytics Warehouse v2.8.0.1.0

Perform the following steps as part of the source application upgrade prerequisites.

  1. Ensure the source application is down.
  2. Ensure the database is up and running in both the source and the target.
  3. Ensure the Golden Gate Processes (Extracts and Replicats) are up and running in the source and the target, respectively.

    Note: Make sure that the pump processes transfer all the changes to the target server. Lag at the Chkpt and Time Since Chkpt should be 0. This can be confirmed by using the info all command after connecting to the ggsci prompt.

  4. Upgrade the source application.

    Note: After a successful upgrade, do not release the source applications to end users. Ensure that you stop the source applications, but keep the source databases up and accessible.

  5. Optional: In case the GoldenGate replication process state is abended after the source upgrade, review the section Replication Abended Issue (Target Server GoldenGate Replication) below.

  6. Make sure the data in the source is in sync with the replication tables. The Lag for Chkpt and Time Since Chkpt in both the source and target databases should be 0.

  7. Stop all Golden Gate Processes (Extracts and Replicats), PMSRVR, JAGENT, MANAGER in the source and the target.

  8. After the source upgrade, make sure all PK constraint are present for all replicated tables in both the source and the target replication schemas.

Back to Top

Replication Abended Issue (Target Server GoldenGate Replication)

As a part of source upgrade, if there are any NOT NULL columns added to the source application schema, these columns will not get added to replication tables by GoldenGate after the upgrade. This is due to a limitation in the Journalising Knowledge Module code used in OUAW 2.7.0.2.1 version. Hence if there are any further DDL operations on these newly added columns, the REPLICAT process will get abended with an 'Invalid Identifier' error.

To solve the issue of the replicat abending and NOT NULL columns not being added to replication tables after the source upgrade, it is necessary to identify and add manually the source tables for which the NOT NULL columns have been added. Once the columns are added, the REPLICAT process can be started again, which will perform the additional DDL operations on the columns.

Note: The issue occurs only when columns that are NOT NULL are added to the source application schema tables. It does not occur for columns defined as NULL.

Follow the steps below to solve the issue:

  1.  Identify the NOT NULL columns that were added to the tables of the source application schema that have been replicated in OUAW. Use the query below to get the list of such tables. The query makes use of the database link created from the MDADM schema to the source application schema.

    For tables replicated out-of-the-box (with Base Replication Flag value of 'Y'):

    Copy 
    SELECT DTC.TABLE_NAME, DTC.COLUMN_NAME 
    fROM DBA_TAB_COLUMNS@<database link name from MDADM to source schema> DTC,  B1_SOURCE_TABLE BSTWHERE DTC.OWNER='<source application schema name>' AND DTC.TABLE_NAME=BST.TBL_NAME 
    AND BST.PROD_FLG='<configured source product code>'  AND BST.BASE_REPLICATE_FLG='Y' AND DTC.NULLABLE='N'
    MINUS
    SELECT DTC.TABLE_NAME, DTC.COLUMN_NAME 
    fROM DBA_TAB_COLUMNS DTC, B1_SOURCE_TABLE BST 
    WHERE DTC.OWNER='<replication schema name>' AND DTC.TABLE_NAME=BST.TBL_NAME
    AND BST.PROD_FLG='<configured source product code>' AND BST.BASE_REPLICATE_FLG='Y' AND DTC.COLUMN_NAME NOT LIKE 'JRN%';

    For example, if the configured source product context is NMS2 and the source application schema and replication schema are oms1_ugbu_phx_465 and NMS2REP, respectively, then the query will look as below:

    Copy 
    SELECT DTC.TABLE_NAME, DTC.COLUMN_NAME 
    fROM DBA_TAB_COLUMNS@NMS2_LINK DTC,  B1_SOURCE_TABLE BST
    WHERE DTC.OWNER='OMS1_UGBU_PHX_465' AND DTC.TABLE_NAME=BST.TBL_NAME 
    AND BST.PROD_FLG='NMS'  AND BST.BASE_REPLICATE_FLG='Y' AND DTC.NULLABLE='N'
    MINUS 
    SELECT DTC.TABLE_NAME, DTC.COLUMN_NAME 
    fROM DBA_TAB_COLUMNS DTC, B1_SOURCE_TABLE BST 
    WHERE DTC.OWNER='NMS2REP' AND DTC.TABLE_NAME=BST.TBL_NAME 
    AND BST.PROD_FLG='NMS' AND BST.BASE_REPLICATE_FLG='Y' AND DTC.COLUMN_NAME NOT LIKE 'JRN%';

    For custom replicated tables (with Custom Replication Flag value of 'Y'):

    Copy 
    SELECT DTC.TABLE_NAME, DTC.COLUMN_NAME 
    fROM DBA_TAB_COLUMNS@<database link name from MDADM to source schema> DTC,B1_SOURCE_TABLE BST
    WHERE DTC.OWNER='<source application schema name>' AND DTC.TABLE_NAME=BST.TBL_NAME 
    AND BST.PROD_FLG='<configured source product code>'  AND BST.CM_REPLICATE_FLG='Y' AND DTC.NULLABLE='N'
    MINUS 
    SELECT DTC.TABLE_NAME, DTC.COLUMN_NAME 
    fROM DBA_TAB_COLUMNS DTC, B1_SOURCE_TABLE BST 
    WHERE DTC.OWNER='<replication schema name>'AND DTC.TABLE_NAME=BST.TBL_NAME 
    AND BST.PROD_FLG='<configured source product code>'AND BST.CM_REPLICATE_FLG='Y'AND DTC.COLUMN_NAME NOT LIKE 'JRN%';

    For example, if the configured source product context is NMS2 and the source application schema and replication schema are oms1_ugbu_phx_465 and NMS2REP, respectively, then the query will look as below:

    Copy 
    SELECT DTC.TABLE_NAME, DTC.COLUMN_NAME 
    fROM DBA_TAB_COLUMNS@NMS2_LINK DTC,  B1_SOURCE_TABLE BST
    WHERE DTC.OWNER='OMS1_UGBU_PHX_465' AND DTC.TABLE_NAME=BST.TBL_NAME 
    AND BST.PROD_FLG='NMS'  AND BST.CM_REPLICATE_FLG='Y' AND DTC.NULLABLE='N'
    MINUS 
    SELECT DTC.TABLE_NAME, DTC.COLUMN_NAME 
    fROM DBA_TAB_COLUMNS DTC, B1_SOURCE_TABLE BST 
    WHERE DTC.OWNER='NMS2REP' AND DTC.TABLE_NAME=BST.TBL_NAME 
    AND BST.PROD_FLG='NMS' AND BST.CM_REPLICATE_FLG='Y' AND DTC.COLUMN_NAME NOT LIKE 'JRN%';

  2.  For each of the columns fetched from the query above, the column definition can be looked up from the table definition in the source schema and the columns can be accordingly added to the corresponding tables in replication schema at the bottom of the table structure.

  3. Once these columns have been added to the replication tables, start the abended replicat processes.

  4. Once the replicates are up, ensure Incremental Changes (DDL and DML changes during the upgrade) are synced.

  5. Continue with step 7 of the Upgrade Source Applications section.

Back to Top