Problems with opening standby database read only

Read our docs 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.

Solution

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).

Steps Performed

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

As you can see, controlfile is ahead of datafiles, so there is no possibility to open the database read only.

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.

So after the application of archivelog the standby database opened read only successfully, because controlfile and all datafiles have the same change#.

There can be however a situation, when you cannot apply archivelog and your standby database has controlfile which is inconsistent with datafiles. There is a solution which can help you. You will have to re-create controlfile from trace. You will loose any backup information stored in controlfile, but you will be able to open the standby database. Steps to do so are following:

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
;

After the file is created, delete / move your current controlfiles

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.

Note, that this was possible only because of the fact, that the datafiles were consistent.

We're here to help!