Maintain and Monitor Golden Gate Replication

In this blog, We are sharing some basic commands for maintenance and monitoring. 
Basic OGG Commands
Login to the servers as Goglengate user, cd ./ggate, run ./ggsci, this will bring you to the GGS interface.
Then you can perform the following example commands. For a complete list of all GGS commands and detailed descriptions, please refer to the Reference Guide.
1. Help  --  This is the most important GGSCI command. All uses for GGSCI are listed.
       GGSCI (GGserver) 7> help (help alter extract, help add replicat, etc) 
2. Info all -- Gives a high level overview of all the OGG processes and their current status.  
GGSCI (GGserver) 1> info all
Program                     Status             Group           Lag         Time Since Chkpt
MANAGER     RUNNING
EXTRACT        RUNNING     EFIN01A    00:00:01      00:00:08
EXTRACT        RUNNING     PFIN01A    00:00:02      00:00:05
REPLICAT       RUNNING     RFIN01A    00:00:00      00:00:04
3. Info “process” and info * -- Provides more detail information about a process/processes.  Tells when the process started and additional information depending on the process. “*” indicates all processes. For RAC, info EXTRACT also show the each redo thread information.
GGSCI (GGserver) 5> info EFIN01A
EXTRACT    EFIN01A  Last Started 2012-05-17 06:40   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2012-05-19 15:47:39  Thread 1, Seqno 378, RBA 276214576
Log Read Checkpoint  Oracle Redo Logs
                     2012-05-19 15:47:40  Thread 2, Seqno 406, RBA 180313624
Log Read Checkpoint  Oracle Redo Logs
                     2012-05-19 15:47:39  Thread 3, Seqno 296, RBA 448861108
GGSCI (GGserver) 2> info *  
EXTRACT    EFIN01A  Last Started 2012-05-17 06:40   Status RUNNING
Checkpoint Lag       00:00:01 (updated 00:00:03 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2012-05-19 15:46:17  Thread 1, Seqno 378, RBA 261401716
Log Read Checkpoint  Oracle Redo Logs
                     2012-05-19 15:46:17  Thread 2, Seqno 406, RBA 178226192
Log Read Checkpoint  Oracle Redo Logs
                     2012-05-19 15:46:18  Thread 3, Seqno 296, RBA 445021088
EXTRACT    PFIN01A  Last Started 2012-05-17 06:40   Status RUNNING
Checkpoint Lag       00:00:04 (updated 00:00:10 ago)
Log Read Checkpoint  File ./dirdat/w1000018
                     2012-05-19 15:46:08.000000  RBA 99685637
REPLICAT   RFIN01A  Last Started 2012-05-17 23:28   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint  File ./dirdat/c2000003
                     2012-05-19 15:46:14.003409  RBA 499617036
Other info commands:
info trandata “owner.table_name” – find out whether the table has supplemental log added. Warning, it not work for table manually added trandata (> 32 columns and without). The alternative way is the query dba_log_groups for the table in question.
Info mgr --  Shows manager status and port number it’s running on.
Info “process” showch --  Shows information about Extract/Replicat checkpoints..
Info “process” detail --  Displays Extract runs history (including start and stop points in the data source) and trails to which the Extract group is writing..
4. stop “process_name”  – This will stop the requested process.  Run info all to insure the process did stop successfully.
GGSCI (GGserver) 4> stop YOUREXTRACT
Sending STOP request to EXTRACT YOUREXTRACT...
Request processed.
GGSCI (GGserver) 11> info all
Program                      Status      Group                              Lag    Time Since Chkpt
MANAGER     RUNNING
EXTRACT      STOPPED     YOUREXTRACT     00:00:00      00:08:24
5. Start “process_name” --  This will start the requested process.  Run info all to insure the process started successfully.
GGSCI (GGserver) 9> start YOUREXTRACT
Sending START request to MANAGER ...
EXTRACT YOUREXTRACT starting
GGSCI (GGserver) 10> info all
Program     Status             Group                     Lag     Time Since Chkpt
MANAGER    RUNNING
EXTRACT     STOPPED     YOUREXTRACT     00:00:00      02:18:39
6. View Report “process_name”   --  This will show the content of latest report file. Typically this is used to find out error message when process abends. An alternative is to directly vi/view/more the latest report file in your /ggate/dirrpt directory. OGG keeps 10 report files for each process and recycle them when new one generates.
GGSCI (GGserver) 2> view report eora
***********************************************************************
                 Oracle GoldenGate Capture for Oracle
Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.
                    Starting at 2012-05-19 12:39:07
***********************************************************************
Operating System Version:
Version 5.1 (Build 2600: Service Pack 3)
Process id: 11548
Description:
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
extract eora
7. View GGSEVT --  This will view the content of ggserr.log file. The alternative is to vi/view/more the ggserr.log file in the GGS directory (in your ./ggate directory).
GGSCI (bzhu-lt-02) 4> view ggsevt
2009-09-21 10:15:15  GGS INFO        399  GoldenGate Command Interpreter for Oracle:  GGSCI command (bzhu): edit param ./GLOBALS.
2009-09-21 10:15:50  GGS INFO        399  GoldenGate Command Interpreter for Oracle:  GGSCI command (bzhu): edit param mgr.
2009-09-21 10:16:45  GGS INFO        399  GoldenGate Command Interpreter for Oracle:  GGSCI command (bzhu): start mgr.
8. Dblogin userid xxx password xxx – used to log ggs into the database which is required to add trandata for tables to be replicated or when a replicat ins added/deleted.  Add trandata creates a supplemental log group to write required information to the redo logs which enable ggs replication to work.
GGSCI (GGserver) 1> dblogin userid ggs password ggs
Successfully logged into database.
GGSCI (GGserver) 2> add trandata corestg.gl_balances
Logging of supplemental redo data enabled for table CORESTG.GL_BALANCES.
9. lag “process” or lag *  – Works on both extract and replicat.  It shows the replication latency at each stage: extraction, pumping, and finally apply SQL to the target database.  When it is required that all records are process it should report no records left to process.  The process MUST be running for lag to work.
GGSCI (GGserver) 21> lag *
Sending GETLAG request to EXTRACT EFIN01A ...
Last record lag: 1 seconds.
Sending GETLAG request to EXTRACT PFIN01A ...
Last record lag: 2 seconds.
Sending GETLAG request to REPLICAT RFIN01A ...
Last record lag: 6 seconds.
At EOF, no more records to process.
10. Show -  Shows information about where ggs writes information.
GGSCI (GGserver) 16> show
Parameter settings:
SET SUBDIRS    ON
SET DEBUG      OFF
Current directory: /u01/app/ogg
Using subdirectories for all process files
Editor:  vi
Reports (.rpt)                 /u01/app/ogg/dirrpt
Parameters (.prm)              /u01/app/ogg/dirprm
Stdout (.out)                  /u01/app/ogg/dirout
Replicat Checkpoints (.cpr)    /u01/app/ogg/dirchk
Extract Checkpoints (.cpe)     /u01/app/ogg/dirchk
11. Obey (relative path)/obey_file -- Runs the request file that contains ggs commands.  Very useful to batch a large number of commands together.  In this example the following file is run.  Typically an obey directory is created under GGS_HOME.
GGserver:/u01/app/ogg\> cat ./dirobey/deltran.obey
dblogin userid ggs1 password ggs1
delete trandata perftest.stress1
GGSCI (GGserver) 1> obey ./dirobey/deltran.obey
GGSCI (GGserver) 2> dblogin userid ggs password ggs
Successfully logged into database.
GGSCI (GGserver) 3> delete trandata perftest.stress1
Logging of supplemental redo log data disabled for table PERFTEST.STRESS1.
12. !  – Repeats the last command
13. History – shows the last 20 commands executed under GGSCI.
GGSCI (GGserver) 6> history
GGSCI Command History
    1: help
    2: vier report YOUREXTRACT
    3: view report YOUREXTRACT
    4:  view report mgr
    5:  help
    6: history
GGSCI (GGserver) 6 > fc 4
GGSCI (GGserver) 6 > view report mgr
GGSCI (GGserver) 6 > ..
14. Send extract “extract_name” logend -- This command validates whether extract has finished processing the log data on the source.  A return of YES shows it is done processing.
GGSCI (GGserver) 39> send extract YOUREXTRACT logend
Sending LOGEND request to EXTRACT YOUREXTRACT...
YES.
15. Send extract “extract_name”, tltrace debug | Send extract “extract_name”, tltrace off -- This set of commands can be useful to see which log GGS extract is processing in a situation when extract goes back to old logs for long running transactions. Perform the second command immediate after the first one. Otherwise, it will generate a huge report file. Exam the report file (end portion) can tell where the extract is processing.
GGSCI (GGserver) > send extract YOUREXTRACT, tltrace debug
GGSCI (GGserver) > send extract YOUREXTRACT, tltrace off
How to Check Throughput and Latency

• All of the following three outputs show the replication latency:

GGSCI > info *
GGSCI > info all
GGSCI > lag *

• To find out throughput, either check the report file or run the “stats’ command.

GGSCI > stats * totalsonly * reportrate sec reset  -- to reset the counter in the latest field set.
GGSCI > stats * totalsonly * reportrate sec  -- report operation/second for all tables and for all processes

• Check the report file as in the following example. It records the “rate” and “delta” of the replication speed by that process. “rate” is the average speed since process started. “delta” is the average speed of the past 2 minutes (this is determined by the setting of a process parameter: REPORTRATE)

How to Check OGG Reports and Log Files

• The ggserr.log in /opt/gg directory which is similar to Oracle’s alert logfile which contains info and error.

• Report files in /opt/gg/dirrpt. Each process should have 10 report files.

• Discard files in /opt/gg/dirrpt. Discard file will contain data information when a record is discarded by the process.

• One can use TLTRACE DEBUG, TRACE and TRACE2 parameters in OGG configuration file to let OGG generate more information into its report file. For information on how to use the tracing parameter please refer to OGG Reference Guide.

• Check the report file

$ cd ./ogg/dirrpt
$ tail -f EFIN01A.rpt – rate is the average speed since the process started, delta is the rate for the past 2 minutes.
            12874348 records processed as of 2012-05-19 16:58:37 (rate 63,delta 68)
            12902508 records processed as of 2012-05-19 17:00:37 (rate 63,delta 234)
            12922232 records processed as of 2012-05-19 17:02:38 (rate 63,delta 163)
            12930188 records processed as of 2012-05-19 17:04:38 (rate 63,delta 66)
            12982731 records processed as of 2012-05-19 17:06:39 (rate 64,delta 435)
            12990499 records processed as of 2012-05-19 17:08:39 (rate 64,delta 64)
            13032281 records processed as of 2012-05-19 17:10:39 (rate 64,delta 348)
            13075796 records processed as of 2012-05-19 17:12:40 (rate 64,delta 362)
            13083236 records processed as of 2012-05-19 17:14:40 (rate 64,delta 61)
            13153092 records processed as of 2012-05-19 17:16:41 (rate 64,delta 578)
How to Reset OGG Processes

• Warning: Reset the check point of OGG processes may cause replication data loss. One needs to be sure what the intention is for the reset and not disrupt the replication.

• From the above example: How to Resync Individual Table, we demonstrated to reset the replicat by issuing an “Alter” statement:  GGSCI > alter replicat <rep_name> extseno <above_trailfile#>,  extrba <above_rba#>

• OGG processes can be altered to position to any point in the redo log (for extract) and in the trail file (for pump and replicat). The position can either be a timestamp or a specific byte position.

How to Skip an Operation or Transaction
·       One can skip a transaction for the extract by using the following command. But please be aware that if the transaction contains data for replicating table, this operation will introduce data lose. The command may be useful when there is a long running transaction by a user which you are sure that user will not change any replication table data. Whether the long running transaction changes any replicating table can be confirmed by run logmnr.  The following is an example on how to skip a transaction on extract.
GGSCI 13>  send eora skiptrans 2.12.2806
Sending skiptrans request to EXTRACT EORA ...
Are you sure you sure you want to skip transaction [XID 2.12.2806, Redo Thread 1 ,
Start Time 2012-05-23:16:31:22, SCN 0.6561561 (6561561)]? (y/n)y
Sending skiptrans request to EXTRACT EORA ...
Transaction [XID 2.12.2806, Redo Thread 1, Start Time 2012-05-23:16:31:22, SCN 0.6561561 (6561561)] skipped.

• To skip operation on Replicat, one can position the replicat to a RBA right after the skipped record. The techniques can be used here are: A) Using an “END” parameter with a timestamp to tell when replicat stops while skip the bad record (either ignore or discard, or even comment out the table). Then comment out the “END” parameter and resume normal replication. B) run the command: GGSCI > start replicat skiptransaction. This will cause the all the operations of the first transaction to be skipped. And normal replication resume from the next transaction.

Warning: skip transaction in a production replication may cause data loss!
Script to Find Long Running Transactions
·       To find any DML transaction which is currently holding a lock on an object?
col username for a15;
col osuser for a15;
col program for a45;
set lines 150;
set pages 100;
alter session set nls_date_format='DD-MON-YY,HH24:MI:SS';
select s.saddr,s.sid,s.username,s.osuser,s.status,s.PROGRAM,t.START_TIME
from gv$session s, gv$transaction t where s.saddr = t.SES_ADDR
order by t.start_time desc;
 
·       To find what object is locked by the above session, run the following query. If you want more info modify the following query accordingly.
Select session_id, d.object_name from gv$locked_object l, dba_objects d
Where l.object_id = d.object_id;

You can learn more about oracle database administration: 

Pro Oracle GoldenGate for the DBA

Oracle Security Tools Installation and Configuration

Expert Oracle Exadata 

Oracle Database 12c Release 2 Real Application Clusters Handbook: Concepts, Administration, Tuning & Troubleshooting

Expert Oracle RAC 12c

 ==========================================================

Please check our other blogs for GoldenGate.