OracleDBAtalk: gg upgrade

OGG Upgrade steps on system hosting Goldengate during DB upgrade (BPEL)

The DB upgrade is happening on source BPEL flph1168 server. DB currently is on 11g. Its getting upgraded to 19c. OGG version 12.3.0.1.4 is being used. We also will be upgrading OGG to 19c on BPEL flph1168 server. We need to install OGG version 19c for 19c database. Hence we need to follow below steps:

Source 1: BPEL

11g DB and OGG 12.3.0.1.4 – Here DB and GG will be upgraded to 19c

flph1168.ffdc.sbc.com

sudo -iu p1nop1d1g

DB is in 11.2.0.4 now and is upgrading to 19c

To connect ot DB:

voyagerdb.snt.bst.bls.com

sudo -iu oracle

sqlplus ggsuser/right14time@p1nop1d1.DB.ATT.COM

Target: One replicat running

mlph638.sfdc.sbc.com

sudo su - p2can1d7g

./ggsci

Steps on Source flph1168(OGG 11g home):

Once application shutdown is completed:

cd /opt/app/p1nop1d1/ggs/12.3.0.1.4

./ggsci

Stop Extract , pump processes:

SEND EP1NOP1D,logend

Run above until u get YES.

STOP EP1NOP1D

Start EP1NOP1D

SEND EP1NOP1D,logend

Run above until u get YES.

STATS EP1NOP1D

STOP EP1NOP1D

SEND PNLPH636 LOGEND

Run above until u get YES.

STOP PNLPH636

START PNLPH636

SEND PNLPH636 LOGEND

STATS PNLPH636

STOP PNLPH636

Dblogin userid ggsuser@p1nop1d1.DB.ATT.COM, password right14time

Info EP1NOP1D,detail

Info PNLPH636,detail

Stop mgr !

exit

‘

Steps on Target mlph638:

Stop Replicat RCANOPI:

SEND RCANOPI,logend

Run above until u get YES.

STOP RCANOPI

Start RCANOPI

SEND RCANOPI,logend

Run above until u get YES.

STATS RCANOPI

STOP RCANOPI

cd /opt/app/p2can1d7/ggs/12.3.0.1.4

./ggsci

Info RCANOPI,detail

4. on source flph1168, Take the backup of existing Goldengate home:

mv /opt/app/p1nop1d1/ggs/12.3.0.1.4 /opt/app/p1nop1d1/ggs/12.3.0.1.4_old

5. Ask SDBAs to Install OGG 19.1.0.0.4 (ogg_191004 present in /orasoft/stage/linux_x86_64bit) under /opt/app/p1nop1d1/ggs/19.1.0.0.4 (Choose appropriate DB version during installation) .

Get 770 permission on 19.1.0.0.4

chmod 770 19.1.0.0.4

6. Upgrade database.

(from the new GG HOME)(19c OGG home):

Ask SDBA to run below:

cd /opt/app/p1nop1d1/ggs/19.1.0.0.4

1. Connect to DB as sysdba:

SQL>@ulg.sql

This script converts the existing supplemental log groups to the new format required by the new release.

This step is required even if you plan to use the new triggerless capture method.

The script should run without error; if errors occur, contact Oracle Support.

2. Verify DB parameter(SDBA tasks)

Alter system set enable_goldengate_replication=TRUE;

execute dbms_goldengate_auth.grant_Admin_privilege(‘GGSUSER’);

select CAPTURE_NAME, STATUS from dba_capture;

alter database add supplemental log data(all) columns;

alter database force logging;

Once SDBA gives us go ahead proceed with below:

cd /opt/app/p1nop1d1/ggs/19.1.0.0.4

7. Copy goldengate process related files from old goldengate home (/opt/app/p1nop1d1/ggs/12.3.0.1.4_old) to the new 19.1.0.0.4 goldengate home (/opt/app/p1nop1d1/ggs/19.1.0.0.4) like .prm in appropriate directories. Also copy dirdef folder from old to new 19c OGG home.

Below needs to be done in 19.1.0.0.4 OGG HOME and after DB upgrade is done:

mkdir -p /opt/app/p1nop1d1/ggs/19.1.0.0.4/admin

mkdir -p /opt/app/p1nop1d1/ggs/19.1.0.0.4/trails

