Applies to: Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform. SymptomsConfiguring Log Auto Delete on Logical Standby fails with ORA-16255
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'TRUE');
BEGIN DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'TRUE'); END;*
ERROR at line 1:
ORA-16255: Log Auto Delete conflicts with another LogMiner session
ORA-06512: at "SYS.DBMS_LOGSTDBY", line 96
ORA-06512: at "SYS.DBMS_INTERNAL_LOGSTDBY", line 771
ORA-06512: at "SYS.DBMS_INTERNAL_LOGSTDBY", line 1778
ORA-06512: at "SYS.DBMS_LOGSTDBY", line 77
ORA-06512: at line 1
CauseSince error complains about another LogMiner session, check for active logminer sessions:
SQL> select SESSION_ID,SESSION_NAME,SESSION_STATE from v$logmnr_session;
SESSION_ID SESSION_NAME SESSION_STATE
---------- ------------------ -------------
45 Logical_Standby$45 ACTIVE
As per above, there are no other active logminer sessions except the one initiated by currently active logical standby apply. Check for other logminer sessions known to the database:
SQL> select SESSION#,SESSION_NAME from system.logmnr_session$;
Here, we can see there are 2 other logminer sessions known to database corresponding to Oracle GoldenGate capture process. Details about the capture process can be checked from view dba_capture
SQL> select CAPTURE_NAME,QUEUE_NAME,STATUS,LOGMINER_ID from dba_capture;
SolutionGoldengate capture is already disabled. So, go ahead and drop the capture:
exec DBMS_CAPTURE_ADM.DROP_CAPTURE(capture_name => 'OGG$CAP_NMS1AAX',drop_unused_rule_sets => true);
exec DBMS_CAPTURE_ADM.DROP_CAPTURE(capture_name => 'OGG$CAP_NMS1ABX',drop_unused_rule_sets => true);
Once capture is dropped, this should delete the corresponding rows from system.logmnr_session$ after which log auto delete can be set for logical standby:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'TRUE');PL/SQL procedure successfully completed.
NOTE: If you are facing ORA-16255 but there is no capture process defined as per dba_capture, the issue could be because of an incorrectly terminated logical standby session (in the past) which might have left entries in system.logmnr_session$ table. In such case, you can identify such entries, delete them manually from system.logmnr_session$ and re-try configuring log auto delete
