Advertisements
Skip to content
March 19, 2012 / Shivananda Rao P

How to setup active dataguard in oracle 11g

The main advantage of setting up dataguard in 11g is that the database can be opened in Read-Only mode allowing the Users to access the physical standby database for fetching reports and on the same time the physical standby database can be in recovery mode. In other words, the physical standby database would be in recovery mode and hand in hand the standby database can be used for reporting purposes.

The read-only physical standby database can be used to offload query from the primary database. Users can use select statements and complex queries against this this database and thereby decreasing the load on the primary database. While the standby is open read-only, the following operations are disallowed

  • Any Data Manipulation Language (DML) except for select statements
  • Any Data Definition Language (DDL)
  • Access of local sequences
  • DMLs on local temporary tables

Steps on how to setup the active dataguard:

Once you setup the physical standby database as described in https://shivanandarao.wordpress.com/2012/03/10/creating-physical-standby-database-on-oracle-11g/ follow the below steps to setup the active dataguard.

Step 1:

Check the status of the Primary database and the latest sequence generated in the primary database.


SQL> select status,instance_name,database_role from v$instance,v$database;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         prim             PRIMARY

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
40

Step 2:

Check the status of the physical standby database and the latest sequence applied on the physcial standby database.

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS   INSTANCE_NAME DATABASE_ROLE
-------- ------------- ---------------------
MOUNTED  stnd          PHYSICAL STANDBY

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
40

Step 3:

Check if the Managed Recovery Process (MRP) is active on the physcial standby database.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED     0
ARCH      CONNECTED     0
ARCH      CONNECTED     0
ARCH      CONNECTED     0
RFS       IDLE          41
RFS       IDLE          0
RFS       IDLE          0
RFS       IDLE          0
MRP0      WAIT_FOR_LOG  41

9 rows selected.

Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.

Step 4:

Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;

STATUS INSTANCE_NAME  DATABASE_ROLE    OPEN_MODE
------ -------------- ---------------- ---------------
OPEN   stnd           PHYSICAL STANDBY READ ONLY

Step 5:

Now start the MRP on the physical standby database.

SQL> alter database recover managed standby database disconnectfrom session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED     0
ARCH      CONNECTED     0
ARCH      CONNECTED     0
ARCH      CONNECTED     0
RFS       IDLE          41
RFS       IDLE          0
RFS       IDLE          0
RFS       IDLE          0
MRP0      WAIT_FOR_LOG  41

9 rows selected.

Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.

Here you go !!

COPYRIGHT

© Shivananda Rao P, 2012, 2013, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

Advertisements

7 Comments

Leave a Comment
  1. Mohan / Nov 13 2012 4:58 pm

    good one

  2. Shivananda Rao P / Nov 13 2012 5:05 pm

    Thank you !

  3. varsha / Feb 22 2013 10:44 pm

    Thanks. How/When to add Tempfiles because when I did this, I am getting errors in my alert log file ORA-25153: Temporary Tablespace is Empty

  4. Shivananda Rao P / Feb 22 2013 11:14 pm

    Hello Varsha,

    ORA-25153 can be seen on the standby database when the temp tablespace does not have any tempfiles. When you create a temporary tablespace with temp files in primary database, as the redo data generated on primary is applied to the standby, the newly created temp tablespace will be available on standby as well but the tempfiles will not be.

    The reason for this is that tempfile definitions are not stored in the “SYS.FILE$” dictionary table. Because adding a tempfile on the primary does not update “SYS.FILE$”, there is no redo generated. So recovery on the standby cannot create the file. However, changes are made to the tablespace dictionary table, “SYS.TS$”, so the tablespace definition is in the standby dictionary.

    Regards,
    Shivananda

  5. Pablo Gallucci / May 15 2013 6:16 pm

    hi, good article, can i make a question? if i have active data guard, so can i offload any of my Oracle Reports 6i to the stand by database? in case we only run select statements…lets say select * from gl_je_headers…so now..how can i run this report…in the stand by? may i use dblinks or what??

    thanks in adavnce
    Pablo.

  6. Shivananda Rao P / May 16 2013 2:22 pm

    Hello Pablo,

    You cannot create a db link only on your standby database. A db link needs to be created on the primary database and that would get reflected on the standby database after the redo is applied. Later on you can connect to the standby and use the db link pointing to the remote database to fetch data.

    Regards,
    Shivananda

  7. Jayant Mane / Aug 15 2014 12:53 pm

    Hi It Is very Important post regarding data guard i Did the same as given steps and successfully done
    thank you very much.

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: