Lag time or sometimes just call the "lag" refers to the period between the database operation (DML or DDL) is committed on the source and the current point in time. In Oracle GoldenGate, there are lag times for the extract and replicat:
- For Extract, the lag time is the difference, in seconds, between the time that a record was processed and written to the trail file by Extract (based on the system clock) and the timestamp of that record changed in the source.
- For Replicat, lag is the difference, in seconds, between the time that the record was processed by Replicat (based on the system clock), and the timestamp of the record changed in the source.
If GoldenGate instance runs on an OS in a timezone different from the source database, you can get the lag unknown issue. Please refer to How to Resolve the Lag Time Unknown Issue for the details.
To monitor lag time, you can query the extract and replicat processes or create a heartbeat table to check the end-to-end lag.
- Configuring the Lag Report Parameters in GoldenGate Manager (mgr.prm). The report is included in the ggserr.log in the Oracle GoldenGate installation directory:
- LAGINFO{SECONDS|MINUES|HOURS} - Basic lag threshod
- LAGREPORT{MINUES|HOURS} - Creating lag reports based on the specify time interval.
- LAGCRITICAL{SECONDS|MINUES|HOURS} - Specify a lag threshold that is considered critical.
- Calling GGSCI commands:
- INFO extract/replicat <extract/replicat_group_name>
- LAG extract/replicat <extract/replicat_group_name>
- Creating heartbeat tables and querying the lag time
To reduce lag time, you need to know what processes contribute to the lag time [1]:
- Extract captures data from the log file
Making sure the capture process has fast I/O (read) and enough resources (e.g., correctly configure the STREAM_POOL_SIZE for Oracle Databases.) - Extract performs data mapping and conversions
Reduce conversions in the extract in general. - Data Pump reads the trail files, performs mappings/conversions
Avoid the mappings and transformations in the data pump, and have a high throughput disk I/O for GoldenGate trail files. - Data Pump compresses or encrypts the data.
- Data Pump delivers data over the network to the target.
- Replicat reads the trail file, applies mapping and conversions and apply data to the target database.
Optimize the disk I/O and target database for SQL transactions.
The detailed lag tuning discussion is available at: How to Reduce the Lag Time of Oracle GoldenGate Replication.
- Advisor Webcast Recording for DB : GoldenGate Integrated Extract/Replicat Lag Troubleshooting held on Mar 23rd, 2017 (Doc ID 2247480.1)