How to Setup CDC/Audit on a Subset of Table Columns

Problem Description

CDC/Audit is creating target table with all columns by default. If you want to exclude some of them in your CDC/Audit setup, you'll need to use the EXCLUDE COLUMN command to exclude all unwanted columns from replication.

Solution

#### Pre-requisites:
Replication pair set as ddl-only, ddl\_run/ddl\_file
Create table in source database
create table t1 (
 id1 integer,
 id2 integer 
);

Prepare table using dbvrep console:

#prepare the tables
PREPARE TABLE "SCOTT"."T1" NODDL RENAME TO "SCOTT"."T1_CDC"

# cdc/audit
CDCAUDIT TABLE "SCOTT"."T1" INSERT YES
CDCAUDIT TABLE "SCOTT"."T1" DELETE NO
CDCAUDIT TABLE "SCOTT"."T1" UPDATE NO
CDCAUDIT TABLE "SCOTT"."T1" OPCOL OPERATION
CDCAUDIT TABLE "SCOTT"."T1" SCN SCN
CDCAUDIT TABLE "SCOTT"."T1" TIMESTAMP_CHANGE DATE_CHANGE

# exclude unwanted columns
EXCLUDE COLUMN "SCOTT"."T1"."ID2"

#prepare script for instantiation
PROCESS SWITCH_REDOLOG
DDL DROP REPLICATE "SCOTT"."T1"
DDL CREATE_FROM_DICT REPLICATE "SCOTT"."T1"
PROCESS PREPARE_DP WRITE DDL_FILE FILE /home/oracle/ZD8005/APPLY.sql USERID SYSTEM/<systempwd>@SRCDB

# shutdown all
SHUTDOWN ALL

Create Audit table on target using previously created script:

$ sqlplus scott/<password>@TGTDB @/home/oracle/ZD8005/APPLY.sql

Now start MINE & APPLY processes:

-- MINE
oracle@dbvlin216[/home/oracle/ZD7924]: ./ZD7924-run-dbvlin216.dbvisit.co.nz.sh
Initializing......done
DDC loaded from database (362 variables).
Dbvisit Replicate version 2.7.14
Copyright (C) Dbvisit Software Limited.  All rights reserved.
DDC file /home/oracle/ZD7924/ZD7924-MINE.ddc loaded.
Starting process MINE...started

[...] 
-- APPLY
oracle@dbvlin217[/home/oracle/ZD7924]: ./ZD7924-run-dbvlin217.dbvisit.co.nz.sh
Initializing......done
DDC loaded from database (363 variables).
Dbvisit Replicate version 2.7.14
Copyright (C) Dbvisit Software Limited.  All rights reserved.
DDC file /home/oracle/ZD7924/ZD7924-APPLY.ddc loaded.
Starting process APPLY...started

Insert record to source table:

Wait for record replication:

Data verification:

How to check, which column is included/excluded? Note: DBVREP8005 is DBVREP username:

Summary

You can add CDC/Audit tables in offline mode to your replication pair.
You can exclude columns by specifying them in the EXCLUDE COLUMN command.

Jan Suchanek July 30, 2015 03:35