Oracle GoldenGate simple hands-on demo

I was working on a project where it involves use of Oracle GoldenGate for data migration from one database to another. We can term this project as cross platform data replication (migration and upgrade if done for whole database) as both the source and target system and database versions are different to each other.
I would like to show a simple demo on setting up Oracle GoldenGate software and configuring the same to use for migration or replication of data between databases (Oracle in our case) where as Oracle GoldenGate can be used for heterogeneous platform as well.

The post consists of the following
1. Environment details
2. GoldenGate installation
3. Prerequisites and Oracle GoldenGate software set up
4. Enabling TRANDATA
5. Param files set up
6. The GoldenGate process (Commands and Explanations)
7. Real time sample demo

Now let's get into the details.

1. Environment details

Source

Oracle 9.2.0.7.0 on SunOS operating system

OGG_source $ uname -sr
SunOS 5.10

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 – Production

Destination

Oracle 11.2.0.4 on Linux 2.6.32-358.0.1.el6.x86_64

OGG_target $ uname -sr
Linux 2.6.32-358.0.1.el6.x86_64

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

GoldenGate software used

Source: Oracle GoldenGate Version 11.1.1.1 13

Destination: Oracle GoldenGate Version 11.2.1.0.17

2. GoldenGate Installation

For GoldenGate installation please see Oracle GoldenGate Installation

3. Prerequisites and Oracle GoldenGate software set up

3.1 Prerequisites
As a prerequisite for enabling and utilizing OGG, archive log should be enabled in the source database. If bidirectional, both the database should run in ARCHIVELOG mode.

For enabling Archivelog mode, see Enabling Archivelog

Once Archivelog has been enabled, enable supplemental logging in source by issuing the below command

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

3.2 OGG software setup
The following has to be performed on source and target databases to set up GG owner and create/grant required privileges and roles required to perform GG activities.
On Source:

export ORACLE_BASE=/r01/app/oracle/
export ORACLE_HOME=/r01/app/oracle/product/9.2.0
export PATH=$PATH:$ORACLE_HOME/bin:/backup/PRNETSDB/ogg/11.1
export ORACLE_SID=PRNETSDB
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SHLIB_PATH=$ORACLE_HOME/lib
export GGH=/backup/PRNETSDB/ogg/11.1

$ cd $GGH
$ ggsci

ggsci> create subdirs

All the GoldenGate required directories will be created under GoldenGate Home directory

SQL> create user ggs_dba identified by ggs_dba default tablespace TOOLS temporary tablespace TEMP profile DEFAULT;
SQL> grant CONNECT to ggs_dba;
SQL> grant SELECT ANY TABLE to ggs_dba; -- This can be restricted to tables that are only involved
SQL> grant ALTER SESSION to ggs_dba;

Change to the OGG HOME (GGH) directory and execute the scripts.

cd $GGH

-- Run the following as SYSDBA user with input "GGS_DBA"

SQL> @marker_setup.sql -- creates GGS_DDL_HIST table under GGS_DBA for DDL replication
SQL> @ddl_setup.sql -- To support DDL replication
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggs_dba;
SQL> @ddl_enable.sql -- Enables GGS trigger

Change to dirprm directory which was created earlier and perform the below

cd $GGH/dirprm
vi mgr.prm
PORT 15000
DYNAMICPORTLIST 15010-15020
USERID GGS_DBA, PASSWORD ggs_dba

ggsci> start manager

ggsci>

On Target:

export ORACLE_BASE=/ne01/app/oracle
export ORACLE_HOME=/ne01/app/oracle/product/11.2.0.4
export PATH=$PATH:$ORACLE_HOME/bin:/backup/gg_kt/ogg/11.2
export ORACLE_SID=STNETSDB
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/backup/gg_kt/ogg/11.2
export SHLIB_PATH=$ORACLE_HOME/lib
export GGH=/backup/gg_kt/ogg/11.2

$ cd GGH
$ ggsci

ggsci> create subdirs

All the GoldenGate required directories will be created under GoldenGate Home directory

SQL> create user ggs_dba identified by ggs_dba default tablespace TOOLS temporary tablespace TEMP profile DEFAULT;
SQL> grant CONNECT to ggs_dba;
SQL> grant SELECT ANY TABLE to ggs_dba; -- This can be restricted to tables that are only involved
SQL> grant ALTER SESSION to ggs_dba;
-- Grant insert, update, delete privilege on tables replicated
SQL> grant insert, delete, update on [list of tables] to ggs_dba;

