Skip to content
Home » Oracle » How to Resolve ORA-01152: file 1 was not restored from a sufficiently old backup

How to Resolve ORA-01152: file 1 was not restored from a sufficiently old backup

When I tried to open the standby database to READ ONLY for later real-time query of Active Data Guard (ADG), I got this error message.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

ORA-01152 means that we might have some data files which are newer than the controlfile, so the database needs to be recovered. This could be:

  • An old controlfile was used for database restoration. Or
  • Some data files were not restored, which make them newer.

Solution

First of all, we need to copy some archived logs from the primary database server to my standby one. Next, I catalog those archived logs.

RMAN> catalog start with '/u01/app/oracle/recovery_area/ORCL/archivelog/';

searching for all files that match the pattern /u01/app/oracle/recovery_area/ORCL/archivelog/

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_03/o1_mf_1_8_h5wdb19h_.arc
File Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_03/o1_mf_1_9_h5wdbz3m_.arc
File Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_03/o1_mf_1_10_h5wfphz1_.arc
File Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_09/o1_mf_1_11_h6dlz856_.arc

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_03/o1_mf_1_8_h5wdb19h_.arc
File Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_03/o1_mf_1_9_h5wdbz3m_.arc
File Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_03/o1_mf_1_10_h5wfphz1_.arc
File Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_09/o1_mf_1_11_h6dlz856_.arc

Then we recovered the database.

RMAN> recover database;

Starting recover at 17-MAR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_03/o1_mf_1_10_h5wfphz1_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_09/o1_mf_1_11_h6dlz856_.arc
archived log file name=/u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_03/o1_mf_1_10_h5wfphz1_.arc thread=1 sequence=10
archived log file name=/u01/app/oracle/recovery_area/ORCL/archivelog/2020_03_09/o1_mf_1_11_h6dlz856_.arc thread=1 sequence=11
unable to find archived log
archived log thread=1 sequence=12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/17/2020 19:14:46
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 12 and starting SCN of 1099426

We can try to open the standby database to READ ONLY now.

SQL> alter database open read only;

Database altered.

We did it.

Preventive Action

To prevent such gap between the standby controlfile and data files, you can use RMAN to restore the standby controlfile from a full and consistent backupset in the first place.

RMAN> restore standby controlfile from '/path/to/the/backup/piece/of/control/file/within/the/same/backupset';

For example:

RMAN> restore standby controlfile from '/u01/app/oracle/recovery_area/ORCL/backupset/2020_03_03/o1_mf_ncnnf_TAG20200303T182818_h5wdb4f8_.bkp';

Please note that, in my plan, I intend to open the standby database to READ ONLY first, then build data guard (broker). Such arrangement can make real-time query enabled at the same time when the data guard starts to work, so I don't need to change the state of the standby database from MOUNT to READ ONLY for enabling real-time query, moreover, the synchronization won't be interrupted.

4 thoughts on “How to Resolve ORA-01152: file 1 was not restored from a sufficiently old backup”

Leave a Reply

Your email address will not be published. Required fields are marked *