Creating Oracle 11g active standby database from physical standby database

Creating Oracle 11g active standby database from physical standby database

Here in this article we will look at implementing an active standby database that allows as to open the standby database in read only mode and the redo keeps on applying. We assume that we already have a physical standby configuration already in place as shown below.

Primary Database- DB11G

Standby Database- DB11GDG

Now you can bring up the standby database in read-only mode and continue to have the standby database updated from the primary. Here are the steps to achieve that:

1. Stop the managed recovery process on standby database:
In DB11GDG ( Standby database )

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

2. Open the DB11GDG – standby database as read-only:
SQL > alter database open read only;
Database altered.

3. Restart the managed recovery process on the standby database:

SQL > alter database recover managed standby database using current logfile disconnect;

Now the standby database is being updated, but it is simultaneously open for read-only access—this is the essence of Oracle Active Data Guard.

4. To test the “active” part of Oracle Active Data Guard, create a table in the primary database:

In Primary database issue the following query
SQL > select table_name from dba_tables where table_name = ‘GJ’;
no rows selected

On Standby databaseissue the following query
SQL > select table_name from dba_tables where table_name = ‘GJ’;
no rows selected

In DB11G database – Primary database
SQL > create table GJ (col_id number(10));

5. After a few seconds, check the existence of the table in the standby database:
SQL > select table_name from dba_tables where table_name = ‘GJ’;

TABLE_NAME
——————————
GJ

The table will be propagated.

The standby database is open in read-only mode and it is applying the logs from the primary database. Voila… This feature enables you to run reports against it without sacrificing the ability to put the standby database into the primary role quickly.

6. To confirm the application of redo logs on the primary database, first switch the log file:
alter system switch logfile;

7. Now observe the alert log of the standby database. Use the automatic diagnostic repository command interpreter (ADRCI) tool, new in Oracle Database 11g:

$ adrci
show alert -tail –f

kcrrvslf: active RFS archival for log 6 thread 1 sequence 15684
RFS[7]: Successfully opened standby log 5: ‘/u01/oradata/DB11GDG/standby02.log’
Media Recovery Log /u01/oradata/DB11GDG/arch/DB11GDG/archivelog/2010_03_10/o1_mf_1_15694_4mk48s9y_.arc
Media Recovery Waiting for thread 1 sequence 15685 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 15695 Reading mem 0
Mem# 0: /u01/oradata/DB11GDG/standby02.log

Conclusion

The physical standby database is a physical replica of the primary database, kept in sync by the application of redo log entries from the primary database. With Oracle Active Data Guard and real time apply, the log entries are applied in real time but the primary database does not wait for the redo application to complete, thereby protecting itself from failures or slowdowns in the shipment of log information from the primary to the standby database. Using the Oracle Active Data Guard option, you can open the physical standby database for read-only operations while the managed recovery process is going on. You can offload all reporting applications and the Oracle RMAN backup to the standby database, easing the load on the primary database considerably. Because the standby database is being recovered continuously with real time apply, the standby database can be activated and used immediately in case the primary database fails. This combination of features makes the investment in Oracle Active Data Guard very worthwhile. Credit Arjun, Arup Nanda