Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

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

March 14, 2010 - Posted by | oracle

8 Comments »

  1. This is the article what I am needing. Excellent explaination for Active data guard of Oracle 11g concept.

    Comment by Jack Nicholson | April 12, 2010 | Reply

  2. Thanks for posting this Guenadi, very helpful.

    Comment by steveharville | April 5, 2011 | Reply

  3. Hi.
    Thanks for sharing such a great expirience!

    Btw. Do you know how to turn it back? I mean disable read-only feature and leave only standby-recovery mode.

    Thanks.

    Comment by Ratik | May 27, 2011 | Reply

    • Hello,

      Restart with only managed standby recovery mode.

      Regards,

      Comment by gjilevski | May 27, 2011 | Reply

      • How do subsequent automatic restarts get handled (for example the standby server reboots)? I am not licensed for and thus do not want to use the Active Standby feature nor do I want to turn it off every time my standby database is automatically bounced. Is there a setting to not open it in read only mode?

        Comment by Kristin | April 13, 2012

      • Hi,

        There are two aspects.

        1. Technical aspect depends on the details related to your setup.
        2. License aspect for which you need to check with the Oracle sales/account rep.

        I strongly recommend to check with the account/sales rep for the licenses.

        As for the tech setup it can be set to mount the standby after a reboot, depending on if GI is used or a custom startup is used.

        In GI you can register the standby database as a resource and specify what action to take and respective role. You can implement similar action with a custom approach. All in all, the technology allow you to control that state of the standby.

        Do you use GI or any customs scripts to start the standby database? How is your setup?

        Regards,

        Comment by gjilevski | April 15, 2012

  4. Hello Sir,

    I have my primary and standby databases in sync and sequence # matches.But I am unable to open my standby in read only mode. Alert log just sits with this message ”Media Recovery Waiting for thread 1 sequence 221 (in transit)”
    Please let me know if there is anything needs to be taken care to open in read only mode.
    DAtabase version I am using is 11.2.0.3.
    Thanks,

    Comment by Krishna | February 26, 2013 | Reply

    • Hi,

      Did you try to

      1. Stop the managed recovery
      2. Open read only
      3. Start the managed recovery if using Active Data Guard

      Best Regards,

      Comment by gjilevski | February 27, 2013 | Reply


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.

Join 782 other followers

%d bloggers like this: