Active Dataguard in Oracle

In this article I will explain you Active Data Guard feature coming with Oracle 11g version.

Active Dataguard in Oracle

Before reading this article, I recommend that you read the article about Data Guard configuration and architecture with following links.

Oracle Dataguard ( Standby ) Architecture -3

Read Only Dataguard ( Standby )

As I mentioned in the above article, Data Guard allowed us to create a Manual Standby with Oracle 7 version. Later versions have come up to the latest version with a lot of features added. In Oracle 11g version, there was another feature announced that attracts a lot of attention. This feature is Active Standby or Active Data Guard feature.

So what does this Active Dataguard feature offer us? Active Data Guard allows users to access ( query ) Standby Database in Read-only mode  while the Redo data from the Primary database is applied in real-time. This means that we can use the standby database in Read Only mode especially for Reporting.

By using this feature, we can reduce the load of the production system so that the production system can be used for OLTP and routine tasks. In addition, to Reduce the production database’s Load, we can take the necessary backups of our production database on Dataguard instead of production.

So is this nice feature free? Of course, like any Oracle feature is licensed separately, and also Active Data Guard is licensed separately except from Oracle Enterprise Edition.

We can activate the Active Data Guard property as described below.

1. The Redo apply in the standby database is stopped. The Redo Apply process was doing the MRP process and we are stopping this process. Before stopping this process was working as follows.

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
DEVECIDG

SQL> select process, client_process,thread#,sequence#,status from v$managed_standby;
PROCESS CLIENT_P THREAD# SEQUENCE# STATUS
--------- -------- ---------- ---------- ------------
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
MRP0 N/A 1 8 WAIT_FOR_LOG
RFS UNKNOWN 0 0 IDLE
RFS UNKNOWN 0 0 IDLE
RFS ARCH 0 0 IDLE
RFS LGWR 1 9 IDLE
10 rows selected.
SQL>

Stop this process with the following script. This will cause Redo Apply to stop.

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

When I run the above query again, the MRP (Managed Recovery Process) process no longer works as shown below.

SQL> select process, client_process,thread#,sequence#,status from v$managed_standby;
PROCESS CLIENT_P THREAD# SEQUENCE# STATUS
--------- -------- ---------- ---------- ------------
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
RFS UNKNOWN 0 0 IDLE
RFS UNKNOWN 0 0 IDLE
RFS ARCH 0 0 IDLE
RFS LGWR 1 9 IDLE
9 rows selected.
SQL>

2. We are opening the Standby database in the Read Only mode, which is in Mount mode as follows.

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
DEVECIDG
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED

SQL> alter database open read only;
Database altered.
SQL>

3. The Redo Apply process is started as follows.

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

When Redo apply is started, we query the open mode of our database.  Now we have to see the result like ” READ ONLY WITH APPLY ”

SQL> select open_mode from v$database;
OPEN_MODE
 --------------------
 READ ONLY WITH APPLY
SQL>

Now we can query in the Standby database all the changes we made in our Production database in a short time.

If you want to learn how to open Dataguard in Read Write mode ( Snapshot Standby ), Read the following post.

Snapshot Standby ( Read Write ) in Oracle Dataguard

If you want to learn how to install Dataguard, Read the following post.

Oracle DataGuard Physical Standby Installation Step by Step Using RMAN -2

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )