Monitoring GoldenGate and understanding process & commands
Manager : information command
=========================
1,
info all
Lag at Chkpt : how lag particular processor have
Time Since Chkpt : whn was the last check point happned
=====================================
2,
status manager
Manager is running (IP port testdb2.xxxx.xxx.7809, Process ID 14092).
here you can the :Process ID 14092
ip address
port number
processor status
================================
3,
send manager childstatus ----- > this command show child processor of the manager
ID Group Process Retry Retry Time Start Time
---- -------- --------- ----- ------------------- -------------------
0 REPDDL 14692 0 None 2016/06/23 18:14:55
1 REPPMP 15769 0 None 2016/06/23 19:23:24
3 MYLOAD2 19313 0 None 2016/06/23 20:35:21
Extract : Related command
============= ===================
1,
info extract ext1, detail
GGSCI (testdb1.sample.com) 2> info extract FIRSTDDL, detail
EXTRACT FIRSTDDL Last Started 2016-06-23 18:11 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 19090
Log Read Checkpoint Oracle Redo Logs
2016-06-24 01:55:53 Seqno 15, RBA 18578944
SCN 0.1228860 (1228860)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/u01/app/oracle/goldengate/dirdat/rt 0 588383 500 RMTTRAIL
Extract Source Begin End
/u01/app/oracle/oradata/testdb1/redo03.log 2016-06-23 17:47 2016-06-24 01:55
Not Available * Initialized * 2016-06-23 17:47
Not Available * Initialized * 2016-06-23 17:47
Not Available * Initialized * 2016-06-23 17:47
Current directory /u01/app/oracle/goldengate
Report file /u01/app/oracle/goldengate/dirrpt/FIRSTDDL.rpt
Parameter file /u01/app/oracle/goldengate/dirprm/firstddl.prm
Checkpoint file /u01/app/oracle/goldengate/dirchk/FIRSTDDL.cpe
Process file /u01/app/oracle/goldengate/dirpcs/FIRSTDDL.pce
Error log /u01/app/oracle/goldengate/ggserr.log
====================================================
2,
send extract ext1 status
GGSCI (testdb1) 2> send extract ext2 status
ERROR: EXTRACT EXT2 not currently running.
GGSCI (testdb1.sample.com) 3> send extract PUMPEXT1 status
Sending STATUS request to EXTRACT PUMPEXT1 ...
EXTRACT PUMPEXT1 (PID 20113)
Current status: Recovery complete: At EOF
Current read position:
Sequence #: 1
RBA: 4371
Timestamp: 2016-06-23 20:09:23.000000
Extract Trail: /u01/app/oracle/goldengate/dirdat/lt
Current write position:
Sequence #: 0
RBA: 21129991
Timestamp: 2016-06-24 01:58:44.459560
Extract Trail: /u01/app/oracle/goldengate/dirdat/pt
=============================================================
3,
stats extract PUMPEXT1
GGSCI (testdb1.sample.com) 4> stats extract PUMPEXT1
Sending STATS request to EXTRACT PUMPEXT1 ...
Start of Statistics at 2016-06-24 02:00:28.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 2.00
Output to /u01/app/oracle/goldengate/dirdat/pt:
Extracting from GGS_OWNER.SECOND to GGS_OWNER.SECOND:
*** Total statistics since 2016-06-23 19:30:36 ***
Total inserts 69532.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 69532.00
*** Daily statistics since 2016-06-23 19:30:36 ***
Total inserts 69532.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 69532.00
*** Hourly statistics since 2016-06-23 19:30:36 ***
Total inserts 69532.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 69532.00
*** Latest statistics since 2016-06-23 19:30:36 ***
Total inserts 69532.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 69532.00
End of Statistics.
=========================================================
4
stats extract ext2 reportrate hr --> hr, min, sec can be used to check the processing rate
GGSCI (testdb1.sample.com) 5> stats extract PUMPEXT1 reportrate min
Sending STATS request to EXTRACT PUMPEXT1 ...
Start of Statistics at 2016-06-24 02:02:47.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 2.00
Output to /u01/app/oracle/goldengate/dirdat/pt:
Extracting from GGS_OWNER.SECOND to GGS_OWNER.SECOND:
*** Total statistics since 2016-06-23 19:30:36 ***
Total inserts/minute: 177.30
Total updates/minute: 0.00
Total deletes/minute: 0.00
Total discards/minute: 0.00
Total operations/minute: 177.30
*** Daily statistics since 2016-06-23 19:30:36 ***
Total inserts/minute: 177.30
Total updates/minute: 0.00
Total deletes/minute: 0.00
Total discards/minute: 0.00
Total operations/minute: 177.30
*** Hourly statistics since 2016-06-23 19:30:36 ***
Total inserts/minute: 177.30
Total updates/minute: 0.00
Total deletes/minute: 0.00
Total discards/minute: 0.00
Total operations/minute: 177.30
*** Latest statistics since 2016-06-23 19:30:36 ***
Total inserts/minute: 177.30
Total updates/minute: 0.00
Total deletes/minute: 0.00
Total discards/minute: 0.00
Total operations/minute: 177.30
End of Statistics.
====================================================
5,
send extract PUMPEXT1, getlag
GGSCI (testdb1) 6> send extract PUMPEXT1, getlag
Sending GETLAG request to EXTRACT PUMPEXT1 ...
Last record lag 3 seconds.
At EOF, no more records to process.
=======================================================
6,
lag extract ext2
GGSCI (testdb1.sample.com) 9> lag extract SECONDEX
2016-06-24 02:06:19 INFO OGG-14054 Lag from heartbeat table requires DBLOGIN.
Sending GETLAG request to EXTRACT SECONDEX ...
Last record lag 2 seconds.
At EOF, no more records to process.
Replicat : Related Commands
=========================
1,
info replicat rep1, detail
GGSCI (testdb2.sample.com) 7> info replicat MYLOAD2, detail
REPLICAT MYLOAD2 Last Started 2016-06-23 23:37 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Process ID 19313
Log Read Checkpoint File /u01/app/oracle/goldengate/dirdat/bb000000000
First Record RBA 0
Current Log BSN value: (requires database login)
Last Committed Transaction CSN value: (requires database login)
Extract Source Begin End
/u01/app/oracle/goldengate/dirdat/bb000000000 * Initialized * First Record
/u01/app/oracle/goldengate/dirdat/bb000000000 * Initialized * First Record
/u01/app/oracle/goldengate/dirdat/bb000000000 * Initialized * First Record
/u01/app/oracle/goldengate/dirdat/bb000000000 * Initialized * First Record
Current directory /u01/app/oracle/goldengate
Report file /u01/app/oracle/goldengate/dirrpt/MYLOAD2.rpt
Parameter file /u01/app/oracle/goldengate/dirprm/myload2.prm
Checkpoint file /u01/app/oracle/goldengate/dirchk/MYLOAD2.cpr
Checkpoint table ggs_owner.CHKPTAB
Process file /u01/app/oracle/goldengate/dirpcs/MYLOAD2.pcr
Error log /u01/app/oracle/goldengate/ggserr.log
=========================================================
2,
send replicat rep1 status
GGSCI (testdb2.sample.com) 10> send replicat REPDDL status
Sending STATUS request to REPLICAT REPDDL ...
Current status: At EOF
Sequence #: 0
RBA: 588383
0 records in current transaction
========================================
3,
stats replicat rep1
GGSCI (testdb2.sample.com) 11> stats replicat REPDDL
Sending STATS request to REPLICAT REPDDL ...
Start of Statistics at 2016-06-24 02:10:30.
Replicating from GGS_OWNER.FIRST to GGS_OWNER.FIRST:
*** Total statistics since 2016-06-23 18:21:36 ***
Total inserts 0.00
Total updates 0.00
Total deletes 1998.00
Total discards 0.00
Total operations 1998.00
*** Daily statistics since 2016-06-24 00:00:00 ***
No database operations have been performed.
*** Hourly statistics since 2016-06-24 02:00:00 ***
No database operations have been performed.
*** Latest statistics since 2016-06-23 18:21:36 ***
Total inserts 0.00
Total updates 0.00
Total deletes 1998.00
Total discards 0.00
Total operations 1998.00
End of Statistics.
=================================================
4,
stats replicat myload2 reportrate sec --> hr, min, sec can be used to check the processing rate
GGSCI (testdb2.sample.com) 15> stats replicat REPPMP reportrate min
Sending STATS request to REPLICAT REPPMP ...
Start of Statistics at 2016-06-24 02:12:16.
DDL replication statistics:
*** Total statistics since replicat started ***
Operations 2.00
Mapped operations 2.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Errors 0.00
Retried errors 0.00
Discarded errors 0.00
Ignored errors 0.00
Replicating from GGS_OWNER.SECOND to GGS_OWNER.SECOND:
*** Total statistics since 2016-06-23 19:30:37 ***
Total inserts/minute: 173.12
Total updates/minute: 0.00
Total deletes/minute: 0.00
Total discards/minute: 0.00
Total operations/minute: 173.12
*** Daily statistics since 2016-06-24 00:00:00 ***
No database operations have been performed.
*** Hourly statistics since 2016-06-24 02:00:00 ***
No database operations have been performed.
*** Latest statistics since 2016-06-23 19:30:37 ***
Total inserts/minute: 173.12
Total updates/minute: 0.00
Total deletes/minute: 0.00
Total discards/minute: 0.00
Total operations/minute: 173.12
End of Statistics.
=================================================
5,
send replicat myload2, getlag
GGSCI (testdb2.sample.com) 16> send replicat myload2, getlag
Sending GETLAG request to REPLICAT MYLOAD2 ...
No records yet processed.
At EOF, no more records to process.
lag replicat myload2*
GGSCI (testdb2.sample.com) 17> lag replicat myload2*
2016-06-24 02:13:13 INFO OGG-14054 Lag from heartbeat table requires DBLOGIN.
Sending GETLAG request to REPLICAT MYLOAD2 ...
No records yet processed.
At EOF, no more records to process.
About Open or Long Running Transactions (Which are not committed)
=====================================================
Oracle Golden Gate Replication will not propagate transactions to the destination which have NOT been committed on the source yet.
However Oracle Golden gate will write the open transaction data to the dirtemp directory in the home directory.
This data will then be read back later when the transaction is committed and needs to be propagated to the destination.
This is especially true when bulk transactions are run, processing a large account of the uncommitted data,
which then has to be written temporarily to the dirtemp location.
Open Database Transactions effecting GoldenGate
-----------------------------------------------------------------
SET LINES 300
COL start_time FOR A20
COL sid FOR 99999
COL serial# FOR 999999
COL username FOR A20
COL status FOR A10
COL schemaname FOR A10
COL process FOR A10
COL machine FOR A15
COL program FOR A30
COL module FOR A35
COL logon_time FOR A20
SELECT t.start_time,s.sid,s.serial#,s.username,
s.status, s.schemaname, s.process,s.machine,
s.program, s.module, used_ublk, used_urec,
TO_CHAR(s.logon_time,'mon-dd-yyyy HH24:MI:SS') logon_time
FROM v$transaction t, v$session s
WHERE s.saddr = t.ses_addR
ORDER BY start_time;
Goldengate Issue and Solution
========================
1, No data found (ora-1403) / Unique constraint errors/ primary key violations
=========================================================
Option 1: Skip the transaction using logdump
Option 2: Skip the transaction
Option 3: Correct the entry in the target side.
Option 4: Use handlecollisions parameter in replicat
2, Missing Archive Log File or archive log in ASM
=====================================
In GGS Error Log
-------------------------
2011-05-10 15:13:23 ERROR OGG-00446 Could not find archived log for sequence 92 thread 1 under default destinations SQL <SELECT name FROM v$archived_log WHERE sequence# = :ora_seq_no AND th
read# = :ora_thread AND resetlogs_id = :ora_resetlog_id AND archived = 'YES' AND deleted = 'NO>, error retrieving redo file name for sequence 92, archived = 1, use_alternate = 0Not ab
le to establish initial position for begin time 2011-05-10 14:38:39.
Complete miss of Archive Log
----------------------------------
Extract looking for archive log 92, we dont have archivelog of 92, so if you want to read next archive then issue following command
alter extract capture, extseqno 93
3, Archive Log missing in destination, but in backup
======================================
rman> list backup of archivelog seqno 92;
rman> restore archivelog seqno 92;
./ggsci
stop extract capture
start extract capture
4. Archive Log in ASM
=================
When archive log is in ASM you must configure extract to tranlogoptions to read archives from ASM. Edit the extract definition
edit params ext1
extract capture
userid ggs, password ggs
tranlogoptions asmuser sys@+ASM, asmpassword password
exttrail /data/ggate/dirdat/aa
table test.myobj;
5, dealing with discard file ( doubt)
=====================
discardfile /goldengate/dirout/apply.dsc, append
Source: drop table test.myobj;
./ggsci
delete extract ext1
Target: drop table test.myobj;
./ggsci
delete replicat rep1
6, Missing Trail File
================
2015-04-16 08:52:08 ERROR OGG-01496 Oracle GoldenGate Capture for Oracle, myload2.prm: Failed to open target trail file /data/ggate/dirdat/bb000007, at RBA 12678462.
In case of missing trail file or files, you can create a extract for specific period and run the extract, in this case you must have archives present in the location
###########################
Simulation
###########################
Stop the replicat
stop replicat myload2
On source: perform dml operation
---------------
delete from test.myobj6;
commit;
On target: Remove latest trail file
-------------
On Source: Check the stats of extract and note down the current trail and rba and time stamp
--------------
info extract myload2, detail
send extract myload2, getlag
stats extract myload2 status
stop extract myload2
For example:- In My case the last update showing 09:04, in
EXTRACT MYLOAD2 Last Started 2015-04-16 09:03 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint Oracle Redo Logs
2015-04-16 09:04:54 Seqno 3613, RBA 5719552
SCN 0.806140 (806140)
On target: Remove the current trail file
-------------
On Source: start the extract now it fails to see the trail file.
-------------
start extract myload2
Now you see the Failed to open target trail file /data/ggate/dirdat/bb000007, at RBA 12678462.
On Source :
----------------
stats extract myload2
info extract myload2,detail
######get the time stamp where its last chkpointed or ended####
######so that is where we have to start over our extract and to new file, etrollover will give you new sequence of trail file
######Also take the checkpoint lag time from now and keep the extract begin from that point.
alter extract myload2 begin 2015-04-16 09:30:00
start extract myload2
on Target
------------
######### alter the replicat to look new sequence trail file and start to look from rba 0, means the header
alter replicat myload2 extseq 17 extrba 0
start replicat myload2
