Oracle DBA Tips and Techniques

By Arjun Raja

11g ACTIVE STANDBY

Posted by Arjun Raja on June 22, 2009

Active Standby :

PRIMARY DB- APEX

STANDBY DB- APEXDG

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 APEXDG ( Standby database )

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

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

3. Restart the managed recovery process ON 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 –
SQL > select table_name from dba_tables where table_name = ‘ARJUN';
no rows selected

On STANDBY
SQL > select table_name from dba_tables where table_name = ‘ARJUN';
no rows selected

In APEX database – PRIMARY
SQL > create table arjun (col1 number);

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 = ‘ARJUN';

TABLE_NAME
——————————
ARJUN

The table will be present !!

The standby database is open in read-only mode, but it is still applying the logs from the primary database.
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 15694
RFS[7]: Successfully opened standby log 5: ‘/u02/oradata/apexdg/standby02.log’
Media Recovery Log /u02/oradata/apexdg/arch/APEXDG/archivelog/2008_12_05/o1_mf_1_15694_4mk48s9y_.arc
Media Recovery Waiting for thread 1 sequence 15695 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 15695 Reading mem 0
Mem# 0: /u02/oradata/apexdg/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.
And 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.

About these ads

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

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: