The Integrated Capture is in the STARTING State for a long time.
Due to some error, one of my Integrated Extract process got ABENDED. I resolved the issue and started the Extract process. I checked the status of the Extract process by issuing the command INFO EXTRACT EXTRACT_NAME
GGSCI (OGGSRV1) 74> info extract UODSEXT
EXTRACT UODSEXT Last Started 2014-12-16 23:58 Status STARTING
Checkpoint Lag 00:42:20 (updated 00:21:05 ago)
Process ID 27525240
Log Read Checkpoint Oracle Integrated Redo Logs
2014-12-16 23:15:45
SCN 1709.4172744209 (7344271853073)
From the above output, I came to know that the Extract process is getting started. Always an Integrated process will take
time to start as it needs to bind with the Logminer Process of Oracle for capturing changes from the Redo logfiles.
I just waited for a long time and again checked the status. But the Status was still STARTING.
GGSCI (OGGSRV1) 76> info extract UODSEXT
EXTRACT UODSEXT Last Started 2014-12-16 23:58 Status STARTING
Checkpoint Lag 00:42:20 (updated 00:47:21 ago)
Process ID 27525240
Log Read Checkpoint Oracle Integrated Redo Logs
2014-12-16 23:15:45
SCN 1709.4172744209 (7344271853073)
Meanwhile something got messed up with my Extract Process 🙁
GGSERR.log file displayed:
2014-12-17 00:01:04 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, uodsext.prm: EXTRACT UODSEXT starting.
2014-12-17 00:01:04 INFO OGG-03059 Oracle GoldenGate Capture for Oracle, uodsext.prm: Operating system character set identified as ISO-8859-1.
2014-12-17 00:01:04 INFO OGG-02695 Oracle GoldenGate Capture for Oracle, uodsext.prm: ANSI SQL parameter syntax is used for parameter parsing.
2014-12-17 00:01:04 INFO OGG-03522 Oracle GoldenGate Capture for Oracle, uodsext.prm: Setting session time zone to source database time zone ‘-04:00’.
2014-12-17 00:01:04 INFO OGG-01815 Oracle GoldenGate Capture for Oracle, uodsext.prm: Virtual Memory Facilities for: BR
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/ogg/BR/UODSEXT.
2014-12-17 00:01:04 INFO OGG-01851 Oracle GoldenGate Capture for Oracle, uodsext.prm: filecaching started: thread ID: 1286.
2014-12-17 00:01:04 INFO OGG-01815 Oracle GoldenGate Capture for Oracle, uodsext.prm: Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
There are queries to manage the Integrated Capture process. Using one of the query I came to know the reason for the Capture Process to get stuck.,
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A7
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A20
SQL> COLUMN TOTAL_MESSAGES_CAPTURED HEADING ‘Redo|Entries|Evaluated|In Detail’ FORMAT 999999999
SQL> COLUMN TOTAL_MESSAGES_ENQUEUED HEADING ‘Total|LCRs|Enqueued’ FORMAT 9999999999
SQL>
SQL> SELECT c.CAPTURE_NAME,
2 SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(‘)+1,4) PROCESS_NAME,
3 c.SID,
4 c.SERIAL#,
5 c.STATE,
6 c.TOTAL_MESSAGES_CAPTURED,
7 c.TOTAL_MESSAGES_ENQUEUED
8 FROM V$STREAMS_CAPTURE c, V$SESSION s
9 WHERE c.SID = s.SID AND
10 c.SERIAL# = s.SERIAL#;
Redo
Capture Session Entries Total
Capture Process Session Serial Evaluated LCRs
Name Number ID Number State In Detail Enqueued
——- ——- ——- ——– ——————– ———- ———–
OGG$CAP TNS 134 11457 WAITING FOR TRANSACT 26742479 25460088
_RMLOBE ION
XT
OGG$CAP TNS 142 1 WAITING FOR TRANSACT 94714177 89206546
_TRAEXT ION
OGG$CAP TNS 274 99 WAITING FOR TRANSACT 21663435 20668294
_MRPTEX ION
T
OGG$CAP TNS 454 20097 WAITING FOR TRANSACT 21003375 20014093
_PCEXT ION
OGG$CAP TNS 587 5 WAITING FOR TRANSACT 128758652 124339162
_ODSEXT ION
OGG$CAP TNS 648 3 WAITING FOR TRANSACT 252487688 247183570
_INVDME ION
XT
OGG$CAP TNS 849 559 WAITING FOR TRANSACT 90116125 87271709
_MIEXT ION
OGG$CAP TNS 1221 923 WAITING FOR DICTIONA 0 0
_UODSEX RY REDO
T
OGG$CAP TNS 1228 1 WAITING FOR TRANSACT 97237138 91719590
_ODSNEX ION
T
OGG$CAP TNS 1229 93 WAITING FOR TRANSACT 135499966 129968076
_UTLEXT ION
OGG$CAP TNS 1353 29971 WAITING FOR TRANSACT 45884211 44309735
_RMEXT ION
OGG$CAP TNS 1476 8695 WAITING FOR TRANSACT 20617577 19647302
_QSEXT ION
12 rows selected.
There might be many reasons for Oracle GoldenGate Integrated Capture process to get stuck. They are below.,
- INITALIZING / DICTIONARY INITIALIZATION (the state alternates between these states), or
- WAITING FOR DICTIONARY REDO: FIRST SCN , or
- WAITING FOR REDO: LAST SCN MINED , or
- WAITING FOR DICTIONARY REDO: FILE
Using the below query I checked if the Capture process was waiting for any archivelog files.
There is a query to display the information about each archive redo log file required by the Integrated Capture Process in a database.
SQL> COLUMN CONSUMER_NAME HEADING ‘Capture|Process|Name’ FORMAT A15
SQL> COLUMN SOURCE_DATABASE HEADING ‘Source|Database’ FORMAT A10
SQL> COLUMN SEQUENCE# HEADING ‘Sequence|Number’ FORMAT 99999
SQL> COLUMN NAME HEADING ‘Required|Archived Redo Log|File Name’ FORMAT A60
SQL>
SQL> SELECT r.CONSUMER_NAME,
2 r.SOURCE_DATABASE,
3 r.SEQUENCE#,
4 r.NAME
5 FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
6 WHERE r.CONSUMER_NAME = c.CAPTURE_NAME AND
7 r.NEXT_SCN >= c.REQUIRED_CHECKPOINT_SCN;
Capture Required
Process Source Sequence Archived Redo Log
Name Database Number File Name
————— ———- ——– ————————————————————
OGG$CAP_UODSEXT GGDB1 33904 /vol1/oradata/arch/GGDB1/archlogs/GGDB1_1_33904_842874721.ARC
OGG$CAP_UODSEXT GGDB1 33905 /vol1/oradata/arch/GGDB1/archlogs/GGDB1_1_33905_842874721.ARC
The output clearly showed that the Integrated Capture process UODSEXT was waiting for the archive logs sequence 33904 and 33905. I got a doubt and checked if the
required sequences exists in the archivelog directory. But unfortunately it was missing.
total 886304
-rwxrwx— 1 oracle oradba 453784576 Dec 16 23:55 GGDB1_1_33909_842874721.ARC
OGGSRV1:/vol1/oradata/arch/GGDB1/archlogs />
REASON: The Archivelogs got delete / removed because a script was scheduled in the CRONTAB for removing the archivelogs.
SUGGESTION: One should have a sufficient number of archivelogs physically present in the disk.
From Oracle Oracle GoldenGate 11g, Extract can be integrated with Oracle RMAN to manage log retention. Using RMAN for deleting the archivelog files will be highly recommended and
will be a best practice, because RMAN does not remove or deletes the archivelog files which are required by the Classic / Integrated Extract Processes.
LOGRETENTION is the parameter introduced from Oracle GoldenGate 11g.
Classic Capture: if you want to ensure that RMAN will retain any needed logs, you must enable LOGRETENTION.
Integrated Capture: LOGRETENTION is automatically enabled.
In this mode, Extract can be integrated with Oracle RMAN to manage log retention.
Loading ...
Total Users : 1768585