Goldengate Integrated Extract – Identifying and resolving extract process hang issue

Goldengate extract process can hang for various reasons. It is very important to have a process in place to monitor goldengate process and detect hanging issues. I recently encountered Goldengate Extract Hang issue. Below is how I resolved this issue:

Copy the following SQL and save to file – dba_capture_info.sql:


col CAPTURE_NAME for a20;
col QUEUE_NAME for a15;
col START_SCN for 9999999999;
col STATUS for a10;
col CAPTURED_SCN for 9999999999;
col APPLIED_SCN for 9999999999;
col SOURCE_DATABASE for a10;
col LOGMINER_ID for 9999999;
col REQUIRED_CHECKPOINTSCN for a30;
col STATUS_CHANGE_TIME for a15;
col ERROR_NUMBER for a15;
col ERROR_MESSAGE for a10;
col START_TIME for a30
col CAPTURE_TYPE for a10;
SELECT CAPTURE_NAME, QUEUE_NAME, START_SCN, STATUS,
CAPTURED_SCN, APPLIED_SCN, SOURCE_DATABASE,
LOGMINER_ID, REQUIRED_CHECKPOINT_SCN,
STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE,
CAPTURE_TYPE, START_TIME
FROM DBA_CAPTURE;


1. Execute the following Query to identify the status of capture and identified that there is a dis-connect between DB and GG Process.


SQL> @dba_capture_info.sql

CAPTURE_NAME QUEUE_NAME START_SCN STATUS CAPTURED_SCN
-------------------- --------------- ----------- ---------- ------------
APPLIED_SCN SOURCE_DAT LOGMINER_ID REQUIRED_CHECKPOINT_SCN STATUS_CHANGE_T
----------- ---------- ----------- ----------------------- ---------------
ERROR_NUMBER ERROR_MESS CAPTURE_TY START_TIME
------------ ---------- ---------- ------------------------------
OGG$CAP_ED_APP_B OGG$Q_ED_APP_B 5008875962 DISABLED 5061564126
5061564118 NUTRIQA 13 5061564118 04-NOV-15
LOCAL

OGG$CAP_ED_APP_A OGG$Q_ED_APP_A 5007741372 ENABLED 5061668324
5061668314 NUTRIQA 12 5061668314 04-NOV-15
LOCAL


2. I tried doing normal stop. Normal stop did not work as the process was just hanging.

GGSCI (localhost as ggadmin@PQADB2) 3> stop ED_APP_B

Sending STOP request to EXTRACT ED_APP_B ...

ERROR: sending message to EXTRACT ED_APP_B (Timeout waiting for message).

3. As the process was hanging, I did “kill” the extract process.

GGSCI (localhost as ggadmin@PQADB2) 5> kill extract ED_APP_B

Sending KILL request to MANAGER ... 4. Check the current status by issuing “info all” command: GGSCI (localhost as ggadmin@PQADB2) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING JAGENT RUNNING EXTRACT RUNNING ED_APP_A 00:00:06 00:00:04 EXTRACT STOPPED ED_APP_B 01:30:06 00:24:48 EXTRACT RUNNING PD_APP_A 00:00:00 00:00:00 EXTRACT RUNNING PD_APP_B 00:00:00 00:00:07 REPLICAT RUNNING RD_APP_A 00:00:00 00:00:01 REPLICAT RUNNING RD_APP_B 00:00:00 00:00:06 5. Start the extract: GGSCI (localhost as ggadmin@PQADB2) 8> start ED_APP_B

Sending START request to MANAGER ... EXTRACT ED_APP_B starting

Leave a Reply

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