Which SCN to use for recovery

Which SCN to use for recovery



Method 1 :

RMAN> list backup of datafile 1;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
645     Full    127.30M    DISK        00:01:02     06-DEC-09
        BP Key: 645   Status: AVAILABLE  Compressed: YES  Tag: TAG20091206T183018
        Piece Name: +BCKP/hac/backupset/2009_12_06/tag20091206t183018_0.7966.704917821
  List of Datafiles in backup set 645
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 35728924   06-DEC-09 +DATA/hac/datafile/system.282.694017165
...


Ckp SCN + 1 is the SCN until which you should recover.


Method  2 :


RESTORE PREVIEW

Media recovery start SCN is 2483662717 -- This is starting SCN of Media recovery
Recovery must be done beyond SCN 2484364327 to clear datafile fuzziness -- This SCN is when the backup ended.So this must be in archived redo logs for a valid backup. When to recover you have to receover until this SCN + 1
Finished restore at 25-MAY-2011 22:03:08


RMAN> list archivelog from scn SCN_bkupstart until scn SCN_bkupend+1;


Get archive logs 

alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where &SCN_bkupstart between first_change# and next_change#;


select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where &SCN_bkupend between first_change# and next_change#;


restore archivelog from logseq=seq_bkupstart until logseq=seq_bkupend;



NOTE  : In Cold backup all datafiles are having same SCN.

Method  3 :

Check SCN of the datafile 1

set linesize 200;
set pagesize 100;
col inst_id for 9999999 heading 'Instance #'
col file_nr for 9999999 heading 'File #'
col file_name for A50 heading 'File name'
col checkpoint_change_nr for 99999999999999 heading 'Checkpoint #'
col checkpoint_change_time for A20 heading 'Checkpoint time'
col last_change_nr for 99999999999999 heading 'Last change #'
SELECT
      fe.inst_id,
      fe.fenum file_nr,
      fn.fnnam file_name,
      TO_NUMBER (fe.fecps) checkpoint_change_nr,
      fe.fecpt checkpoint_change_time,
      fe.fests last_change_nr,
      DECODE (
              fe.fetsn,
              0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
              DECODE (BITAND (fe.festa, 18),
                      0, 'OFFLINE',
                      2, 'ONLINE',
                      'RECOVER')
      ) status
FROM x$kccfe fe,
     x$kccfn fn
WHERE    (   (fe.fepax != 65535 AND fe.fepax != 0 )
          OR (fe.fepax = 65535 OR fe.fepax = 0)
         )
     AND fn.fnfno = fe.fenum
     AND fe.fefnh = fn.fnnum
     AND fe.fedup != 0
     AND fn.fntyp = 4
     AND fn.fnnam IS NOT NULL
     AND BITAND (fn.fnflg, 4) != 4
ORDER BY fe.fenum
;