Upgrade Source Application

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

On this page:

Upgrade Source Applications

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

The source applications’ databases must be in archive log mode during their upgrade. Oracle GoldenGate processes should be up and running on both source and target during the source application(s) upgrade.

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

Connect to MDADM schema in the target Oracle Utilities Analytics database. Run the following SQL to get all the context codes.
Copy
```
Select CONTEXT_CD from b1_prod_instance where CONTEXT_CD <>'B1';
```
For each context returned by the query:
1. Connect to MDADM schema in the Oracle Utilities Analytics database.
2. Run the SQL below to get the source GoldenGate db server, SID and Oracle GoldenGate host, home on source server:
  Copy
```
Select DS_HOST,DS_SID,DS_HOME,CONTEXT_CD,DS_FLAG from b1_server_cfg where CONTEXT_CD='<Context_code>';
```
3. Connect to the source database with SYS user and run the following command:
4. Make sure that the pump processes transfer all the changes to the target server.
5. The lag at the Chkpt and Time Since Chkpt should be zero. This can be confirmed using the info all command after connecting to the ggsci prompt.
  Copy
```
./ggsci
info all
```

Run the following query to get GoldenGate Owner name in the target database.

Copy

select SCHEMA_NAME from MDADM.B1_CONTEXT_CFG_VW where LSCHEMA_NAME = 'Journal' and CONTEXT_CD = '<Context_code>';

Upgrade the source application databases.

Note: After a successful upgrade, do not release the source applications to end users. Ensure to stop the source applications, but the source databases should be up and accessible.
After source upgrade ensure that all PK constraint must be present for all replicated tables in both source and target replication schema.

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 Journalizing Knowledge Module code used in OUA 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 an Invalid Identifier error.

If these issues of replicat abending and NOT NULL columns not being added to replication tables occur after the source upgrade, then, as a resolution, it is necessary to identify the source tables for which the NOT NULL columns have been added, and to add the columns manually. Once the columns are added, the REPLICAT process can be started again. This 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 the steps below as a resolution for the issue:

Identify the NOT NULL columns that have been newly added to the tables of the source application schema that have been replicated in OUA. 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 (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 CCB1 and the source application schema and replication schema are CISADM and CCB1REP respectively, then the query will look as below:

Copy

SELECT DTC.TABLE_NAME, DTC.COLUMN_NAME 
fROM DBA_TAB_COLUMNS@CCB1_LINK DTC,  B1_SOURCE_TABLE BST
WHERE DTC.OWNER='CISADM' AND DTC.TABLE_NAME=BST.TBL_NAME 
AND BST.PROD_FLG='CCB'  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='CCB1REP' AND DTC.TABLE_NAME=BST.TBL_NAME 
AND BST.PROD_FLG='CCB' 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 CCB1and the source application schema and replication schema are CISADM and CCB1REP respectively, then the query will look as below:

Copy

SELECT DTC.TABLE_NAME, DTC.COLUMN_NAME 
fROM DBA_TAB_COLUMNS@CCB1_LINK DTC,  B1_SOURCE_TABLE BST
WHERE DTC.OWNER='CISADM' AND DTC.TABLE_NAME=BST.TBL_NAME 
AND BST.PROD_FLG='CCB'  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='CCB1REP' AND DTC.TABLE_NAME=BST.TBL_NAME 
AND BST.PROD_FLG='CCB' AND BST.CM_REPLICATE_FLG='Y' AND DTC.COLUMN_NAME NOT LIKE 'JRN%';

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.
Once these columns have been added to the replication tables, start the abended replicat processes.
Once the replicates are up, ensure Incremental Changes (DDL & DML Changes during the upgrade) are synced.