ORA-16255 When Configuring Log Auto Delete on Logical Standby

郑全

ORA-16255 When Configuring Log Auto Delete on Logical Standby

ORA-16255 When Configuring Log Auto Delete on Logical Standby (文档 ID 2581213.1)

---

                               
登录/注册后可看大图

In this Document

---

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$;


SESSION#   SESSION_NAME
--------   ------------------
39         OGG$CAP_NMS1AAX
41         OGG$CAP_NMS1ABX
45         Logical_Standby$45

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;


CAPTURE_NAME     QUEUE_NAME     STATUS    LOGMINER_ID
---------------  -------------  --------  -----------
OGG$CAP_NMS1AAX  OGG$Q_NMS1AAX  DISABLED  39
OGG$CAP_NMS1ABX  OGG$Q_NMS1ABX  DISABLED  41


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