alter table & multi-master replication
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: alter table & multi-master replication

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    alter table & multi-master replication

    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.

  2. #2
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510

    Re: alter table & multi-master replication

    (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?

  3. #3
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    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;
    /

  4. #4
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    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

  5. #5
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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!

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    My article I wrote a while back may help...


    http://www.dbasupport.com/oracle/ora9i/mmradd.shtml
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    Thanks for the link George... that is exactly what I did.

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

  8. #8
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    If you wan to do that try Oracle Streams, it can propogate both dmls as well as ddls.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width