Adding Columns in a Synchronous Multi-Master Replicated Environment


Note: Tested on Oracle 8.1.7.3 Sun o/s 7

The following is a guide for adding a column to a table that is currently in a synchronous multi-master replicated environment. This guide assumes that the replicated environment has already been created.

The current replicated table has the following details and belongs to the master group REP_TEST_SETS.

CREATE TABLE TEST_SETS ( 
  USER_ID               VARCHAR2 (20)  NOT NULL, 
  CONSTRAINT TEST_SETS_PK
  PRIMARY KEY ( USER_ID ) 
    USING INDEX 
     TABLESPACE INDEX_SML)
   TABLESPACE DATA_MED


The column being added has the following features:

Name: ADDED_COLUMN

Datatype: CHAR(1)

Default: ‘N’

Constraint: NOT NULL

Here are the necessary steps:

1. Suspend the required Master Group of which the table exists:

     exec DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('REP_TEST_SETS');

2. Add a new column to the table:

     exec DBMS_REPCAT.ALTER_MASTER_REPOBJECT('SCOTT', 'TEST_SETS', 'TABLE', -
     ddl_text=> 'alter table scott.test_sets add (ADDED_COLUMN CHAR(1))');

     PL/SQL procedure successfully completed.

3. Add the DEFAULT value to the column:

     exec DBMS_REPCAT.ALTER_MASTER_REPOBJECT('SCOTT','TEST_SETS','TABLE',-
     ddl_text=> 'alter table scott.test_sets modify (ADDED_COLUMN default ''N'')');

     PL/SQL procedure successfully completed.

4. Regenerate application support for the TABLE:

     exec DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (sname => 'SCOTT',oname => 'TEST_SETS', -
     type => 'TABLE', min_communication => TRUE);

5. Resume multi-master replication:

     exec DBMS_REPCAT.RESUME_MASTER_ACTIVITY('REP_TEST_SETS',true);

6. Using SQLPLUS, update the new column with the default value:

     UPDATE TEST_SETS
     SET ADDED_COLUMN = 'N';

     9 rows updated.

     COMMIT;

     Commit complete.

7. Suspend replication on the Master Group again in order to add the NOT NULL constraint:

     Exec DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('REP_TEST_SETS');

     PL/SQL procedure successfully completed

8. Add the NOT NULL Constraint:

     exec DBMS_REPCAT.ALTER_MASTER_REPOBJECT('SCOTT','TEST_SETS', 'TABLE', -
     ddl_text=> 'alter table scott.test_sets modify ADDED_COLUMN constraint TEST_SETS_AD_NN not null');

     PL/SQL procedure successfully completed.

9. Regenerate application support for the TABLE:

     exec DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(sname => 'SCOTT', oname => 'TEST_SETS', type => 'TABLE', min_communication => TRUE);

     PL/SQL procedure successfully completed.

10. Resume Multi-master replication:

     Exec DBMS_REPCAT.RESUME_MASTER_ACTIVITY('REP_TEST_SETS',true);

     PL/SQL procedure successfully completed.

The Table now will look like the following.

CREATE TABLE TEST_SETS ( 
  USER_ID               VARCHAR2 (20)  NOT NULL, 
  ADDED_COLUMN          CHAR (1)      DEFAULT 'N'
     CONSTRAINT TEST_SETS_AD_NN NOT NULL, 
  CONSTRAINT TEST_SETS_PK
  PRIMARY KEY ( USER_ID ) 
    USING INDEX 
     TABLESPACE INDEX_SML)
   TABLESPACE DATA_MED

NB: I attempted this process on a table in which I have to make three column additions. On the third column
during the update, I received the following error.

     UPDATE
     SET = 'N';

     The following error has occurred:

     ORA-01006: bind variable does not exist

     ORA-02063: preceding line from

If you get this, you’ll have to recreate the MASTER GROUP again. After you do this, you can continue to step 7.
I’m not sure what the issue is, though.

About the Author: Greg Johnson is an OCP 8i Database Administrator with four years experience currently working for Dialog Information Technology as an Oracle Database Consultant. Dialog Information Technology is one of Australia’s leading technology services organizations, trading nationally and internationally. Dialog provides a wide range of information technology services, including outsourcing and technology management, systems integration and application development, implementation, training and support. Greg can be contacted at Johnsog123@hotmail.com.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles