Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle GoldenGate Tutorial 9 – Monitoring GoldenGate

  • Posted by Gavin Soorma
  • On February 26, 2010
  • 4 Comments
  • GoldenGate, lag, Monitoring, report, stats, view ggsevt

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

Coming Next! – using GoldenGate to perform a 10g to 11g Cross Platform database upgrade and platform migration ….

 

4 Comments

Riaz
  • Feb 1 2011
Gavin, How we can trace the replication lag time between source and target server ? Thanks in advance.
Jayesh
  • Feb 9 2011
Gavin, could you please paste tutorial for Bi-directional replication setup? Thanks, ~ Jayehs
PRAVEEN
  • Feb 23 2011
You are awesome dude. These Tutorial are really helpful for beginners. Possible can you please elaborate it more in to theory and add some real time examples that would be more helpful. Thank you very much for the GOLDEN GATE Tutorial.
MAW
  • Mar 15 2011
Hi Gavin, Your Golden Gate tutorials looks awesome. Could you please post a tutorial on Bi-directional (aka active-active) golden gate setup? Thanks, MAW

Leave Reply

Your email address will not be published. Required fields are marked *