Oracle GoldenGate upgrade 19c

A. Pre-Upgrade Tasks

1. Download the latest OGG software from support.oracle.com. In our demo we have downloaded Oracle_GoldenGate_19.1.0.0.4.zip for Linux 64 bit.

2. Copy the OGG software to the source and target servers under “/u01/app” or a directory of your choice.

3. Unzip and Extract (or untar) the OGG 19c software into a new directory.

4. Perform this step on both source and target.

$ cd /u01/app/
$ mkdir 19.1_soft
$ unzip Oracle_GoldenGate_19.1.0.0.4.zip
$ cd fbo_ggs_Linux_x64_shiphome
$./runInstaller

5. Create a test table on the source database which will be used to check if the replication is working fine after the OGG upgrade.

On Source and target database, create a table GGEMP under GGBI.

SQL> create table ggemp
(emp_id number,
emp_name_name varchar2(20),
mgr number,
last_dml timestamp default systimestamp);

SQL> alter table ggemp add constraint pk_ggemp primary key (emp_id) ;

Table altered.

SQL> grant all on ggemp to gg_admin;

Grant succeeded.

GGSCI (source) 1> dblogin useridalias ggadminweb
Successfully logged into database.

GGSCI (source) 2> add trandata ggbi.ggemp
Logging of supplemental redo data enabled for table GGBI.GGEMP.

6. Update the Extract/Pump parameter files on the source to include the ggbi.ggemp table as part of replication.

7. Update the Replicat parameter file on the target to include ggbi.ggemp.

B. Upgrade Steps

1. Stop the OGG monitoring (crontab) on both source and target to avoid unnecessary alerts and pages.

2. On the source, stop the OGG 12.3.2 extract process, use the LOGEND command and make a note of the stop timestamp. This timestamp will be used to re-position the extract to pickup the transactions from the archive or the redo logs, generated during the OGG upgrade.

GGSCI (source) 4> send EXOMTWEB, LOGEND
Sending LOGEND request to EXTRACT EXOMTWEB …
YES.
GGSCI (source) 5> send EXOMTWEB, LOGEND
Sending LOGEND request to EXTRACT EXOMTWEB …
YES.
GGSCI (source) 6> info EXOMTWEB
EXTRACT EXOMTWEB Last Started 2020-02-20 07:41 Status RUNNING
Checkpoint Lag 00:00:03 (updated 00:00:08 ago)
Process ID 948
Log Read Checkpoint Oracle Integrated Redo Logs
2020-02-20 07:45:19
SCN 21.1015791929 (91210105145)

See if the RBA # change stop which means Extract completed the last transaction log and No longer do extract.
Now stop extract process. Always stop the extract process first.
GGSCI (source) 7> stop EXOMTWEB
Sending STOP request to EXTRACT EXOMTWEB …
Request processed.
GGSCI (source) 6> info EXOMTWEB

EXTRACT EXOMTWEB Last Started 2020-02-20 07:41 Status STOPPED
Checkpoint Lag 00:00:06 (updated 00:00:13 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2020-02-20 07:46:16
SCN 21.1015794797 (91210108013)

NOTE: (Source system if not upgrading Replicat) If you are not upgrading Replicat on the target systems at this time, add the following parameter to the Extract parameter file to specify the version of Oracle GoldenGate that is running on the target. This parameter causes Extract to write a version of the trail that is compatible with the older version of Replicat. Before making changes to the Extract parameter file, make sure you have a backup of the Oracle GoldenGate parameter files.
{EXTTRAIL | RMTTRAIL} file_name FORMAT RELEASE major.minor

Where version specifies an Oracle GoldenGate release version. The major value is the major version number, and minor is the minor version number, such as 12.3. Make certain to include the dot.

Example: EXTTRAIL /u01/app/ggdata19/dirdat/AA format 12.3

3. Stop the OGG Pump and Replicat processes on the source and target environments respectively. Wait for some time and make sure that there is NO LAG at the PUMP and the REPLICAT processes. Then stop PUMP and REPLICAT processes.

Source
GGSCI (source) 4> info PUOMTWEB

EXTRACT PUOMTWEB Last Started 2020-02-20 07:41 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 951
Log Read Checkpoint File /acfsmounts/ggvol01/ggdata2/dirdat/AA000000027
2020-02-20 07:41:42.354445 RBA 1527

GGSCI (source) 8> stop PUOMTWEB

Sending STOP request to REPLICAT PUOMTWEB …
Request processed.

Target
GGSCI (target) 4> info RPOMTWEB

REPLICAT RPOMTWEB Last Started 2020-02-20 07:41 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Process ID 953
Log Read Checkpoint File /acfsmounts/ggvol01/ggdata2/dirdat/AP000000028
2020-02-20 07:41:42.354554 RBA 1664

GGSCI (target) 8> stop RPOMTWEB
Sending STOP request to REPLICAT RPOMTWEB …
Request processed.

4. Stop the OGG Manager Process on the source and target.

Source
GGSCI (source) 3> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y
Sending STOP request to MANAGER …
Request processed.
Manager stopped.
Target:
GGSCI (target) 3> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y
Sending STOP request to MANAGER …
Request processed.
Manager stopped.
5. Backup the current OGG ggdata2 directory. Make sure that you have sufficient space before making a copy. Perform this step on both source and target.

$ cd /u01/app
$ df -h
$ cp -pR ggdata2/ ggdata12_3_bkp/
$ ls -l ggdata12_3_bkp/

6. Rename the ggdata2 directory to ggdata19 and copy the contents from ggdata19_software to the new ggdata19 directory. Perform this step on both source and target.

$ cd /u01/app
$ mv ggdata2 ggdata19
$ cd ggdata19
$ chmod -R u+rw *
$ cd /acfsmounts/ggvol01/19.1_soft
[oracle@prod01 19.1_soft]$ cp -rp * /acfsmounts/ggvol01/ggdata19/

7. Update the .bash_profile file for the GGS user with the new OGG location “/acfsmounts/ggvol01/ggdata19”. Perform this step on both source and target.

export GG_HOME=/acfsmounts/ggvol01/ggdata19
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/acfsmounts/ggvol01/ggdata19

8. Start the Oracle GoldenGate Manager process on both the source and target.

$ cd $GG_HOME
$ ./ggsci
$ GGSCI> start mgr
Repeat the above steps for target as well.
9. Create or Re-create the CHECKPOINT TABLE in the target database.
Copy the script “chkpt_ora_create.sql” from ggdata19 directory to /tmp or some other location.

Execute the script by connecting as SYSTEM or any other DBA privileged user account.

$ cp /u01/app/ggdata19/chkpt_ora_create.sql /tmp/
SQL> @/tmp/chkpt_ora_create.sql
10. Upgrade the CHECKPOINT TABLE by loging into the target database from the GGSCI prompt.
$ cd $GGS_HOME
$ ./ggsci
$ GGSCI> dblogin userid ggs@srcdb, password oracle
$ GGSCI> upgrade checkpointtable ggs_checkpoint
$ GGSCI> upgrade checkpointtable ggs_checkpoint_lox

GGSCI (prod01.oracle.com as admingg@WEBQA12) 6> UPGRADE CHECKPOINTTABLE admingg.CKPTAB

***
Upgrade operation was not performed because the checkpoint table admingg.CKPTAB is already upgraded.

***

11. Re-create the OGG processes and trails. Create the extract process to BEGIN at the timestamp captured in the previous steps. The trail files need to be generated as the trail file location has got changed from “/u01/app/ggdata2/dirdat” to “/u01/app/ggdata19/dirdat”.
Source:

GGSCI (prod01.oracle.com as admingg@WEBQA12) 7> info EXOMTWEB

EXTRACT EXOMTWEB Last Started 2020-02-20 07:41 Status STOPPED
Checkpoint Lag 00:00:06 (updated 00:45:42 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2020-02-20 07:46:16
SCN 21.1015794797 (91210108013)

GGSCI (prod01.oracle.com as admingg@WEBQA12) 8> view param EXOMTWEB

EXTRACT EXOMTWEB
SETENV (ORACLE_HOME = ‘/usr/local/oracle/12.2.0.1’)
–SETENV (ORACLE_SID = ‘WEBQA1’)
USERIDALIAS ggadminweb
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
EXTTRAIL /acfsmounts/ggvol01/ggdata2/dirdat/AA
–==========================================================================
— KKANNAPPAN
–==========================================================================
TABLE KKANNAPPAN.*;
TABLE ggbi.ggemp;

GGSCI> dblogin useridalias ggadminweb
GGSCI (prod01.oracle.com as admingg@WEBQA12) 14> delete EXTTRAIL /acfsmounts/ggvol01/ggdata2/dirdat/AA
Deleting extract trail /acfsmounts/ggvol01/ggdata2/dirdat/AA for extract EXOMTWEB

GGSCI (prod01.oracle.com as admingg@WEBQA12) 16> delete extract EXOMTWEB
Deleted EXTRACT EXOMTWEB.

GGSCI (prod01.oracle.com as admingg@WEBQA12) 17> add extract EXOMTWEB, integrated tranlog, BEGIN 2020-02-20 07:41, THREADS 2
EXTRACT added.

GGSCI (prod01.oracle.com as admingg@WEBQA12) 18> add exttrail /acfsmounts/ggvol01/ggdata19/dirdat/AA, extract EXOMTWEB, megabytes 100
EXTTRAIL added.

GGSCI (prod01.oracle.com as admingg@WEBQA12) 10> info PUOMTWEB

EXTRACT PUOMTWEB Last Started 2020-02-20 07:41 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:46:08 ago)
Log Read Checkpoint File /acfsmounts/ggvol01/ggdata2/dirdat/AA000000027
2020-02-20 07:41:42.354445 RBA 1527

GGSCI (prod01.oracle.com as admingg@WEBQA12) 11> view param PUOMTWEB

EXTRACT PUOMTWEB
SETENV (ORACLE_HOME = ‘/usr/local/oracle/12.2.0.1’)
–SETENV (ORACLE_SID = ‘WEBQA1’)
USERIDALIAS ggadminweb
RMTHOST 10.1.40.197, MGRPORT 7809
RMTTRAIL /acfsmounts/ggvol01/ggdata2/dirdat/AP
–==========================================================================
TABLE KKANNAPPAN.*;
TABLE ggbi.ggemp;

GGSCI> delete RMTTRAIL /acfsmounts/ggvol01/ggdata2/dirdat/AP

GGSCI> delete extract PUOMTWEB
Deleted EXTRACT PUOMTWEB.

GGSCI (prod01.oracle.com as admingg@WEBQA12) 22> add extract PUOMTWEB, exttrailsource /acfsmounts/ggvol01/ggdata19/dirdat/AA
EXTRACT added.

GGSCI (prod01.oracle.com as admingg@WEBQA12) 23> add rmttrail /acfsmounts/ggvol01/ggdata19/dirdat/AP, extract PUOMTWEB, megabytes 100
RMTTRAIL added.

Target :
********

GGSCI (prod01.oracle.com as admingg@WEBQA12) 24> dblogin USERIDALIAS ggadminwebqa2
Successfully logged into database.

GGSCI (prod01.oracle.com as admingg@WEBQA12) 12> info RPOMTWEB

REPLICAT RPOMTWEB Last Started 2020-02-20 07:41 Status STOPPED
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:47:07 ago)
Log Read Checkpoint File /acfsmounts/ggvol01/ggdata2/dirdat/AP000000028
2020-02-20 07:41:42.354554 RBA 1664

