Filter the data to be replicated
Dbvisit Replicate allow you to replicate only those data that fulfills certain condition. For example, only replicate data from the current year. You can set a common filtering condition for all DMLs or each type of DML can have different condition. You can configure filtration of data to be replicate in Step 3 of setup wizard.
In following example, only those data where column jobcode starts with '1' will be replicated. In other words the condition must be satisfied in order for the rows to be replicated.
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] ALL Filter condition for table AVI.SAMPLE: [] jobcode like '1%'
In following example, different filtering criteria are configured for each type of DML.
Filter the data to be replicated? If yes, use single condition for ALL DML operations or use CUSTOM conditionsfor each operation? (NO/ALL/CUSTOM) [NO] CUSTOM Filter condition for INSERT for table AVI.SAMPLE (empty means no condition): [] sdate>='01-Jan-12' Filter condition for UPDATE (old values) for table AVI.SAMPLE (empty means no condition): [sdate>='01-Jan-12'] Filter condition for UPDATE (new values) for table AVI.SAMPLE (empty means no condition): [sdate>='01-Jan-12'] Filter condition for DELETE for table AVI.SAMPLE (empty means no condition): [sdate>='01-Jan-12'] jobcode like '1%'
For filtering to work, the columns used in the conditions must be always included in the redo log. This can be achieved simply by forcing Oracle to include them for every DML operation on the table by creating a supplemental log group. Therefore when data is filtered for replication, enable supplemental logging for participating column. It is also helpful when PL/SQL is used for conflict handling.
Another example, to prevent any deleted to go to the target database when replicating at a schema level
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 AVI (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 AVI (empty means no condition): [] Filter condition for UPDATE (old values) for schema AVI (empty means no condition): [] Filter condition for UPDATE (new values) for schema AVI (empty means no condition): [] Filter condition for DELETE for schema AVI (empty means no condition): [] 1=0
Click here to learn more about Supplemental logging and how to turn it on.
Although it is normal to set the filtering when you initially use the Setup Wizard you can also set up filtering after the fact.
Make sure that the table is already prepared. The normal procedure is to run the command with the OFFLINE feature.
Below we have an example of a filter that would make sure that all of the inserts equal to 10 on the scott.emp table were replicated. Any other values would not be replicated.
In the second example, only updates with the value of empno greater than 200 would be replicated for the scott.emp table.
dbvrep> FILTER OFFLINE TABLE SCOTT.EMP on INSERT SET TO deptno = 10 dbvrep> FILTER OFFLINE TABLE SCOTT.EMP on PRE-UPDATE SET TO empno >200
When target is not oracle database then avoid using inbuilt functions in filtering as same functions might not be available on target environment.