Or grant Oracle GoldenGate user DBA role till the migration activity is complete and then revoke the grant if required.

Change to the OGG HOME (GGH) directory and execute the scripts.

cd $GGH

Run the following as SYSDBA user with input "GGS_DBA"

SQL> @marker_setup.sql -- creates GGS_DDL_HIST table under GGS_DBA for DDL replication
SQL> @ddl_setup.sql -- To support DDL replication
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggs_dba;
SQL> @ddl_enable.sql -- Enable GGS trigger

Change to dirprm directory which was created earlier and perform the below

cd $GGH/dirprm
vi mgr.prm
PORT 12000
DYNAMICPORTLIST 12010-12020
USERID GGS_DBA, PASSWORD ggs_dba
cd $GGH
ggsci
ggsci 1> start manager
ggsci 2> edit params ./GLOBALS -- This creates a vi file named GLOBALS* under the current directory
GGSCHEMA ggs_dba 
CHECKPOINTTABLE ggs_dba.checkpoint
:wq – to save the file
ggsci 3> dblogin userid ggs_dba Password ggs_dba
ggsci 4> add checkpointtable ggs_dba.checkpoint

Enable ENABLE_GOLDENGATE_REPLICATION process as this is a mandatory step if the database is of Oracle version 11g and above.

SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

* To learn more about GLOBALS and other parameter files, refer this oracle documentation link

4. Enabling TRANDATA

Enabling TRANDATA (transaction data) is explained in Enable TRANDATA
Once the TRANDATA is enabled for individual tables, then the parameter files can be created

5. Param files set up

All the parameters set up required for our demo is given in this link Simple parameter files for Oracle GoldenGate
Upon completion of Parameter files creation, we can start the Oracle GoldenGate replication process.

6. The GoldenGate process (Commands and Explanations)

Please see this link OGG step by step process for the Oracle GoldenGate commands, step by step processes and their simple explanations.
Thus we have completed our Oracle GoldenGate set up and replication.

7. Real time sample demo

As we have completed the Oracle GoldenGate replication between 2 databases of different versions and platforms, the real time demo shown will be of much useful in understanding the process better.

Please see here for the demo - Oracle GoldenGate sample demo.

Happy Replicating!!

OGG sample real time demo

This post gives a real time demo of Oracle GoldenGate replication. For complete installation and set up, see this link

Terminologies used
OGG_source $ -- refers commands executed from source server
OGG_target $ -- refers commands executed from target server
SQL@OGG_source > -- refers commands executed from source SQLPLUS terminal
SQL@OGG_target > -- refers commands executed from target SQLPLUS terminal
GGSCI (source) -- refers commands executed from source GGSCI terminal
GGSCI (target) -- refers commands executed from target GGSCI terminal

At line 69, the initial extract is started from OS as a background process. The reason being the initial extract might run for hours depending on the amount of data it has to unload from the source database and hence to let it run without any issues till it completes, we are starting the process as a background process.
Also we are grepping the start time of the extract at line 74 to use it at a later time in the replication operation. See line 245 where this captured time is used.

OGG_source $ cd $GGH
OGG_source $ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 13067027
Solaris, sparc, 64bit (optimized), Oracle 9i on Oct 14 2011 08:02:21

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.



GGSCI (source) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING


GGSCI (source) 2> ADD EXTRACT DEMO1E, TRANLOG, THREADS 2, BEGIN NOW
EXTRACT added.


GGSCI (source) 3> ADD EXTTRAIL /backup/PRNETSDB/ogg/11.1/dirdat/ea, EXTRACT DEMO1E, Megabytes 200
EXTTRAIL added.


GGSCI (source) 4> start extract DEMO1E

Sending START request to MANAGER ...
EXTRACT DEMO1E starting


GGSCI (source) 5> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DEMO1E      00:00:07      00:00:06


GGSCI (source) 6> ADD EXTRACT DEMO1P, EXTTRAILSOURCE /backup/PRNETSDB/ogg/11.1/dirdat/ea
EXTRACT added.


GGSCI (source) 7> ADD RMTTRAIL /backup/gg_kt/ogg/11.2/dirdat/pa, EXTRACT DEMO1P, Megabytes 100
RMTTRAIL added.


GGSCI (source) 8> start extract DEMO1P

Sending START request to MANAGER ...
EXTRACT DEMO1P starting


GGSCI (source) 9> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DEMO1E      00:00:00      00:00:06
EXTRACT     RUNNING     DEMO1P      00:00:00      00:00:22


GGSCI (source) 10> ADD EXTRACT DEMO1I, sourceistable
EXTRACT added.


