source database - troy
downstream database - crow
target database - london
catalog database - catdb” Lag at Chkpt” is continuosuly increasing but extract is running
GGSCI (oel06) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E1_ICR01 63:46:22 00:00:05
EXTRACT RUNNING E_ICR01 63:46:22 00:00:05
EXTRACT RUNNING P1_ICR01 00:00:00 00:00:02
EXTRACT RUNNING P_ICR01 00:00:00 00:00:01
GGSCI (oel06 as ggadmin@TROY_1) 6> SEND EXTRACT E_ICR01, STATUS
Sending STATUS request to EXTRACT E_ICR01 ...
EXTRACT E_ICR01 (PID 14801)
Current status: In recovery[1]: Processing data
Current read position:
Redo thread #: 0
Sequence #: 0
RBA: 0
Timestamp: 2020-09-25 13:00:08.000000
SCN: 4004.2993996876 (17200043050060) ======================>
Current write position:
Sequence #: 1744
RBA: 1526
Timestamp: 2020-09-28 04:55:51.504882
Extract Trail: /u01/acfs/ogg/EXTRACT/TROY/EA
GoldenGate Integrated Capture and Integrated Replicat Healthcheck Script (Doc ID 1448324.1)
INTEGRATED EXTRACT Not Moving Read RBA In Recovery Mode (Doc ID 1913523.1)
We will run health check script and as per 1448324.1. For our db version (11.2.0.3) we need to run – Integrated Capture Health Check script for Oracle Database 11.2.0.3
if your db is not container db then modify the spool file sction as below otherwise you will receive error
select '&&COLLNAME'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||to_char(sysdate,'YYYYMMDD_HH24MISS')||'.htm' SRDCSPOOLNAME from v$instance, v$database;It will generate an html file. then we will go to ++ GOLDENGATE CAPTURE STATISTICS ++ section then in the thrid table we we “Logminer Last Processed Message = 17200043050060” since we using downstream setup.
you will notice that this is same as we got in extarct status above
OGG$CAP_E1_ICR01 17200043050060 17200049603105
OGG$CAP_E_ICR01 17200043050060 17200049603105now if we go to ++ Registered Log Files for Capture ++ section. we will see that “Archived Log Last SCN” column of 14924 archive seq no is 17200043050060 this means this SCN is on an ARCHIVE LOG boundary.
now see if very next log seq after 12924 is avaialble in downstream db or not. i can see that thread=1 – seq no 14925 is not avaialble in downstream db. I verified the directory and somehow it is missing in downstread db
I checked the source db and its backup is avaiable
OGG$CAP_E1_ICR01 TROY 1 14924 +FRA/crow/archivelog/2020_09_25 /thread_1_seq_14924.586.1052053243 17200040846165 2020-09-25 07:00:33 17200043050060 2020-09-25 13:00:08 2020-09-27 07:54:16 NO NO NO
OGG$CAP_E_ICR01 TROY 1 14924 +FRA/crow/archivelog/2020_09_25 /thread_1_seq_14924.586.1052053243 17200040846165 2020-09-25 07:00:33 17200043050060 2020-09-25 13:00:08 2020-09-27 07:54:16 NO NO NO
if it was availble we need to simply register it in downstream db using
SQL> ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE '+FRA/crow/archivelog/2020_09_25/thread_1_seq_14924.586.1052053243' FOR 'OGG$CAP_E_ICR01';
Database altered.
SQL> ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE '+FRA/crow/archivelog/2020_09_25/thread_1_seq_14924.586.1052053243' FOR 'OGG$CAP_E1_ICR01';
but in our case it is missing so we need to restore it in source from wher eit will fetched automatically by extract
LIST BACKUP OF ARCHIVELOG ALL;
BS Key Size
------- ----------
1578522 205.46M
List of Archived Logs in backup set 1578522
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 14925 17200043050060 25-SEP-20 17200043056355 25-SEP-20
2 15184 17200040846168 25-SEP-20 17200043050078 25-SEP-20
3 15024 17200043050057 25-SEP-20 17200043057012 25-SEP-20restore the missing archive in source db
connect target /
connect catalog RMAN_PROD/RMAN_PROD@oel88:1977/catdb
Starting restore at 28-SEP-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 instance=TROY_1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=488 instance=TROY_1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=103 instance=TROY_1 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=435 instance=TROY_1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=14925
channel ORA_DISK_1: reading from backup piece +FRA/TROY/backupset/2020_09_25/annnf0_arch_TROY_25sep20_13_00_0_0.5447.1052053255
channel ORA_DISK_1: piece handle=+FRA/TROY/backupset/2020_09_25/annnf0_arch_TROY_25sep20_13_00_0_0.5447.1052053255 tag=ARCH_TROY_25SEP20_13_00_01
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 28-SEP-20ASMCMD> pwd
+FRA/TROY/ARCHIVELOG
ASMCMD> cd 2020_09_28/
ASMCMD> ls
thread_1_seq_14925.6083.1052287005now extract will automatically start processing transactions
GGSCI (oel06 as ggadmin@TROY_1) 31> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E1_ICR01 00:00:05 00:00:07
EXTRACT RUNNING E_ICR01 00:00:03 00:00:04
EXTRACT RUNNING P1_ICR01 00:00:00 00:00:02
EXTRACT RUNNING P_ICR01 00:00:00 00:00:06
replicat status
GGSCI (oel07 as ggadmin@LONDON_2) 50> lag R_ICJ01
Lag Information From Heartbeat Table
LAG AGE FROM TO PATH
4.86s 30.10s TROY LONDON E_ICR01 ==> P_ICR01 ==> R_ICJ01
Sending GETLAG request to REPLICAT R_ICJ01 ...
Last record lag 4 seconds.
At EOF, no more records to process
GGSCI (oel07 as ggadmin@LONDON_2) 51> lag R1_ICJ01
Lag Information From Heartbeat Table
LAG AGE FROM TO PATH
5.72s 36.18s TROY LONDON E1_ICR01 ==> P1_ICR01 ==> R1_ICJ01
Sending GETLAG request to REPLICAT R1_ICJ01 ...
Last record lag 3 seconds.
At EOF, no more records to process
DBATracker 