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.


Monday, September 06, 2010

Undocumented 11g new features of standby database

Apart from the documented new features of being able to use BCT (block change tracking) on the physical standby, ability to use rman duplicate command over the sqlnet to build physical standby from the primary database, here are some of the undocumented features (or change in behavior) introduced as of

1) Ability to defer log boundary checkpoints ("_defer_log_boundary_ckpt")
2) Ability to define number of logs checkpoint can get behind ("_defer_log_count")
3) Media recovery process now reads maximum of 1024 pending asynch block reads
4) DBWR only do the writes for incremental checkpoint unless free buffer is requested
5) v$recovery_progress view is updated even when only few redo blocks applied by the MRP
6) Removed v$standby_apply_snapshot view.
7) More smoothened incremental recovery checkpoint writes

The best feature among the above is the ability to defer checkpoints happen at the log boundary.

Parameters "_defer_log_boundary_ckpt" set to "TRUE" by default and "_defer_log_count" set to 2 by default will automatically get in to play only when the physical standby is actively applying the logs instead of waiting for redo or logs to arrive in managed recovery mode.

During my tests, changing "_defer_log_count" to different values didn't work, Oracle seems to use value 2 only (more testing is needed).

If standby ever falls behind or catching up, it doesn't have to wait for the mandatory checkpoint at the log boundary to finish before applying the next log, which is a big deal if the underlying storage system can only support limited iops.

Media recovery process on the standby will read the data blocks in parallel, Oracle used "db file parallel read" wait event for this until 9i, starting 10g they changed it to "recovery read" wait event.

In 10g, MRP process can keep issuing asynch data block read requests in batches until the overall outstanding block reads for a given MRP process reach 4096. This has caused a havoc in our 10g standbys, as this huge burst of I/O caused underlying SAN to suffer even worse (finally causing corruptions). They have done it to improve the performance of redo apply.

Oracle silently changed it to read 1024 outstanding asynch block reads in 11g.

In order to even out the write activity, dbwr seems to be doing incremental recovery checkpoint writes only, unless dbwr is asked to free up buffers, (you can verify it by setting "_disable_incremental_recovery_ckpt" to true ). By default dbwr is writing once the checkpoint batch size reach 500 as defined by the parameter "_incremental_recovery_ckpt_min_batch".

These are great improvements to the performance of physical standby apply process, but it'd be even better if we can control how many logs checkpoint can defer by so that we can do one big mandatory checkpoint for every few logs and have a big enough sga to control the physical reads, this will improve the performance of standby apply process and reduce the overall I/O.

Only downside to this is, if standby crashes, it'd have to apply all the logs since the last checkpoint. But physical standby for RAC databases will greatly benefit from it especially when you have many nodes.

I've opened enhancement request 9943911 for the above request.

Overall, these are great improvements Oracle has introduced in 11g.

Labels: , , , ,

Saturday, September 04, 2010

Beware of data loss in BCT based RMAN backups

Block change tracking feature introduced in Oracle 10g is intended for RMAN incremental backups to directly go after the changed data blocks. It can even be used on active data guard standby in 11g.

Alex Gorbachev has written excellent white paper about BCT, it is at

By default BCT maintains 8 bitmap versions for a given datafile in the change tracking file, background process ctwr process is responsible for maintaining BCT bitmap file.

BCT background process ctwr is hooked up with the redo apply mechanism, as user process executes any transaction on the primary database (internally Oracle will apply the generated redo to the data blocks for making changes to the data blocks) and as media recovery process applies the redo on the standby database.

One of the most popular database backup method is to run rman incremental level 0 image copy of all datafiles, followed by periodic incremental level 1 backups which can be merged with the previous level 0 backup for rolling full backup of the database. BCT is very useful for this purpose.

So BCT is a good feature and it has very light overhead on primary or the standby database and it has been out there for few years now with many customers using it; are there any issues with using this feature?

There is a possibility of data loss in rman incremantal backups based on BCT.

Data loss scenario 1:
BCT works on the physical standby only when managed recovery is in use and of course active data guard license is needed.

Enable BCT on the standby.
Create a test table on the primary and insert one record.
Identify which datafile the test table belongs to.
Use standby managed recovery to bring it current with the primary.
Stop the managed recovery on the standby.
Take rman incremental backup (backup incremental level 1 for recover of copy with tag 'bct1' tablespace test_tbs)
Insert second row in test table on the primary and switch the logfile.
Apply new logs on the standby using traditional recovery (i.e. recover standby database)
Run the rman incremental backup again with the same command as above
Merge the rman incremental backup with the first image copy (i.e. recover copy of datafile 5 with tab 'bct1')
Offline drop the datafile having the test table on the standby and rename it to the rman backup copy of that datafile.
Now open the standby in read only mode and select from the test table. Second row will be missing.
Bug# 10094823 was opened for this. It is now fixed and the patch is available for

Data loss scenario 2:
If you ever have to refresh a datafile on the standby with later checkpoint time (SCN) and you run the rman incremental backup right after that before bringing rest of the datafiles on the standby to the consistent checkpoint time with the recently refreshed datafile, then there will be a data loss in the just refreshed datafile if the same backup is restored (and rename the just refresh datafile to the rman backup copy) before running the next rman incremental.

This problem can get automatically corrected in the next rman incremental but only if you use the latest backup.

Data loss scenario 3:
Offline drop a datafile on the standby
Create a test table on production. Make sure that the table extents are in the above datafile.
Copy the datafile from production with later checkpoint time and leave the datafile in offline status.
Apply few logs on the standby using managed recovery mode.
Online the datafile.
Apply logs on the standby using managed recovery until standby is caught up with live.
Take rman incremental backup of that datafile
Update incremental backup copy with "recover copy of datafile with tag" command
Rename datafile to the rman backup copy
Apply few more logs and open the standby in read only mode
You will now see the test table data missing in that datafile

Reliability of BCT:
On standby, I've seen managed standby recovery failing to start until BCT is reset at least while running the above tests. I'm working with Oracle support to get all these issues fixed.

As of, make sure to get the above mentioned issues addressed before using BCT on the standby.

Update (09/23/2010):
I've opened an enhancement request for Oracle support to implement the following features in BCT:

1) Ability to enable/disable BCT at datafile level.
2) Make rman to check checkpoint_time seen from BCT for a given datafile with the actual checkpoint_time from the datafile header and use BCT only if both the checkpoint_time's match.

Labels: , , ,