alter table & multi-master replication

[khussain]

I need to add a few columns to a multi-master replicated object. This is what I plan to do:

(1) Suspend Master Group
(2) Alter object using dbms_repcat pkg --- Question here
(3) Generate Replication support
(4) Make sure object was modified
(5) Resume Master activity

Questions:
(1) Do I have to suspend the activity at both the Master sites ?
(2) Will the DDL get replicated ?
(3) Can I also add column comments "COMMENT ON COLUMN" in the DDL section ?

Thanks.

[gopi]

(1) Do I have to suspend the activity at both the Master sites ?

any site you want to change ddl

(2) Will the DDL get replicated ?

not through replication itself...DML only gets replicated.

(3) Can I also add column comments "COMMENT ON COLUMN" in the DDL section ?

If it's valid DDL, why not?

[gbrabham]

Suspend replication at the master def site ...
BEGIN
DBMS_DEFER_SYS.SET_DISABLED ('masterdef_name',TRUE);
END;
/
commit;

DDL will get replicated ...

BEGIN
DBMS_REPCAT.EXECUTE_DDL (
GNAME => 'CODES',
MASTER_LIST => 'masterdef_site,other_mastersite',
DDL_TEXT => 'alter table mis.QUALITY ADD (
SHOW_IN_INVY CHAR(1),
GOOD_PROD_FOR_SHOP CHAR(1),
TRANSFER CHAR(1),
ALLOW_WASTING CHAR(1))');
END;
/

[khussain]

After doing what I had planned, here is the result, for the benefit of other:

(1) Do I have to suspend the activity at both the Master sites ?
-- I only did a suspend on the Mater Defenition site for that Master Group; that Quiescied the replication process on both the Master sites

(2) Will the DDL get replicated ?
-- Yes, the DDL (alter table add) did get replicated to the other Master site

[gopi]

I apologize for the wrong (or outdated) info in #2. That's one of the great things about this forum - there are plenty of people to correct mistakes.

Now, I'm still wonderin' if DDL propogation was available in 8.0.5? That is the version that I based my answer on when I worked with A/R. I recall, stopping everything and manually doing the DDL changes. This does not mean that DDL propergation wasn't available back then too. If anyone knows, please share.

sorry again!

[grjohnson]

My article I wrote a while back may help...


http://www.dbasupport.com/oracle/ora9i/mmradd.shtml

[khussain]

Thanks for the link George... that is exactly what I did.

Do you know if I can add column comments without "suspending" replication ?

[adewri]

If you wan to do that try Oracle Streams, it can propogate both dmls as well as ddls.