The scenario
To describe starting situation, let us suppose this restore/recover scenario (we are talking about incomplete recover).- startup nomount
- restore control file
- alter database mount
- restore database
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 01/11/2010 12:08:19 ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '+DATA/hac/datafile/system.366.708004385'So we are stacked because recover could not be consistent. The main problem is that restored data files need ALL to be recovered at least to largest SCN number+1.
This situation is explained world wide but main main question is: what is the lowest SCN that can recover database enough to be open?
Solution-RMAN list backup
First solution is to look in RMAN repository or use control file records to retrieve backup of datafile 1. In a case of RMAN catalog you'll not have too much trouble because you'll find enough information to see backup of wanted datafile and it's SCN. Output may looks like: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
...
If you are lucky and get result look in column "Ckp SCN" and you'll get missing SCN. Unfortunately this may not help you always.In nocatalog mode it is more then likely that your command may retrieve no data:
RMAN> list backup of datafile 1; RMAN>No backup! The reason is no backup was taken in time provided with control_file_record_keep_time parameter.
Solution-RMAN list copy
Next solution is usage of RMAN's "list copy", which shows only backup of "datafile 1", with the same retention as described in previously explained in "List backup" solution. However if you are lucky and get result it is more or less like:RMAN> list copy of datafile 1; List of Datafile Copies Key File S Completion Time Ckp SCN Ckp Time Name ------- ---- - --------------- ---------- --------------- --------------------------------------------- 1917 1 A 08-JAN-10 49152352 08-JAN-10 /u01/HAC_I-1799195971_TS-SYSTEM_FNO-1_nvl2urjr 1867 1 X 07-JAN-10 49125664 07-JAN-10 /u01/HAC_I-1799195971_TS-SYSTEM_FNO-1_m4l2sii8 1841 1 X 07-JAN-10 49117957 07-JAN-10 /u01/HAC_I-1799195971_TS-SYSTEM_FNO-1_l7l2sb2u 1813 1 X 18-SEP-09 9643175 18-SEP-09 /u01/HAC_I-1799195971_TS-SYSTEM_FNO-1_edkpitiv 1666 1 A 17-SEP-09 9174432 17-SEP-09 /u01/HAC_I-1799195971_TS-SYSTEM_FNO-1_ahkpfgq1
Again, regardless shown several SCNs this might not be enough to help you.
Solution-use x$ (C based) tables
x$ tables are Oracle's core tables based on pure C data and should be handle with additional care. The beauty of them is they are available when database in mounted and when all "dba_*" views are not available as well as some v$ based ones.To see status of all restored datafiles can be done with this SQL
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
; The output might looks like:
Instance # File # File name Checkpoint # Checkpoint time Last change # STATUS
---------- -------- -------------------------------------------------- --------------- -------------------- ---------------- ----------------------------
1 1 /db/SOME_DB/dbf01/system01.dbf 12039685200 03/10/2021 17:18:23 SYSTEM
1 2 /db/SOME_DB/dbf01/sysaux01.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 3 /db/SOME_DB/und_01/undotbs01.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 4 /db/SOME_DB/dbf01/users01.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 5 /db/SOME_DB/dbf01/gen_gfc_prod01.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 6 /db/SOME_DB/dbf01/gen_gfc_prod02.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 7 /db/SOME_DB/dbf01/gen_gfc_prod03.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 8 /db/SOME_DB/dbf01/gen_gfc_prod04.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 9 /db/SOME_DB/dbf01/gen_gfc_prod05.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 10 /db/SOME_DB/dbf01/gen_gfc_prod06.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 11 /db/SOME_DB/dbf01/gen_gfc_prod07.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 12 /db/SOME_DB/dbf01/sysaud01.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 13 /db/SOME_DB/dbf01/gen_gfc_prod08 9340774962 01/04/2021 11:04:30 9341443930 RECOVER
1 14 /db/SOME_DB/dbf01/gen_gfc_prod08.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 15 /db/SOME_DB/dbf01/gen_gfc_prod09.dbf 12039685200 03/10/2021 17:18:23 ONLINE
1 16 /db/SOME_DB/dbf01/gen_gfc_prod10.dbf 12039685200 03/10/2021 17:18:23 ONLINE
16 rows selected.
As you see File# 13 needs recovery and last applied change was in SCN=9341443930. If you cannot retrive exact time (scn_to_timestamp), here you have in column Checkpoint time date value which you can uses as well.
Remember that SCN for recover is always xxxxxxxx+1, for one bigger then one found in list!
To cover the whole story, for successful recover of any backup set like that, you need to apply archive logs which's SCN's are between min(xxxxxxxx)-max(xxxxxxxx)+1 but you do not need to specify min(xxxxxxxx) because RMAN will know that automatically.
So your restore should use until SCN clause in RMAN script. In pseudo language that looks like:
startup nomount; restore controlfile; alter database mount; set until scn 9341443931 restore database; recover database; alter database open resetlogs;
If all SCNs from previous query are the same then (as they are in our case) then you are dealing with consistent (aka cold backup). Cold backup means that all datafiles are having same SCN so practicly they do not need recover but only as declarative term (you do not need any archived log file beside). In a cases like that, your restore RMAN script might looks like:
restore database; recover database noredo; alter database open resetlogs;
However, in some consistent backup cases, I remember that I found on some database version a bug as explained at the begginig (ORA-01152). Based on previously shown real numbers, your "full proof" restore script for consistent backup, should look like:
set until scn=9341443931; restore database; recover database noredo; alter database open resetlogs;So there is no affraid of use until clause even in restoring consistent backups. This is especially the case in "backup as copy" when you deal with backup outside of ASM storage.
Solution-RMAN restore database preview
To prevent searching for information in restore/recover time, there is elegant method that will tell you which SCN is needed for successful restore.After regular database backup, use MAN command which will collect and show all important data for this backupset.
backup database; restore database preview;At the end of command shows exact SCN that you need for successful recover. At the end of log file output is like:
Media recovery start SCN is 9341443930 Recovery must be done beyond SCN 9341443930 to clear data files fuzziness Finished restore at 17-SEP-21 released channel: t1 released channel: t2 RMAN> Recovery Manager complete.where xxxxxxxxx+1 represent SCN you are searching for.
Cheers!
