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.
Thank you
My pleasure!
Thanks a lot!
You’re welcome!