Oracle GoldenGate 11gr2 Upgrade

This article demonstrates the steps to perform an in-place Oracle GoldenGate 11gr2 Upgrade (11.2.0.2).

Expectations and Requirements

  • There should be NO DATA LOSS.
  • There should be NO database downtime.
  • Oracle GoldenGate processes names will remain unchanged.
  • Oracle GoldenGate replication will be DOWN for the period of OGG upgrade activity.
  • Upgrade Oracle GoldenGate binaries Source and target in the same maintenance window.

Environment Details

Item Source/Primary Target/Secondary
OS Linux 64 bit Linux 64 bit
GoldenGate Version 11.1.1.0.29 11.1.1.0.29
Software Location /u01/app/11.1 /u01/app/11.1
GoldenGate Processes ext_src & dpump rep_tgt
Replication Type One-way DML One-way DML
TrailFile /u01/app/11.1/dirdat /u01/app/11.1/dirdat
DB Name srcdb tgtdb
DB Type NON-RAC NON-RAC
DB Version 11.1.0.7 11.1.0.7

A. Pre-Upgrade Tasks

Essentially there are seven pre-upgrade tasks.

1. Download the latest OGG software from support.oracle.com. In our demo we have downloaded p18322848_1121020_Linux-x86-64.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 11.2 software into a new directory.

4. Perform this step on both source and target.

$ cd /u01/app/
$ mkdir 11.2_software
$ unzip p18322848_1121020_Linux-x86-64.zip
$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

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.

SQL> create table scott.test(NAME varchar2(20), ID number (5) primary key);
Table created.

GGSCI (source) 1> dblogin userid ggs@srcdb,password oracle
Successfully logged into database.

GGSCI (source) 2> add trandata scott.test
Logging of supplemental redo data enabled for table SCOTT.TEST.

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

7. Update the Replicat parameter file on the target to include scott.test.

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 11.1 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 ext_src, LOGEND
Sending LOGEND request to EXTRACT ext_src ...
YES.
GGSCI (source) 5> send ext_src, LOGEND
Sending LOGEND request to EXTRACT ext_src ...
YES.
GGSCI (source) 6> info ext_src
EXTRACT ext_src Last Started 2014-03-10 09:22 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint Oracle Redo Logs
2014-04-16 02:36:19 Thread 1, Seqno 35671, RBA 535552
SCN 540.2935258147 (2322217597987)
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 ext_src
Sending STOP request to EXTRACT ext_src ...
Request processed.
GGSCI (source) 6> info ext_src
EXTRACT ext_src Last Started 2014-03-10 09:22 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint Oracle Redo Logs
2014-04-16 02:36:19 Thread 1, Seqno 35671, RBA 535552
SCN 540.2935258147 (2322217597987)

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 11.1 or 11.2. Make certain to include the dot.

Example: EXTTRAIL /u01/app/11.2/dirdat/es format 11.1

3. Stop the OGG Pump and Replicat processes on the source and target enviornments 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 dpump

EXTRACT dpump Last Started 2014-01-12 02:20 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint File /u01/app/11.1/dirdat/ext_src/ea000037
2014-04-16 02:35:27.000604 RBA 53062713

GGSCI (source) 8> stop dpump

Sending STOP request to REPLICAT dpump ...
Request processed.

Target
GGSCI (target) 4> info rep_tgt

REPLICAT rep_tgt Last Started 2014-01-12 02:20 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint File
/u01/app/11.1/dirdat/dpump/ra000027
2014-04-16 02:35:27.000604 RBA 53062713

GGSCI (target) 8> stop rep_tgt
Sending STOP request to REPLICAT rep_tgt ...
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 11.1 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 11.1 11.1_bkp
$ ls -l 11.1_bkp


6. Rename the 11.1 directory to 11.2 and copy the contents from 11.2_software to the new 11.2 directory. Perform this step on both source and target.

$ cd /u01/app
$ mv 11.1 11.2
$ cd 11.2
$ chmod -R u+rw *

$ cd /u01/app/11.2_software
$ cp * /u01/app/11.2/

7. Update the .bash_profile file for the GGS user with the new OGG location “/u01/app/11.2″. Perform this step on both source and target.

export GGS_HOME=/u01/app/11.2
export LD_LIBRARY_PATH = $ORACLE_HOME/lib:$ORACLE_HOME/lib32:/u01/app/11.2


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

$ cd $GGS_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 11.2 directory to /tmp or some other location.

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

$ cp /u01/app/11.2/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

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/11.1/dirdat” to “/u01/app/11.2/dirdat”.

Source:
GGSCI> dblogin userid ggs@srcdb, password oracle
GGSCI> delete EXTTRAIL /u01/app/11.1/dirdat/es
GGSCI> delete extract ext_src
GGSCI> add extract ext_src tranlog, BEGIN 2014-03-10 09:22
GGSCI> add exttrail /u01/app/11.2/dirdat/es, extract ext_src, megabytes 100
GGSCI> delete extract dpump
GGSCI> delete RMTTRAIL /u01/app/11.1/dirdat/rt
GGSCI> add extract DPUMP, exttrailsource /u01/app/11.2/dirdat/es
GGSCI> add rmttrail /u01/app/11.2/dirdat/rt, extract DPUMP, megabytes 100

Target
GGSCI> delete replicat rep_tgt
GGSCI> add replicat rep_tgt, exttrail /u01/app/11.2/dirdat/rt, checkpointtable GGS.GGS_CHECKPOINT

12. Rename or move the OLD trail files available under the /u01/app/11.2/dirdat directory so that the extract/pump starts from the new trail sequence 000000.

Source
$ cd /u01/app/11.2/dirdat/
$ mkdir bkup
$ mv es* bkup/

Target
$ cd /u01/app/11.2/dirdat/
$ mkdir bkup
$ mv et* bkup/

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 ext_src, BEING 2014-03-10 09:22
$ GGSCI> start extract ext_src
$ GGSCI> info dpump

Start the PUMP process.
$ GGSCI> Start extract dpump
$ GGSCI> info dpump

Target
$ cd $GGS_HOME
$ ./ggsci
$ GGSCI> start replicat rep_tgt
$ info rep_tgt
$ info all

C. Post Upgrade Steps

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

Connect to the source database and insert few records in scott.test table.

SQL> insert into scott.test values ('test1’,1);
SQL> insert into scott.test values ('test2’,2);
SQL> commit;
SQL> select count(*) from scott.test;

Connect to the target database and see if the records are replicated.
SQL> select count(*) from scott.test;