We're here to help!

How to export/import current conflict handlers when you need to reconfigure Replicate

Follow
Problem Description

Your Replicate environment contains user-modified conflict handlers on table level and you need to re-run *-all.sh for various reasons. *-all.sh drops and re-creates dbvrep repositories so you lose your configuration by default but there is a way how to export & import conflict handlers settings.

Solution

Current configuration

dbvrep> list prepare
List of prepared schemas:
none
List of prepared tables:
DBVREPCH.DBRSCOMMON_HEARTBEAT -> DBVREPCHTGT (schema rename)
DBVREPCH.DBRSOBJ$ -> DBVREPCHTGT (schema rename)
DBVREPCH.DBRSUSER$ -> DBVREPCHTGT (schema rename)
SCOTT.M1 -> SCOTT.M2 (table rename) (DDL)

dbvrep> show_conflict_handlers for table scott.m1
The table called SCOTT.M1 on source is handled on apply (APPLY) as follows:
The table is renamed to SCOTT.M2.
UPDATE (error): handler: RETRY logging: LOG
UPDATE (no_data): handler: RETRY logging: LOG
UPDATE (too_many): handler: RETRY logging: LOG
DELETE (error): handler: RETRY logging: LOG
DELETE (no_data): handler: RETRY logging: LOG
DELETE (too_many): handler: RETRY logging: LOG
INSERT (error): handler: RETRY logging: LOG
TRANSACTION (error): handler: RETRY logging: LOG

Let's change some conflict handler

dbvrep> set_conflict_handlers for table scott.m1 for update on no_data to overwrite nolog
Connecting to running apply: [The table called SCOTT.M1 on source is handled on apply (APPLY) as follows:
The table is renamed to SCOTT.M2.
UPDATE (error): handler: RETRY logging: LOG
UPDATE (no_data): handler: OVERWRITE logging: NOLOG
UPDATE (too_many): handler: RETRY logging: LOG
DELETE (error): handler: RETRY logging: LOG
DELETE (no_data): handler: RETRY logging: LOG
DELETE (too_many): handler: RETRY logging: LOG
INSERT (error): handler: RETRY logging: LOG
TRANSACTION (error): handler: RETRY logging: LOG]

dbvrep> show_conflict_handlers for table scott.m1
The table called SCOTT.M1 on source is handled on apply (APPLY) as follows:
The table is renamed to SCOTT.M2.
UPDATE (error): handler: RETRY logging: LOG
UPDATE (no_data): handler: OVERWRITE logging: NOLOG
UPDATE (too_many): handler: RETRY logging: LOG
DELETE (error): handler: RETRY logging: LOG
DELETE (no_data): handler: RETRY logging: LOG
DELETE (too_many): handler: RETRY logging: LOG
INSERT (error): handler: RETRY logging: LOG
TRANSACTION (error): handler: RETRY logging: LOG

Now shutdown processes

dbvrep> shutdown all
Dbvisit Replicate APPLY process shutting down.
Dbvisit Replicate MINE process shutting down.

dbvrep> exit

Before you exetute *-all.sh, you have to backup DBRSAPPLY_CONFLICT_HANDLERS residing in the target database

oracle@czoffice:/home/oracle/CH> expdp system/manager schemas=dbvrepchtgt include=table:\"=\'DBRSAPPLY_CONFLICT_HANDLERS\'\" directory=data_pump_dir dumpfile=handlers.dmp reuse_dumpfiles=y

Export: Release 11.2.0.4.0 - Production on Tue Jun 7 16:33:17 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=dbvrepchtgt include=table:"='DBRSAPPLY_CONFLICT_HANDLERS'" directory=data_pump_dir dumpfile=handlers.dmp reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "DBVREPCHTGT"."DBRSAPPLY_CONFLICT_HANDLERS" 17.63 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/CZO/dpdump/handlers.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jun 7 16:33:26 2016 elapsed 0 00:00:08

And you can proceed to run setup wizard and/or execute *-all.sh

oracle@czoffice:/home/oracle/CH> ./CH-all.sh

Once completed, start dbvrep console and prepare table if not already configured in setup wizard

dbvrep> prepare offline table scott.m1 rename to scott.m2 
Table SCOTT.M1 instantiated at SCN 19026546

