How to open physical standby database in read only mode – Active Data Guard Part -III

One of the benefit of Physical standby data guard is to use standby database for query access for offloading and reporting purpose by making standby database as a read only mode. So that we can efficiently use or manage primary database resources for core purpose.

Note:
When physical standby database in read only mode, archive log transmission from primary database to standby database continues, but managed recovery is stopped. Once your database back to mount state than managed recovery will be start.

Previously, We covered procedure forΒ Oracle 11g Data Guard Switchover and Switchback

Let me demonstrate the scenario for you, In this we will open our standby database in read-only mode for query access.

Verify database name, its open mode and database role by following command:

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

Database is in mount state.

To make it online, gracefully shutdown the database and open in mount state as follows:

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 905972144 bytes
Database Buffers 553648128 bytes
Redo Buffers 7958528 bytes
Database mounted.

Issue following command to make database in read only mode:

SQL> alter database open read only;
Database altered.

Again verify database name, its open mode and database role, your database is in READ ONLY mode.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ ONLY PHYSICAL STANDBY

As we discussed, while standby database is in READ ONLY mode, archive log transfer from primary to standby database is continues, but managed recovery has been stopped.

To simulate environment, Manually i have generated archive logs at primary database and it automatically transferred to standby database while opened in READ ONLY mode.
Archive logs details on standby before generating multiple manual archive logs, Current log sequence: 61

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 59
Next log sequence to archive 0
Current log sequence 61

Archive logs details on standby after generating manual archive logs, Current log sequence: 66

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 61
Next log sequence to archive 0
Current log sequence 66

To get back to managed recovery, gracefully shutdown standby database, Startup with mount state and finally start managed recovery with the help of following sort of commands:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 905972144 bytes
Database Buffers 553648128 bytes
Redo Buffers 7958528 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.

Verify database name, its open mode and database role by following command, Its get back to mount state.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

With above method we can open standby database in read only mode and get back to its managed recovery.

Kindly tune with me to know how to how to apply primary database redo information to standby database while database is in read only mode. Its new feature introduced in Oracle 11g.

 

Stay tune. πŸ™‚

Leave a Reply