GGSCI (source) 11> exit
OGG_source $ nohup extract paramfile $GGH/dirprm/DEMO1I.prm reportfile $GGH/dirrpt/DEMO1I.rpt > DEMO1I.out 2>&1 &
[1]     17810
OGG_source $
[1] +  Done                    nohup extract paramfile $GGH/dirprm/DEMO1I.prm reportfile $GGH/dirrpt/DEMO1I.rpt > DEMO1I.out 2>&1 &
OGG_source $ cd dirrpt
OGG_source $ more DEMO1I.rpt|grep "Starting at"
                    Starting at 2015-08-07 08:02:26
OGG_source $ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Aug 7 08:03:15 2015

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL@OGG_source > select count(*) from sdas.ogg_test;

  COUNT(*)
----------
      7004

********** Operation on Target system starts here **********

SQL@OGG_target > select count(*) from sdas.tgt_ogg_test;

  COUNT(*)
----------
         0

SQL@OGG_target > !
OGG_target $ cd $GGH
OGG_target $ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.17 17888650 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131215.2328_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 16 2013 03:43:25

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.



GGSCI (target) 1> add rep TDEMO1I, exttrail /backup/gg_kt/ogg/11.2/dirdat/ia, nodbcheckpoint
REPLICAT added.


GGSCI (target) 2> start replicat TDEMO1I

Sending START request to MANAGER ...
REPLICAT TDEMO1I starting


GGSCI (target) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TDEMO1I     00:00:00      00:00:01


GGSCI (target) 4> stats replicat TDEMO1I

Sending STATS request to REPLICAT TDEMO1I ...

Start of Statistics at 2015-08-07 08:08:46.

Replicating from SDAS.OGG_TEST to SDAS.TGT_OGG_TEST:

*** Total statistics since 2015-08-07 08:08:29 ***
        Total inserts                                   7004.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                7004.00

*** Daily statistics since 2015-08-07 08:08:29 ***
        Total inserts                                   7004.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                7004.00

*** Hourly statistics since 2015-08-07 08:08:29 ***
        Total inserts                                   7004.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                7004.00

*** Latest statistics since 2015-08-07 08:08:29 ***
        Total inserts                                   7004.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                7004.00

End of Statistics.


GGSCI (target) 5> send rep TDEMO1I, getlag

Sending GETLAG request to REPLICAT TDEMO1I ...
Last record lag: 359 seconds.
At EOF, no more records to process.


GGSCI (target) 6> stop rep TDEMO1I

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


GGSCI (target) 7> exit
OGG_target $ exit
exit

SQL@OGG_target > select count(*) from sdas.tgt_ogg_test;

  COUNT(*)
----------
      7004

********** Insert and update some values in the source ************

SQL@OGG_source > insert into sdas.ogg_test values (20202,'Selvakumar Nagulan',sysdate,'N',20202);

1 row created.

SQL@OGG_source > update sdas.ogg_test set emp_name='Priyanka' where emp_id=7000;

1 row updated.

SQL@OGG_source > commit;

Commit complete.

SQL@OGG_source > select count(*) from sdas.ogg_test;

  COUNT(*)
----------
      7005

*********** Check the changes in target which still didn't reflect here ***********
*********** Do the rest of OGG operations to enable real time sync ****************

SQL@OGG_target > select * from sdas.tgt_ogg_test where emp_id=7000;

EMP_ID     EMP_NAME                                           JOINDATE  S     SALARY
---------- -------------------------------------------------- --------- - ----------
7000       Priya                                              20-MAY-15 A       7000

SQL@OGG_target > select * from sdas.tgt_ogg_test where emp_id=20202;

no rows selected

SQL@OGG_target > !
OGG_target $ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.17 17888650 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131215.2328_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 16 2013 03:43:25

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.



GGSCI (target) 1> dblogin userid ggs_dba password ggs_dba
Successfully logged into database.

GGSCI (target) 2> ADD REPLICAT DEMO1R, EXTTRAIL /backup/gg_kt/ogg/11.2/dirdat/pa, checkpointtable ggs_dba.CHECKPOINT
REPLICAT added.


GGSCI (target) 3> ALTER REPLICAT DEMO1R, BEGIN 2015-08-07 08:02:26
REPLICAT altered.


GGSCI (target) 4> start replicat DEMO1R

Sending START request to MANAGER ...
REPLICAT DEMO1R starting


GGSCI (target) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     DEMO1R      00:06:19      00:00:08
REPLICAT    STOPPED     TDEMO1I     00:00:00      00:02:48


