We're here to help!

Filtering Deletes at a Transaction Level so Data is not deleted in APPLY

Follow
Problem Description
  • A filter was not configured during instantiation. Now data from the source database is to be deleted but you do not wish to have it deleted from the target database.
  • Another use case is the desire to periodically purge the production database while keeping the data in the target (ie OLTP vs Historical).
Assumptions
  • Replicate is configured and the schema/tables are being successfully replicated to target.
  • There is a need/desire to not propagate deletes to target.
Solution

a)

Any Transaction starting with DBREPL_ is ignored by default when MINE prevention is enabled within Dbvisit Replicate. This is done by editing the *-MINE.ddc file and adding the following two lines:

set _MINE_LOOP_PREVENTION = YES
set MINE._MINE_LOOP_PREVENTION = YES
in Replicate >= release 2.9.00 the value is 
set MINE_LOOP_PREVENTION = YES

MINE must be shut-down and restarted.

You can verify this is now enabled by grep'ing the log file for "Mine loop prevention" and checking it is now enabled:

$ grep "Mine loop prevention" *.log
2014/08/30 01:40:52 INFO> Mine loop prevention disabled. <- default
2014/08/30 01:42:42 INFO> Mine loop prevention enabled. <- after edit & restarting MINE

Anytime you want transactions that contain a delete on the source database not to be propagated to target use the Oracle command:

SET TRANSACTION NAME 'DBREPL_%'

For example:

SQL> SET TRANSACTION NAME 'DBREPL_TEST_DELETE1';
SQL> delete from test.reptest where id >=31;
SQL> COMMIT;

 

 

 

b)

Another option instead is to configure a FILTER when you run the setup wizard to start the
configure replication, this however may/will have a negative impact on speed / delays in replication. The target  may have increased / significant lag time from the source.

The above transactional method (a) is preferred, if possible.

Setup wizard. Step 3 - Example...

Specify rename name or filter condition for any of the specified schemas? (Yes/No) [No] YES       
(PREPARE_SCHEMA_EXCEPTIONS) - Specify tables to exclude from PREPARE SCHEMA, if any:       
Rename name for schema NICKY (empty means no rename):                                                     

Filter the data to be replicated? If yes, use single condition for ALL DML operations or use CUSTOM conditions for each operation? (No/ALL/CUSTOM)     [No] CUSTOM

Filter condition for INSERT for schema NICKY (empty means no condition): Filter condition for UPDATE (old values) for schema NICKY (empty means no condition):                                         

Filter condition for UPDATE (new values) for schema NICKY (empty means no condition):             

Filter condition for DELETE for schema NICKY (empty means no condition): [] 1=0

 

Donna Zehl September 04, 2014 01:50

Have more questions? Submit a request

Comments