Monday, March 5, 2012

Difference between Dataguard and Active Dataguard


I  found  people are bit confused between Dataguard and Active Data guard . They assume that Active dataguard is having different configuration or properties .  Here, i have tried to cover the Active dataguard .

Active Data Guard is a new option for Oracle Database 11g Enterprise Edition . Oracle Active Data Guard enables read-only access to a physical standby database . Active Data Guard is the possibility to query  our Physical Standby Database while it is constantly actualized to reflect the state of the Primary Database . It is additional to 11g Data Guard and comes with an extra charge .

Oracle Active Data Guard enhances the Quality of Service (QoS) for production databases by off-loading resource-intensive operations to one or more standby databases, which are synchronized copies of the production database. With Oracle Active Data Guard, a physical standby database can be used for real-time reporting, with minimal latency between reporting and production data. Compared with traditional replication methods, Active Data Guard is very simple to use, transparently supports all datatypes, and offers very high performance. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database, and be done very fast using intelligent incremental backups. Oracle Active Data Guard thus is a very effective way to insulate interactive users and critical business tasks on the production system from the impact of such long-running operations. Oracle Active Data Guard provides the additional benefit of high availability and disaster protection by quickly failing over to the standby database in the event of a planned or an unplanned outage at the production site.

The Active Data Guard  contains the following features :

  • Physical Standby with Real-time Query
  • Fast Incremental Backup on Physical Standby
  • Automatic Block Repair

If a physical standby database in a Data Guard configuration has any of the above features enabled, then the Active Data Guard option must be licensed for every such physical standby, and also for the primary database.

Conversion from Physical standby to Active Data Guard :
We can convert the physical standby  into active Data Guard standby . Below are the steps

1.) Stop Apply Services
SQL> alter database recover managed standby database cancel ;

2.) Shut the database and open in mount stage
SQL> shut immediate
SQL> startup mount
SQL> alter database recover managed standby database using current logfile disconnect ;
SQL> alter database open ;
It enables us to have a physical standby read only open, while redo apply is still done in the background .

How to Check if Active Data Guard is  Enabled  or Not 
Use the following query to confirm that Data Guard is in active mode:

SQL> select  'Using Active Data Guard' ADG   from v$managed_standby m,v$database d where m.process like 'MRP%'  ;
        ADG
-----------------------
Using Active Data Guard

or from standby datbase

SQL> select open_mode,controlfile_type from v$database;
OPEN_MODE                              CONTROLFILE
--------------------                        ------------------
READ ONLY WITH APPLY            STANDBY


If the query does not return the above result, and instead returns : no rows selected, then Active Data Guard is not enabled.


Enjoy     :-) 


13 comments:

Unknown said...

This is in ref. to last query that u have written to verify if Active Data Guard is running or not , Shouldn't your query say d.open_mode="READ ONLY WITH APPLY" instead of d.open_mode='READ_ONLY' ?

Anonymous said...

Nice post...

This is in ref. to last query that u have written to verify if Active Data Guard is running or not , Shouldn't your query say d.open_mode="READ ONLY WITH APPLY" instead of d.open_mode='READ_ONLY' ?

NEERAJ VISHEN said...

@Anonymous

Thanks for correcting me .....

Since, we have active dataguard so ,its very obvious that open_mode will show the "READ ONLY WITH APPLY" .

Thanks

Regards

Neeraj

Anonymous said...

Thanks Neeraj :_)

Anonymous said...

Your second to last sql is not valid. It is always going to return you are using Active Data Guard.

Unknown said...

Hi Neeraj , you mean to say physical standby databases would be either in read only state ( no real time apply) or it would be in recovery mode ( no reporting done)??

If standby database has ACTIVE dataguard enabled we can have standby database in both read only and real time apply!! right?

Pls clarify me

andrea chiu said...

Time is gold. We should treasure every second of it so that in time we will not regret anything because we only live ones.I have read your article and i was glad and inspired. Thanks for it, Cheers!
Visit my site if you have time, Enjoy.

n8fan.net

www.n8fan.net

Indikafern said...

Well said bro

avinash shitole said...

Hi Neeraj,
Quick query on if I can use Active DataGuard Standby Database for Data refresh to minimise data copy time

1.Setup ADG between Primary and StandBy
2.Stop ADG/Sync between Primary and Standby
3.Open and Mount Standby database in Read-Write Mode
4. Now use this Standby Database as my Working copy ( may be rename the db etc) where I will use it as UAT to make my read write operations for another month or so.

Next time now I want to refresh database after one month
5. Enable ADG/Sync
6. Take Incremental Backup of Primary Database
7. Restore Incremental backup of Standby Database so I dont have to refresh whole database with old data.
8. Now I have Standby database again in sync with Production copy in short time

Does this work ??

avinash shitole said...

Hi Neeraj,
Quick query on if I can use Active DataGuard Standby Database for Data refresh to minimise data copy time

1.Setup ADG between Primary and StandBy
2.Stop ADG/Sync between Primary and Standby
3.Open and Mount Standby database in Read-Write Mode
4. Now use this Standby Database as my Working copy ( may be rename the db etc) where I will use it as UAT to make my read write operations for another month or so.

Next time now I want to refresh database after one month
5. Enable ADG/Sync
6. Take Incremental Backup of Primary Database
7. Restore Incremental backup of Standby Database so I dont have to refresh whole database with old data.
8. Now I have Standby database again in sync with Production copy in short time

Does this work ??

avinash shitole said...

Hi Neeraj,
Quick query on if I can use Active DataGuard Standby Database for Data refresh to minimise data copy time

1.Setup ADG between Primary and StandBy
2.Stop ADG/Sync between Primary and Standby
3.Open and Mount Standby database in Read-Write Mode
4. Now use this Standby Database as my Working copy ( may be rename the db etc) where I will use it as UAT to make my read write operations for another month or so.

Next time now I want to refresh database after one month
5. Enable ADG/Sync
6. Take Incremental Backup of Primary Database
7. Restore Incremental backup of Standby Database so I dont have to refresh whole database with old data.
8. Now I have Standby database again in sync with Production copy in short time

Does this work ??

avinash shitole said...

Hi Neeraj,
Quick query on if I can use Active DataGuard Standby Database for Data refresh to minimise data copy time

1.Setup ADG between Primary and StandBy
2.Stop ADG/Sync between Primary and Standby
3.Open and Mount Standby database in Read-Write Mode
4. Now use this Standby Database as my Working copy ( may be rename the db etc) where I will use it as UAT to make my read write operations for another month or so.

Next time now I want to refresh database after one month
5. Enable ADG/Sync
6. Take Incremental Backup of Primary Database
7. Restore Incremental backup of Standby Database so I dont have to refresh whole database with old data.
8. Now I have Standby database again in sync with Production copy in short time

Does this work ??

Anonymous said...

@Chamarths Yes!