GGSCI (target) 6> stats rep DEMO1R

Sending STATS request to REPLICAT DEMO1R ...

Start of Statistics at 2015-08-07 08:12:03.

Replicating from SDAS.OGG_TEST to SDAS.TGT_OGG_TEST:

*** Total statistics since 2015-08-07 08:11:44 ***
        Total inserts                                      1.00
        Total updates                                      1.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Daily statistics since 2015-08-07 08:11:44 ***
        Total inserts                                      1.00
        Total updates                                      1.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Hourly statistics since 2015-08-07 08:11:44 ***
        Total inserts                                      1.00
        Total updates                                      1.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Latest statistics since 2015-08-07 08:11:44 ***
        Total inserts                                      1.00
        Total updates                                      1.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

End of Statistics.


GGSCI (target) 7> send rep DEMO1R, ggetlag

Sending GGETLAG request to REPLICAT DEMO1R ...

ERROR: unknown request.

2015-08-07 08:12:28  WARNING OGG-01742  Command sent to REPLICAT DEMO1R returned with an ERROR response.


GGSCI (target) 8> send rep DEMO1R, getlag

Sending GETLAG request to REPLICAT DEMO1R ...
Last record lag: 379 seconds.
At EOF, no more records to process.


GGSCI (target) 9> stop rep DEMO1R

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


GGSCI (target) 10> exit
OGG_target $ exit
exit

SQL@OGG_target > select count(*) from sdas.tgt_ogg_test;

  COUNT(*)
----------
      7005

SQL@OGG_target > select * from sdas.tgt_ogg_test where emp_id=7000;

EMP_ID     EMP_NAME                                           JOINDATE  S     SALARY
---------- -------------------------------------------------- --------- - ----------
7000       Priyanka                                           20-MAY-15 A       7000

SQL@OGG_target > select * from sdas.tgt_ogg_test where emp_id=20202;

EMP_ID     EMP_NAME                                           JOINDATE  S     SALARY
---------- -------------------------------------------------- --------- - ----------
20202      Selvakumar Nagulan                                 07-AUG-15 N      20202

SQL@OGG_target > !
OGG_target $ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.17 17888650 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131215.2328_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 16 2013 03:43:25

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.



GGSCI (target) 1> edit params DEMO1R

********* Here remove the HANDLECOLLISIONS line from the param file and start the replicat **********

GGSCI (target) 2> start rep DEMO1R

Sending START request to MANAGER ...
REPLICAT DEMO1R starting


GGSCI (target) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     DEMO1R      00:00:00      00:00:06
REPLICAT    STOPPED     TDEMO1I     00:00:00      00:05:50


GGSCI (target) 4> exit
OGG_target $ exit
exit

********* The source and target are now in sync with real time replication enabled **********

SQL@OGG_source > insert into sdas.ogg_test values (30303,'Rafael Nadal',sysdate,'N',30303);

1 row created.

SQL@OGG_source > commit;

Commit complete.

SQL@OGG_source >

SQL@OGG_target >
SQL@OGG_target > select * from sdas.tgt_ogg_test where emp_id=30303;

EMP_ID     EMP_NAME                                           JOINDATE  S     SALARY
---------- -------------------------------------------------- --------- - ----------
30303      Rafael Nadal                                       07-AUG-15 N      30303

SQL@OGG_target >

Happy Replicating!!

OGG step by step process

In this post let's look at the Oracle GoldenGate step by step process for replicating data between source and target databases. For all the installation, prerequisites and OGG set up part, see this link.

Now with all the pre requisites and other setup have been complete, we can start the actual process of GoldenGate replication. The data extraction and replication involves the below steps.

Start real time Extract process (Source)

Start Pump process (Source)

Start Initial extract (Source)

Start Initial replication (Target)

Start real time replication with handle collisions (Target)

Start real time replication without handle collisions (Target)

Let’s see one by one in action below.

1. Start real time Extract process (Source)

ggsci> ADD EXTRACT DEMO1E, TRANLOG, THREADS 2, BEGIN NOW
ggsci> ADD EXTTRAIL /backup/PRNETSDB/ogg/11.1/dirdat/ea, EXTRACT DEMO1E, Megabytes 200
ggsci> start extract DEMO1E

Explanation of commands:

1. Add an extract DEMO1E with transaction log as data source. When TRANLOG is specified it should be accompanied with BEGIN and we are starting with the current time. THREADS is 2 here as per cluster setup.
2. Add a trail dump file to extract DEMO1E with 200MB each
3. Start the extract

