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

  • by

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'

This is because the progress of data files are behind the standby controlfile, they require recovery in order to be consistent with the controlfile.

SQL> set heading off;
SQL> select distinct CHECKPOINT_CHANGE# from v$datafile;

                1086497
SQL> select CURRENT_SCN from v$database;

    1088733

As you can see, the SCN of standby controlfile advances data files.

Solution

That is to say, we should recover those data files to be at least over 1088733.

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

Let's see the result.

SQL> select distinct CHECKPOINT_CHANGE# from v$datafile;

           1099426

SQL> select CURRENT_SCN from v$database;

    1099425

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

SQL> alter database open read only;

Database altered.

We did it.

Another way to solve the error is to build data guard first to make the standby database synchronized with the primary. This can guarantee data files consistent with the controlfile, then we can open the standby database to READ ONLY.

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.

Leave a Reply

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