GGSCI (prod01.oracle.com as admingg@WEBQA12) 13> view param RPOMTWEB

REPLICAT RPOMTWEB
SETENV (ORACLE_HOME = ‘/usr/local/oracle/12.2.0.1’)
–SETENV (ORACLE_SID = ‘WEBQA2’)
–SOURCE (XOMT – WEBQA1)
AssumeTargetDefs
–SourceDefs /acfsmounts/ggvol01/ggdata2/dirdef/OTC_COS
DiscardFile /acfsmounts/ggvol01/ggdata2/dirrpt/rpomtweb.dsc, Purge, megabytes 2147
USERIDALIAS ggadminwebqa2
DBOPTIONS SETTAG 0935
REPERROR (-1, DISCARD)
–=====================================================================================================================
— KKANNAPPAN
–=====================================================================================================================
MAP KKANNAPPAN.*, TARGET KKANNAPPAN.*;
MAP ggbi.ggemp, TARGET ggbi.ggemp;

GGSCI (prod01.oracle.com as admingg@WEBQA22) 25> delete replicat RPOMTWEB

2020-02-20 08:50:13 INFO OGG-02529 Successfully unregistered REPLICAT RPOMTWEB inbound server OGG$RPOMTWEB from database.

2020-02-20 08:50:13 INFO OGG-14052 No Heartbeat entries with [RPOMTWEB], none deleted.
Deleted REPLICAT RPOMTWEB.