2. Start Pump process (Source)

ggsci> ADD EXTRACT DEMO1P, EXTTRAILSOURCE /backup/PRNETSDB/ogg/11.1/dirdat/ea
ggsci> ADD RMTTRAIL /backup/gg_kt/ogg/11.2/dirdat/pa, EXTRACT DEMO1P, Megabytes 100
ggsci> start extract DEMO1P

Explanation of commands:

1. Add an extract DEMO1P with trail dump source name
2. Add remote trial file to extract DEMO1P with size limited to 100MB
3. Start the extract

3. Start Initial extract (Source)

ggsci> ADD EXTRACT DEMO1I, sourceistable
OGG_source $ nohup extract paramfile $GGH/dirprm/DEMO1I.prm reportfile $GGH/dirrpt/DEMO1I.rpt > DEMO1I.out 2>&1 &

Explanation of commands:

1. Add an extract DEMO1I with SOURCEISTABLE keyword which means table is the source for this extract. This is used for initial loading extract and is a one time run extract.
2. Since we don’t know how long the extract would run, we run the extract job as a background job so that it completed without any issues. 
3. Extract using the parameter file DEMO1I.prm and use DEMO1I.rpt as a report file.

4. Start Initial replication (Target)

ggsci> add rep TDEMO1I, exttrail /backup/gg_kt/ogg/11.2/dirdat/ia, nodbcheckpoint
ggsci> start replicat TDEMO1I

Explanation of commands:

1. Add a replicat by name TDEMO1I with trail dump file name (This should be the name from the initial extract file at source). NODBCHECKPOINT ensures that this replicat will not write checkpoints to a checkpoint table
2. Start the replicat

5. Start real time replication with handle collisions (Target)

ggsci> dblogin userid ggs_dba password ggs_dba
ggsci> ADD REPLICAT DEMO1R, EXTTRAIL /backup/gg_kt/ogg/11.2/dirdat/pa, checkpointtable ggs_dba.CHECKPOINT
ggsci> ALTER REPLICAT DEMO1R, BEGIN 2014-23-12 04:30:03 -- (Initial extract start Time)
ggsci> start replicat DEMO1R

Explanation of commands:

1. Login to database via OGG as OGG user
2. Add replicat DEMO1R with trail file name (This should be the name of the pump extract at source) and specify a CHECKPOINTTABLE table name to write check point information.
3. Alter the replicat to begin at a specific time. This is the time when the initial extract is started. You can note the time when extract is started or you can find this from the report file of the initial extract.
4. Start the replicat

The parameter file will contain a keyword HANDLECOLLISIONS which is used to detect and resolve conflicts in data integrity. More on this keyword can be found in this oracle documentation link

6. Start real time replication without handle collisions (Target)
Now with all the initial data has been loaded into the target table and when there is no data to be loaded from the initial extract (see how to see status in the next section 7) we can restart the replicat to apply changes without error and conflicts detection by removing the HANDLECOLLISIONS keyword.

ggsci> stop replicat DEMO1R
ggsci> edit params DEMO1R
/* Remove the HANDLECOLLISIONS lines from DEMO1R.prm file
using edit option or with any other file editor */
ggsci> start replicat DEMO1R

Explanation of commands:

1. Stop the replicat
2. Edit the parameter file DEMO1R.prm and remove handlecollisions
3. Start the replicat

7. Information/status about the processes
One can check the status of any process by using the below commands.

ggsci> info  DEMO1E  -- (we will be able to see the Lag)
ggsci> stats replicat DEMO1E -- (summary of records processed)
ggsci> info manager -- (or info mgr)
ggsci> send rep DEMO1E, getlag -- To get lag time

Happy replicating!!

OGG simple parameter files

In this post, we will see about the simple parameter files required for the Oracle GoldenGate replication operation.
For details on installation, prerequisites, set up and demo see this link

Parameter files are an important part of the OGG setup as these files provide the details required for the extract/replication operation. We can separate the tasks as streams. For eg., if we have 100 tables to be replicated then it is not required to include every table in a single parameter file and we can segregate set of tables under different streams. Since we are dealing with only one table for demo purpose here, we have 1 stream with 1 table which would contain 3 files in source and 2 files in target system.

I have used I, E, P and R terminologies to indicate whether it is Initial extract, Extract, Pump or Replicat parameter file.

1. Source Param files

Initial extract param file - DEMO1I.prm

EXTRACT DEMO1I
SOURCEISTABLE
setenv NLS_CHARACTERSET = ""WE8MSWIN1252""
setenv NLS_LANG = ""AMERICAN_AMERICA.WE8MSWIN1252""
USERID ggs_dba, PASSWORD ggs_dba
RMTHOST 172.20.161.193, MGRPORT 12000
RMTFILE /backup/gg_kt/ogg/11.2/dirdat/ia, maxfiles 25, MEGABYTES 1000, PURGE
TABLE SDAS.OGG_TEST;

Extract param file - DEMO1E.prm

EXTRACT DEMO1E
setenv NLS_CHARACTERSET = "WE8MSWIN1252"
setenv NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252"
USERID ggs_dba, PASSWORD ggs_dba
THREADOPTIONS PROCESSTHREADS EXCEPT 2
EXTTRAIL /backup/PRNETSDB/ogg/11.1/dirdat/ea
DISCARDFILE /backup/PRNETSDB/ogg/11.1/dirrpt/DEMO1E.dsc, APPEND
REPORTCOUNT EVERY 100 RECORDS, RATE
GETTRUNCATES
TABLE SDAS.OGG_TEST;

Data pump param file - DEMO1P.prm

EXTRACT DEMO1P
setenv NLS_CHARACTERSET = "WE8MSWIN1252"
setenv NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252"
PASSTHRU
RMTHOST 172.20.161.193, MGRPORT 12000
RMTTRAIL /backup/gg_kt/ogg/11.2/dirdat/pa
GETTRUNCATES
TABLE SDAS.OGG_TEST;

The above .prm files should be created under $GGH/dirprm of the source server using vi command

Note: When the source database is an ASM instance, then Oracle GoldenGate change data capture requires ASM authentication for accessing the transaction logs. The extract parameter TRANLOGOPTIONS handles authentication to the ASM instance.

TRANLOGOPTIONS ASMUSER SYS@CONN_STRG, ASMPASSWORD [ENCRYPTKEY { | DEFAULT}]

Eg: TRANLOGOPTIONS ASMUSER sysasm@ASMDB, ASMPASSWORD password

Oracle GoldenGate with database version 11.2.0.4 and above, we can use integrated capture method which is better than the Classic capture method explained in this demo. To learn more about integrated capture and apply methods, see this oracle documentation link

2. Target Param files

Replicat file - DEMO1R.prm

REPLICAT DEMO1R
setenv NLS_CHARACTERSET = "WE8MSWIN1252"
setenv NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252"
USERID ggs_dba, PASSWORD ggs_dba
DISCARDFILE /backup/gg_kt/ogg/11.2/dirrpt/DEMO1R.dsc, APPEND
REPORTCOUNT EVERY 100 RECORDS, RATE
ASSUMETARGETDEFS
HANDLECOLLISIONS
MAP SDAS.OGG_TEST, TARGET SDAS.TGT_OGG_TEST;

Initial Replicat file - TDEMO1I.prm

REPLICAT TDEMO1I
setenv NLS_CHARACTERSET = "WE8MSWIN1252"
setenv NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252"
USERID ggs_dba, PASSWORD ggs_dba
DISCARDFILE /backup/gg_kt/ogg/11.2/dirrpt/TDEMO1I.dsc, APPEND
REPORTCOUNT EVERY 100 RECORDS, RATE
ASSUMETARGETDEFS
MAP SDAS.OGG_TEST, TARGET SDAS.TGT_OGG_TEST;

The above .prm files should be created under $GGH/dirprm of the target server using vi command or any other text editors.

Happy replication!!

Enabling TRANDATA for Oracle GoldenGate

One of the prerequisites for Oracle GoldenGate replication is to enable TRANDATA for the tables or whole schema. For other prerequisites, installation and complete process see here.

Use ADD TRANDATA to enable Oracle GoldenGate to acquire the transaction information that it needs from the transaction records. This can be done by either ggsci or sqlplus command prompts.

Examples of both are shown below.

ggsci 1> dblogin userid ggs_dba Password ggs_dba
ggsci 2> add trandata SCHEMA.TABLE_NAME

or from database as
To specify an unconditional supplemental log group for PRIMARY KEY column(s):

SQL > ALTER TABLE SCHEMA.TABLE_NAME ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

To specify an unconditional supplemental log group that includes ALL TABLE columns:

SQL > ALTER TABLE SCHEMA.TABLE_NAME ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

To specify an unconditional supplemental log group that includes SELECTED columns:

SQL> ALTER TABLE SCHEMA.TABLE_NAME ADD SUPPLEMENTAL LOG GROUP t1_g1 (C1,C2) ALWAYS;

See this oracle documentation link for more details on why and how supplemental logging has to be enabled.

One can also use ADD SCHEMATRANDATA to enable supplemental logging for the whole schema and use exclusion parameter to exclude objects that are not required to be replicated. More about ADD SCHEMATRANDATA can be seen in this oracle documentation link

For our demo purpose, I’ll be replicating OGG_TEST table under SDAS user to be replicated

GGSCI (source) 3> add trandata sdas.ogg_test

2015-08-06 10:08:53  WARNING OGG-00869  No unique key is defined for table OGG_TEST. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table SDAS.OGG_TEST.

This is because we don’t have a unique or primary key defined on the table. I’ll now alter the table to drop the supplemental log group, create primary key on a field and then enable supplemental log group. Then let’s see how it behaves.

SQL> alter table sdas.ogg_test drop SUPPLEMENTAL LOG GROUP GGS_OGG_TEST_39719;

Table altered.

SQL> alter table sdas.ogg_test add constraint ogg_test_pk primary key (emp_id) using index;

Table altered.

Now try adding trandata..

GGSCI (source) 5> add trandata sdas.ogg_test

Logging of supplemental redo data enabled for table SDAS.OGG_TEST.

Now supplemental log data enabled using the unique key.

Happy OGG set up!!

Oracle GoldenGate Installation

In this post, let's look at how to install Oracle GoldenGate as part of our demonstration. For other prerequisites, set up and process please see this link

Source:

Download the Oracle GoldenGate software from here (Choose correct version as per your server environment)

The file name we use here is p13087235_111110_SOLARIS64.zip

Copy to desired OGG home location and unzip the software bundle and untar the actual file. Once untar, all the binaries required to run Oracle GoldenGate will be extracted and installation is considered complete.

$ cd $OGG
$ unzip p13087235_111110_SOLARIS64.zip
$ tar -xvf ggs_Solaris_sparc_ora9i_64bit.tar

Destination:

Download the Oracle GoldenGate software from here (Choose correct version as per your server environment)

The file name we use here is p17952585_1121017_Linux-x86-64.zip

Copy the software to the target server OGG home location and unzip the software bundle using unzip. Untar to complete installation.

$ cd $OGG
$ unzip p17952585_1121017_Linux-x86-64.zip
$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

Note: Until Oracle GoldenGate version 11.2, unzip of the bundle provides all the required executable/binaries for working with GoldenGate. Starting Oracle GoldenGate version 12.1, the extracted file will be used to install Oracle GoldenGate using runInstaller. Steps are as below.

$ unzip 121210_fbo_ggs_Linux_x64_shiphome.zip
$ ls -lrt
total 256
drwxr-xr-x 3 oracle dba   4096 Aug  7  2014 fbo_ggs_Linux_x64_shiphome
-rw-r--r-- 1 oracle dba   1540 Aug 11  2014 OGG-12.1.2.1.0-README.txt
-rw-r--r-- 1 oracle dba 246718 Aug 11  2014 OGG-12.1.2.1.0-ReleaseNotes.pdf
$ cd /oracle/U04/gg_home/12.1/fbo_ggs_Linux_x64_shiphome/Disk1/response

Edit all the required parameters (using any test editor) in the response file under /oracle/U04/gg_home/12.1/fbo_ggs_Linux_x64_shiphome/Disk1/response for GoldenGate software installation.

Parameters required:

INSTALL_OPTION - specify whether for ORA12c or ORA11g

SOFTWARE_LOCATION - specify the GG software install location (OGG home location)

INVENTORY_LOCATION - specify the inventory location

UNIX_GROUP_NAME - specify unix group name

Other optional parameters are START_MANAGER, MANAGER_PORT and DATABASE_LOCATION.

Save the file oggcore.rsp and start the installation as below.

$ pwd
/oracle/U04/gg_home/12.1/fbo_ggs_Linux_x64_shiphome/Disk1
$ ./runInstaller -silent -responseFile /oracle/U04/gg_home/12.1/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

You would get something like below to say Software installation is successful.

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 14621 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 2832 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-08-03_02-49-44PM. Please wait ...$
…
…
…
The installation of Oracle GoldenGate Core was successful.
Please check '/apps/app/oracle/oraInventory/logs/silentInstall2015-08-03_02-49-44PM.log' for more details.
Successfully Setup Software.

Happy Installation!

Enabling Archivelog

For non clustered, database archive logging can be enabled by the below method

Set the below parameters if Flash Recovery Area is being used

