Oracle GoldenGate - Heartbeat process to monitor lag and performance in GoldenGate [ID 1299679.1]
		修改时间 24-MAY-2011 类型 HOWTO 状态 PUBLISHED

In this Document
Goal
Solution

---

Applies to:

Oracle GoldenGate - Version: 10.4.0.0 and later [Release: 10.4.0 and later ]
Oracle GoldenGate - Version: 10.4.0.0 and later [Release: 10.4.0 and later]
Information in this document applies to any platform.
Affected Database: Oracle

Goal

This documents a procedure to reliably measure lag between source and target systems through the use of 'heartbeat' tables.

Solution

The basic idea is to have a table on the source system that gets updated at a predetermined interval. In your capture processes you would capture the update from the heartbeat table. Using tokens you would add some additional information to the heartbeat record to be able to tell which extract process was capturing the update. This additional information would be used downstream to calculate the real lag time between the source and target systems for a given extract and by checking the last update time on the heartbeat at the target you could also determine if data has stopped flowing between the source and target. Configuration Overview For the heartbeat table to function you will need to create a "heartbeat" table on the source and a heartbeat table on the target. The source table can be very simple since all you are doing is updating a single timestamp on the source. In this version of the heartbeat we are adding additional information that is extracted from issuing a "INFO SHOWCH" in GGSCI. The output is parsed and then updated in the heartbeat table. Extract will then extract the updated information and then add a couple of tokens to the record. That is then passed in the trail down to the target. On the target system the record will then be inserted into the GGS_LAGTIME table. When the record is inserted, additional information will be added to the table via tokens and a trigger. For this example, the target will include information from the source, information on the data pump if used, and target information. This information can be used to determine where the lag is developing and create a history of the flow of data.

Step 1 - Source and Target users

Both the source and target will need a schema in order to create the source and target table. Below is just a suggested user. Any schema, that is not one of the schema containing user tables that need to be replicated, can be used and it is suggested to use the GoldenGate user that you have already set up as part of the base GoldenGate configuration.

On Source system -

One Target system -

Step 2 - Heartbeat table Source

The source and target heartbeat table have the same columns. The difference is that in the target you have two tables, one is a history table which is insert all rows and the other is the current status table that just shows the latest update. I also added a table level trigger to do the calulations as the data is loaded into the target tables.

Heartbeat table Source

The source and target heartbeat table have the same columns. The source table only has the extract information populated at the source.

Column	Contents
ID	Unique Sequence number
Extract_name	Name of the extract process
Source_commit	Commit time in the source
Target_commit	Commit time on the target
CAPTIME	Time the row was extracted on the source
CAPGROUP	Same as the extract name
CAPLAG	The difference between the Source Commit time and the capture time.
PMPTIME	Timestamp when record passes thru the data pump
PMPGROUP	Data Pump process name
PMPLAG	Lag at the data pump
DELTIME	Replicat time
DELGROUP	Replicat Group name
DELLAG	Lag in replicat
TOTALLAG	Total lag
Lag_hh_mi_ss	Current lag time that is reported in the info showch command
Lag_as_of_hh_mi_ss	Time of lag
Thread	If the source database is RAC you would see a thread number greater than 1
Status	Current status of extract process.
Current_seqno	Current redo log that extract is reading from.
Current_rba	Current position inside the log file that we are extract from.
Start_time	Time that the extract was started
Report_time	From the showch "Last Update Time =" under the status section.
Recovery_seqno	This is the recovery checkpoint. It shows which log file the oldest outstanding transaction is located.
Recovery_rba	The position that recovery must start at.
Recovery_timestamp	The recovery timestamp
Update_timestamp	The time that the heartbeat table was updated.

Run sqlplus script to create the heartbeat table and add a row to that table -

Heartbeat table Sql script -

Add trandata to the heartbeat table -

Shell script to Update heartbeat table on the source -

See attached shell script - heartbeat_timestamp_TS_v9.sh (10Kb) - Heartbeat Shell script to update heartbeat on source system.

This shell script is to be run in the background -

This script issues a GGSCI info comand and parses the output and inserts the results into the heartbeat table.

Step 3 - Heartbeat table setup - Target

This table has added columns to capture the history of inserts. The additional columns are added from the tokens and the firing of the trigger which does the calculations of lag times.

NOTE: The system times across all system must be synchronized. If not, then the calculated lag times will be inaccurate. If you see negative times, check the clocks.
Note: lag times expressed in microseconds

In sqlplus run the SQL script to create the target heartbeat tables -

This is the sql script - (Also attached)

Step 4 Extract Configuration

In the extract parameter file a MAP statement will need to be added in order to capture the update to the heartbeat table. Along with the update, a couple of tokens also need to be added in order to tell which extract and host the data originated. The best way to add the heartbeat map statement is to use an include file with the statement. This way you can add the same include file to all of your extract parameter files.

Here is the include file with the heartbeat map statement for the heartbeat table

Parameter file - OGG_HOME/dirprm/HB_Extract.inc

This is example of a complete extract parameter file with the include file for the heartbeat

Extract Parameter file - OGG_HOME/dirprm/ext_hb.prm

Step 5 - Data Pump Configuration

In the Data Pump parameter file you will need to include a map statement so that the pump name and current timestamp are added to the record as the record is passed thru the data pump. In the data pump you do not need the same where clause that was used in the extract because the filtering was already done during the extract. Again the best practice is to use an include file for the data pump heartbeat map statement.

The include file for the heartbeat in the data pump is as follows:

Include file for Data Pump - dirprm/HB_pmp.inc

Example of a complete Data Pump parameter file

./dirprm/pmp_hb.prm

Step 6 - Replicat Configuration

The replicat will need to have the heartbeat added to the map statements along with the token mapping. When the replicat inserts the row into the table a "before insert" trigger will fire and update the values in the GGS_HEARTBEAT table.

There are two heartbeat tables, the first is the heartbeat table that has the current heartbeat. It will have only one row for each replicat. The second table is the history table that contains all of the heartbeats. This table can be used to graph the lag time in each replicat end to end.

As with the extract and data pump, we are adding data to the record when we insert the row into the target heartbeat table. Again, using an include file for the map statement.

This is the include file for the Map statement:

./dirprm/HB_Rep.inc

This is an example of a complete Replicat parameter file:

./dirprm/rep_hb.prm

Step 7 - helpful Sqlplus Queries on the target system

-----------------------------------------------------------------
--
-- Heartbeat SQL query for Replicat
--
-----------------------------------------------------------------
./dirsql/del_lag.sql

./dirsql/HB_table.sql

Conclusion

In order to calculate the true lag you will need to add the heartbeat table into both the extract and replicats. By using the tokens that are added to the trail and the commit time on the target you can tell the true lag between systems even with low data flow. Also using this method you can tell on the target if the data flow from the source has been interrupted because you can check the last update time and compare that to the current time. It is critical that clocks on both the source and target systems are in sync. Note, OGG does correct the commit timestamp for differences between the source and target systems.

Appendix - Data Guard Scripts

The following scripts are used to position extract on a standby database upon a database fail-over. The idea is that if you fail over to the standby database you will need to reposition the extract on the standby system to start where the primary system stopped. If the primary system is still available, you would use the checkpoint information in the checkpoint file to position the extract on the standby system. You can get the current checkpoint information by issuing the GGSCI command:

However, if the source system is not available, you can use the heartbeat table to determine the recovery position in which to set the extract. Note, since the heartbeat is set to a interval, you will likely have some overlap in the data when you reposition the extract. In that case you may have to use HANDLECOLLISION parameter on the target database.

More details on how to use these scripts can be found in the Best Practice document GGS Best Practice - GoldenGate and Oracle Data Guard

Shell script alter_ext_begin.sh

You will need to alter the GGS_DIR parameter reflect the GoldenGate home directory.

Sql script alter_ext_begin.sql
This script will need to be placed in the "dirsql" subdirectory in the GoldenGate home directory. You will also need to edit the extract name to match your extract names.

Appendix II - Troubleshooting

Problem: Two rows in target heartbeat table
Solution: Check you map statements in the data pump and make sure you the table is not captured because of wild carding and the explicit map statement.

Problem: Lag time is in negative numbers
Solution: The system clocks on the source and target are out of sync. Note, by default the commit timestamp is corrected for time differences between source and target. If the source and target systems are out of sync, you will notice lag time between system is wrong but overall lag is correct.

附件 Setup for Source system using OGG internal test script (19.64 KB) Setup for Target system using OGG internal test script (14.03 KB) Sample variable test file for use with OGG test function (2.21 KB) Heartbeat Shell script to update heartbeat on source system (9.61 KB)

相关的 产品 Middleware > Data Integration > GoldenGate > Oracle GoldenGate Middleware > Data Integration > GoldenGate > Oracle GoldenGate 关键字 CHECKPOINT; CONFIGURATION; DATA GUARD; GOLDENGATE; GOOD PRACTISE; HEARTBEAT; PERFORMANCE

[@more@]