Perform Pre-Upgrade Steps

This page describes the pre-upgrade steps for the target database server.

Notes:

You must upgrade to the latest tech stack on 2.7.0.1.3 before performing the upgrade activities from 2.7.0.1.3 to 2.8.0.0.
Before proceeding with the prerequisite steps on the target database server, ensure you have a proper backup plan for reverting the changes in case of any failure.

Log in to Oracle Utilities Analytics ODI server and start the ODI client.
Copy
```
export JAVA_HOME=<JAVA HOME>
export PATH=$JAVA_HOME/bin:$PATH
cd <FMW Home>/odi/studio
./odi.sh
```
Note: Oracle Data Integrator Studio 12.2.1.4 is not supported in the AIX and Solaris operating systems. Install Oracle Data Integrator Studio client in either Linux or Windows machines to connect to the target database.
Stop BI_RUN_ALL. from ODI studio and follow these steps:
1. Log in to Oracle Database Integrator Studio.
2. Navigate to Topology > Scheduling > BI_RUN_ALL
3. Double-click Global/WLS_AGENT.
4. Select Inactive, and clickSave.
5. On the studio, navigate to the Topology tab. Right-click OracleDIAgent and select Update Schedule.
Run the following query from the target database server to check if all jobs are complete.
Copy
```
SELECT * FROM mdadm.b1_jobs_vw WHERE status_flg NOT IN ('D', 'E', 'RP');
```
Note: No rows should be returned from the query mentioned above. If any rows are returned, that means jobs are not yet completed. Wait until all the jobs are completed.

Stop the Oracle Data Integrator admin and managed servers.

cd <Domain Home>/<odi_domain>/bin
./stopManagedWeblogic.sh <ODI_server1>
./stopWeblogic.sh

Sync all the Oracle GoldenGate processes.
1. Connect to MDADM schema in the target Oracle Utilities Analytics database and execute the SQL mentioned below to get all the context codes.
  Copy
```
Select CONTEXT_CD from b1_prod_instance where CONTEXT_CD<>'B1';
```
2. For each context returned by the query, perform the following steps.
  1. Connect to the source database corresponding to the context code as sys user and execute the following command:
    Copy
    alter system switch logfile
  2. Connect to MDADM schema in the target Oracle Utilities Analytics database and execute the SQL mentioned below to get source GoldenGate server and Oracle GoldenGate Home on source server.
    Copy
    select DS_HOST,DS_HOME from b1_server_cfg where CONTEXT_CD='<Context_code>';
  3. Ensure that the pump processes transfer all the changes to the target server. Lag at the Chkpt and Time Since Chkpt should be 0.
  4. On the Oracle Utilities Analytics database server, ensure the replicate processes apply all changes for the context code.
    1. Connect to the MDADM schema in target database and execute the query below to get the target server and GoldenGate Home details on the target server.
      Copy
      select DS_HOST,DS_HOME, DS_FLAG from b1_server_cfg where CONTEXT_CD='B1';
    2. Connect to the target server and login to the GoldenGate prompt. Execute the commands below.
      Copy
      cd <GG_Home > ./ggsci dblogin userid MDADM,password <MDADM user password> Send replicat <Context_codeXX>,status
      Note: The current status will be at EOF. Check this for each replicat process of the context code.
  5. Ensure all extracts and replicates of all sources are in sync.

Make sure that the alert logs are enabled to capture DDL changes on replication schema after technology upgrade (before Oracle Utilities Analytics upgrade).

Check and enable auditing at target database level.

Copy

show parameter audit_trail
alter system set audit_trail=db,extended scope=spfile;

Restart the database.
Copy
```
shutdown immediate;
startup;
```
Confirmation of audit logs enabled.
Copy
```
show parameter audit_trail
```

Run the following audit commands to ONLY enable DDL auditing on schema tables.

Note: Replace Replication Owner below with the user to be audited.

Copy

AUDIT TABLE BY <Replication Owner> by ACCESS; --> This will audit "CREATE TABLE","DROP TABLE","TRUNCATE TABLE"
AUDIT INDEX BY <Replication Owner> by ACCESS; --> This will audit "CREATE INDEX","DROP INDEX","ALTER INDEX","ANALYZE INDEX"
AUDIT ALTER TABLE by <Replication Owner> BY ACCESS; --> This will audit alter table statements

Run the following query to find audited entries on the dba_audit_trail table.

Copy

select username, to_char(timestamp,'dd-mon hh24:mi') timest , owner, obj_name, action_name, sql_text from dba_audit_trail where username='<Replication Owner>';

