How to Exclude Columns and Not Replicate Them to Target Database

Problem Description

In order to exclude columns and not to propagate them to target side, you have to load the target table with methods like CTAS, insert+select etc.

Solution

Edit .dbvrep File to Add Tables and Exclude Columns

Replicate has an inbuilt mechanism, that can use load of table by select statement instead of datapumps. It is not the fastest, but in this case it might be handy. In Step 2, when you define replication pair, you can choose load as an instantiation method:

What data instantiation script to create?

dp_networklink : Data Pump with network link. No export datafile is created (APPLY.sh)
dp_exp : Data Pump with export datafile. Creates an export datafile (APPLY.sh)
exp : Pre-datapump exp/imp with export datafile (APPLY.sh)
ddl_file : DDL file created (APPLY.sql) 
ddl_run : DDL is automatically executed on target 
load : All replicated data is created and loaded automatically
none (dp_networklink/dp_exp/exp/ddl_file/ddl_run/load/none) [dp_networklink] load

After you finish the wizard (before *all.sh script is run) , you have to add the exclude column commands to <DDC>/config/*.dbvrep file. I recomend putting it after the #prepare script for instantiation section. For Example:

#prepare script for instantiation
EXCLUDE COLUMN AHOREJSP.TEST_EXCLUDE.COL3

After you run all.sh script and start the processes, the table should be loaded to target without the excluded columns.

Adding Other Table to Replication

If you want to add the table later on to the replication, you have to:

Shutdown MINE
prepare table AHOREJSP.TEST_EXCLUDE Table AHOREJSP.TEST_EXCLUDE instantiated at SCN 1108430512
Remember the SCN from this result
create your own CTAS table on the TARGET without the columns you do not want to replicate eg.:
Start the MINE back up.

Use Mechanism of Replicate to Create and Load Target Tables

This method uses dbvrep mechanisms to recreate and load target tables. The following grants are necessary in order to use this method:

Grant select on tables mentioned to be prepared.
Grant flashback on tables mentioned to be prepared.

Or you can use following power grants:

Grant select on tables necessary to load or grant select any table to dbvrep.
Grant flashback on all tables, necessary to load or grant flashback any table to dbvrep.

Then you should follow these steps:

Shutdown MINE.
Prepare tables for load:
Exclude columns from loading:
Command mine to load the tables:
Start the MINE:

Petr Horejs July 23, 2015 02:15