We're here to help!

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

Follow
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:

SQL> insert into t1 (id1) values (8);
1 row created.
SQL> commit;
Commit complete.

Wait for record replication:

MINE IS running. Currently at plog 977 and SCN 19373768 (07/30/2015 01:46:26).
APPLY IS running. Currently at plog 977 and SCN 19373746 (07/30/2015 01:46:22).
Progress of replication ZD7924:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
SCOTT.T1/SCOTT.T1_CDC: 100% Mine:1/1 Unrecov:0/0 Applied:1/1 Conflicts:0/0 Last:30/07/2015 01:38:54/OK
--------------------------------------------------------------------------------------------------------------------------------------------
1 tables listed.
dbvrep>

Data verification:

SQL> desc t1_cdc
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER
OPERATION CHAR(1)
DATE_CHANGE DATE
SCN NUMBER

SQL> select * from t1_cdc;

ID1 O DATE_CHAN SCN
---------- - --------- ----------
8 I 30-JUL-15 19371146

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

select u.name owner, o.name tabname, c.name colname, decode(c.mine_out,'Y','included','excluded') column_status
from dbrsobj$ o, dbrscol$ c, dbrsuser$ u
where o.obj_ = c.obj_
 and o.owner_ = u.user_
 and o.mine_out = 'Y'
 and u.name != 'DBVREP8005';

OWNER                   TABNAME             COLNAME             COLUMN_S
-----------------------------------------------------------------------------------------
SCOTT                   T1                  ID1                 included
SCOTT                   T1                  ID2                 excluded

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

Have more questions? Submit a request

Comments