You can verify that conflict handlers are set to their default values

dbvrep> show_conflict_handlers for table scott.m1        
The table called SCOTT.M1 on source is handled on apply (APPLY) as follows:
The table is renamed to SCOTT.M2.
UPDATE (error): handler: RETRY logging: LOG
UPDATE (no_data): handler: RETRY logging: LOG
UPDATE (too_many): handler: RETRY logging: LOG
DELETE (error): handler: RETRY logging: LOG
DELETE (no_data): handler: RETRY logging: LOG
DELETE (too_many): handler: RETRY logging: LOG
INSERT (error): handler: RETRY logging: LOG
TRANSACTION (error): handler: RETRY logging: LOG

Now import old DBRSAPPLY_CONFLICT_HANDLERS as temporary table

oracle@czoffice:/u01/app/oracle/admin/CZO/dpdump> impdp system/manager directory=data_pump_dir dumpfile=handlers.dmp remap_table=dbrsapply_conflict_handlers:tmp_handlers exclude=index,constraint,statistics,comment

Import: Release 11.2.0.4.0 - Production on Tue Jun 7 16:46:34 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=data_pump_dir dumpfile=handlers.dmp remap_table=dbrsapply_conflict_handlers:tmp_handlers exclude=index,constraint,statistics,comment
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DBVREPCHTGT"."TMP_HANDLERS" 17.62 KB 4 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Tue Jun 7 16:46:36 2016 elapsed 0 00:00:01

Connect as target repository owner and merge data

DBVREPCHTGT@CZO>merge into dbrsapply_conflict_handlers c
2 using tmp_handlers t
3 on (c.source_object_id = t.source_object_id)
4 when matched then
5 update set c.update_method_error = t.update_method_error
6 ,c.update_log_error = t.update_log_error
7 ,c.update_addinfo_error = t.update_addinfo_error
8 ,c.update_method_no_data = t.update_method_no_data
9 ,c.update_log_no_data = t.update_log_no_data
10 ,c.update_addinfo_no_data = t.update_addinfo_no_data
11 ,c.update_method_too_many = t.update_method_too_many
12 ,c.update_log_too_many = t.update_log_too_many
13 ,c.update_addinfo_too_many = t.update_addinfo_too_many
14 ,c.delete_method_error = t.delete_method_error
15 ,c.delete_log_error = t.delete_log_error
16 ,c.delete_addinfo_error = t.delete_addinfo_error
17 ,c.delete_method_no_data = t.delete_method_no_data
18 ,c.delete_log_no_data = t.delete_log_no_data
19 ,c.delete_addinfo_no_data = t.delete_addinfo_no_data
20 ,c.delete_method_too_many = t.delete_method_too_many
21 ,c.delete_log_too_many = t.delete_log_too_many
22 ,c.delete_addinfo_too_many = t.delete_addinfo_too_many
23 ,c.insert_method_error = t.insert_method_error
24 ,c.insert_log_error = t.insert_log_error
25 ,c.insert_addinfo_error = t.insert_addinfo_error
26 ,c.transaction_method_error = t.transaction_method_error
27 ,c.transaction_log_error = t.transaction_log_error
28* ,c.transaction_addinfo_error = t.transaction_addinfo_error;

1 row merged.

DBVREPCHTGT@CZO>commit;

Commit complete.

DBVREPCHTGT@CZO>drop table tmp_handlers purge;

Table tmp_handlers dropped.

Now verify the settings in dbvrep console

dbvrep> show_conflict_handlers for table scott.m1
The table called SCOTT.M1 on source is handled on apply (APPLY) as follows:
The table is renamed to SCOTT.M2.
UPDATE (error): handler: RETRY logging: LOG
UPDATE (no_data): handler: OVERWRITE logging: NOLOG
UPDATE (too_many): handler: RETRY logging: LOG
DELETE (error): handler: RETRY logging: LOG
DELETE (no_data): handler: RETRY logging: LOG
DELETE (too_many): handler: RETRY logging: LOG
INSERT (error): handler: RETRY logging: LOG
TRANSACTION (error): handler: RETRY logging: LOG

We will add export/import of conflict handlers in next releases.

Have more questions? Submit a request

Comments