Asynch CDC: Delete old archived redo logs

The following steps need to be taken to be able to delete archived redo logs that are no longer required by the CDC capture process:

1. Determine the new first_scn for your capture process

SELECT
cap.CAPTURE_NAME,
cap.FIRST_SCN,
cap.APPLIED_SCN,
cap.REQUIRED_CHECKPOINT_SCN
FROM DBA_CAPTURE cap, CHANGE_SETS cset
WHERE cap.CAPTURE_NAME = cset.CAPTURE_NAME

As per Oracle documentation: This query “determines a new first_scn value that is greater than the original first_scn value and less than or equal to the applied_scn and required_checkpoint_scn”. So the new first_scn is the lower value of applied_scn and required_checkpoint_scn.

2. Raise the first_scn for the change source:

exec dbms_cdc_publish.ALTER_AUTOLOG_CHANGE_SOURCE (change_source_name => <name of change source>,first_scn => 11395421887)

3. Identify which redo log files can be deleted:

SELECT *
FROM DBA_LOGMNR_PURGED_LOG

4. Write a script to delete or archive obsolete archived redo logs based on the results of the query in step 3.

Asynch CDC: Waiting For Dictionary Redo

I have come across (so far) four scenarios that show capture process “hanging” as WAITING FOR DICTIONARY REDO.

1. Wrong first_scn supplied for create_autolog_change_source: The first_scn when building the the data dictionary in the redo log via dbms_capture_adm.build does not match the first_scn when creating the change source on the downstream server. You need to supply the first_scn of the archived redo log that contains the data dictionary from the source.

Solution:  (1) Drop the change source and all associated change sets and change tables. (2) Get the correct first_scn from the source by querying

SELECT * FROM V$ARCHIVED_LOG
WHERE dictionary_begin = 'YES'
ORDER BY completion_time DESC

(3) Recreate the change source using the first_scn from the previous query

2. Source table has not been instantiated: You forgot to instantiate the source table with dbms_capture_adm.prepare_table_instantiation

Solution: execute procedure dbms_capture_adm.prepare_table_instantiation.

3. Second change source: You created a second change source that points to the same database source as your first one.

Solution: only ever use one change source per database source.

4. A second, third etc. change set was added at a later stage. You initially created a change source with just one change set. At a later stage you added an additional change set.

ALTER DATABASE REGISTER LOGICAL
LOGFILE '/oracle/archdata/UDAT/usis_arch/1_12672_655855801.arc' FOR 'CDC$C_PERF_REG_SET3'

You get the archived redo log that contains the data dictionary by querying:

SELECT consumer_name, name
FROM DBA_REGISTERED_ARCHIVED_LOG
WHERE dictionary_begin = 'YES'