Create the item(item.sql) and inventory (inventory.sql) table and insert the sample data by running the following SQL command on both WEST and EAST databases.
SQL>@item.sql SQL>@inventory.sql
Extract exwest SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "ogg11r2") ExtTrail ./dirdat/ew UserID ogguser@ogg11r2, Password Welcome1 DDL INCLUDE MAPPED OBJNAME west.* DDLOPTIONS ADDTRANDATA StatOptions ResetReportStats ReportCount Every 10 Records GetUpdateBefores TranLogOptions ExcludeUser ogguser Table west.account GetBeforeCols(on update all); Table west.branch GetBeforeCols(on update all); Table west.customer GetBeforeCols(on update all); Table west.item getbeforecols (on update all); Table west.inventory getbeforecols (on update all);
Replicat rewest SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "ogg11r2") AssumeTargetDefs DiscardFile ./dirrpt/rewest.dsc, Purge UserID ogguser@ogg11r2, Password Welcome1 DDLERROR DEFAULT IGNORE getupdates getinserts getdeletes Map east.account,Target west.account, comparecols(on update all) resolveconflict(updaterowexists, (delta_resolution_method, usedelta, cols(account_balance)), (max_resolution_method, usemax(update_ts), cols(account_type,account_number,account_opened, account_branch_id,update_ts)), (default overwrite)); Map east.branch,Target west.branch, comparecols(on update all) resolveconflict(updaterowexists, (max_resolution_method, usemax(update_ts), cols(branch_street_type,branch_street_name, branch_street_number,branch_level,branch_city,branch_state, branch_country,branch_size,branch_opened,zip_code, branch_name,update_ts)), (default overwrite)); Map east.customer,Target west.customer, comparecols(on update all) resolveconflict(updaterowexists, (max_resolution_method, usemax(update_ts), cols(name,middle_name,surname,full_name,dob,nationality,email,update_ts)), (default overwrite)); MAP east.item, Target west.item; MAP east.inventory, Target west.inventory, SQLEXEC (id qty, query "SELECT item_quantity from west.inventory where item_id = :v_item_id AND item_size= :v_item_size AND item_color = :v_item_color", params (v_item_id = item_id, v_item_size = item_size, v_item_color = item_color)), COLMAP (item_id = item_id, item_size = item_size, item_color = item_color, item_quantity = @if(qty.item_quantity <> BEFORE.item_quantity, @compute(qty.item_quantity - (before.item_quantity -item_quantity)), item_quantity));
Extract exeast SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "dwnstr") ExtTrail ./dirdat/ee UserID ogguser@dwnstr, Password Welcome1 DDL INCLUDE MAPPED OBJNAME east.* DDLOPTIONS ADDTRANDATA StatOptions ResetReportStats ReportCount Every 10 Records GetUpdateBefores TranLogOptions ExcludeUser ogguser Table east.account GetBeforeCols(on update all); Table east.branch GetBeforeCols(on update all); Table east.customer GetBeforeCols(on update all); Table east.item getbeforecols(on update all); Table east.inventory getbeforecols(on update all);
Replicat reeast AssumeTargetDefs DiscardFile ./dirrpt/reeast.dsc, Purge UserID ogguser@dwnstr, Password Welcome1 DDLERROR DEFAULT IGNORE GetUpdates GetInserts GetDeletes Map west.account, Target east.account, comparecols(on update all) resolveconflict(updaterowexists, (delta_resolution_method, usedelta, cols(account_balance)), (max_resolution_method, usemax(update_ts), cols(account_type,account_number,account_opened, account_branch_id,update_ts)), (default overwrite)); Map west.branch, Target east.branch, comparecols(on update all) resolveconflict(updaterowexists, (max_resolution_method, usemax(update_ts), cols(branch_street_type,branch_street_name, branch_street_number,branch_level,branch_city,branch_state, branch_country,branch_size,branch_opened,zip_code, branch_name,update_ts)), (default overwrite)); Map west.customer, Target east.customer, comparecols(on update all) resolveconflict(updaterowexists, (max_resolution_method, usemax(update_ts), cols(name,middle_name,surname,full_name,dob,nationality, email,update_ts)), (default overwrite)); MAP west.item, Target east.item; MAP west.inventory, Target east.inventory, SQLEXEC (id qty, query "SELECT item_quantity from east.inventory where item_id = :v_item_id AND item_size = :v_item_size AND item_color = :v_item_color", params (v_item_id = item_id, v_item_size = item_size, v_item_color = item_color)), COLMAP (item_id = item_id, item_size = item_size, item_color = item_color, item_quantity = @if(qty.item_quantity <> BEFORE.item_quantity, @compute(qty.item_quantity - (before.item_quantity - item_quantity)), item_quantity));
update inventory set item_quantity = item_quantity - 12 where item_id = 1 and item_size='LARGE' and item_color = 'orange' and item_quantity = 42;
update inventory set item_quantity = item_quantity - 8 where item_id = 1 and item_size='LARGE' and item_color = 'orange' and item_quantity = 42;
*********************************************************************** ** Run Time Messages ** *********************************************************************** Opened trail file ./dirdat/pe000001 at 2015-07-17 12:56:00 Switching to next trail file ./dirdat/pe000002 at 2015-07-17 12:58:46 due to EOF, with current RBA 3079 Opened trail file ./dirdat/pe000002 at 2015-07-17 12:58:46 Processed extract process graceful restart record at seq 2, rba 1202. MAP resolved (entry west.inventory): MAP "WEST"."INVENTORY", Target east.inventory, SQLEXEC (id qty, query "SELECT item_quantity from east. inventory where item_id = :v_item_id AND item_size = :v_item_size AND item_color = :v_item_color", param s (v_item_id = item_id, v_item_size = item_size, v_item_color = item_color)), COLMAP (item_id = item_id, item_size = item_size, item_color = item_color, item_quantity = @if(qty.item_quantity <> BEFORE.item_qu antity, @compute(qty.item_quantity - (before.item_quantity - item_quantity)), item_quantity)); Using the following key columns for target table EAST.INVENTORY: ITEM_ID, ITEM_SIZE, ITEM_COLOR.
select * from inventory where item_id = 1 and item_size='LARGE' and item_color = 'orange'; ITEM_ID ITEM_SIZE ITEM_COLOR ITEM_QUANTITY ---------- ------------ -------------------- ------------- 1 LARGE orange 22
- Enabling database logging on both database
- Avoid bi-directional replication for sequence and DDL operations.
- Define the conflict detection and resolution rules