Skip to Main Content
  • Questions

  • Oracle Active Data Guard vs Golden Gate

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ashwin.

Asked: March 31, 2016 - 4:48 pm UTC

Last updated: April 01, 2016 - 11:46 am UTC

Version: 11g R2

Viewed 10K+ times! This question is

You Asked

Hi,

We have a OLTP DB currently. Every 5 minutes we use triggers to pump data into couple of DB from OLTP DB. We perform ETL on these tables and generate reports out of them.

We want to achieve real time reporting and hence want to overhaul the above architecture.

New proposed architecture

We would have Oracle Active Data Guard or Golden Gate to pump data to passive DB. We would have a virtual layer which would apply the ETL using the passive DB as source and pass it to Reporting layer.

I want to know is this correct way to work for reporting layer? Which way should be prefer Golden Gate or Oracle Active Data Guard.

You comments would be very valuable.

Regards
Ashwin

and Connor said...

Active Data Guard (ADG) will give you a real-time read-only replica of your database. So the key points are:

1) read only. You cannot modify anything
2) replica. Its a one-for-one copy of the source database

The reason for this is that ADG is in effect doing a database recovery process using the standard database logs, just not on the source, but on the target.


GoldenGate is different. GoldenGate takes those same logs, and (in essence) reverse engineers those logs back into the original SQL commands (insert/update/delete/etc) that generated those logs in the first place. Those SQL commands can then be run again the target. This means

1) the target can be read-write
2) the target can be divergent from the source

So GoldenGate *sounds* better, but everything comes at a cost :-) It takes more effort to setup and manage, and also, the benefit of divergence can also be a drawback. Because if you get something wrong in the configuration, then you might be divergent without knowing it... and that's a bad place to be.


Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Exa-Data and design challenge

Ashwin Shah, April 01, 2016 - 5:19 am UTC

Hi,

Thank you for your response. It is really helpful. I have another leading question to this.

For real time reporting purpose we will only require read only access to the Data base Hence can we use our 11gR2 with Exa-Data OLTP DB for reporting purpose also. Do you see any issues with this setup?

Will real time reading the OLTP DB impact its performance?

Oracle Data Guard is just replicating but if OLTP performance is not affected from reads then do we really need Oracle Active Data guard?

Regards
Ashwin

Connor McDonald
April 01, 2016 - 11:46 am UTC

For me, the ideal number of copies of data is one. If your server can handle both the transactional activity and the reporting activity, then why bother with multiple copies etc. (Sure, you may *one day* need to do that...but if you dont *have* to, then I would not).

You might want to consider Resource Manager and IO Resource Manager within the Exadata platform, so that you can control how resources are allocated between transactional and reporting needs.

Hope this helps.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.