[Debezium Oracle Connector] java.sql.SQLException: ORA-00308: cannot open archived log
394 views
Skip to first unread message
masami310
Jan 6, 2022, 3:19:53 AM1/6/22
to debezium
Hi,
I am getting the following error in our production environment.
「java.sql.SQLException: ORA-00308: cannot open archived log」
The version of Debezium is 1.8.0.Final.
Can you give me some advice on how to investigate and how to solve it?
Also, if I ignore this error, is it possible to lose track of the changed data?
Thank you.
[Log details]
1640711067094,"2021-12-29 02:04:27,093 ERROR Oracle|server1|streaming Mining session stopped due to the {} [io.debezium.connector.oracle.logminer.LogMinerHelper]"
1640711067094,java.sql.SQLException: ORA-00308: cannot open archived log '+RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665'
1640711067094,ORA-17503: ksfdopn:2 Failed to open file +RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665
1640711067094,ORA-15012: ASM file '+RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665' does not exist
1640711067094, at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)
1640711067094, at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)
1640711067094, at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)
1640711067094, at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)
1640711067094, at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)
1640711067094, at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)
1640711067094, at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:148)
1640711067094, at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:928)
1640711067094, at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1158)
1640711067094, at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1093)
1640711067094, at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1402)
1640711067094, at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1285)
1640711067094, at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3735)
1640711067094, at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3847)
1640711067094, at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1098)
1640711067094, at io.debezium.connector.oracle.logminer.processor.AbstractLogMinerEventProcessor.process(AbstractLogMinerEventProcessor.java:181)
1640711067094, at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:168)
1640711067094, at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:57)
1640711067094, at io.debezium.pipeline.ChangeEventSourceCoordinator.streamEvents(ChangeEventSourceCoordinator.java:172)
1640711067094, at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:139)
1640711067094, at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:108)
1640711067094, at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
1640711067094, at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
1640711067094, at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
1640711067094, at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
1640711067094, at java.base/java.lang.Thread.run(Thread.java:829)
1640711067094,"Caused by: Error : 308, Position : 0, Sql = SELECT SCN, SQL_REDO, OPERATION_CODE, TIMESTAMP, XID, CSF, TABLE_NAME, SEG_OWNER, OPERATION, USERNAME, ROW_ID, ROLLBACK, RS_ID FROM V$LOGMNR_CONTENTS WHERE SCN > :1 AND SCN <= :2 AND SRC_CON_NAME = 'BAKS6PDB' AND ((OPERATION_CODE IN (6,7,34,36) OR (OPERATION_CODE = 5 AND USERNAME NOT IN ('SYS','SYSTEM') AND INFO NOT LIKE 'INTERNAL DDL%' AND (TABLE_NAME IS NULL OR TABLE_NAME NOT LIKE 'ORA_TEMP_%')) ) OR (OPERATION_CODE IN (1,2,3) AND TABLE_NAME != 'LOG_MINING_FLUSH' AND SEG_OWNER NOT IN ('APPQOSSYS','AUDSYS','CTXSYS','DVSYS','DBSFWUSER','DBSNMP','GSMADMIN_INTERNAL','LBACSYS','MDSYS','OJVMSYS','OLAPSYS','ORDDATA','ORDSYS','OUTLN','SYS','SYSTEM','WMSYS','XDB') AND (REGEXP_LIKE(SEG_OWNER,'^BAKSADM01$','i')) AND (REGEXP_LIKE(SEG_OWNER || '.' || TABLE_NAME,'^BXXXX01.TBL001$','i') OR REGEXP_LIKE(SEG_OWNER || '.' || TABLE_NAME,'^BXXXX01.TBL003$','i')) )), OriginalSql = SELECT SCN, SQL_REDO, OPERATION_CODE, TIMESTAMP, XID, CSF, TABLE_NAME, SEG_OWNER, OPERATION, USERNAME, ROW_ID, ROLLBACK, RS_ID FROM V$LOGMNR_CONTENTS WHERE SCN > ? AND SCN <= ? AND SRC_CON_NAME = 'BAKS6PDB' AND ((OPERATION_CODE IN (6,7,34,36) OR (OPERATION_CODE = 5 AND USERNAME NOT IN ('SYS','SYSTEM') AND INFO NOT LIKE 'INTERNAL DDL%' AND (TABLE_NAME IS NULL OR TABLE_NAME NOT LIKE 'ORA_TEMP_%')) ) OR (OPERATION_CODE IN (1,2,3) AND TABLE_NAME != 'LOG_MINING_FLUSH' AND SEG_OWNER NOT IN ('APPQOSSYS','AUDSYS','CTXSYS','DVSYS','DBSFWUSER','DBSNMP','GSMADMIN_INTERNAL','LBACSYS','MDSYS','OJVMSYS','OLAPSYS','ORDDATA','ORDSYS','OUTLN','SYS','SYSTEM','WMSYS','XDB') AND (REGEXP_LIKE(SEG_OWNER,'^BAKSADM01$','i')) AND (REGEXP_LIKE(SEG_OWNER || '.' || TABLE_NAME,'^BXXXX01.TBL001$','i') OR REGEXP_LIKE(SEG_OWNER || '.' || TABLE_NAME,'^BXXXX01.TBL003$','i')) )), Error Msg = ORA-00308: cannot open archived log '+RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665'"
1640711067094,ORA-17503: ksfdopn:2 Failed to open file +RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665
1640711067094,ORA-15012: ASM file '+RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665' does not exist
1640711067094, at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)
1640711067094, ... 25 more
I am getting the following error in our production environment.
「java.sql.SQLException: ORA-00308: cannot open archived log」
The version of Debezium is 1.8.0.Final.
Oracle is 12.2.
Can you give me some advice on how to investigate and how to solve it?
Also, if I ignore this error, is it possible to lose track of the changed data?
Thank you.
[Log details]
1640711067094,"2021-12-29 02:04:27,093 ERROR Oracle|server1|streaming Mining session stopped due to the {} [io.debezium.connector.oracle.logminer.LogMinerHelper]"
1640711067094,java.sql.SQLException: ORA-00308: cannot open archived log '+RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665'
1640711067094,ORA-17503: ksfdopn:2 Failed to open file +RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665
1640711067094,ORA-15012: ASM file '+RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665' does not exist
1640711067094, at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)
1640711067094, at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)
1640711067094, at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)
1640711067094, at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)
1640711067094, at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)
1640711067094, at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)
1640711067094, at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:148)
1640711067094, at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:928)
1640711067094, at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1158)
1640711067094, at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1093)
1640711067094, at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1402)
1640711067094, at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1285)
1640711067094, at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3735)
1640711067094, at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3847)
1640711067094, at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1098)
1640711067094, at io.debezium.connector.oracle.logminer.processor.AbstractLogMinerEventProcessor.process(AbstractLogMinerEventProcessor.java:181)
1640711067094, at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:168)
1640711067094, at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:57)
1640711067094, at io.debezium.pipeline.ChangeEventSourceCoordinator.streamEvents(ChangeEventSourceCoordinator.java:172)
1640711067094, at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:139)
1640711067094, at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:108)
1640711067094, at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
1640711067094, at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
1640711067094, at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
1640711067094, at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
1640711067094, at java.base/java.lang.Thread.run(Thread.java:829)
1640711067094,"Caused by: Error : 308, Position : 0, Sql = SELECT SCN, SQL_REDO, OPERATION_CODE, TIMESTAMP, XID, CSF, TABLE_NAME, SEG_OWNER, OPERATION, USERNAME, ROW_ID, ROLLBACK, RS_ID FROM V$LOGMNR_CONTENTS WHERE SCN > :1 AND SCN <= :2 AND SRC_CON_NAME = 'BAKS6PDB' AND ((OPERATION_CODE IN (6,7,34,36) OR (OPERATION_CODE = 5 AND USERNAME NOT IN ('SYS','SYSTEM') AND INFO NOT LIKE 'INTERNAL DDL%' AND (TABLE_NAME IS NULL OR TABLE_NAME NOT LIKE 'ORA_TEMP_%')) ) OR (OPERATION_CODE IN (1,2,3) AND TABLE_NAME != 'LOG_MINING_FLUSH' AND SEG_OWNER NOT IN ('APPQOSSYS','AUDSYS','CTXSYS','DVSYS','DBSFWUSER','DBSNMP','GSMADMIN_INTERNAL','LBACSYS','MDSYS','OJVMSYS','OLAPSYS','ORDDATA','ORDSYS','OUTLN','SYS','SYSTEM','WMSYS','XDB') AND (REGEXP_LIKE(SEG_OWNER,'^BAKSADM01$','i')) AND (REGEXP_LIKE(SEG_OWNER || '.' || TABLE_NAME,'^BXXXX01.TBL001$','i') OR REGEXP_LIKE(SEG_OWNER || '.' || TABLE_NAME,'^BXXXX01.TBL003$','i')) )), OriginalSql = SELECT SCN, SQL_REDO, OPERATION_CODE, TIMESTAMP, XID, CSF, TABLE_NAME, SEG_OWNER, OPERATION, USERNAME, ROW_ID, ROLLBACK, RS_ID FROM V$LOGMNR_CONTENTS WHERE SCN > ? AND SCN <= ? AND SRC_CON_NAME = 'BAKS6PDB' AND ((OPERATION_CODE IN (6,7,34,36) OR (OPERATION_CODE = 5 AND USERNAME NOT IN ('SYS','SYSTEM') AND INFO NOT LIKE 'INTERNAL DDL%' AND (TABLE_NAME IS NULL OR TABLE_NAME NOT LIKE 'ORA_TEMP_%')) ) OR (OPERATION_CODE IN (1,2,3) AND TABLE_NAME != 'LOG_MINING_FLUSH' AND SEG_OWNER NOT IN ('APPQOSSYS','AUDSYS','CTXSYS','DVSYS','DBSFWUSER','DBSNMP','GSMADMIN_INTERNAL','LBACSYS','MDSYS','OJVMSYS','OLAPSYS','ORDDATA','ORDSYS','OUTLN','SYS','SYSTEM','WMSYS','XDB') AND (REGEXP_LIKE(SEG_OWNER,'^BAKSADM01$','i')) AND (REGEXP_LIKE(SEG_OWNER || '.' || TABLE_NAME,'^BXXXX01.TBL001$','i') OR REGEXP_LIKE(SEG_OWNER || '.' || TABLE_NAME,'^BXXXX01.TBL003$','i')) )), Error Msg = ORA-00308: cannot open archived log '+RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665'"
1640711067094,ORA-17503: ksfdopn:2 Failed to open file +RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665
1640711067094,ORA-15012: ASM file '+RECOC1/BXYZU11/ARCHIVELOG/2021_12_23/thread_1_seq_77646.6728.1092081665' does not exist
1640711067094, at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)
1640711067094, ... 25 more
Chris Cranford
Jan 6, 2022, 12:26:21 PM1/6/22
to debe...@googlegroups.com, masami310
HI masami310 -
This generally means one of a few things:
- File was deleted directly on the file system and RMAN wasn't used, which has created a state inconsistency in Oracle
- File is corrupted and simply cannot be read by Oracle
- If the path is to a mounted drive, perhaps the mount is offline?
In the event of the first two points, there isn't much you can do other than re-snapshot. An archive log cannot be prematurely deleted or has gotten corrupted if it is still needed by Debezium.
Chris
This generally means one of a few things:
- File was deleted directly on the file system and RMAN wasn't used, which has created a state inconsistency in Oracle
- File is corrupted and simply cannot be read by Oracle
- If the path is to a mounted drive, perhaps the mount is offline?
In the event of the first two points, there isn't much you can do other than re-snapshot. An archive log cannot be prematurely deleted or has gotten corrupted if it is still needed by Debezium.
Chris
--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/4c4bf6d3-5cfc-46d2-941b-6849533f6052n%40googlegroups.com.
masami310
Jan 7, 2022, 6:58:43 AM1/7/22
to debezium
Hi, Chris
However, when I contacted the DBA, none of the three errors were true.
Is it an error that cannot occur under normal operation?
If there is a way to prevent the error, please give me some advice.
2022年1月7日金曜日 2:26:21 UTC+9 Chris Cranford:
Chris Cranford
Jan 10, 2022, 9:31:42 AM1/10/22
to debe...@googlegroups.com, masami310
Hi masami310 -
Could your DBA check to see if a .TRC file was created on the OS for the failure, maybe it gives more details?
Ultimately, Debezium simply gathers a list of log files that need to be mined and provides that list to Oracle. This error was triggered by Oracle itself, it simply could not open / read the file. If the file does exist and isn't deleted, I can only then assume that perhaps some other process had a lock on the file and LogMiner was unable to read it? Do you have Data Guard or some other integration that works with redo/archive logs that could have locked the file temporarily?
Chris
Could your DBA check to see if a .TRC file was created on the OS for the failure, maybe it gives more details?
Ultimately, Debezium simply gathers a list of log files that need to be mined and provides that list to Oracle. This error was triggered by Oracle itself, it simply could not open / read the file. If the file does exist and isn't deleted, I can only then assume that perhaps some other process had a lock on the file and LogMiner was unable to read it? Do you have Data Guard or some other integration that works with redo/archive logs that could have locked the file temporarily?
Chris
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/177c14ae-3c07-470d-bc39-c66304230262n%40googlegroups.com.
masami310
Jan 11, 2022, 9:20:37 PM1/11/22
to debezium
Hi Chris
Thank you for your reply.
Request DBA to investigate.
Thank you for your reply.
Request DBA to investigate.
2022年1月10日月曜日 23:31:42 UTC+9 Chris Cranford:
Reply all
Reply to author
Forward
0 new messages