Oracle Goldengate

I always laugh at Oracle’s sale pitch for Oracle Goldengate. The conversation is like this:
We need to implement an ETL process …
Oracle Goldengate is just for that.
We need a long distance disaster recovery solution …
Oracle Goldengate is great for it.
We have RAC but facing downtime for no rolling patching …
Oracle Goldengate is a must.
We need to pull data from other database vendor’s databases for real time BI …
Oracle Goldengate of course.

However, after some research on Oracle Golden gate, I start to like it more. First, it supports replication of a wide range of database vendors from MySQL to DB2. MySQL is quite popular for software development nowadays but few replication products support it. Secondly, it uses transaction logs (redo log for oracle, binLog for MySQL) for Changed Data Capture (CDC) that has performance advantage and no change to the databases involved comparing with other CDC methods, such as trigger based. The third, it has rich set of built-in functions for data transformation and mapping on fly. In conclusion, it may not be the solution for every database problem, but can meet many IT challenges if used properly.

MySQL replication

MySQL replication is easy to setup but very basic comparing to the Oracle replication. But my biggest complaint is that it allows user to setup Master-to-Master replication. Because MySQL replication is asynchronous, The ACID property of modern relational database is down to the drain with the MySQL Master-to-Master replication. The correct way for MySQL Master-to-Master is MySQL cluster setup.

This example shows the problem with MySQL Master-to-Master replication. Master A executes "update table a_table set col1 = 1 where id =1;" and master B executes "update table a_table set col1 = 2 where id =1;" at about the same time, there are three possible outcomes:
1. master A: a_table col1=2 where id = 1 and master B: a_table col1=1 where id = 1, the two masters are out of sync
2. for both masters: a_table col1=1 where id = 1
3. for both masters: a_table col1=2 where id = 1
There is no way to predict what outcome of one the three with MySQL multimaster replication.

Change Database name

We all know how to change the database name by recreating the control file. However, I consider this is a risky operation especially if you have a large database that takes many hours to recover. Start with Oracle 10g, we can use a new tool called nid to do the same thing without do resetlogs operation, plus the DB ID is not changed.

servername$nid target=sys/ dbname=newsidname setname=yes
DBNEWID: Release 11.2.0.1.0 - Production on Wed Jan 25 10:43:08 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database sidname (DBID=3903245474)
Connected to server version 11.2.0
Control Files in database:
/ora01/app/oracle/oradata/oldsidname/control01.ctl
/ora02/app/oracle/oradata/oldsidname/control02.ctl
Change database name of database oldsidname to newsidname? (Y/[N]) => Y
Proceeding with operation
Changing database name from oldsidname to newsidname
Control File /ora01/app/oracle/oradata/oldsidname/control01.ctl - modified
Control File /ora02/app/oracle/oradata/oldsidname/control02.ctl - modified
Datafile /ora01/app/oracle/oradata/oldsidname/system01.db - wrote new name
Datafile /ora01/app/oracle/oradata/oldsidname/sysaux01.db - wrote new name
Datafile /ora01/app/oracle/oradata/oldsidname/undotbs01.db - wrote new name
Datafile /ora02/app/oracle/oradata/oldsidname/users01.db - wrote new name
Datafile /ora01/app/oracle/oradata/oldsidname/temp01.db - wrote new name
Control File /ora01/app/oracle/oradata/oldsidname/control01.ctl - wrote new name
Control File /ora02/app/oracle/oradata/oldsidname/control02.ctl - wrote new name
Instance shut down
Database name changed to newsidname.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.