Oracle Bi-Directional Data Replication - Page 3

Step 20: Import application schema on sfdb

On trout4, create or use an existing directory on the filesystem to store the export dump file from ladb. Transfer the dump file from trout3 to this directory.

$> mkdir -p /u01/app/oracle/admin/sfdb/import

As the sys user on sfdb, create an Oracle directory:

create directory impdir as 
  '/u01/app/oracle/admin/sfdb/import';

Import the application schema from ladb:

impdp system/system directory=impdir dumpfile=
  apps.dmp logfile=impapps.log

The supplemental log groups are imported together with the rest of the application schema’s objects.

SQL> connect apps/apps@sfdb.world
SQL> select log_group_name, table_name
  2  from dba_log_groups
  3  where owner='APPS';
LOG_GROUP_NAME                 TABLE_NAME
------------------------------ ---------------
LOGGRP_EMP                     EMP
LOGGRP_DEPT                    DEPT

Step 21: Configure conflict resolution on sfdb

Set up an update conflict handler to overwrite changes on sfdb with ladb’s during conflict resolution.

The overwrite handler is called when an update conflict occurs to at least one of the columns in the column_list. You may encounter ora-23460 if the column specified in the column_list is not in the supplemental log group. To rectify the issue, simply add the missing column to a new supplemental log group.

connect strmadmin/strmadmin@sfdb.world
declare
  field  dbms_utility.name_array;
begin
  field(1) := 'ename';
  field(2) := 'site';
  field(3) := 'job';
  field(4) := 'deptno';
 
  dbms_apply_adm.set_update_conflict_handler(
    object_name =>  'apps.emp',
    method_name =>  'overwrite',
    resolution_column =>  'site',  
    column_list =>  field);
end;
/
declare
  field  dbms_utility.name_array;
begin
  field(1) := 'dname';
  field(2) := 'site';
 
  dbms_apply_adm.set_update_conflict_handler(
    object_name =>  'apps.dept',
    method_name =>  'overwrite',
    resolution_column =>  'site',  
    column_list =>  field);
end;
/

Step 22: Start capture and apply processes on sfdb

Setting the disable_on_error parameter to ‘N’ allows the apply process to continue applying row LCRs even when it encounters errors. The default value is ‘Y’ which disables the apply process automatically on the first error encountered.

connect strmadmin/strmadmin@sfdb.world
begin
 dbms_apply_adm.set_parameter (
  apply_name => 'apply_src_ladb',
  parameter => 'disable_on_error',
  value => 'N');
end;
/
begin
 dbms_apply_adm.start_apply (
  apply_name => 'apply_src_ladb');
end;
/
begin
 dbms_capture_adm.start_capture (
  capture_name => 'capture_sfstrm');
end;
/

Step 23: Start capture and apply processes on ladb

connect strmadmin/strmadmin@ladb.world
begin
 dbms_apply_adm.set_parameter (
  apply_name => 'apply_src_sfdb',
  parameter => 'disable_on_error',
  value => 'N');
end;
/
begin
 dbms_apply_adm.start_apply (
  apply_name => 'apply_src_sfdb');
end;
/
begin
 dbms_capture_adm.start_capture (
  capture_name => 'capture_lastrm');
end;
/

Congratulations, you have set up a bi-directional replicated environment!

Step 24: It’s time to test drive …

We will replicate DMLs and DDLs between ladb and sfdb and test the discard and overwrite conflict handlers by simulating update conflicts.

SQL> connect apps/apps@ladb.world
SQL> select * from emp;
     EMPNO ENAME                SITE       JOB            DEPTNO
---------- -------------------- ---------- ---------- ----------
         1 MIKE                 LADB       CLERK               1
         6 JANE                 LADB       ANALYST             1
        11 ZACH                 LADB       ENGINEER           11
SQL> select * from dept;
    DEPTNO DNAME                SITE
---------- -------------------- ----------
         1 FINANCE              LADB
         6 HR                   LADB
        11 RESEARCH             LADB
SQL> connect apps/apps@sfdb.world
SQL> select * from emp;
     EMPNO ENAME                SITE       JOB            DEPTNO
---------- -------------------- ---------- ---------- ----------
         1 MIKE                 LADB       CLERK               1
         6 JANE                 LADB       ANALYST             1
        11 ZACH                 LADB       ENGINEER           11
SQL> select * from dept;
    DEPTNO DNAME                SITE
---------- -------------------- ----------
         1 FINANCE              LADB
         6 HR                   LADB
        11 RESEARCH             LADB

Test DML

SQL> connect apps/apps@ladb.world
SQL> insert into emp (empno,ename,job,deptno) values (empno_laseq.nextval,'SALLY','DEVELOPER',11);
1 row created.
SQL> commit;
Commit complete.
SQL> connect apps/apps@sfdb.world
SQL> select * from emp;
     EMPNO ENAME                SITE       JOB            DEPTNO
---------- -------------------- ---------- ---------- ----------
         1 MIKE                 LADB       CLERK               1
         6 JANE                 LADB       ANALYST             1
        11 ZACH                 LADB       ENGINEER           11
       101 SALLY                LADB       DEVELOPER          11
SQL> insert into emp (empno,ename,job,deptno) values (empno_sfseq.nextval,'AMY','ACCOUNTANT',1);
1 row created.
SQL> commit;
Commit complete.
SQL> connect apps/apps@ladb.world
Connected.
SQL> select * from emp;
     EMPNO ENAME                SITE       JOB            DEPTNO
---------- -------------------- ---------- ---------- ----------
         1 MIKE                 LADB       CLERK               1
         6 JANE                 LADB       ANALYST             1
        11 ZACH                 LADB       ENGINEER           11
       101 SALLY                LADB       DEVELOPER          11
         2 AMY                  SFDB       ACCOUNTANT          1

Test DDL

SQL> connect apps/apps@ladb.world
SQL> alter table emp add (delete_yn varchar2(1) default 'N' not null);
Table altered.
SQL> connect apps/apps@sfdb.world
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(10)
 ENAME                                     NOT NULL VARCHAR2(20)
 SITE                                      NOT NULL VARCHAR2(10)
 JOB                                                VARCHAR2(10)
 DEPTNO                                             NUMBER(10)
 DELETE_YN                                 NOT NULL VARCHAR2(1)
SQL> select * from emp;
     EMPNO ENAME                SITE       JOB            DEPTNO D
---------- -------------------- ---------- ---------- ---------- -
         1 MIKE                 LADB       CLERK               1 N
         6 JANE                 LADB       ANALYST             1 N
        11 ZACH                 LADB       ENGINEER           11 N
       101 SALLY                LADB       DEVELOPER          11 N
         2 AMY                  SFDB       ACCOUNTANT          1 N
SQL> alter table emp add constraint delete_yn_chk check (delete_yn in ('Y','N'));
Table altered.
SQL> connect apps/apps@ladb.world
SQL> select constraint_name,constraint_type,table_name,search_condition 
 from user_constraints where constraint_name='DELETE_YN_CHK';
CONSTRAINT_NAME           C TABLE_NAME      SEARCH_CONDITION
------------------------- - --------------- -------------------------
DELETE_YN_CHK             C EMP             delete_yn in ('Y','N')

Test Update Conflict Resolution – Discard handler at ladb

To test the update conflict handlers, we will create conflicts on the dname column for deptno=6 in both ladb and sfdb. By setting the Streams tag to a value other than the default NULL at ladb, we instruct the capture process not to capture the changes in the redo logs. In this case, the updated SALES value was not applied at sfdb.

SQL> connect apps/apps@ladb.world
SQL> exec dbms_streams.set_tag(hextoraw('1'));
PL/SQL procedure successfully completed.
SQL> select dbms_streams.get_tag from dual;
GET_TAG
---------------------------------------------------------------
01
SQL> update dept set dname='SALES' where dname='HR';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from dept;
    DEPTNO DNAME                SITE
---------- -------------------- ----------
         1 FINANCE              LADB
         6 SALES                LADB
        11 RESEARCH             LADB
SQL> connect apps/apps@sfdb.world
SQL> select * from dept;
    DEPTNO DNAME                SITE
---------- -------------------- ----------
         1 FINANCE              LADB
         6 HR                   LADB
        11 RESEARCH             LADB
SQL> update dept set dname='PARTS' where dname='HR';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from dept;
    DEPTNO DNAME                SITE
---------- -------------------- ----------
         1 FINANCE              LADB
         6 PARTS                LADB
        11 RESEARCH             LADB
SQL> connect apps/apps@ladb.world
SQL> select * from dept;
    DEPTNO DNAME                SITE
---------- -------------------- ----------
         1 FINANCE              LADB
         6 SALES                LADB
        11 RESEARCH             LADB

The discard handler was invoked in ladb and the conflict was resolved by discarding the row LCR from sfdb.

Test Update Conflict Resolution – Overwrite handler at ladb

SQL> connect apps/apps@ladb.world
SQL> update dept set dname='HR' where dname='SALES';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from dept;
    DEPTNO DNAME                SITE
---------- -------------------- ----------
         1 FINANCE              LADB
         6 HR                   LADB
        11 RESEARCH             LADB
SQL> connect apps/apps@sfdb.world
SQL> select * from dept;
    DEPTNO DNAME                SITE
---------- -------------------- ----------
         1 FINANCE              LADB
         6 HR                   LADB
        11 RESEARCH             LADB

The overwrite handler was invoked in sfdb and the conflict was resolved by overwriting PARTS with HR.

Conclusion

The procedure of setting up bi-directional data replication is relatively simple, however, without defining the best conflict resolution strategy and a well-thought out system design, you may find yourself spending long hours working on resolving conflicts and synchronizing data. Though setting up conflict resolution is optional in a bi-directional or multi-directional replicated environment, you should always have one in place to handle most if not all the possible conflicts.

» See All Articles by Columnist Vincent Chan