Wednesday, September 22, 2010

What is Oracle Goldengate?

After Oracle corp. acquiring Goldengate software there is a lot of buzz about Oracle Goldengate and it is one of the hot topics at Oracle open world 2010.

Oracle Goldengate can be used as a replication tool, ETL, and even as a DR solution.

Oracle Goldengate (GG) is probably the best replication software and it is very easy to configure and deploy it in large scale environment. Here are some of the things you need to be aware of:

1) All GG configuration files are ascii text based files. Very easy to make changes but it is prone to human errors in an environment having many DBA's working on it.

2) In order to use parallel apply threads, GG breaks down the database transaction into multiple transactions based on the hashing key defined for range split of the data. So, transactional consistency will not be guaranteed during real time but there won't be any data loss, but make sure that your application can tolerate this.

3) If there is no primary key or unique index exists on any table, GG will use all the columns as supplemental logging key pair for both extracts and replicats. But if you define key columns in the GG extract parameter file and if you don't have the supplemental logging enabled on that key columns combination, then GG will assume missing key columns record data as "NULL", which is a huge deal, and this will introduce logical data corruption on the target.

4) GG started supporting bulk data loads with their 11.1 release but any NOLOGGING data changes will be silently ignored without any warning.

5) GG doesn't support compression on the source database.

6) GG does support DDL replication but it is not easy to do selective DDL replication, it replicates every DDL that happens on the source database which is not desirable for some customers.

7) Tables being replicated to on the target can also be written to by any other application or DBA's.

8) GG supports ignoring data conflicts for updates after the first instantiation of the target database until it catches up. But it is very easy to forget turning off that parameter and any updates being lost will not be alerted by GG.

9) GG still works by reverse engineering the Oracle redolog. This may not be totally true with GG 11, but I expect GG to interpret Oracle redo more directly in later versions of 11 or 12.

10) GG dynamically decides to change the key columns that form the supplemental logging based on the state of primary key (i.e. in VALIDATED or NONVALIDATED state), which can introduce data corruptions on the target databases as the expected key columns data is missing in the trail files and they will be set to NULL. They now have the patch available for this, you can set "_USEALLKEYCOLUMNS and ALLOWNONVALIDATEDKEYS" parameters in GLOBALS file to get around this problem.

Use cases:

I think Oracle is not promoting logical standby as much as they should have. Oracle logical standby guarantees data consistency, data integrity, maintains order of transactions, and doesn't let target database tables to be modified by others which by itself offer great confidence in data quality.

Oracle Xstreams offers greatest flexibility and superior performance in extracting data from the source database and applying the same to the target database.

For Oracle database upgrades or having a logical DR standby it is better to use Oracle logical standby, use Xstreams if you want more flexibility and high performance in moving data across databases, and use GG for keeping the downstream database up to date for reporting, ETL purposes, or to move data across hybrid databases.



At October 6, 2010 at 5:04:00 PM PDT, Blogger Noons said...

A good summary of GG and its applicability. Very timely for us.

At December 10, 2010 at 1:22:00 AM PST, Blogger Brijesh said...

for this also visit

At December 14, 2010 at 10:43:00 PM PST, Anonymous Anonymous said...

Very Nice.


Programmer's and Developer's Forum

At March 2, 2011 at 5:30:00 AM PST, Anonymous anonymous transactions said...

I have had great time reading your article. It’s actually quiet awesome, it is surprising how you have learn t to write your feelings and thoughts in so precise manner. It's good really. anonymous transactions

At March 15, 2011 at 3:59:00 AM PDT, Anonymous Helder Oliveira said...

First time i have read about GG.
Thanks man

At June 11, 2011 at 3:50:00 PM PDT, Anonymous Sandeep Kumar said...

It's Simple and informative.

At June 16, 2011 at 9:32:00 PM PDT, Blogger KK said...

great one...really useful !!!

At June 23, 2011 at 2:58:00 PM PDT, Blogger ARUNDAS KALATHIL said...

I have a detailed hands on for Goldengate Active/Active replication

check out my blog

At July 15, 2011 at 5:55:00 AM PDT, Anonymous dashboarding services said...

That'a a nice described specification listing of oracle Golden gate. The post says its a nice replication tool.

At October 25, 2011 at 6:21:00 PM PDT, Anonymous Vladimir Grigorian said...

I have a question about Flashback Data Archive tables in Goldengate replication. Are these tables usually excluded in an extract like "TABLEEXCLUDE .SYS_FBA_*"?
What is the approach for replicating and initial load of Flash Back Data Archive tables?
Here is my problem. I exported a user schema using Oracle DataPump, imported into destination database. GoldenGate abended saying some tables dont exist on dest. I checked tables and there were about 200 tables that were not exported because they are FBDA tables and DataPump just ignores them. So, I recreated them on source with scripts.
So, how is this done? Source and destination have their own FBDA and these tables should not be neither recreated with scripts on dest or replicated? Or they have to be replicated with contents?

At June 21, 2012 at 6:44:00 AM PDT, Anonymous GridwiseTech said...

With the simple deployment of GoldenGate between databases, one can build a highly available architecture with each database holding accurate copies of each other's data.
Thus Oracle GoldenGate guarantees that even in an unstable environment where networks and host servers occasionally drop out, transactions will never be missed or skipped.

Read more about business continuity and real-time integration:

At January 23, 2013 at 8:33:00 AM PST, Anonymous Anonymous said...

This comment has been removed by a blog administrator.

At February 7, 2013 at 7:31:00 AM PST, Blogger Unknown said...

I have a replicate and it has 200+ sqlexec statement. When i started replicate it thrown an error saying that more than 100 sqlexec is not supported. I am using GG in ODI and all the script is generating by ODI jkm.
Please help me on this.

At October 23, 2013 at 12:08:00 AM PDT, Blogger Unknown said...

That is a very nice described specification listing of oracle golden gate

At November 17, 2014 at 2:56:00 AM PST, Blogger Unknown said...

Please use 12c Version of Golden gate

At January 4, 2015 at 10:50:00 PM PST, Anonymous Anonymous said...

I have a questions. is it possible to to replicate whole database from using GG? i dont want to replicate a particular schema. the schemas will be created randomly and want them to be replicated into target database,Is it possible?

At February 7, 2016 at 8:05:00 AM PST, Blogger Unknown said...

Whats the way to learn and work on this technology?


Post a Comment

<< Home