Upgrade Source Applications

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

Note:

  • Before proceeding with source upgrade, 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.9.0.0.0.

Upgrade Network Management System (NMS) Source Application

Note:

Perform the following steps only if the upgraded source application is NMS, and you are upgrading to version 2.6.0 or later.

  1. Ensure the source application is down.

  2. Ensure the database is up and running in both the source and the target.

  3. Upgrade the NMS source application only—without executing NMS UTC data conversion migration scripts, and without executing the normal setup process which runs other migrations involving DDL changes.

  4. 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. If the source database is not in UTC, then do the following:

    1. Stop all Golden Gate Processes (EXTRACTS and REPLICATS in the source and the target).

    2. Execute the NMS UTC data conversion migration script as part of the upgrade to NMS 2.6.0.1 before changing the source database time zone to UTC.

    3. Update the source database time zone to UTC as part of the upgrade to NMS 2.6.0.1.

  6. If the source database is in UTC and the source Application is not in UTC, perform the following steps:
    1. Alter the initial checkpoint of the Golden Gate EXTRACT & PUMP processes using BEGIN NOW in the source. 
      GGSCI > ALTER EXTRACT <NMS1>AAX BEGIN NOW
GGSCI > ALTER EXTRACT <NMS1>AAP BEGIN NOW

Note: Here the product context code is <NMS1>.

    2. Ensure the source application is still down, and then start all Golden Gate Processes (EXTRACTS and REPLICATS) in the source and target.

  7. If the source database and source Application are in UTC, ensure the source application is still down, and then start all Golden Gate Processes (EXTRACTS and REPLICATS) in the source and target.

  8. Execute the normal setup process which runs other migrations involving DDL changes as part of upgrade to NMS 2.6.0.1.

  9. Ensure that the table NMS_ACCOUNTS_HISTORY is created in the source database.

  10. Ensure Incremental Changes (DDL and DML changes during the upgrade) are synced. The Lag for Chkpt and Time Since Chkpt in both the source and target databases should be 0.

  11. Stop all Golden Gate Processes (EXTRACTS, REPLICATS, PMSRVR, JAGENT, MANAGER) in the source and the target.

Upgrade Other Source Applications

Note:

Perform the following steps if the upgraded source applications are Meter Data Management (MDM), Customer Care and Billing (CCB), Customer to Meter (C2M), Work and Asset Management (WAM), and NMS (if you are upgrading to a version below 2.6.0).

  1. Ensure the source application is down.

  2. Ensure the database is up and running in both the source and the target.

  3. Start all Golden Gate Processes (EXTRACTS, REPLICATS, PMSRVR, JAGENT, MANAGER) in the source and the target.

    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.

  4. Upgrade the source application.

  5. 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.

  6. Ensure Incremental Changes (DDL and DML changes during the upgrade) are synced. 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. (Optional) If replicats are in target GoldenGate and are in "abended" states, refer to the Instructions to be Followed for Replication Abended Issue section.

  8. Stop all Golden Gate Processes (EXTRACTS, REPLICATS, PMSRVR, JAGENT, MANAGER) in the source and the target.

Upgrade OUAW without Upgrading Source Applications

Note:

This section refers to the sources that you are not required to upgrade. It is valid only for sources that are compatible with both Oracle Utilities Analytics Warehouse 2.7.1.0.3 and 2.9.0.0.0.

  1. Ensure the source application, source database, and target database are up and running.

  2. Start all Golden Gate Processes (EXTRACTS, REPLICATS, PMSRVR, JAGENT, MANAGER) in the source and the target.

  3. 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.

  4. Stop all Golden Gate Processes (EXTRACTS, REPLICATS, PMSRVR, JAGENT, MANAGER) in the source and the target.

Instructions to be Followed for Replication Abended Issue

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 Journalizing Knowledge Module code used in Oracle Utilities Analytics 2.7.0.1.3 version. Hence, if there are any further DDL operations on these newly added columns, the REPLICAT process will get abended with the "Invalid Identifier" error.

If these issues of "replicat abending" and "NOT NULL columns not being added to replication tables" occur after the source upgrade, as a resolution the source tables for which the NOT NULL columns have been added need to be identified and the columns need to be added manually. 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 in the source application schema tables. It does not occur for columns defined as NULL.
Follow these steps as a resolution for the issue:

  1. Identify the NOT NULL columns that have been newly added to the tables of the source application schema that have been replicated in OUAW. Use the below query 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 (that is with Base Replication Flag value of 'Y').
    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.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: 
    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 (that is with Custom Replication Flag value of 'Y').
    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: 
    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 above query, 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 & DML Changes during the upgrade) are synced.

  5. Continue with the Source Upgrade steps.