Active Dataguard in Oracle

Hi,

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

Dataguard2

 

 

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

 

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.

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *