Why does my AWS DMS CDC task that uses Oracle as a source fail with a "Sequence does not exist" error?

Short description

When you use an Oracle database as a source for your migration task, AWS DMS gets the data from the table in the full-load phase. In the CDC phase, AWS DMS reads from the archived redo logs. Then, AWS DMS captures the redo logs from the source Oracle database and applies only the committed changes to the target database.

If AWS DMS looks for the redo log in the wrong destination or when DEL is YES and the archivelog sequence doesn't exist on the source Oracle database, then you might get an error message. The error message looks similar to the following:

"03980512: 2022-05-23T12:33:11 [SOURCE_CAPTURE ]E: Archived Redo log with the sequence 232488 does not exist, thread 1 [1022318] (oradcdc_thread.c:624"

Resolution

Note: The source on-premises database or RDS for Oracle database purges archive redo logs. AWS DMS doesn't purge archive redo logs. Instead, AWS DMS confirms that it can't find the log when it tries to process the next LSN.

Check whether the archive log file and sequence exist

Complete the following steps:

1. To check whether your archive log sequence number (LSN) is available on the source Oracle database server, run the following command:

   select name, dest_id, thread#, sequence#, archived, applied, deleted, status, first_time, next_time, completion_time from v$archived_log where sequence# = 232488;

   Note: Replace 232488 with the archive LSN in your sequence log error.
2. Check the DEL column.
   Note: If the column lists NO, then the archive LSN exists in the source database. If it lists YES and you use an on-premises Oracle server, then the archive LSN doesn't exist and Oracle might have purged it from the source. Continue to the DEL is YES and the archivelog sequence doesn't exist on the source Oracle database section of this article. In Amazon RDS, if Oracle purged the archive LSN, then the DEL column shows NO. This information comes from the control file.
3. If you use Amazon RDS for Oracle, then check that the redo log exists in Amazon RDS. Run the following query:

   select * from table(rdsadmin.rds_file_util.listdir('ARCHIVELOG_DIR')) where filename='example-redolog.arc' order by mtime desc;

   Note: Replace example-redolog.arc with your archive redo log name. If ARCHIVELOG_DIR doesn't exist, then create the ARCHIVELOG_DIR and run the query again.

AWS DMS looks for the redo log in the wrong destination

By default, AWS DMS captures the redo logs on DEST_ID 1. The AWS DMS task fails because it looks for the redo log in DEST_ID 1, but the redo log file exists in a different DEST_ID.

To change the DEST_ID value, complete the following steps:

1. To check the sequence DEST_ID value, run the following query on the source database:

   select name, dest_id, thread#, sequence#, archived, applied, deleted, status, first_time, next_time, completion_time from v$archived_log where sequence# in (232488);

   Note: Replace 232488 with your archive LSN.
   Example output for the preceding query:

   NAME DEST_ID THREAD# SEQUENCE# ARC APPLIED DEL S FIRST_TIM NEXT_TIME COMPLETIO --------------------------------------------- ---------- ---------- ---------- --- --------- --- - --------- --------- --------- /orafra/prdsvbo/arc/1_232488_950180179.arc 2 1 232488 YES YES NO A 07-JUN-22 07-JUN-22 07-JUN-22

2. Check the DEST_ID column, and then check your DMS task logs to verify the DEST_ID.
   Example logs:

   01788702: 2022-06-07T17:10:31:658376 [SOURCE_CAPTURE ]I: Used Oracle archived Redo log destination id is '1' (oracdc_merger.c:639)
   01788702: 2022-06-07T17:10:31:658420 [SOURCE_CAPTURE ]I: Oracle instance uses more than one archived Redo log destination id. Please configure the correct destination id, if Redo logs of '1' destination cannot be accessed (oracdc_merger.c:642)

3. To change the DEST_ID that AWS DMS uses, add the additionalArchivedLogDestId extra connection attribute (ECA) to your source endpoint.
   Note: Make sure that the value for this ECA is the DEST_ID value found in the preceding step. For more information, see Endpoint settings when using Oracle as a source for AWS DMS.

4. Resume the DMS task. The logs show that AWS DMS can now capture the available redo LSN from your updated DEST_ID.
   Example logs:

   01898667: 2022-06-08T05:45:08:535588 [SOURCE_CAPTURE ]D: Going to retrieve archived REDO log with sequence 232488, thread 1 (oradcdc_thread.c:510)
   01898667: 2022-06-08T05:45:08:535607 [SOURCE_CAPTURE ]T: Use a prepared statement to access v$archived_log, thread 1 (oradcdc_thread.c:587)
   01898667: 2022-06-08T05:45:08:598396 [SOURCE_CAPTURE ]D: Going to open Redo Log with original name '/orafra/prdsvbo/arc/1_232488_950180179.arc', thread id '1' (oradcdc_redo.c:492)
   01898667: 2022-06-08T05:45:08:599614 [SOURCE_CAPTURE ]D: archived Redo log '/orafra/prdsvbo/arc/1_232488_950180179.arc' with sequence 232488 is opened, thread id '1' (oradcdc_redo.c:747)

DEL is YES and the archivelog sequence doesn't exist on the source Oracle database

If DEL is YES, then AWS DMS purged the archive log sequence from the source Oracle database. Oracle instances delete the archive log files to minimize the space that the archive logs take up.

Increase archive retention period for on-premises Oracle database

If you scheduled the Recovery Manager (RMAN) to delete noprompt archivelog until time SYSDATE-1, then this schedule deletes all archive log files older than 1 day. To increase this, modify the schedule to SYSDATE-2, or turn off the schedule.

To see your current configurations and increase archive log retention, complete the following steps:

1. Connect to RMAN.
2. To show all configurations on your Oracle database, run the following query:

   RMAN> SHOW ALL;

   Note: The default Archivelog deletion policy value is NONE.
3. To increase the retention period, run the following command:

   CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO SBT;

   Note: Replace SBT with your storage device type, such as DISK or ANY.
4. Restore your backup archived redo logs, and then resume the DMS task.
   Note: If you don't have your archive redo logs backed up, then restart the task from the full-load phase and start the migration over.

Increase archive retention period and download your archived redo logs for Amazon RDS

Amazon RDS deletes the archive log files that aren't within the archive log retention period. Amazon RDS also keeps a copy in an Amazon Simple Storage Solution (Amazon S3) bucket based on your Amazon RDS backup retention policy. You can use this copy to do a point-in-time restore.

Complete the following steps:

1. To view your current archive log retention period, run the following query:

   exec rdsadmin.rdsadmin_util.show_configuration;

2. To increase your archive log retention period, run the following command:

   begin rdsadmin.rdsadmin_util.set_configuration( name => 'archivelog retention hours', value => '24');
   end;
   /
   commit;

   Note: Replace 24 with a value greater than your current archive log retention period.

3. To verify your increased retention period and find your log file names, run the following command:

   exec rdsadmin.rdsadmin_util.show_configuration;

4. Download your archived redo logs.
   Note: All the redo logs that follow your LSN must be present on the source before you can successfully resume the AWS DMS task. If you can't restore the missing archive log sequence, then restart the task from the full-load phase and start the migration over.

Related information

Using an Oracle database as a source for AWS DMS

Performing common log-related tasks for Oracle DB instances