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