Standby Database in READ ONLY Mode

Standby Database in Read Only mode
----------------------------------------------


Purpose:

As explained before, a physical standby database can be toggled between recovery and read-only mode. If the database is currently in managed recovery mode, in order to open it for reporting, cancel the recovery and open it as read-only.


First of all we have to cancel the recover mode of standby database.

SQL> alter database recover managed standby database cancel;
Database altered.


Open the standby database in read only mode.

SQL> alter database open read only;
Database altered.


Once you finished your reporting u have to shutdown the standby database.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start up the database in mount mode.



SQL> startup mount
ORACLE instance started.
Total System Global Area 7348420608 bytes
Fixed Size                  2150272 bytes
Variable Size            1201614976 bytes
Database Buffers         6006243328 bytes
Redo Buffers              138412032 bytes
Database mounted.


You can start recovery mode again using below command.



SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;



You can monitor the archive log files.



SQL> SELECT SEQUENCE#,FIRST_TIME,NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;