Re: ** mv versus advanced replication
- From: "Mark Strickland" <strickland.mark@xxxxxxxxx>
- To: ajoshi977@xxxxxxxxx
- Date: Tue, 5 Sep 2006 12:03:00 -0700
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 10.2.0.2, but we tested it in 10.2.0.2 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 10.1.0.3 but does work in 10.1.0.5. We're upgrading
to 10.1.0.5 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 10.2.0.4
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.
HTH,
Mark Strickland
Seattle, WA, USA
--
//www.freelists.org/webpage/oracle-l
Other related posts:
