Asynch CDC: Delete old archived redo logs

Posted: October 9th, 2008 | Author: Uli Bethke | Filed under: Oracle | Tags: , , , , | No Comments »

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.

Leave a Reply