Oracle GoldenGate - Version 12.2.0.1.0 and later Information in this document applies to any platform. SYMPTOMS Can not register extract process.
GGSCI > register extract EX0000 database 2017-10-25 17:25:48 ERROR OGG-08221 Cannot register or unregister EXTRACT EX0000 because of the following SQL error: OCI Error 24,062.
CAUSE OCI Error 24,062 means ORA-24062: ORA-24062 : "Subscriber table %s inconsistent with queue table %s"
This is an issue of AQ Metadata corruption Orphan objects left in the AQ metadata dictionary, possibly from a previous failed attempt to register the extract SOLUTION
After enabling event 24062 errorstack and tracing the registration session, the trace shows:
----- Error Stack Dump ----- ORA-24062: Subscriber table GGATE.AQ$_OGG$Q_<<Queue-Table-Name>>_EX000_S inconsistent with queue table GGATE.OGG$<<Queue-Table-Name>>_EX000 . .
Further checking :
sqlplus /nolog connect / as sysdba SELECT object_id, object_name, object_type FROM dba_objects WHERE owner='GGATE' and object_name like '%<<Queue-Table-Name>>_EX0%';
OBJECT_ID OBJECT_NAME OBJECT_TYPE ------------------------------------------- 2434445 AQ$OGG$<<Queue-Table-Name>>_EX0000 VIEW 2435426 AQ$OGG$<<Queue-Table-Name>>_EX0000_R VIEW 2435425 AQ$OGG$<<Queue-Table-Name>>_EX0000_S VIEW 2288967 AQ$_OGG$<<Queue-Table-Name>>_EX0000_C TABLE 2288962 AQ$_OGG$<<Queue-Table-Name>>_EX0000_D TABLE 2434452 AQ$_OGG$<<Queue-Table-Name>>_EX0000_E QUEUE 2288957 AQ$_OGG$<<Queue-Table-Name>>_EX0000_G TABLE 2288960 AQ$_OGG$<<Queue-Table-Name>>_EX0000_H TABLE 2288953 AQ$_OGG$<<Queue-Table-Name>>EX0000_I TABLE 2434588 AQ$_OGG$<<Queue-Table-Name>>_EX0000_N SEQUENCE 2289564 AQ$_OGG$<<Queue-Table-Name>>_EX0000_P TABLE 2288951 AQ$_OGG$<<Queue-Table-Name>>_EX0000_T TABLE 2434443 AQ$_OGG$<<Queue-Table-Name>>_EX0000_V EVALUATION CONTEXT 2289970 AQ$_OGG$<<Queue-Table-Name>>_EX0000_Y INDEX 2289604 OGG$<<Queue-Table-Name>>_EX0000 TABLE 15 rows selected.
There is not an object called AQ$_OGG$<<Queue-Table-Name>>_EX0000_S (this is the Subscriber table) Which indicates orphan objects (AQ metadata corruption).
To clean up: 1) Drop the queue and underlying objects:
sqlplus /nolog connect / as sysdba
exec dbms_aqadm.drop_queue_table('GGATE.OGG$<<Queue-Table-Name>>_EX0000', true);
-- After this successfully executes, below query should return no rows
SELECT object_id, object_name, object_type FROM dba_objects WHERE owner='GGATE' and object_name like '%<<Queue-Table-Name>>_EX0%';
2) Execute:
sqlplus /nolog connect / as sysdba
exec dbms_streams_adm.remove_streams_configuration
Then attempt to register the Integrated Extract again. NOTE: In the images and/or the document content above, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner. | |
Show More Didn't find what you are looking for? Ask in Community... |