2 Replies Latest reply: Feb 6, 2013 2:45 AM by AsimDeo RSS

    get redo sequence number against SCN

    AsimDeo
      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
          Salman Qureshi
          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
            thanks, got the required info.