Let's walk through an Oracle GoldenGate active-active replication configuration.The example includes the CDR solutions using master-slave overwrite, timestamp, quantity column and the SQLEXEC to resolve the conflicts.
Using Quantity Columns
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.
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
Configure the capture and replicat on the WEST DB side. Make sure to restart both extract and replicat groups after the parameter change.
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));
Configure the extract and replicat on the EAST DB side. Make sure to restart both extract and replicat groups after the parameter change.
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));
Then stop the pump and make the following changes on the WEST DB:
update inventory set item_quantity = item_quantity - 12 where item_id = 1 and item_size='LARGE' and item_color = 'orange' and item_quantity = 42;
Make the changes on the EAST DB:
update inventory set item_quantity = item_quantity - 8 where item_id = 1 and item_size='LARGE' and item_color = 'orange' and item_quantity = 42;
Start the pump. You should see the resolution in the report file of the replicats on both side. An example output is shown as follows:
*********************************************************************** ** 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.
You can check the result with the following SQL query:
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
Note that it's important to restart the extract file on both side to make sure the new table ITEM and INVENTORY changes are captured. Otherwise, it may cause the data inconsistency.
But there are two operations for DDL and Sequence replication. The followings are the check list:
- Enabling database logging on both database
- Avoid bi-directional replication for sequence and DDL operations.
- Define the conflict detection and resolution rules
Create: 7/17/2015, Last Updated: 10/12/2017