Princeton IT ServicesResources / Blogs
Goldengate Integrated Extract – Identifying and resolving extract process hang issue
[vc_row][vc_column][vc_row_inner][vc_column_inner][vc_raw_html]R29sZGVuZ2F0ZSUyMGV4dHJhY3QlMjBwcm9jZXNzJTIwY2FuJTIwaGFuZyUyMGZvciUyMHZhcmlvdXMlMjByZWFzb25zLiUyMEl0JTIwaXMlMjB2ZXJ5JTIwaW1wb3J0YW50JTIwdG8lMjBoYXZlJTIwYSUyMHByb2Nlc3MlMjBpbiUyMHBsYWNlJTIwdG8lMjBtb25pdG9yJTIwZ29sZGVuZ2F0ZSUyMHByb2Nlc3MlMjBhbmQlMjBkZXRlY3QlMjBoYW5naW5nJTIwaXNzdWVzLiUyMEklMjByZWNlbnRseSUyMGVuY291bnRlcmVkJTIwR29sZGVuZ2F0ZSUyMEV4dHJhY3QlMjBIYW5nJTIwaXNzdWUuJTIwQmVsb3clQzIlQTBpcyUyMGhvdyUyMEklMjByZXNvbHZlZCUyMHRoaXMlMjBpc3N1ZSUzQSUwQSUwQUNvcHklMjB0aGUlMjBmb2xsb3dpbmclMjBTUUwlMjBhbmQlMjBzYXZlJTIwdG8lMjBmaWxlJTIwLSUyMGRiYV9jYXB0dXJlX2luZm8uc3FsJTNBJTBBJTNDcHJlJTNFJTNDY29kZSUzRWNvbCUyMENBUFRVUkVfTkFNRSUyMGZvciUyMGEyMCUzQiUwQWNvbCUyMFFVRVVFX05BTUUlMjBmb3IlMjBhMTUlM0IlMEFjb2wlMjBTVEFSVF9TQ04lMjBmb3IlMjA5OTk5OTk5OTk5JTNCJTBBY29sJTIwU1RBVFVTJTIwZm9yJTIwYTEwJTNCJTBBY29sJTIwQ0FQVFVSRURfU0NOJTIwZm9yJTIwOTk5OTk5OTk5OSUzQiUwQWNvbCUyMEFQUExJRURfU0NOJTIwZm9yJTIwOTk5OTk5OTk5OSUzQiUwQWNvbCUyMFNPVVJDRV9EQVRBQkFTRSUyMGZvciUyMGExMCUzQiUwQWNvbCUyMExPR01JTkVSX0lEJTIwZm9yJTIwOTk5OTk5OSUzQiUwQWNvbCUyMFJFUVVJUkVEX0NIRUNLUE9JTlRTQ04lMjBmb3IlMjBhMzAlM0IlMEFjb2wlMjBTVEFUVVNfQ0hBTkdFX1RJTUUlMjBmb3IlMjBhMTUlM0IlMEFjb2wlMjBFUlJPUl9OVU1CRVIlMjBmb3IlMjBhMTUlM0IlMEFjb2wlMjBFUlJPUl9NRVNTQUdFJTIwZm9yJTIwYTEwJTNCJTBBY29sJTIwU1RBUlRfVElNRSUyMGZvciUyMGEzMCUwQWNvbCUyMENBUFRVUkVfVFlQRSUyMGZvciUyMGExMCUzQiUwQVNFTEVDVCUyMENBUFRVUkVfTkFNRSUyQyUyMFFVRVVFX05BTUUlMkMlMjBTVEFSVF9TQ04lMkMlMjBTVEFUVVMlMkMlMEFDQVBUVVJFRF9TQ04lMkMlMjBBUFBMSUVEX1NDTiUyQyUyMFNPVVJDRV9EQVRBQkFTRSUyQyUwQUxPR01JTkVSX0lEJTJDJTIwUkVRVUlSRURfQ0hFQ0tQT0lOVF9TQ04lMkMlMEFTVEFUVVNfQ0hBTkdFX1RJTUUlMkMlMjBFUlJPUl9OVU1CRVIlMkMlMjBFUlJPUl9NRVNTQUdFJTJDJTBBQ0FQVFVSRV9UWVBFJTJDJTIwU1RBUlRfVElNRSUwQUZST00lMjBEQkFfQ0FQVFVSRSUzQiUwQSUwQSUzQyUyRmNvZGUlM0UlM0MlMkZwcmUlM0UlMEExLiUyMEV4ZWN1dGUlMjB0aGUlMjBmb2xsb3dpbmclMjBRdWVyeSUyMHRvJTIwaWRlbnRpZnklMjB0aGUlMjBzdGF0dXMlMjBvZiUyMGNhcHR1cmUlMjBhbmQlMjBpZGVudGlmaWVkJTIwdGhhdCUyMHRoZXJlJTIwaXMlMjBhJTIwZGlzLWNvbm5lY3QlMjBiZXR3ZWVuJTIwREIlMjBhbmQlMjBHRyUyMFByb2Nlc3MuJTBBJTNDcHJlJTNFJTNDY29kZSUzRVNRTCUyNmd0JTNCJTIwJTQwZGJhX2NhcHR1cmVfaW5mby5zcWwlMEElMEFDQVBUVVJFX05BTUUlMjBRVUVVRV9OQU1FJTIwU1RBUlRfU0NOJTIwU1RBVFVTJTIwQ0FQVFVSRURfU0NOJTBBLS0tLS0tLS0tLS0tLS0tLS0tLS0lMjAtLS0tLS0tLS0tLS0tLS0lMjAtLS0tLS0tLS0tLSUyMC0tLS0tLS0tLS0lMjAtLS0tLS0tLS0tLS0lMEFBUFBMSUVEX1NDTiUyMFNPVVJDRV9EQVQlMjBMT0dNSU5FUl9JRCUyMFJFUVVJUkVEX0NIRUNLUE9JTlRfU0NOJTIwU1RBVFVTX0NIQU5HRV9UJTBBLS0tLS0tLS0tLS0lMjAtLS0tLS0tLS0tJTIwLS0tLS0tLS0tLS0lMjAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSUyMC0tLS0tLS0tLS0tLS0tLSUwQUVSUk9SX05VTUJFUiUyMEVSUk9SX01FU1MlMjBDQVBUVVJFX1RZJTIwU1RBUlRfVElNRSUwQS0tLS0tLS0tLS0tLSUyMC0tLS0tLS0tLS0lMjAtLS0tLS0tLS0tJTIwLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tJTBBT0dHJTI0Q0FQX0VEX0FQUF9CJTIwT0dHJTI0UV9FRF9BUFBfQiUyMDUwMDg4NzU5NjIlMjBESVNBQkxFRCUyMDUwNjE1NjQxMjYlMEE1MDYxNTY0MTE4JTIwTlVUUklRQSUyMDEzJTIwNTA2MTU2NDExOCUyMDA0LU5PVi0xNSUwQUxPQ0FMJTBBJTBBT0dHJTI0Q0FQX0VEX0FQUF9BJTIwT0dHJTI0UV9FRF9BUFBfQSUyMDUwMDc3NDEzNzIlMjBFTkFCTEVEJTIwNTA2MTY2ODMyNCUwQTUwNjE2NjgzMTQlMjBOVVRSSVFBJTIwMTIlMjA1MDYxNjY4MzE0JTIwMDQtTk9WLTE1JTBBTE9DQUwlMEElMEElM0MlMkZjb2RlJTNFJTNDJTJGcHJlJTNFJTBBMi4lMjBJJTIwdHJpZWQlMjBkb2luZyUyMG5vcm1hbCUyMHN0b3AuJTIwTm9ybWFsJTIwc3RvcCUyMGRpZCUyMG5vdCUyMHdvcmslMjBhcyUyMHRoZSUyMHByb2Nlc3MlMjB3YXMlMjBqdXN0JTIwaGFuZ2luZy4lMEElMEElM0NwcmUlM0UlM0Njb2RlJTNFR0dTQ0klMjAlMjhsb2NhbGhvc3QlMjBhcyUyMGdnYWRtaW4lNDBQUUFEQjIlMjklMjAzJTI2Z3QlM0IlMjBzdG9wJTIwRURfQVBQX0IlMEElMEFTZW5kaW5nJTIwU1RPUCUyMHJlcXVlc3QlMjB0byUyMEVYVFJBQ1QlMjBFRF9BUFBfQiUyMC4uLiUwQSUwQUVSUk9SJTNBJTIwc2VuZGluZyUyMG1lc3NhZ2UlMjB0byUyMEVYVFJBQ1QlMjBFRF9BUFBfQiUyMCUyOFRpbWVvdXQlMjB3YWl0aW5nJTIwZm9yJTIwbWVzc2FnZSUyOS4lMEElM0MlMkZjb2RlJTNFJTNDJTJGcHJlJTNFJTBBJTNDcCUzRTMuJTIwQXMlMjB0aGUlMjBwcm9jZXNzJTIwd2FzJTIwaGFuZ2luZyUyQyUyMEklMjBkaWQlMjAlMjJraWxsJTIyJTIwdGhlJTIwZXh0cmFjdCUyMHByb2Nlc3MuJTNDJTJGcCUzRSUwQSUzQ3ByZSUzRSUzQ2NvZGUlM0VHR1NDSSUyMCUyOGxvY2FsaG9zdCUyMGFzJTIwZ2dhZG1pbiU0MFBRQURCMiUyOSUyMDUlMjZndCUzQiUyMGtpbGwlMjBleHRyYWN0JTIwRURfQVBQX0IlMEFTZW5kaW5nJTIwS0lMTCUyMHJlcXVlc3QlMjB0byUyME1BTkFHRVIlMjAuLi4lMEElM0MlMkZjb2RlJTNFJTNDJTJGcHJlJTNFJTBBJTBBJTNDcCUzRTQuJTIwQ2hlY2slMjB0aGUlMjBjdXJyZW50JTIwc3RhdHVzJTIwYnklMjBpc3N1aW5nJTIwJTIyaW5mbyUyMGFsbCUyMiUyMGNvbW1hbmQlM0ElM0MlMkZwJTNFJTBBJTNDcHJlJTNFJTNDY29kZSUzRSUwQUdHU0NJJTIwJTI4bG9jYWxob3N0JTIwYXMlMjBnZ2FkbWluJTQwUFFBREIyJTI5JTIwNiUyNmd0JTNCJTIwaW5mbyUyMGFsbCUyMCUwQVByb2dyYW0lMjBTdGF0dXMlMjBHcm91cCUyMExhZyUyMGF0JTIwQ2hrcHQlMjBUaW1lJTIwU2luY2UlMjBDaGtwdCUwQSUwQU1BTkFHRVIlMjBSVU5OSU5HJTBBSkFHRU5UJTIwUlVOTklORyUwQUVYVFJBQ1QlMjBSVU5OSU5HJTIwRURfQVBQX0ElMjAwMCUzQTAwJTNBMDYlMjAwMCUzQTAwJTNBMDQlMEFFWFRSQUNUJTIwU1RPUFBFRCUyMEVEX0FQUF9CJTIwMDElM0EzMCUzQTA2JTIwMDAlM0EyNCUzQTQ4JTBBRVhUUkFDVCUyMFJVTk5JTkclMjBQRF9BUFBfQSUyMDAwJTNBMDAlM0EwMCUyMDAwJTNBMDAlM0EwMCUwQUVYVFJBQ1QlMjBSVU5OSU5HJTIwUERfQVBQX0IlMjAwMCUzQTAwJTNBMDAlMjAwMCUzQTAwJTNBMDclMEFSRVBMSUNBVCUyMFJVTk5JTkclMjBSRF9BUFBfQSUyMDAwJTNBMDAlM0EwMCUyMDAwJTNBMDAlM0EwMSUwQVJFUExJQ0FUJTIwUlVOTklORyUyMFJEX0FQUF9CJTIwMDAlM0EwMCUzQTAwJTIwMDAlM0EwMCUzQTA2JTBBJTNDJTJGY29kZSUzRSUzQyUyRnByZSUzRSUwQSUzQ3AlM0U1LiUyMFN0YXJ0JTIwdGhlJTIwZXh0cmFjdCUzQSUzQyUyRnAlM0UlMEElM0NwcmUlM0UlM0Njb2RlJTNFJTBBR0dTQ0klMjAlMjhsb2NhbGhvc3QlMjBhcyUyMGdnYWRtaW4lNDBQUUFEQjIlMjklMjA4JTI2Z3QlM0IlMjBzdGFydCUyMEVEX0FQUF9CJTBBU2VuZGluZyUyMFNUQVJUJTIwcmVxdWVzdCUyMHRvJTIwTUFOQUdFUiUyMC4uLiUwQUVYVFJBQ1QlMjBFRF9BUFBfQiUyMHN0YXJ0aW5nJTBBJTNDJTJGY29kZSUzRSUzQyUyRnByZSUzRQ==[/vc_raw_html][/vc_column_inner][/vc_row_inner][/vc_column][/vc_row]
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
LOCAL2. 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:065. Start the extract:
GGSCI (localhost as ggadmin@PQADB2) 8> start ED_APP_B
Sending START request to MANAGER ...
EXTRACT ED_APP_B starting
30 TB moved with zero downtime
