Audit/CDC Replication : adding table or alter existing table

Problem Description

This article explains how to add or alter a table to Audit/CDC(Change Data Capture) replication.

Steps Performed

1. First create new table on target with selected values, ie.

Create table reptest3 (

OLD_ID                      NUMBER(8),

NEW_ID                     NUMBER(8),

OLD_FLAG                  VARCHAR2(4),

NEW_FLAG                VARCHAR2(4),

OPERATION              CHAR(1),

DATE_CHANGE         DATE,

SCN                            NUMBER,

OLD_MYFLAG            VARCHAR2(1),

NEW_MYFLAG           VARCHAR2(1),

primary key (id)

);

2. Create table on source, ie.

create table reptest3 (

id number(8),

flag varchar2(4),

primary key (id)

);

3. In console window pause mine/apply

dbvrep> pause mine

dbvrep> pause apply

4. Run below commands 

(note lines in the file <DDC>-setup.dbvrep under config for guidance

1. a) if you setup using ddl_run option for the format of commands substituting table name

2. b) if you used ddl_file you can copy/paste lines you need into another file & substitute table name

)

dbvrep> PREPARE OFFLINE TABLE "TODD"."REPTEST3" NODDL RENAME TO "BRIAN"."REPTEST3"

CDCAUDIT TABLE "TODD"."REPTEST3" INSERT YES

CDCAUDIT TABLE "TODD"."REPTEST3" DELETE YES

CDCAUDIT TABLE "TODD"."REPTEST3" UPDATE YES

CDCAUDIT TABLE "TODD"."REPTEST3" OLDCOL_PREFIX old_

CDCAUDIT TABLE "TODD"."REPTEST3" NEWCOL_PREFIX new_

CDCAUDIT TABLE "TODD"."REPTEST3" OPCOL OPERATION

CDCAUDIT TABLE "TODD"."REPTEST3" SCN SCN

CDCAUDIT TABLE "TODD"."REPTEST3" TIMESTAMP_CHANGE DATE_CHANGE

5. Un-pause MINE/APPLY

dbvrep> resume mine

dbvrep> resume apply

#######################################

Add a column to a Audit/CDC replicated table

1. In console window pause mine/apply

dbvrep> pause mine

dbvrep> pause apply

2. On target – add field for before & after old_myflag on to target table, ie.

alter table reptest1 add (old_myflag varchar(1), new_myflag varchar2(1));

3. On source table add the field, ie. ‘myflag’

alter table reptest1 add myflag varchar(1);

4. In console window pause mine/apply

dbvrep> resume mine

dbvrep> resume apply