Configuring GoldenGate Replication and Heartbeat Table to Measure Lag

In my previous posts, I showed how you can install GoldenGate.  Now let’s setup replication while also presenting a reliable method of measuring the lag time between the source and target databases by using a “heartbeat table” as defined in the Oracle Note mentioned below.

Oracle GoldenGate ­ Heartbeat process to monitor lag and performance in GoldenGate [ID 1299679.1]

I will also be using the Integrated Capture environment here that will demonstrate how to register the capture process with the database.

The basic idea is this:

  • the heartbeat table on the source is updated at a predetermined interval
  • the extract capture process would capture the updates to the table
  • the extract capture and pump processes each add their group name and date to the heartbeat record
  • the information in the record will be used to calculate the lag between the processes on the source and target servers

Scripts

All the files and scripts mentioned in this article are in the zip file that can be downloaded from here.  Most of them were downloaded from Oracle’s note mentioned above, but they do have some modification due to differences in environments.  There is no guarantee that they will work for you.

Install the Database Objects

Create the heartbeat table in the source database

sourcedb> cd source/scripts
sourcedb> sqlplus ggate/ggate
SQL> @heartbeat_table_source_TS_v9.sql

Create the heartbeat and heartbeat_history tables in the target database

targetdb> cd target/scripts
targetdb> sqlplus ggate/ggate
SQL> @heartbeat_table_target_TS_v9.sql

Setup Replication

Configuration on Source Server

sourcedb> cd $GG_HOME
sourcedb> ggsci
GGSCI> DBLOGIN USERID ggate, PASSWORD ggate
GGSCI> MININGDBLOGIN USERID ggate, PASSWORD ggate
GGSCI> REGISTER EXTRACT cgghb DATABASE
GGSCI> ADD EXTRACT cgghb, INTEGRATED TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL /u01/app/oracle/product/gg/dirdat/Hb, EXTRACT cgghb
GGSCI> ADD EXTRACT pgghb, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/Hb
GGSCI> ADD RMTTRAIL /u01/app/oracle/product/gg/dirdat/hb, EXTRACT pgghb
GGSCI> exit

Create the following files in the $GG_HOME/dirprm directory. These can also found in the zip file downloaded earlier.  Keep in mind that you will need to modify the oby files for your environment.

sourcedb> cd dirprm
sourcedb> ls -lhF
total 80
-rw-r--r-- 1 oracle oracle 272B Jan 9 09:02 cgghb.inc
-rw-r--r-- 1 oracle oracle 808B Jan 9 09:02 cgghb.prm
-rw-r--r-- 1 oracle oracle 29B Jan 9 09:02 dblogin.oby
-rw-r--r-- 1 oracle oracle 138B Jan 9 09:02 env.oby
-rw-r--r-- 1 oracle oracle 54B Jan 9 09:02 iclogin.oby
-rw-r--r-- 1 oracle oracle 82B Jan 9 09:02 maint.oby
-rw-r--r-- 1 oracle oracle 208B Jan 9 09:02 pgghb.inc
-rw-r--r-- 1 oracle oracle 327B Jan 9 09:02 pgghb.prm

Startup the extract processes

sourcedb> cd $GG_HOME
sourcedb> ggsci
GGSCI> start extract *
Sending START request to MANAGER ...
EXTRACT CGGHB starting
Sending START request to MANAGER ...
EXTRACT PGGHB starting
GGSCI> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING 
EXTRACT RUNNING CGGHB 00:00:03 00:00:06 
EXTRACT RUNNING PGGHB 00:00:00 00:00:01

Configuration on Target Server

targetdb> cd $GG_HOME
targetdb> ggsci
GGSCI> dblogin userid ggate, password ggate
GGSCI> add checkpointtable ggate.checkpoint
GGSCI> add replicat rgghb, exttrail /u01/app/oracle/product/gg/dirdat/hb, checkpointtable ggate.checkpoint
GGSCI> exit

Copy the target files over to the $GG_HOME/dirprm directory. These can also found in the zip file downloaded earlier.

targetdb> cd dirprm
targetdb> ls -lhF
-rw-r--r-- 1 oracle oracle 29B Jan 9 09:02 dblogin.oby
-rw-r--r-- 1 oracle oracle 142B Jan 9 09:02 env.oby
-rw-r--r-- 1 oracle oracle 139B Jan 9 09:02 hb_setup.oby
-rw-r--r-- 1 oracle oracle 82B Jan 9 09:02 maint.oby
-rw-r--r-- 1 oracle oracle 1.0K Jan 9 09:02 rgghb.inc
-rw-r--r-- 1 oracle oracle 431B Jan 9 09:02 rgghb.prm

Startup the replicat process

targetdb> cd $GG_HOME
targetdb> ggsci
GGSCI> start replicat *
Sending START request to MANAGER ...
REPLICAT RGGHB starting
GGSCI> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING 
REPLICAT RUNNING RGGHB 00:00:00 00:00:03

Start the Heartbeat Timestamp Script

On the Source Database Server, start the heartbeat_timestamp_TS_v9.sh script. This script will initially populate the table with a row containing information about the extract capture process. Afterwards, it will update the record with the current time at the specified interval. Both the capture name and the interval time are specified within the script. This is an Oracle supplied script that is supplied in Note ID 1299679.1 mentioned earlier; however, it was designed to work with classic capture, not integrated capture, so I made some modification to it accordingly since the output of the “info showch” command differs between the two capture modes.

sourcdb> nohup heartbeat_timestamp_TS_v9.sh &

 Monitoring the Lag Time

On the target database server, connect to Oracle and run the heartbeat_lag.sql script.
Note that the output from the sql script was modified for brevity and formatting.

> sqlplus ggate/ggate

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

GGATE:RPT> @heartbeat_lag.sql

hb_lag_01

hb_lag_02

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s