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


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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: