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

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
2 min read

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:

 

 

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