GoldenGate GGSCI Register / Unregister Extract Failed with OGG-08221 OCI Er

[송지환]

Problem Description: 

GoldenGate GGSCI Register or Unregister Extract failed with following error.

GGSCI (Node 1 as PCS_OWNER@KPMUAT1) 10> register extract EXT_STS database 2016-09-21 14:02:11 ERROR OGG-08221 Cannot register or unregister EXTRACT EXT_STS because of the following SQL error: OCI Error 26,665.

Solution Description: 

The issue is because the streams process is already existing. You have to drop the existing streams process with the same name. You have to use dbms_capture_adm.drop_capture package to drop the capture. 

 1. Find out the capture name using following query.

SQL> SELECT capture_name, queue_owner, queue_name FROM dba_capture; CAPTURE_NAME QUEUE_OWNER QUEUE_NAME ------------------------------ ------------------------------ --------------- OGG$CAP_EXT_STS PCS_OWNER OGG$Q_EXT_STS

advertisements

2. Drop the capture. Pass the capture name as the argument.

Connect to PCS_OWNER user SQL>

begin dbms_capture_adm.drop_capture( capture_name => 'OGG$CAP_EXT_STS', drop_unused_rule_sets => TRUE); end; PL/SQL procedure successfully completed.

SQL> SELECT capture_name, queue_owner, queue_name FROM dba_capture; no rows selected

3. Add the extract again and register.

GGSCI (Node 1 as PCS_OWNER@KPMUAT1) 15> ADD EXTRACT EXT_STS, integrated TRANLOG, BEGIN NOW register extract EXT_STS database ADD EXTTRAIL ./dirdat/gt, EXTRACT EXT_STS MEGABYTES 500 EXTRACT (Integrated) added.

======================================

Answer:  The OGG-01755 error is as follows:

OGG-01755: Cannot register or unregister EXTRACT {0} because of the following SQL error: {1}. See Extract user privileges in the Oracle GoldenGate for Oracle Installation and Setup Guide.

Cause: A REGISTER EXTRACT or UNREGISTER EXTRACT command was issued and an error occurred either while querying the database or when calling a PL/SQL procedure.

Action: Issue DBLOGIN with the appropriate privileges that are required for REGISTER EXTRACT or UNREGISTER EXTRACT. See the Oracle GoldenGate reference documentation.

The OGG-01755 can also be a bug per MOSC 11.2.0.3 Database specific bundle patch for Integrated Extract 11.2.x (Doc ID 1411356.1).

exec dbms_capture_adm.drop_capture('OGG$CAP_MY_EXTRACT', true);
 
This time when I re-ran the "register" command, it completed successfully.

Check whether all the sys object are valid, and check whether all the GoldenGate user objects are valid; you need to re-create but check with MOSC or oracle documentation.

To check the current capturing extract in your environment.

select capture_name,status from dba_capture;

To stop the Extract

SQL> exec dbms_capture_adm.stop_capture('OGG$CAP_E0000019',true);

To Drop the Extract from the Registry

SQL> exec dbms_capture_adm.drop_capture('OGG$CAP_E0000019',true);

안될경우 

https://www.oracle-scn.com/error-cannot-register-or-unregister-extract-uodsext-because-of-the-following-sql-error-oci-error-26665/