Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** mv versus advanced replication

Re: ** mv versus advanced replication

From: Mark Strickland <>
Date: Tue, 5 Sep 2006 12:03:00 -0700
Message-ID: <>

Even with the issues that we are encountering with Logical Standby, I can still recommend (with a straight face) 10g Logical Standby for ease of setup and administration and for operational reliability. We replicate a 160-Gb OLTP database but it's not a very busy database. The issues that we have aren't with day-to-day operations of Logical Standby, but rather, with large DML operations. Specifically, a large DML operation in the primary database is converted into individual row-level DML statements in the logical standby. Our testing indicates that any DML that impacts over 100,000 rows in a single transaction will slow down linearly (or is that exponentially?) in the logical standby. For example, an update to a column in all of the rows in a million-row table in the primary database will take about 3 minutes but, in the logical standby, it will take 5 hours for the resulting million update statements to complete. As SQL Apply munches through the statements, it gets slower and slower and slower... We have an open SR with Oracle Support (I have a difficult time putting those two words together with a straight face) and there was supposedly a bug fix for, but we tested it in and it didn't fix the problem. So, the workaround until there is a verifiable fix is either to break apart large DML operations into 100,000 row chunks with a commit after each chunk or to skip the table during the DML operation, then re-instantiate it afterward. Given that it's an OLTP database we don't often have large DML operations, really, only when a new column is added to a large table and the column has to be set immediately to a non-null value.

The other issue we have with Logical Standby is related to Change Data Capture. In our logical standby, we need the ability to capture before- and after-images of row changes in selected tables and we want to use Change Data Capture to accomplish that. Turns out that CDC doesn't work in but does work in We're upgrading to in Production in October so that issue is expected to resolve itself.

We don't plan to upgrade to 10gR2 until there is at least a patchset available. We believe that Oracle releases become generally stable by the 4th patchset.

We use Data Guard Broker for managing both our physical standby and logical standby. I recommend it.

Mark Strickland
Seattle, WA, USA

Received on Tue Sep 05 2006 - 14:03:00 CDT

Original text of this message