Applying DDL on replicated tables - A Test Case

Database DBTESTA and DBTESTB are configured with Oracle Advanced Replication. This test shows that we can not apply DDL directly to the replcated tables from SQL Plus command line.

(1) RG_MYGROUP is a replication group, including two replicated tables: tab1 and tab2

repadmin@DBTESTA> select sname, oname, status from dba_repobject where
gname='RG_MYGROUP';

SNAME ONAME STATUS
------------------------------ ------------------------------ ---------
MYSCHA TAB1 VALID
MYSCHA TAB1$RP VALID
MYSCHA TAB1$RP VALID
MYSCHA TAB2 VALID
MYSCHA TAB2$RP VALID
MYSCHA TAB2$RP VALID

(2) DML operation works:

************** DML at DBTESTA ************
myscha@DBTESTA> select * from tab2;

no rows selected

myscha@DBTESTB> select * from tab2;

no rows selected

myscha@DBTESTA> insert into tab2 values(1,2);

1 row created.

myscha@DBTESTA> commit;

Commit complete.

myscha@DBTESTA> select * from tab2;

A B
---------- ----------
1 2

**************** What happend at DBTESTB **********
myscha@DBTESTB> select * from tab2;

A B
---------- ----------
1 2

Note: replication works properly !

(3) Applying DDL through SQL Plus is NOT good for the replication

************** DDL at DBTESTA and DBTESTB *******

myscha@DBTESTA> alter table tab2 add (c number);

Table altered.

myscha@DBTESTA> select * from tab2;

A B C
---------- ---------- ----------
1 2
myscha@DBTESTB> alter table tab2 add (c number);

Table altered.

myscha@DBTESTB> select * from tab2;

A B C
---------- ---------- ----------
1 2

*************After applying DDL, DML at DBTESTA *******

myscha@DBTESTA> insert into tab2 values(2,3,4);

1 row created.

myscha@DBTESTA> commit;

Commit complete.

myscha@DBTESTA> select * from tab2;

A B C
---------- ---------- ----------
1 2
2 3 4

myscha@DBTESTA> insert into tab2 values(3,4,5);

1 row created.

myscha@DBTESTA> commit;

Commit complete.

myscha@DBTESTA> select * from tab2;

A B C
---------- ---------- ----------
1 2
2 3 4
3 4 5

************* what happened at DBTESTB ? **************

myscha@DBTESTB> select * from tab2;

A B C
---------- ---------- ----------
1 2
2 3


myscha@DBTESTB> select * from tab2;

A B C
---------- ---------- ----------
1 2
2 3
3 4

Note: replication does not work for colume C !!!!!


(4) what's the solution?

a. drop the tab2 from the RG_MYGROUP
repadmin@DBTESTA> @dr_ro

PL/SQL procedure successfully completed.

repadmin@DBTESTA> select sname, oname, status from dba_repobject where
gname='RG_MYGROUP';

SNAME ONAME STATUS
------------------------------ ------------------------------ ---------
MYSCHA TAB1 VALID
MYSCHA TAB1$RP VALID
MYSCHA TAB1$RP VALID

b. apply ddl to it at both DBTESTA and DBTESTB
alter table tab2 add (d number);

c. add tab2 to the RG_MYGROUP
resume the replicaiton

d. verify it works
*** befor apply DML

myscha@DBTESTA> select * from tab2;

A B C D
---------- ---------- ---------- ----------
1 2
2 3 4
3 4 5

myscha@DBTESTB> select * from tab2;

A B C D
---------- ---------- ---------- ----------
1 2
2 3
3 4

**** after DML
myscha@DBTESTA> insert into tab2 values(4,5,6,7);

1 row created.

myscha@DBTESTA> commit;

Commit complete.

myscha@DBTESTA> select * from tab2;

A B C D
---------- ---------- ---------- ----------
1 2
2 3 4
3 4 5
4 5 6 7

myscha@DBTESTB> select * from tab2;

A B C D
---------- ---------- ---------- ----------
1 2
2 3
3 4
4 5 6 7