cp -R /opt/app/p1nop1d1/ggs/12.3.0.1.4_old/admin/* /opt/app/p1nop1d1/ggs/19.1.0.0.4/admin/

cp -R /opt/app/p1nop1d1/ggs/12.3.0.1.4_old/dirdef/* /opt/app/p1nop1d1/ggs/19.1.0.0.4/dirdef

cp -R /opt/app/p1nop1d1/ggs/12.3.0.1.4_old/dirprm/* /opt/app/p1nop1d1/ggs/19.1.0.0.4/dirprm

cp /opt/app/p1nop1d1/ggs/12.3.0.1.4_old/GLOBALS /opt/app/p1nop1d1/ggs/19.1.0.0.4/GLOBALS

cp -R /opt/app/p1nop1d1/ggs/12.3.0.1.4_old/dirchk/* /opt/app/p1nop1d1/ggs/19.1.0.0.4/dirchk

--cp -R /opt/app/p1nop1d1/ggs/12.3.0.1.4_old/trails/* /opt/app/p1nop1d1/ggs/19.1.0.0.4/trails

--mv /opt/app/p1nop1d1/ggs/19.1.0.0.4/BR /opt/app/p1nop1d1/ggs/19.1.0.0.4/BR_bkp

--cp -R /opt/app/p1nop1d1/ggs/12.3.0.1.4_old/BR /opt/app/p1nop1d1/ggs/19.1.0.0.4/BR

cp -R /opt/app/p1nop1d1/ggs/12.3.0.1.4_old/dirwlt/* /opt/app/p1nop1d1/ggs/19.1.0.0.4/dirwlt

cp -R /opt/app/p1nop1d1/ggs/12.3.0.1.4_old/dircrd/* /opt/app/p1nop1d1/ggs/19.1.0.0.4/dircrd

cp /opt/app/p1nop1d1/ggs/12.3.0.1.4_old/ENCKEYS /opt/app/p1nop1d1/ggs/19.1.0.0.4/ENCKEYS

Rename the param files:

cd /opt/app/p1nop1d1/ggs/19.1.0.0.4/dirprm

mv mgr.prm mgr.prm_30072023

mv mgr.prm_prodday mgr.prm

mv ep1nop1d.prm ep1nop1d.prm_30072023

mv ep1nop1d.prm_prodday ep1nop1d.prm

mv pnlph636.prm pnlph636.prm_30072023

mv pnlph636.prm_prodday pnlph636.prm

cd /opt/app/p1nop1d1/ggs/19.1.0.0.4

./ggsci

--Add credentialstore

./ggsci

Add credentialstore

Alter credentialstore add user ggsuser@p1nop1d1.DB.ATT.COM, password right14time,alias ggsuser

Dblogin useridalias ggsuser

Start mgr

Dblogin useridalias ggsuser

Delete extract EP1NOP1D

Delete extract PNLPH636

Dblogin useridalias ggsuser

add trandata IDIS_OMG.UA_HISTORY,allcols

add trandata IDIS_OMG.UA_PREFERENCE,allcols

add trandata IDIS_OMG.UA_CONTACT_INFO,allcols

add trandata IDIS_OMG.UA_ENTITY,allcols

add trandata IDIS_OMG.UA_ENTITY_INFO,allcols

add trandata IDIS_OMG.UA_GROUPS_TO_PERMISSIONS,allcols

add trandata IDIS_OMG.UA_PERMISSION,allcols

add trandata IDIS_OMG.UA_USERS_TO_GROUPS,allcols

add trandata IDIS_OMG.AI_ABSTRACT_AI,allcols

add trandata IDIS_OMG.AI_CLASS_PROPERTIES,allcols

add trandata IDIS_OMG.AI_IDIS_CONTEXT,allcols

add trandata IDIS_OMG.AI_IDIS_ORDER_AI,allcols

add trandata IDIS_OMG.CNL_TECHNICAL_ORDER_VERSION,allcols

add trandata IDIS_OMG.EVC_ORDER_TO_UNI_SITE,allcols

add trandata IDIS_OMG.EVC_TECHNICAL_ORDER_VERSION,allcols

add trandata IDIS_OMG.EXTERNAL_INTERFACE_HISTORY,allcols

add trandata IDIS_OMG.LINK_DATA,allcols

add trandata IDIS_OMG.LINK_ORDER_TO_LINK_DATA,allcols

add trandata IDIS_OMG.PROJECT_ORDER,allcols

add trandata IDIS_OMG.PROJECT_ORDER_VERSION,allcols

add trandata IDIS_OMG.PROJECT_TECH_ORDER_VERSION,allcols

add trandata IDIS_OMG.TECHNICAL_ORDER,allcols

add trandata IDIS_OMG.TECHNICAL_ORDER_VERSION,allcols

add trandata IDIS_OMG.UNI_PROJECT_ORDER_VERSION,allcols

add trandata IDIS_OMG.UNI_SITE,allcols

add trandata IDIS_OMG.UNI_TECHNICAL_ORDER_VERSION,allcols

add trandata IDIS_OMG.EDW_CACHE,allcols

add trandata IDIS_OMG.LAG_TECHNICAL_ORDER_VERSION,allcols

add trandata IDIS_OMG.LAG_PROJECT_ORDER_VERSION,allcols

add trandata IDIS_OMG.INL_TECHNICAL_ORDER_VERSION,allcols

add trandata IDIS_OMG.INL_PROJECT_ORDER_VERSION,allcols

add trandata IDIS_AVOS.AEB4PTASK,allcols

add trandata IDIS_AVOS.AEB4PTBY,allcols

add trandata IDIS_RPT.MERGEAVOS,allcols

add trandata IDIS_OMG.OMX_EVC_REQ,allcols

Add extract EP1NOP1D,integrated tranlog,begin now

Add exttrail /opt/app/p1nop1d1/ggs/19.1.0.0.4/trails/ct,extract EP1NOP1D

Add extract PNLPH636,exttrailsource /opt/app/p1nop1d1/ggs/19.1.0.0.4/trails/ct

Add rmttrail /opt/app/p2can1d7/ggs/12.3.0.1.4/trails/p1cnp1d1/rt,extract PNLPH636

SQL>

select CAPTURE_NAME, STATUS from dba_capture;

CAPTURE_NAME

--------------------------------------------------------------------------------

STATUS

--------

OGG$CAP_EP1NOP1D

ENABLED

Make necessary changes in param file. Ensure to add FORMAT RELEASE parameters in EXTRACT and Pump Param file:

View param mgr

view param EP1NOP1D

view param PNLPH636

Dblogin useridalias ggsuser

Start EP1NOP1D

Start PNLPH636

Note: Check on taget mlph638 to confirm if they are receiving the trail files

On Target mlph638:

cd /opt/app/p2can1d7/ggs/12.3.0.1.4

./ggsci

Alter rcanopi extseqno 0,extrba 0

Start rcanopi

___Release the system here if we are running out of time

14. After ensuring all goldengate processes are running, start the applications accessing the database.

15. Update the profile to point to new GG home on flph1168:

mv /opt/app/p1nop1d1/p1nop1d1g/.profile /opt/app/p1nop1d1/p1nop1d1g/.profile_old

mv /opt/app/p1nop1d1/p1nop1d1g/.profile_prodday /opt/app/p1nop1d1/p1nop1d1g/.profile

exit

sudo -iu p1nop1d1g

pwd

15. Edit Manager Param file and add AUTOSTART and AUTORESTART parameter as per old home and also use PURGEOLDEXTRACTS command to point to new home

16. Take backup of crontab and update crontab entries with new OGG Home:

UnComment out any autostart and autorestart parameters in manager parameter file in both sources.

Restart mgr after Step 15

17. Build the dictionary. Copy the job from CIM/OL

CREATE OR REPLACE procedure GGSUSER.Dictionary_Build is

cdate date;

begin

dbms_output.enable(10000000);

select sysdate into cdate from dual;

DBMS_LOGMNR_D.BUILD(options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

select sysdate into cdate from dual;

end;

BEGIN

SYS.DBMS_SCHEDULER.CREATE_JOB

(

job_name => 'GGSUSER.DICTIONARY_BUILD_JOB'

,start_date => TO_TIMESTAMP_TZ('2023/07/31 03:30:00.000000 -04:00','yyyy/mm/dd hh24:mi:ss.ff tzr')

,repeat_interval => 'FREQ=DAILY; BYHOUR=23;BYMINUTE=30;'

,end_date => NULL

,job_class => 'DEFAULT_JOB_CLASS'

,job_type => 'STORED_PROCEDURE'

,job_action => 'Dictionary_Build'

,comments => 'OSD GGS Dictionary'

);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE