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
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
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 18.104.22.168.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options GGATE:RPT> @heartbeat_lag.sql