SQL> ALTER SYSTEM SET db_recovery_file_dest='/u01/FRA/ORCL/'; -- starting from version 10g and above
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=10G;

If you decide not to use FRA, then the below parameters should be set

SQL> ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile; -- required for 9i
SQL> ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/ORCL/archive/' SCOPE=spfile;
SQL> ALTER SYSTEM SET log_archive_format='arch_%t_%s.arc' SCOPE=spfile;

If the above has been set perform the below.

SQL> SHUT IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

For clustered database, follow the steps below
Logon to any of the node in the cluster

$ sqlplus / as sysdba

Set the relevant archive parameters such as log_archive_dest_1 and log_archive_format as explained above and perform the below steps

SQL> ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile; -- required for DB version 10g and below

$ srvctl stop database -d DB_NAME

SQL> STARTUP MOUNT;
SQL> ARCHIVE LOG START; -- required for DB version 9i only
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile; -- required for DB version 10g and below
SQL> SHUTDOWN IMMEDIATE;

$ srvctl start database -d DB_NAME

Now archive log has been turned on.

Happy logging!!

Dropping a Goldengate user schema - ORA-20782

Dropping an Oracle Goldengate user schema involves dropping Goldengate related triggers first before schema drop.
The reason being the error that we receive while trying to drop the schema.

ORA-00604: error occurred at recursive SQL level 2
ORA-20782: GoldenGate DDL Replication Error: Code :ORA-20782: Cannot DROP
object used in GoldenGate replication while trigger is enabled. Consult
GoldenGate documentation and/or call GoldenGate Technical Support if you wish
to do so., error stack: Error stack is avalaible only on Oracle 10.1 and above
ORA-06512: at line 951

We have 2 options to drop the Oracle Goldengate user schema. Both options are presented below and is straight forward.

Option 1:

Run the below 3 scripts as SYSDBA user from Goldengate home directory with input as OGG schema name

@ddl_disable.sql
@ddl_remove.sql
@marker_remove.sql

SQL> drop user ggs_dba cascade;
drop user ggs_dba cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot
DROP object used in Oracle GoldenGate replication while trigger is enabled.
Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate Technical
Support if you wish to do so., error stack: ORA-06512: at line 231
ORA-06512: at line 1030


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ cd $GGH
$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 6 06:42:11 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @ddl_disable.sql

Trigger altered.

SQL> @ ddl_remove.sql

DDL replication removal script.
WARNING: this script removes all DDL replication objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:GGS_DBA
Working, please wait ...
Spooling to file ddl_remove_spool.txt

Script complete.
SQL> @marker_remove.sql

Marker removal script.
WARNING: this script removes all marker objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:GGS_DBA

PL/SQL procedure successfully completed.


Sequence dropped.


Table dropped.


Script complete.
SQL>
SQL> drop user ggs_dba cascade;

User dropped.

Option 2:

Drop the trigger that is preventing the drop of schema and try to drop user.
Use the below query to find GGS user related trigger.

SELECT A.OBJ#, A.SYS_EVTS, B.NAME
FROM TRIGGER$ A,OBJ$ B
WHERE A.SYS_EVTS > 0
AND A.OBJ#=B.OBJ#
AND BASEOBJECT = 0;

Complete process is as below.

SQL> drop user GGS_DBA cascade;
drop user GGS_DBA cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20782: GoldenGate DDL Replication Error: Code :ORA-20782: Cannot DROP
object used in GoldenGate replication while trigger is enabled. Consult
GoldenGate documentation and/or call GoldenGate Technical Support if you wish
to do so., error stack: Error stack is avalaible only on Oracle 10.1 and above
ORA-06512: at line 951


SQL> select * from dba_triggers db where owner='GGS_DBA';

no rows selected

SQL> SELECT A.OBJ#, A.SYS_EVTS, B.NAME
FROM TRIGGER$ A,OBJ$ B
WHERE A.SYS_EVTS > 0
AND A.OBJ#=B.OBJ#
AND BASEOBJECT = 0;  2    3    4    5

      OBJ#   SYS_EVTS NAME
---------- ---------- ------------------------------
      5115        128 AW_DROP_TRG
      6261         16 SYS_LOGOFF
      6263          8 SYS_LOGON
      6265        128 NO_VM_DROP
      6267         32 NO_VM_CREATE
     39446     524256 GGS_DDL_TRIGGER_BEFORE

6 rows selected.

SQL> drop trigger GGS_DDL_TRIGGER_BEFORE;

Trigger dropped.

SQL> drop user GGS_DBA cascade;

User dropped.

Happy dropping!