Read - only Standby and Active Data Guard feature in Oracle Data Guard

A physical standby database can be opened in Read only mode so that it can be used to query access. This feature of Data Guard is used to freeing up resource on Primary database.

When opened in Read - only mode , Log shipping from primary database to Standby continues but Managed recovery stopped. It makes the standby database out of data from Primary.

Steps to Switch standby database in Read - Only mode :-


SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2213384 bytes
Variable Size             817891832 bytes
Database Buffers          213909504 bytes
Redo Buffers                9871360 bytes
Database mounted.
SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

--NOTE :- Database can be used for querying , reporting purposes.

We can check that Redo will be shipped to Standby database but it will not be applied :-

1. Check alert log of primary database :-

Thu Jul 25 17:02:29 2013

Thread 1 advanced to log sequence 32 (LGWR switch)

  Current log# 1 seq# 32 mem# 0: /disk1/STBY/STBY/redo01.log

Thu Jul 25 17:02:29 2013

Archived Log entry 32 added for thread 1 sequence 31 ID 0x7e5a4c15 dest 1:

LNS: Standby redo logfile selected for thread 1 sequence 31 for destination LOG_ARCHIVE_DEST_2

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

LNS: Standby redo logfile selected for thread 1 sequence 32 for destination LOG_ARCHIVE_DEST_2

################### Log has been copied to standby dataabse#####################

2. Check Alert log of Standby Database :-

RFS[1]: Selected log 4 for thread 1 sequence 31 dbid 2119844559 branch 821698130

Thu Jul 25 16:01:46 2013

Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST

Archived Log entry 43 added for thread 1 sequence 31 ID 0x7e5a4c15 dest 1:

Thu Jul 25 16:01:46 2013

RFS[2]: Assigned to RFS process 26018132

RFS[2]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 60030994

Primary database is in MAXIMUM PERFORMANCE mode

RFS[2]: Selected log 4 for thread 1 sequence 32 dbid 2119844559 branch 821698130

Thu Jul 25 16:01:48 2013

RFS[3]: Assigned to RFS process 27787546

RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 12648536

#####################Log has been reached but not applied#########################

To Resume Managed recovery and we have to change standby database from Read - only mode to recovery mode :-

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2213384 bytes

Variable Size             817891832 bytes

Database Buffers          213909504 bytes

Redo Buffers                9871360 bytes

Database mounted.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

Active Data Guard :- Its a feature of Oracle Data Guard introduced in 11g version. In this we can open our physical standby database in Read - Only mode and still apply redo information to it.

In this feature a standby is available for querying but still be up to date with Primary. As managed recovery continues with Active data Guard so we do not have to switch back to managed recovery mode from read only mode .

Steps to implement Active Data Guard :-

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2213384 bytes
Variable Size             817891832 bytes
Database Buffers          213909504 bytes
Redo Buffers                9871360 bytes
Database mounted.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

I hope this article helped you.

Regards,
Amit Rath