GGSCI (prod01.oracle.com as admingg@WEBQA22) 26> add replicat RPOMTWEB, exttrail /acfsmounts/ggvol01/ggdata19/dirdat/AP, checkpointtable ADMINGG.CKPTAB
REPLICAT added.

12. Rename or move the OLD trail files available under the /acfsmounts/ggvol01/ggdata19/dirdat directory so that the extract/pump starts from the new trail sequence 000000.

Source
$ cd /acfsmounts/ggvol01/ggdata19/dirdat
$ mkdir bkup_ext
$ mv AA* bkup_ext

Target
$ cd /acfsmounts/ggvol01/ggdata19/dirdat
$ mkdir bkup_rep
$ mv AP* bkup_rep

13. Start the OGG Extract/Pump processes on the source and replicat process on the target.

Source
If you have NOT already created extract process to BEGIN at the stop timestamp captured in previous steps you can now alter the extract to BEGIN at stop timestamp captured earier. Finally start the extract process.

$ cd $GGS_HOME
$ ./ggsci
$ GGSCI> alter extract EXOMTWEB, BEGIN NOW
$ GGSCI> start extract EXOMTWEB
$ GGSCI> info PUOMTWEB
Start the PUMP process.
$ GGSCI> Start extract PUOMTWEB
$ GGSCI> info PUOMTWEB

Target
$ cd $GG_HOME
$ ./ggsci
$ GGSCI> start replicat RPOMTWEB
$ info RPOMTWEB
$ info all

C. Post Upgrade Steps

After the successful upgrade of OGG to ggdata19, we can now test to see if the replication is working fine.

Connect to the source database and insert few records in ggbi.ggemp table.

SQL> SQL> insert into ggemp values (7,’DB2′,106,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ggemp;

EMP_ID EMP_NAME_NAME MGR
———- ——————– ———-
LAST_DML
—————————————————————————
1 goldengate 100
19-FEB-20 03.33.40.000000 PM

2 oracle 101
19-FEB-20 03.42.54.000000 PM

5 SQL 104
20-FEB-20 06.37.21.000000 AM

EMP_ID EMP_NAME_NAME MGR
———- ——————– ———-
LAST_DML
—————————————————————————
3 bigdata 102
20-FEB-20 09.20.41.000000 AM

6 Postgres 105
20-FEB-20 09.23.34.000000 AM

7 DB2 106
20-FEB-20 09.33.42.000000 AM

6 rows selected.

Connect to the target database and see if the records are replicated.