Re: ** Schema change on replicated env

From: A Joshi <>
Date: Sun, 20 Apr 2008 09:51:37 -0700 (PDT)
Message-ID: <>


     Thanks. I was hoping since generate support updates the $RP stored procedure : if there is some way to make manual change (again avoidable but ....). Also I will not be able to quiesce replicaition if there are deftran. So is there a way to store those. Like copy the deftran to a new table. Then delete deftran, then quiesce and do the generate support. then break the push job then resume replicaition. then insert back the saved deftran then unbreak job. Will something like this work? Thanks

Lyndon Tiu <> wrote:
  Hi Joshi,

If a new column was added to a table in-error to one side of the replication group - not through the replication API and without adding to the other nodes in the group, the next time an update/insert is made to that table, an error will result (wrong number of arguments/attributes/field error). See it through:

select * from deferror;

From my experience, this will freeze replication - affects all the other tables and your replicated database will become out of sync quickly.

The best way to fix is:

If that table with the new column has not yet been updated/inserted into since the new column was added, remove the new column the same way it was added (outside of the replication API).

Now, do not do this to the production database. First, do this to your development environment. Test it and make sure you like what you see before implementing in production.

If updates/inserts have already been made to the table in question, then I have no answer for you other than to quiesce the database and add the column properly through the replication API, then regenerate replication support, then fix any data out of sync errors, then resume replication.

Last step is to fire the person who added the column without going through the replication API.

A Joshi wrote:
> Hi,
> A column was added to a table in replication. Without doing a quisce.
> I know it is avoidable but it happened. Can you tell what are the
> implications to this and best way to recover. It is difficult to get
> downtime on the database. Thanks for help.
> */A Joshi /* wrote:
> Hi,
> I have Oracle multi master replication setup. We are doing heavy
> processing and replication is behind. I need to add a column to a
> table. Generally this is done by doing quisce. then generate
> support. Right now I cannot do that quisce. Is there alternate or
> round about way to do this? Thanks for help. Also if column is added
> on the source as it is without quisce, it will work but I do not
> know the consequences. It is small table so I can sync it up later
> too. Thanks
> ------------------------------------------------------------------------
> Be a better friend, newshound, and know-it-all with Yahoo! Mobile.
> Try it now.
> ------------------------------------------------------------------------
> Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try
> it now.
> > >

Lyndon Tiu

Be a better friend, newshound, and know-it-all with Yahoo! Mobile.  Try it now.
Received on Sun Apr 20 2008 - 11:51:37 CDT

Original text of this message