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 to 2018. 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.
Shivananda Rao 
good one
Thank you !
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
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
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.
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
Hi It Is very Important post regarding data guard i Did the same as given steps and successfully done
thank you very much.