Monday, December 3, 2012

ORA-01356: active logminer sessions found & ORA-01307: no LogMiner session is currently active

When I tried to switch tablespace to new tablespace which created for log miner I faced some ORA-??? errors.



SQL> create tablespace test_tbs datafile '/home/oracle/oracle/product/10.2.0/oradata/mydb/test_tbs01.dbf' size 100M autoextend on maxsize unlimited;

Tablespace created.

Now want to move objects from SYSAUX tablecpase (this is default for log miner) to new one.

SQL> EXEC DBMS_LOGMNR_D.SET_TABLESPACE('TEST_TBS');
BEGIN DBMS_LOGMNR_D.SET_TABLESPACE('TEST_TBS'); END;

*
ERROR at line 1:
ORA-01356: active logminer sessions found
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 232
ORA-06512: at line 1

This means there are active logminer sessions in our database. I tried to use dbms_logmnr_end_logmnr procedure, but this time new Ora exception raised.

SQL> EXEC DBMS_LOGMNR.END_LOGMNR();
BEGIN DBMS_LOGMNR.END_LOGMNR(); END;

*
ERROR at line 1:
ORA-01307: no LogMiner session is currently active
ORA-06512: at "SYS.DBMS_LOGMNR", line 76
ORA-06512: at line 1

SQL>



On our database streams configured and works! To stop LogMiner session we have to stop the capture process.


SQL> select capture_name, capture_user from dba_capture;

CAPTURE_NAME                   CAPTURE_USER
------------------------------ ------------------------------
CAPTURE_EMP                    SYS

SQL>



SQL> EXEC DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => 'capture_emp');

PL/SQL procedure successfully completed.


Now repeat above action to switch tablespace.


SQL> EXEC DBMS_LOGMNR_D.SET_TABLESPACE('TEST_TBS');

PL/SQL procedure successfully completed.

SQL>

That`s it.


1 comment: