ORA-01152: file 1 was not restored from a sufficiently old backup - Solution

Sample Restore and Recover scenario in Oracle using RMAN:

After restoring database, when I tried to recover database, found "RMAN-06556: datafile 1 must be restored from backup..." error which is common in most of the recover scenarios. There are many ways to solve this issues. Using SCN or using LOG Sequence or using UNTIL time you can recover the database.

In this scenario, I am facing with until SCN. You can see below error:

RMAN> run {
allocate channel c0 device type sbt parms="SBT_LIBRARY=/u01/tivoli/Base/libobk.so,BLKSIZE=1048576";
set until scn 7460264;
recover database noredo;
}
2> 3> 4> 5> 
allocated channel: c0
channel c0: SID=762 device type=SBT_TAPE
channel c0: CommVault Systems for Oracle: Version 11.0.0(BUILD80)

executing command: SET until clause

Starting recover at 18-FEB-19
released channel: c0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/18/2019 20:29:06
RMAN-06556: datafile 1 must be restored from backup older than SCN 7460264

RMAN> 

So, I tried to find out what SCN I can use to recover:

SQL> select CHECKPOINT_CHANGE# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
           7460265
           7460265
           7460265
           7460264
           7460264

SQL>
break on file_type skip 1

select 'D' file_type, file#, checkpoint_change#, status from V$datafile_header
union all
select 'L', group#, first_change#, status from V$log order by 1,3,2;



F      FILE# CHECKPOINT_CHANGE# STATUS
- ---------- ------------------ ----------------
D          4            7460264 ONLINE
           5            7460264 ONLINE
           1            7460265 ONLINE
           2            7460265 ONLINE
           3            7460265 ONLINE

L          1            7441809 INACTIVE
           2            7446438 INACTIVE
           3            7456460 CURRENT

But, No luck. still same error...
Example:

RMAN> run {
allocate channel c0 device type sbt parms="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
set until scn 7441809;
recover database noredo;
}2> 3> 4> 5> 

allocated channel: c0
channel c0: SID=762 device type=SBT_TAPE
channel c0: CommVault Systems for Oracle: Version 11.0.0(BUILD80)

executing command: SET until clause

Starting recover at 18-FEB-19
released channel: c0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/18/2019 20:33:06
RMAN-06556: datafile 1 must be restored from backup older than SCN 7441809

Even tried to open with resetlog. Still same error.

Example:

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 02/18/2019 20:34:49
ORA-01152: file 1 was not restored from a sufficiently old backup 
ORA-01110: data file 1: '/u02/oradata/ORCL/datafile/o1_mf_system_k9ww6f19_.dbf'

RMAN> 

Then, I thought let us try to recover the database using LOG SEQUENCE. Used "list backup of archivelog all" command to find out what log sequence used during restore.

Find last log sequence used

RMAN> list backup of archivelog all;

...
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
701     Unknown    SBT_TAPE    00:00:00     18-FEB-19      
        BP Key: 705   Status: AVAILABLE  Compressed: NO  Tag: TAG20190216T164243
        Handle: 1169468_ORCL_lttq1og3_1_1   Media: V_880953_3272683

  List of Archived Logs in backup set 701
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    815     7421975    16-FEB-19 7430618    16-FEB-19
  1    816     7430618    16-FEB-19 7441809    16-FEB-19
  1    817     7441809    16-FEB-19 7446438    16-FEB-19
  1    818     7446438    16-FEB-19 7456460    16-FEB-19
  1    819     7456460    16-FEB-19 7460470    16-FEB-19
  1    820     7460470    16-FEB-19 7460491    16-FEB-19

Now we can use 820 as logsequence# for thread 1 and start recover.

-- Finally able to recover
Example:
run
{
SET UNTIL SEQUENCE 820 THREAD 1;
recover database;
ALTER DATABASE OPEN RESETLOGS;
}

output:


RMAN> run
{
SET UNTIL SEQUENCE 820 THREAD 1;
recover database;
ALTER DATABASE OPEN RESETLOGS;
}2> 3> 4> 5> 6> 

executing command: SET until clause

Starting recover at 18-FEB-19
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=762 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK

starting media recovery

channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=819
channel ORA_SBT_TAPE_1: reading from backup piece 1169468_ORCL_lttq1og3_1_1
channel ORA_SBT_TAPE_1: piece handle=1169468_ORCL_lttq1og3_1_1 tag=TAG20190216T164243
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2019_02_18/o1_mf_1_819_g6pqfpov_.arc thread=1 sequence=819
channel default: deleting archived log(s)
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2019_02_18/o1_mf_1_819_g6pqfpov_.arc RECID=631 STAMP=1000586214
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-FEB-19

Statement processed

RMAN> 

-- Now verify the database status

SQL> col HOST_NAME for a30
SQL> select instance_name,host_name,startup_time,status from v$instance;

INSTANCE_NAME    HOST_NAME                 STARTUP_TIME       STATUS
---------------- ------------------------ ------------------ ------------
orcl             exammple09                18-FEB-19          OPEN

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      READ WRITE

I hope it may help you. Post your comments.