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>insert into dbrsapply_conflict_handlers 
2 select t.* from tmp_handlers t
3 where t.source_object_id not in (
4 select c.source_object_id from dbrsapply_conflict_handlers c);
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

Remap_table funcation was introduce for 11g datababase. If it's your case, create temporary user and use remap_user instead.

Example:

SQL> create user tmp_dbvrep
SQL> grant dba to tmp_dbvrep
$ impdp system/manager directory=data_pump_dir dumpfile=handlers.dmp remap_schema=dbvrep:tmp_dbvrep exclude=index,constraint,statistics,comment
SQL> grant select on tmp_dbvrep.dbrsapply_conflict_handlers to public;

DBVREPCHTGT@CZO>insert into dbrsapply_conflict_handlers 
2 select t.* from tmp_dbvrep.dbrsapply_conflict_handlers t
3 where t.source_object_id not in (
4 select c.source_object_id from dbrsapply_conflict_handlers c);
DBVREPCHTGT@CZO>merge into dbrsapply_conflict_handlers c  2  using tmp_dbvrep.dbrsapply_conflict_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;
DBVREPCHTGT@CZO>conn / as sysdba
SQL> drop user tmp_dbvrep cascade;

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


Have more questions? Submit a request

Comments