RE: Is it possible to reorganize tables for logical standby while maintain the synchronization?

  • From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
  • To: <jkstill@xxxxxxxxx>, <wqhhp@xxxxxxxxx>
  • Date: Wed, 28 Jun 2006 11:08:21 -0500

A Primary Key (and/or a Unique Key) is also required for a Logical
Standby.  So either way you will need to have a way to uniquely identify
each row. 


Ric Van Dyke

Hotsos Enterprises

Cell 248-705-0624


Hotsos Symposium March 4-8, 2007.  Be there.


From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
Sent: Wednesday, June 28, 2006 11:50 AM
To: wqhhp@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Is it possible to reorganize tables for logical standby
while maintain the synchronization?


An alternative would be to use DBMS_REDEFINITION to recreate tables in a
new tablespace.
The downtime to switch to the new table is minimal.  

A Caveat here:I haven't actually used DBMS_REDEFINITION for this.  I
have done it manually 
using  materialized views on prebuilt tables,  which I believe is the
mechanism underlying

DBMS_REDEFINITION was buggy on the Oracle version I had to do this on,
hence the manual
process.  Great learning experience. :) 

There is one requirement for doing this:  Your table must either have a
primary key, or it must
have column(s) upon which you can temorarily build a PK.  Some systems
(such as SAP) do
not use PK in the database, but they can be easily added and dropped. 


On 6/27/06, qihua wu <wqhhp@xxxxxxxxx> wrote:

Our production database is of version 9.2 but still using DMT because it
is upgraded from 8174. And we want to move the table to a LMT
tablespace, althougth we can use some oracle package to convert DMT to
LMT, but it's not perfect as TOM pointed. We can reorg these tables
while the database is online, but the performance will be adversely


So is it possible to create a logical standy based on the production
database, and then reorg the logical standby, after the reorg is done,
then switch the role of the standby to primary database? As far as I
know, the logical standy use sql appying instead of redo applying, so
the transaction should be able to applied to logical standby while the
reorg is under going. 


Sneak preview the all-new
<*http:/> . It's
not radically different. Just radically better. 

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Other related posts: