ERROR: Cannot register or unregister EXTRACT because of the following SQL error: OCI Error 26,665.

ERROR: Cannot register or unregister EXTRACT UODSEXT because of the following SQL error: OCI Error 26,665.

 

The status of the UODSEXT extract was in STARTING for a long time. As I discussed in my previous post, This Capture process was checking for an Archivelog which was not available or deleted.

Now I thought to stop the Extract Process, UnRegister it and again Register. I am sharing my experience on it where I faced few problems and which was also resolved.

GGSCI (OGGSRV1) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED UODSDP 00:00:00 22:27:53
EXTRACT ABENDED UODSEXT 00:42:20 175:01:08
EXTRACT RUNNING UTILDP 00:00:00 00:00:01
EXTRACT RUNNING UTLEXT 00:00:03 00:00:02

GGSCI (OGGSRV1) 3> unregister extract uodsext database
ERROR: EXTRACT UODSEXT must be registered with the database to perform this operation.

I tried to Register the Extract Process and got the below error.,

GGSCI (OGGSRV1) 4> register extract UODSEXT database
ERROR: Cannot register or unregister EXTRACT UODSEXT because of the following SQL error: OCI Error 26,665.

I checked the state of the Integrated Capture Process and it was ‘WAITING FOR DICTIONARY REDO’.

SQL> set lines 200 pages 2000
SQL> COLUMN CAPTURE_NAME HEADING ‘Capture|Name’ FORMAT A20
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A20
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A20
SQL> COLUMN TOTAL_MESSAGES_CAPTURED HEADING ‘Redo|Entries|Evaluated|In Detail’ FORMAT 999999999
SQL> COLUMN TOTAL_MESSAGES_ENQUEUED HEADING ‘Total|LCRs|Enqueued’ FORMAT 9999999999
SQL>
SQL> SELECT c.CAPTURE_NAME,
2 SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(‘)+1,4) PROCESS_NAME,
3 c.SID,
4 c.SERIAL#,
5 c.STATE,
6 c.TOTAL_MESSAGES_CAPTURED,
7 c.TOTAL_MESSAGES_ENQUEUED
8 FROM V$STREAMS_CAPTURE c, V$SESSION s
9 WHERE c.SID = s.SID AND
10 c.SERIAL# = s.SERIAL# AND
11 c.CAPTURE_NAME=’OGG$CAP_UODSEXT’;

Redo
Capture Session Entries Total
Capture Process Session Serial Evaluated LCRs
Name Number ID Number State In Detail Enqueued
——————– ——————– ——- ——– ——————– ———- ———–
OGG$CAP_UODSEXT TNS 1221 923 WAITING FOR DICTIONA 0 0
RY REDO

Checked the Information of the Session with ID 1221.,

SQL> select sid,serial#,status,action,program,event,state from v$session where sid=1221;Session
Session Serial
ID Number STATUS ACTION PROGRAM EVENT State
——- ——– ——– ——————————————— ——————– ——————————————— ——————–
1221 923 ACTIVE OGG$CAP_UODSEXT – Capture extract@OGGSRV1 Streams capture: waiting for archive log WAITING

I tried to stop the Capture process using the below command and checked the status, but there was no improvement. The session was still active and it waiting for the archive log.

SQL> exec DBMS_CAPTURE_ADM.STOP_CAPTURE(‘OGG$CAP_UODSEXT’);

PL/SQL procedure successfully completed.

SQL> select sid,serial#,status,action,program,event,state from v$session where sid=1221;Session
Session Serial
ID Number STATUS ACTION PROGRAM EVENT State
——- ——– ——– ——————————————— ——————– ——————————————— ——————–
1221 923 ACTIVE OGG$CAP_UODSEXT – Capture extract@OGGSRV1 Streams capture: waiting for archive log WAITING

Then I tried to Drop the Capture process using the below command and ended up with the below error.,

SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE(‘OGG$CAP_UODSEXT’);
BEGIN DBMS_CAPTURE_ADM.DROP_CAPTURE(‘OGG$CAP_UODSEXT’); END;*
ERROR at line 1:
ORA-01338: Other process is attached to LogMiner session
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 732
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 703
ORA-06512: at “SYS.DBMS_CAPTURE_ADM”, line 566
ORA-06512: at line 1

The above error clearly states that, a Logminer Session is holding the Extract Process and not getting released. So, I just killed that session with SID 1221 and checked the status.

SQL> select sid,serial#,status,action,program,event,state from v$session where sid=1221;no rows selected

After killing the session, I again tried to stop and drop the Capture process and at last the Capture process was dropped successfully.,

SQL> exec DBMS_CAPTURE_ADM.STOP_CAPTURE(‘OGG$CAP_UODSEXT’);PL/SQL procedure successfully completed.SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE(‘OGG$CAP_UODSEXT’);PL/SQL procedure successfully completed.

There was no Capture process with name OGG$CAP_UODSEXT. It was totally removed / dropped.

SQL> COLUMN CAPTURE_NAME HEADING ‘Capture|Name’ FORMAT A20
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A20
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A20
SQL> COLUMN TOTAL_MESSAGES_CAPTURED HEADING ‘Redo|Entries|Evaluated|In Detail’ FORMAT 999999999
SQL> COLUMN TOTAL_MESSAGES_ENQUEUED HEADING ‘Total|LCRs|Enqueued’ FORMAT 9999999999
SQL>
SQL> SELECT c.CAPTURE_NAME,
2 SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(‘)+1,4) PROCESS_NAME,
3 c.SID,
4 c.SERIAL#,
5 c.STATE,
6 c.TOTAL_MESSAGES_CAPTURED,
7 c.TOTAL_MESSAGES_ENQUEUED
8 FROM V$STREAMS_CAPTURE c, V$SESSION s
9 WHERE c.SID = s.SID AND
10 c.SERIAL# = s.SERIAL# AND
11 c.CAPTURE_NAME=’OGG$CAP_UODSEXT’;no rows selected

So I started proceeding with Unregister the Extract Process UODSEXT, still I was not able to do it,

GGSCI (OGGSRV1) 9> unregister extract UODSEXT database
ERROR: EXTRACT UODSEXT must be registered with the database to perform this operation.

This is because, we had externally killed and dropped the Logminer process which was attached to the Capture Process. This means, a logminer server process will be attached whenever an Extract is registered to the Database and it will be dettached whenever it is unregistered. This is called Integrated Extract Process.

Now I tried to Register the Extract process and it got registered without any error.,

GGSCI (OGGSRV1) 10> register extract UODSEXT database
Extract UODSEXT successfully registered with database at SCN 7345485133524.
SQL> COLUMN CAPTURE_NAME HEADING ‘Capture|Name’ FORMAT A20
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A20
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A20
SQL> COLUMN TOTAL_MESSAGES_CAPTURED HEADING ‘Redo|Entries|Evaluated|In Detail’ FORMAT 999999999
SQL> COLUMN TOTAL_MESSAGES_ENQUEUED HEADING ‘Total|LCRs|Enqueued’ FORMAT 9999999999
SQL>
SQL> SELECT c.CAPTURE_NAME,
2 SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(‘)+1,4) PROCESS_NAME,
3 c.SID,
4 c.SERIAL#,
5 c.STATE,
6 c.TOTAL_MESSAGES_CAPTURED,
7 c.TOTAL_MESSAGES_ENQUEUED
8 FROM V$STREAMS_CAPTURE c, V$SESSION s
9 WHERE c.SID = s.SID AND
10 c.SERIAL# = s.SERIAL# AND
11 c.CAPTURE_NAME=’OGG$CAP_UODSEXT’;Redo
Capture Session Entries Total
Capture Process Session Serial Evaluated LCRs
Name Number ID Number State In Detail Enqueued
——————– ——————– ——- ——– ——————– ———- ———–
OGG$CAP_UODSEXT CP04 646 16955 LOADING (step 66 of 0 0
66)

