Oracle Bi-Directional Data Replication - Page 2

April 13, 2006

Step 10: Create Streams queues

As the Streams administrator, create the capture and apply queues on ladb and sfdb.

On ladb

begin
dbms_streams_adm.set_up_queue(
  queue_table => 'apply_laqtab',
  queue_name  => 'apply_laq',
  queue_user  => 'strmadmin');
end;
/
begin
dbms_streams_adm.set_up_queue(
  queue_table => 'capture_laqtab',
  queue_name  => 'capture_laq',
  queue_user  => 'strmadmin');
end;
/

On sfdb

begin
dbms_streams_adm.set_up_queue(
  queue_table => 'apply_sfqtab',
  queue_name  => 'apply_sfq',
  queue_user  => 'strmadmin');
end;
/
begin
dbms_streams_adm.set_up_queue(
  queue_table => 'capture_sfqtab',
  queue_name  => 'capture_sfq',
  queue_user  => 'strmadmin');
end;
/

Step 11: Configure capture process on ladb

Rules are added in positive or negative rule sets using the dbms_streams_adm package. Changes are captured using positive rule set by specifying a true value for the inclusion_rule parameter.

Create and configure the capture process on ladb.

connect strmadmin/strmadmin@ladb.world
begin
dbms_streams_adm.add_schema_rules (
schema_name  => 'apps',
  streams_type => 'capture',
  streams_name => 'capture_lastrm',
  queue_name  => 'capture_laq',
  include_dml => true,
  include_ddl => true,
  inclusion_rule => true);
end;
/

Step 12: Configure apply process on ladb

Create and configure the apply process on ladb and add rules to the positive rule set. The apply process dequeues the LCR events from sfdb and applies the changes to the application schema.

connect strmadmin/strmadmin@ladb.world
begin
dbms_streams_adm.add_schema_rules (
schema_name  => 'apps',
  streams_type => 'apply',
  streams_name => 'apply_src_sfdb',
  queue_name  => 'apply_laq',
  include_dml => true,
  include_ddl => true,
  source_database => 'sfdb.world');
end;
/

Step 13: Configure conflict resolution on ladb

Set up update conflict handler on ladb to discard changes from sfdb during conflict resolution. For the discard and overwrite handlers, the resolution_column parameter is not used to resolve conflicts. Any column from the column_list can be passed to the resolution_column. For the minimum and maximum handlers, the resolution_column is used to determine if the row LCR should be discarded or applied.

The discard 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.

ora-23460:

// *Cause: before resolving conflicts, some values necessary resolving
//         conflicts are not available,
//         or after resolving conflicts, some values necessary for
//         re-trying of the SQL are not available
connect strmadmin/strmadmin@ladb.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            =>  'discard',
      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            =>  'discard',
      resolution_column      =>  'site',  
      column_list            =>  field);
end;
/

Step 14: Configure propagation process on ladb

The configuration for the propagation process is similar to the capture and apply process. We add rules to the positive rule set and specify the source queue name and destination queue name. Changes are propagated from the source queue to the destination queue.

connect strmadmin/strmadmin@ladb.world

connect strmadmin/strmadmin@ladb.world
begin
dbms_streams_adm.add_schema_propagation_rules (
  schema_name   => 'apps',
  streams_name   => 'prop_ladb_to_sfdb',
  source_queue_name  => 'capture_laq',
  destination_queue_name  => 'apply_sfq@sfdb.world',
  include_dml   => true,
  include_ddl   => true,
  source_database  => 'ladb.world');
end;
/

Step 15: Configure capture process on sfdb

Similarly, create and configure the capture process on sfdb.

connect strmadmin/strmadmin@sfdb.world
begin
 dbms_streams_adm.add_schema_rules (
  schema_name  => 'apps',
  streams_type  => 'capture',
  streams_name  => 'capture_sfstrm',
  queue_name  => 'capture_sfq',
  include_dml  => true,
  include_ddl  => true);
end;
/

Step 16: Set the schema instantiation SCN on ladb

Setting the schema instantiation SCN for sfdb at ladb ensures that only changes after the instantiation SCN from sfdb are applied at ladb.

connect strmadmin/strmadmin@sfdb.world
declare
 v_scn number;
begin
 v_scn := dbms_flashback.get_system_change_number();
 dbms_apply_adm.set_schema_instantiation_scn@ladb.world (
 source_schema_name => 'apps',
 source_database_name => 'sfdb.world',
 instantiation_scn => v_scn,
 recursive             => true);    
end;
/

Step 17: Configure apply process on sfdb

Create and configure the apply process on sfdb and add rules to the positive rule set. The apply process dequeues the LCR events from ladb and applies the changes to the application schema.

connect strmadmin/strmadmin@sfdb.world
begin
 dbms_streams_adm.add_schema_rules (
  schema_name  => 'apps',
  streams_type  => 'apply',
  streams_name  => 'apply_src_ladb',
  queue_name  => 'apply_sfq',
  include_dml  => true,
  include_ddl  => true,
  source_database => 'ladb.world');
end;
/

Step 18: Configure propagation process on sfdb

Configure the propagation process to propagate changes from sfdb to ladb.

connect strmadmin/strmadmin@sfdb.world
begin
 dbms_streams_adm.add_schema_propagation_rules (
  schema_name  => 'apps',
  streams_name  => 'prop_sfdb_to_ladb',
  source_queue_name => 'capture_sfq',
  destination_queue_name => 'apply_laq@ladb.world',
  include_dml  => true,
  include_ddl  => true,
  source_database => 'sfdb.world');
end;
/

Step 19: Export application schema on ladb

On trout3, create or use an existing directory on the filesystem to store the export dump file.

$> mkdir -p /u01/app/oracle/admin/ladb/export

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

create directory expdir as '/u01/app/oracle/admin/ladb/export';

Obtain the current SCN on ladb:

select dbms_flashback.get_system_change_number() from dual;

Export the application schema using the current SCN obtained above:

$> expdp system/system  schemas=apps  directory=expdir \
    logfile=expapps.log dumpfile=apps.dmp FLASHBACK_SCN=<current SCN>