This discussion is archived
2 Replies Latest reply: Feb 6, 2013 12:45 AM by AsimDeo RSS

get redo sequence number against SCN

AsimDeo Newbie
Currently Being Moderated
hi all,

environment is oracle 10gr2(10.2.0.5.0)...

1 PROD Server
1 DR Server (Dataguard Configuration)
1 Reporting Server (replication is configured via oracle streams on some large tables....)

Due to shortage of disk space i need to remove archivelogs from FRA on daily basis.
My requirement is to identify which archived logs are required by capture process , so that archive logs that are not required could removed.

could not identify the relationship among all_capture and v$log_history/v$archived_log views

can get the capture process and their relevant SCN numbers from
select c.CAPTURE_NAME, c.START_SCN,c.CAPTURED_SCN,c.APPLIED_SCN, c.FIRST_SCN,c.LAST_ENQUEUED_SCN,c.LOGMINER_ID
FROM all_capture c;
But how to get archivelog sequence number?
  • 1. Re: get redo sequence number against SCN
    SalmanQureshi Expert
    Currently Being Moderated
    Hi
    It is from 11.1, but should also work for your version.
    COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15
    COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10
    COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 99999
    COLUMN NAME HEADING 'Required|Archived Redo Log|File Name' FORMAT A40
    
    SELECT r.CONSUMER_NAME,
           r.SOURCE_DATABASE,
           r.SEQUENCE#, 
           r.NAME 
      FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
      WHERE r.CONSUMER_NAME =  c.CAPTURE_NAME AND
            r.NEXT_SCN      >= c.REQUIRED_CHECKPOINT_SCN;  
    http://docs.oracle.com/cd/B28359_01/server.111/b28321/strms_cpmon.htm#CHDBBJCF
    Salman
  • 2. Re: get redo sequence number against SCN
    AsimDeo Newbie
    Currently Being Moderated
    thanks, got the required info.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points