The State here is LOADING. This means the Extract process is getting integrated with the Database.

I just waited for few minutes and again checked the status of the Extract Process.,

SQL> COLUMN CAPTURE_NAME HEADING ‘Capture|Name’ FORMAT A20
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A20
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A20
SQL> COLUMN TOTAL_MESSAGES_CAPTURED HEADING ‘Redo|Entries|Evaluated|In Detail’ FORMAT 999999999
SQL> COLUMN TOTAL_MESSAGES_ENQUEUED HEADING ‘Total|LCRs|Enqueued’ FORMAT 9999999999
SQL>
SQL> SELECT c.CAPTURE_NAME,
2 SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(‘)+1,4) PROCESS_NAME,
3 c.SID,
4 c.SERIAL#,
5 c.STATE,
6 c.TOTAL_MESSAGES_CAPTURED,
7 c.TOTAL_MESSAGES_ENQUEUED
8 FROM V$STREAMS_CAPTURE c, V$SESSION s
9 WHERE c.SID = s.SID AND
10 c.SERIAL# = s.SERIAL# AND
11 c.CAPTURE_NAME=’OGG$CAP_UODSEXT’;Redo
Capture Session Entries Total
Capture Process Session Serial Evaluated LCRs
Name Number ID Number State In Detail Enqueued
——————– ——————– ——- ——– ——————– ———- ———–
OGG$CAP_UODSEXT CP04 646 16955 WAITING FOR INACTIVE 1 1
DEQUEUERS

Now you can after the state of the Capture process has been changed.

WAITING FOR INACTIVE DEQUEUERS – Waiting for the capture process’s queue subscribers to start. The capture process stops enqueuing LCRs if there are no active subscribers to the queue.

This is because, I only started the Extract Process and DataPump Process is yet to be started.

Now I am going to UnRegister the Capture Process

GGSCI (OGGSRV1) 11> unregister extract UODSEXT database
Successfully unregistered EXTRACT UODSEXT from database.

Check if the background process (Logminer Process) Still Exists.,

SQL> COLUMN CAPTURE_NAME HEADING ‘Capture|Name’ FORMAT A20
SQL> COLUMN PROCESS_NAME HEADING ‘Capture|Process|Number’ FORMAT A20
SQL> COLUMN SID HEADING ‘Session|ID’ FORMAT 999999
SQL> COLUMN SERIAL# HEADING ‘Session|Serial|Number’ FORMAT 9999999
SQL> COLUMN STATE HEADING ‘State’ FORMAT A20
SQL> COLUMN TOTAL_MESSAGES_CAPTURED HEADING ‘Redo|Entries|Evaluated|In Detail’ FORMAT 999999999
SQL> COLUMN TOTAL_MESSAGES_ENQUEUED HEADING ‘Total|LCRs|Enqueued’ FORMAT 9999999999
SQL>
SQL> SELECT c.CAPTURE_NAME,
2 SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(‘)+1,4) PROCESS_NAME,
3 c.SID,
4 c.SERIAL#,
5 c.STATE,
6 c.TOTAL_MESSAGES_CAPTURED,
7 c.TOTAL_MESSAGES_ENQUEUED
8 FROM V$STREAMS_CAPTURE c, V$SESSION s
9 WHERE c.SID = s.SID AND
10 c.SERIAL# = s.SERIAL# AND
11 c.CAPTURE_NAME=’OGG$CAP_UODSEXT’;no rows selected

Hope this cleared you, reason and solution for the error

ERROR: EXTRACT UODSEXT must be registered with the database to perform this operation.
ERROR: Cannot register or unregister EXTRACT UODSEXT because of the following SQL error: OCI Error 26,665.

 

 

You may also like...

Leave a Reply

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