Skip to content
Oracle DBA Scripts & Database Utilities

Minimum Archive Log for Streams Restart (streams_min_arch_log.sql)

What This Script Does

Section titled “What This Script Does”

This script helps manage Oracle Streams capture processes by:

  • Calculating the minimum SCN required for capture restart
  • Identifying the specific archive log file(s) needed
  • Ensuring you retain necessary logs for capture recovery
  • Preventing ORA-01291 errors on capture restart

Script

Section titled “Script”
prompt =========================================================================================
prompt
prompt ++ Minimum Archive Log Necessary to Restart Capture ++
prompt
set serveroutput on
DECLARE
 hScn number := 0;
 lScn number := 0;
 sScn number;
 ascn number;
 alog varchar2(1000);
begin
  select min(start_scn), min(applied_scn) into sScn, ascn
    from dba_capture ;




  DBMS_OUTPUT.ENABLE(2000);


  for cr in (select distinct(a.ckpt_scn)
             from system.logmnr_restart_ckpt$ a
             where a.ckpt_scn <= ascn and a.valid = 1
             and exists (select * from system.logmnr_log$ l
               where a.ckpt_scn between l.first_change# and l.next_change#)
             order by a.ckpt_scn desc)
  loop
    if (hScn = 0) then
       hScn := cr.ckpt_scn;
    else
       lScn := cr.ckpt_scn;
       exit;
    end if;
  end loop;


  if lScn = 0 then
    lScn := sScn;
  end if;


 dbms_output.put_line('Capture will restart from SCN ' || lScn ||' in the following file:');
   for cr in (select name, first_time
               from DBA_REGISTERED_ARCHIVED_LOG
               where lScn between first_scn and next_scn order by thread#)
  loop


     dbms_output.put_line(cr.name||' ('||cr.first_time||')');


  end loop;
end;
/

Usage

Section titled “Usage”
SQL> @streams_min_arch_log.sql

Required Privileges

Section titled “Required Privileges”
  • SELECT on DBA_CAPTURE
  • SELECT on SYSTEM.LOGMNR_RESTART_CKPT$
  • SELECT on SYSTEM.LOGMNR_LOG$
  • SELECT on DBA_REGISTERED_ARCHIVED_LOG

Sample Output

Section titled “Sample Output”
=========================================================================================


++ Minimum Archive Log Necessary to Restart Capture ++


Capture will restart from SCN 123456789 in the following file:
/u01/archive/1_2345_987654321.arc (15-JAN-24 02:30:45)
/u01/archive/2_1234_987654321.arc (15-JAN-24 02:30:47)

Understanding the Output

Section titled “Understanding the Output”

SCN Information

Section titled “SCN Information”
  • Restart SCN: The system change number from which capture will restart
  • Archive Log: The specific file(s) containing this SCN
  • Timestamp: When the archive log was created

Critical Information

Section titled “Critical Information”
  • Never delete the identified archive logs
  • These logs are required for capture restart
  • Deleting them causes capture initialization failure

Common Use Cases

Section titled “Common Use Cases”

  1. Archive Log Management

    • Before purging old archive logs
    • Setting up automated cleanup scripts
    • Planning backup retention policies

  2. Capture Maintenance

    • Before capture process restart
    • After database maintenance
    • Following capture errors

  3. Disaster Recovery

    • Identifying critical logs for DR
    • Planning archive log shipping
    • Validating backup completeness

Integration with RMAN

Section titled “Integration with RMAN”

Safe Archive Deletion

Section titled “Safe Archive Deletion”
-- Configure RMAN to respect Streams requirements
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;


-- Or for Streams specifically
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

Manual Cleanup Script

Section titled “Manual Cleanup Script”
#!/bin/bash
# Run the streams check first
sqlplus -s / as sysdba <<EOF > /tmp/streams_min_scn.log
@streams_min_arch_log.sql
EOF


# Extract required log names and ensure they're not deleted
# Add your cleanup logic here

Troubleshooting

Section titled “Troubleshooting”

Common Issues

Section titled “Common Issues”
  1. No Output: Capture process may not exist
  2. Invalid SCN: Check capture status and errors
  3. Missing Logs: Already purged required archives

Verification Commands

Section titled “Verification Commands”
-- Check capture status
SELECT capture_name, status, status_change_time, error_number
FROM dba_capture;


-- View checkpoint information
SELECT capture_name, start_scn, applied_scn, max_checkpoint_scn
FROM dba_capture;


-- Check available archive logs
SELECT thread#, sequence#, first_change#, next_change#
FROM v$archived_log
WHERE first_change# <= (SELECT min(start_scn) FROM dba_capture)
ORDER BY first_change#;

Best Practices

Section titled “Best Practices”

  1. Run Regularly

    • Before archive log maintenance
    • As part of daily checks
    • Include in monitoring scripts

  2. Automate Protection

    • Mark required logs as non-deleteable
    • Implement retention policies
    • Alert on missing required logs

  3. Documentation

    • Record minimum required logs
    • Track SCN progression
    • Plan for growth
Section titled “Related Scripts”