Make sure that the ODI operator logs are available during the course of upgrade and after the upgrade.
Note: This can be ensured by increasing the log retention period to 60 days from the Global Configuration Page in Apex.
Make sure the ODI log level is set to maximum in Global Configuration.
Note: This can be ensured by increasing the ODI log level to 5 or 6 from the Global Configuration page in Apex.

Make sure that the primary keys of all replication tables are present. Use the following query to retrieve the tables that have the primary key missing.

Copy

SELECT * FROM MDADM.B1_SOURCE_TABLE S WHERE PROD_FLG = '<configured source product value>' AND BASE_REPLICATE_FLG ='Y'    AND NOT EXISTS (SELECT 1 FROM ALL_CONSTRAINTS C WHERE CONSTRAINT_TYPE ='P' AND OWNER='<replication schema name for configured source>' AND S.TBL_NAME=C.TABLE_NAME);

Example: Oracle Utilities Customer Care and Billing

Copy

SQL> SELECT *
FROM    
MDADM.B1_SOURCE_TABLE S    
WHERE PROD_FLG = 'CCB'    
AND BASE_REPLICATE_FLG ='Y'    
AND NOT EXISTS (SELECT 1 FROM ALL_CONSTRAINTS C    
WHERE CONSTRAINT_TYPE ='P'    
AND OWNER='CCB1REP'    
AND S.TBL_NAME=C.TABLE_NAME);

Note: If the primary keys are not present, recreate them as follows. Make sure to run the following steps only if the primary keys of the tables are missing.

Retrieve the keys for the tables using the following query from the source side.

Copy

SELECT
ST.TABLE_NAME , SC.COL_NAME, rank() OVER (PARTITION BY
ST.TABLE_NAME ORDER BY SKC.POS DESC) KEY_POS
FROM
<work repository schema>.SNP_KEY_COL SKC,
<work repository schema>.SNP_KEY SK,
<work repository schema>.SNP_TABLE ST,
<work repository schema>.SNP_COL SC,
<work repository schema>.SNP_MODEL SM,
(SELECT
TBL_NAME
FROM
MDADM.B1_SOURCE_TABLE S
WHERE PROD_FLG = '<configured source product value>'
AND BASE_REPLICATE_FLG ='Y'
AND NOT EXISTS (SELECT 1 FROM ALL_CONSTRAINTS C
WHERE CONSTRAINT_TYPE ='P'
AND OWNER='<replication schema name for product configured>'
AND S.TBL_NAME=C.TABLE_NAME)) TAB_LIST
WHERE SKC.I_KEY =SK.I_KEY
AND SK.I_TABLE=ST.I_TABLE
AND SKC.I_COL=SC.I_COL
AND ST.I_MOD=SM.I_MOD
AND SM.COD_MOD LIKE '%REP%'
AND ST.TABLE_NAME= TAB_LIST.TBL_NAME AND SK.CONS_TYPE='PK';

Example: Oracle Utilities Customer Care and Billing

Copy

SELECT
ST.TABLE_NAME , SC.COL_NAME, rank() OVER (PARTITION BY
ST.TABLE_NAME ORDER BY SKC.POS DESC) KEY_POS
FROM
OUA_WORK.SNP_KEY_COL SKC,
OUA_WORK.SNP_KEY SK,
OUA_WORK.SNP_TABLE ST,
OUA_WORK.SNP_COL SC,
OUA_WORK.SNP_MODEL SM,
(SELECT
TBL_NAME
FROM
MDADM.B1_SOURCE_TABLE S
WHERE PROD_FLG = 'CCB'
AND BASE_REPLICATE_FLG ='Y'
AND NOT EXISTS (SELECT 1 FROM ALL_CONSTRAINTS C
WHERE CONSTRAINT_TYPE ='P'
AND OWNER='CCB1REP'
AND S.TBL_NAME=C.TABLE_NAME)) TAB_LIST
WHERE SKC.I_KEY =SK.I_KEY
AND SK.I_TABLE=ST.I_TABLE
AND SKC.I_COL=SC.I_COL
AND ST.I_MOD=SM.I_MOD
AND SM.COD_MOD LIKE '%REP%'
AND ST.TABLE_NAME= TAB_LIST.TBL_NAME AND SK.CONS_TYPE='PK';

After creating the table list using the query, create the primary key.

Copy

ALTER TABLE <replication schema name>.<table name>
ADD CONSTRAINT <constraint name> PRIMARY KEY (<column list-separated by comma>);

Example:

Copy

SQL> ALTER TABLE CCB1REP.C1_USAGE ADD CONSTRAINT SYS_C0021279 PRIMARY KEY (USAGE_ID);

Result: The table is altered.