Problem Description


Oracle log based CDC job fails with the below error. 


[INFO] 2019-01-28 07:44:40,374 [main] infoworks.discovery.incremental.LogBasedCDCUtil:612 :: Log file query SELECT name,SEQUENCE# FROM sys.v_$archived_log where dest_id = 1 and SEQUENCE# > 81195 order by SEQUENCE#[ERROR] 2019-01-28 07:44:40,497 [main] infoworks.discovery.incremental.LogBasedCDCUtil:225 :: infoworks.tools.ExceptionHandling.IWException: Error while getting log file list at infoworks.discovery.incremental.LogBasedCDCUtil.getArchiveLogFileList(LogBasedCDCUtil.java:378) at infoworks.discovery.incremental.LogBasedCDCUtil.startOracleLogMiner(LogBasedCDCUtil.java:138) at infoworks.discovery.incremental.LogBasedCDCUtil.loadTemplogTableIfRequired(LogBasedCDCUtil.java:86) at infoworks.discovery.utils.CrawlUtils.oracleLogBasedCDCUsingTempTable(CrawlUtils.java:173) at infoworks.discovery.dbcrawler.rdbms.CrawlController.importDB(CrawlController.java:677) at infoworks.discovery.incremental.CDCMain.main(CDCMain.java:44) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:233) at org.apache.hadoop.util.RunJar.main(RunJar.java:148)



Cause


This issue occurs if some of the entries for the archive logs are missing between 81195 (Min log sequence number) and 81405 (max log sequence number) in the sys.v_$archived_log that we query during the log based CDC.


The Max log sequence number can be obtained from the job log.


[INFO] 2019-01-28 07:44:40,369 [main] infoworks.tools.utils.IWUtil:2163 :: Got max log sequence number81405


Query the sys.v_$archived_log in the Source oracle database and see if any files are missing.


In this case, four of the log files whose sequence number >81195 are missing.


Solution:


a) Do an Initialize and ingest for that table.
b) Once the Initialize and ingest is successful, ask your DBA to copy only the archive files that will be generated after Initialize and ingest into the ORACLE_ARCHIVE_LOG_PATH location which is /cbcp009-archive/oradata/arch as we have ingested all the data till this point.
c) Then run the CDC job.d) Wait for any archive logs to be generated after this point and then copy them to ORACLE_ARCHIVE_LOG_PATH location and do a CDC. 

This should resolve the issue

Here are some of the links with more information on Oracle Log Minor.
https://blog.yannickjaquier.com/oracle/logminer-with-oracle-10g-and-above.htmlhttps://oracle-base.com/articles/8i/logminer
https://docs.oracle.com/database/121/SUTIL/GUID-1D510A2F-4CE8-4D69-AB18-CDD58FB3458C.htm


IWX versions: 2.3.x,2.4.x,2.5.x,2.6.x