Oracle GoldenGate Tutorial 9 – Monitoring GoldenGate

The following tutorial will briefly discuss the different commands we can use to monitor the GoldenGate environment and get statistics and reports on various extract and replicat operations which are in progress.

More details can be obtained from Chapter 19 of the Oracle GoldenGate Windows and Unix Administration guide – Monitoring GoldenGate processing.

Information on all GoldenGate processes running on a system GGSCI (devu007) 21> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DPUMP 00:00:00 00:00:04 EXTRACT RUNNING EXT1 00:00:00 00:00:09 EXTRACT RUNNING EXT2 00:00:00 00:00:07 EXTRACT ABENDED GAVIN 00:00:00 73:29:25 EXTRACT STOPPED WORKPLAN 00:00:00 191:44:18 REPLICAT RUNNING MYLOAD2 00:00:00 00:00:09 REPLICAT RUNNING MYREP 00:00:00 00:00:08 Find the run status of a particular process GGSCI (devu007) 23> status manager Manager is running (IP port devu007.7809). GGSCI (devu007) 24> status extract ext1 EXTRACT EXT1: RUNNING Detailed information of a particular process GGSCI (devu007) 6> info extract ext1, detail EXTRACT EXT1 Last Started 2010-02-19 11:19 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:02 ago) Log Read Checkpoint Oracle Redo Logs 2010-02-26 10:45:18 Seqno 786, RBA 44710400 Target Extract Trails: Remote Trail Name Seqno RBA Max MB /u01/oracle/software/goldengate/dirdat/lt 2 55644 10 Extract Source Begin End /u02/oradata/apex/redo03.log 2010-02-19 11:13 2010-02-26 10:45 /u02/oradata/apex/redo02.log 2010-02-19 11:04 2010-02-19 11:13 /u02/oradata/apex/redo02.log 2010-02-18 10:42 2010-02-19 11:04 Not Available * Initialized * 2010-02-18 10:42 Current directory /u01/oracle/software/goldengate Report file /u01/oracle/software/goldengate/dirrpt/EXT1.rpt Parameter file /u01/oracle/software/goldengate/dirprm/ext1.prm Checkpoint file /u01/oracle/software/goldengate/dirchk/EXT1.cpe Process file /u01/oracle/software/goldengate/dirpcs/EXT1.pce Stdout file /u01/oracle/software/goldengate/dirout/EXT1.out Error log /u01/oracle/software/goldengate/ggserr.log Monitoring an Extract recovery GGSCI (devu007) 35> send extract ext1 status Sending STATUS request to EXTRACT EXT1 ... EXTRACT EXT1 (PID 1925238) Current status: Recovery complete: At EOF Sequence #: 786 RBA: 40549888 Timestamp: 2010-02-26 09:59:57.000000 Output trail #1 Current write position: Sequence #: 2 RBA: 55644 Timestamp: 2010-02-26 09:59:54.337574 Extract Trail: /u01/oracle/software/goldengate/dirdat/lt Monitoring processing volume - Statistics of the operations processed GGSCI (devu007) 33> stats extract ext1 Sending STATS request to EXTRACT EXT1 ... Start of Statistics at 2010-02-26 09:58:27. DDL replication statistics (for all trails): *** Total statistics since extract started *** Operations 19.00 Mapped operations 2.00 Unmapped operations 9.00 Other operations 8.00 Excluded operations 17.00 Output to /u01/oracle/software/goldengate/dirdat/lt: Extracting from GGS_OWNER.GGS_MARKER to GGS_OWNER.GGS_MARKER: *** Total statistics since 2010-02-19 11:21:03 *** No database operations have been performed. *** Daily statistics since 2010-02-26 00:00:00 *** No database operations have been performed. *** Hourly statistics since 2010-02-26 09:00:00 *** No database operations have been performed. *** Latest statistics since 2010-02-19 11:21:03 *** No database operations have been performed. Extracting from MONITOR.WORK_PLAN to MONITOR.WORK_PLAN: *** Total statistics since 2010-02-19 11:21:03 *** Total inserts 4.00 Total updates 46.00 Total deletes 0.00 Total discards 0.00 Total operations 50.00 *** Daily statistics since 2010-02-26 00:00:00 *** Total inserts 0.00 Total updates 16.00 Total deletes 0.00 Total discards 0.00 Total operations 16.00 *** Hourly statistics since 2010-02-26 09:00:00 *** No database operations have been performed. *** Latest statistics since 2010-02-19 11:21:03 *** Total inserts 4.00 Total updates 46.00 Total deletes 0.00 Total discards 0.00 Total operations 50.00 End of Statistics. View processing rate - can use 'hr','min' or 'sec' as a parameter GGSCI (devu007) 37> stats extract ext2 reportrate hr Sending STATS request to EXTRACT EXT2 ... Start of Statistics at 2010-02-26 10:04:46. Output to /u01/oracle/ggs/dirdat/cc: Extracting from SH.CUSTOMERS to SH.CUSTOMERS: *** Total statistics since 2010-02-26 09:29:48 *** Total inserts/hour: 0.00 Total updates/hour: 95258.62 Total deletes/hour: 0.00 Total discards/hour: 0.00 Total operations/hour: 95258.62 *** Daily statistics since 2010-02-26 09:29:48 *** Total inserts/hour: 0.00 Total updates/hour: 95258.62 Total deletes/hour: 0.00 Total discards/hour: 0.00 Total operations/hour: 95258.62 *** Hourly statistics since 2010-02-26 10:00:00 *** No database operations have been performed. *** Latest statistics since 2010-02-26 09:29:48 *** Total inserts/hour: 0.00 Total updates/hour: 95258.62 Total deletes/hour: 0.00 Total discards/hour: 0.00 Total operations/hour: 95258.62 End of Statistics. View latency between the records processed by Goldengate and the timestamp in the data source GGSCI (devu007) 13> send extract ext2, getlag Sending GETLAG request to EXTRACT EXT2 ... Last record lag: 3 seconds. At EOF, no more records to process. GGSCI (devu007) 15> lag extract ext* Sending GETLAG request to EXTRACT EXT1 ... Last record lag: 1 seconds. At EOF, no more records to process. Sending GETLAG request to EXTRACT EXT2 ... Last record lag: 1 seconds. At EOF, no more records to process.

Viewing the GoldenGate error log as well as history of commands executed and other events

We can use the editor depending on operating system – vi on Unix for example to view the ggserr.log file which is located at the top level GoldenGate software installation directory.

We can also use the GGSCI command VIEW GGSEVT as well.

View the process report

Every Manager, Extract and Replicat process will generate a report file at the end of each run and this
report can be viewed to diagnose any problems or errors as well as view the parameters used, the environment variables is use, memory consumption etc

For example:

GGSCI (devu007) 2> view report ext1 GGSCI (devu007) 2> view report rep1 GGSCI (devu007) 2> view report mgr Information on Child processes started by the Manager GGSCI (devu007) 8> send manager childstatus Sending CHILDSTATUS request to MANAGER ... Child Process Status - 6 Entries ID Group Process Retry Retry Time Start Time ---- -------- ---------- ----- ------------------ ----------- 0 EXT1 1925238 0 None 2010/02/19 11:07:54 1 DPUMP 2195496 0 None 2010/02/19 11:08:02 2 MSSQL1 422034 0 None 2010/02/22 13:54:59 4 MYREP 1302702 0 None 2010/02/23 09:08:34 6 MYLOAD2 1200242 0 None 2010/02/23 11:05:01 7 EXT2 2076844 0 None 2010/02/26 08:29:22