Problems with opening standby database read only.
Problem Details
Problem Statement
It is not possible to open standby database in read only mode
Applicable Error Code
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
Affected Versions
All
Affected Platforms
All
Description
Sometimes it may happen, that you are not able to open your database in read only mode. While attempting to do so, you will receive an error:
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/DBVONE/system02.dbf'
This usually happens, when you try to open your standby database directly after switchover/synchronizing or creation.
This is expected behavior and it is necessary to apply at least one arhivelog to the standby database. After the archivelog is applied, the alter database open read only will work.
The reason for this error is, that in some situations (like directly after the switchover or standby database sync) the controlfile has different change# than the datafiles (But the datafiles together are consistent).
1) To verify, that you are in this situation, do following:
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> select checkpoint_change#, controlfile_change# from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
------------------ -------------------
1701592 1701619
SQL> set linesize 200;
SQL> col name for A50 heading 'File name'
SQL> select file#, name, checkpoint_change#, last_change#, status, enabled from v$datafile;
FILE# File name CHECKPOINT_CHANGE# LAST_CHANGE# STATUS ENABLED
---------- -------------------------------------------------- ------------------ ------------ ------- ----------
1 /u01/app/oracle/oradata/DBVONE/system02.dbf 1701592 SYSTEM READ WRITE
2 /u01/app/oracle/oradata/DBVONE/sysaux01.dbf 1701592 ONLINE READ WRITE
3 /u01/app/oracle/oradata/DBVONE/undotbs01.dbf 1701592 ONLINE READ WRITE
4 /u01/app/oracle/oradata/DBVONE/users01.dbf 1701592 ONLINE READ WRITE
2) simply run dbvisit (or dbvctl) command to send archivelog from primary and apply it to your standby database afterwards. After applying archivelog, the situation should look like this:
SQL> select checkpoint_change#, controlfile_change# from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
------------------ -------------------
1701592 1702015
SQL> select file#, name, checkpoint_change#, last_change#, status, enabled from v$datafile;
FILE# File name CHECKPOINT_CHANGE# LAST_CHANGE# STATUS ENABLED
---------- -------------------------------------------------- ------------------ ------------ ------- ----------
1 /u01/app/oracle/oradata/DBVONE/system02.dbf 1702015 SYSTEM READ WRITE
2 /u01/app/oracle/oradata/DBVONE/sysaux01.dbf 1702015 ONLINE READ WRITE
3 /u01/app/oracle/oradata/DBVONE/undotbs01.dbf 1702015 ONLINE READ WRITE
4 /u01/app/oracle/oradata/DBVONE/users01.dbf 1702015 ONLINE READ WRITE
SQL> alter database open read only;
Database altered.
1) Do the same check as in 1) for previous case - the output has to be the same (all datafiles are consistent together, only controlfile is not)
2) Your standby database should be mounted, therefore you are able to backup current controlfile to trace:
SQL> alter database backup controlfile to trace as '/u01/app/oracle/oradata/DBVONE/ctl.sql'; Database altered. SQL> shu abort; ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 281021520 bytes Database Buffers 549453824 bytes Redo Buffers 2371584 bytes
3) Now you will have to edit your controlfile trace - take "RESETLOGS" section. In the end, the script should look like this:
CREATE CONTROLFILE REUSE DATABASE "DBVONE" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '... redo_1_1.log', '... redo_1_2.log' ) SIZE 512M BLOCKSIZE 512, GROUP 2 ( '... /redo_2_1.log', '... /redo_2_2.log' ) SIZE 512M BLOCKSIZE 512 DATAFILE '... system02.dbf', ... ... CHARACTER SET AL32UTF8 ;
4) Create the controlfile and open the database:
SQL> startup nomount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 281021520 bytes Database Buffers 549453824 bytes Redo Buffers 2371584 bytes SQL> @/u01/app/oracle/oradata/DBVTWO/ctl.sql Control file created. SQL> alter database open resetlogs; Database altered.
Comments