MultiMaster Replication | Alter objects into Existing Mutimaster Group

When you want to alter a table structure you should use QUIESCE the group and use following API to replicate DDL changes at all sites.

BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
sname => ‘SCOTT’,
oname => ‘EMP’,
type => ‘TABLE’,
ddl_text => ‘ALTER TABLE SCOTT.EMP ADD (STATUS VARCHAR2(10))’);
END;

I wanted to observe what Oracle does when we push changes without using above API

repa> ALTER TABLE SCOTT.EMP ADD (STATUS VARCHAR2(10) );
Table altered.

repa> update EMP set STATUS=’ACTIVE’ where EMPNO=7369;

repa>COMMIT:

Strange enough but no errors returned and nothing was replicated at REPB site.

repA> SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;

COUNT(*)
———-
0
repA>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y NORMAL

I then inserted a new ROW into EMP at repA site

repA> Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, DEPTNO, STATUS)
Values (1000, ‘SAGAR’, ‘CONSULTAN’, 7369, TO_DATE(’09/19/2008 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), 20, ‘ACTIVE’);
repa>COMMIT;

On this occassion I could see a record being replicated to site repB leaving column STATUS which was added at repA site.

Do U get the point? Oracle didn’t throw any error message at all for missing STATUS column at Site B but went ahead and only copied all data except STATUS Column.

How do we fix this Problem?

.repA>SELECT GNAME, STATUS FROM DBA_REPGROUP;

GNAME STATUS
—————————— ———
SCOTT_REPG NORMAL

SQL> BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (gname => ‘scott_repg’);
END;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

repA> SELECT GNAME, STATUS FROM DBA_REPGROUP;

GNAME STATUS
—————————— ———
SCOTT_REPG QUIESCED

repB> SELECT GNAME, STATUS FROM DBA_REPGROUP;

GNAME STATUS
—————————— ———
SCOTT_REPG QUIESCED

repA>BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
sname => ‘SCOTT’,
oname => ‘EMP’,
type => ‘TABLE’,
ddl_text => ‘ALTER TABLE SCOTT.EMP ADD (STATUS VARCHAR2(10))’); 2
END; 3 4 5 6 7
8 /
BEGIN
*
ERROR at line 1:
ORA-23318: a ddl failure has occurred
ORA-06512: at “SYS.DBMS_REPCAT_MAS”, line 679
ORA-06512: at “SYS.DBMS_REPCAT”, line 344
ORA-06512: at line 2

But this Statement did add a new Column STATUS at repB. I thought now changes at repA.EMP should be replicated. Next I tried genrating a replication support for EMP which returned following error

.repA>BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘scott’,
oname => ’emp’,
type => ‘TABLE’,
min_communication => TRUE); 2
END;
/
3 4 5 6 7 8 BEGIN
*
ERROR at line 1:
ORA-23308: object SCOTT.EMP does not exist or is invalid
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.DBMS_REPCAT_MAS”, line 2906
ORA-06512: at “SYS.DBMS_REPCAT”, line 766
ORA-06512: at line 2

repA> select role,source,request,message from DBA_REPCATLOG;

ROLE SOURCE REQUEST MESSAGE
MASTERDEF REPA.US.ORACLE.COM ALTER_MASTER_REPOBJECT ORA-01430: column being added already exists in table

repB>desc scott.emp;
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
STATUS VARCHAR2(10)

repA>update EMP set STATUS=’ACTIVE’ where EMPNO= 7369;

repA>update EMP set STATUS=’ACTIVE’ where EMPNO= 7499;

At this moment I was thinking that I may see updates at STATUS COLUMN from repA but I couldn’t.

repB>select STATUS from scott.emp;

STATUS
———-

repA>select STATUS from scott.emp;

STATUS
———-
ACTIVE

ACTIVE

So how did I fixed it ?

Drop replication support for EMP table
repA>execute dbms_repcat.drop_master_repobject(‘SCOTT’,’EMP’,’TABLE’);

Create replication object with COPY_ROWS=TRUE if you want to COPY data from repA to repB.

repA>BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘scott_repg’,
type => ‘TABLE’,
oname => ‘EMP’,
sname => ‘scott’,
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

Generate replication Suport for an EMP
repA>BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘scott’,
oname => ’emp’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

Start Replication & monitor the changes

repA>BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘scott_repg’);
END;
/

repA>select STATUS from scott.emp;

Status
—————
ACTIVE
ACTIVE

ACTIVE
ACTIVE

repB>select STATUS from scott.emp;

STATUS
———-
ACTIVE
ACTIVE

ACTIVE
ACTIVE