Oracle Change Data Capture (CDC)

CDC replication is an inexpensive and easily implemented way to replicate data between distributed databases in a data warehouse environment.

A note on currency: Oracle Change Data Capture has not been part of the database since Oracle Database 12c and has been replaced by Oracle GoldenGate. This page remains as a reference for existing systems. For new work we advise on GoldenGate and on log-based alternatives.

Overview

Oracle CDC replication can be used synchronously or asynchronously and is a straightforward solution for replicating data between Oracle databases. CDC also supports multiple consumers.

How it works, by example

The following example shows how a table is replicated using a synchronous CDC change set.

For every table to be replicated with CDC, Oracle creates a change table in which all changes to the base table are recorded. Each consumer receives a subscriber view containing the data it needs, and each consumer can independently remove the data it has already processed from that view.

The starting point is the sample table DEPT, for which a change table is created in the next step. Dropping and altering the change table are shown as examples as well.

SQL / PL/SQL

select * from DEPT;

DEPTNO 	DNAME 	LOC
10	ACCOUNTING 	NEW YORK
20	RESEARCH 	DALLAS
30	SALES 		CHICAGO
40	OPERATIONS 	BOSTON

Change tables are created through Oracle-internal packages.

SQL / PL/SQL

declare
begin
dbms_cdc_publish.create_change_table(
	owner => 'DEPLOY',
	change_table_name => 'CLS_DEPT',
	change_set_name => 'SYNC_SET',
	source_schema => 'SCHEMA_OWNER',
	source_table => 'DEPT',
	column_type_list => '
	DEPTNO  NUMBER(2),
    DNAME   VARCHAR2(14),
    LOC     VARCHAR2(13)',
	capture_values=>'both',
	rs_id=>'Y',
	row_id=>'Y',
	user_id=>'N',
	timestamp=>'Y',
	object_id=>'N',
	source_colmap=>'N',
	target_colmap=>'N',
	options_string=>null);
end;

If necessary, a change table created this way can be dropped or altered as follows:

SQL / PL/SQL

exec dbms_cdc_publish.drop_change_table('DEPLOY','CLS_DEPT','Y');

--
--Erweitern eines Change Tables um die Spalte Loc2
--

begin
dbms_cdc_publish.alter_change_table(
	owner => 'DEPLOY',
	change_table_name => 'CL_DEPT',
	operation    => 'ADD',
	column_list => 'LOC2  VARCHAR2(13)',
	rs_id=>'N',
	row_id=>'N',
	user_id=>'N',
	timestamp=>'N',
	object_id=>'N',
	source_colmap=>'N',
	target_colmap=>'N');
end;

In the next step we create a subscription and the associated view. This view should be created in a schema the subscribing user has access to, and it should not reside in the schema that holds the change table.

SQL / PL/SQL

BEGIN
       DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
       change_set_name   => 'SYNC_SET',
       description       => 'Change data for test',
       subscription_name => 'TEST');
END;
/

The new subscription can be verified as follows:

SQL / PL/SQL

select * from DBA_SUBSCRIPTIONS;

HANDLE 	SET_NAME 	USERNAME 	CREATED 	STATUS 	EARLIEST_SCN 	LATEST_SCN 	DESCRIPTION 	LAST_PURGED 	LAST_EXTENDED 	SUBSCRIPTION_NAME
1	SYNC_SET 	SCHEMA_OWNER 	05.09.2009 09:56:21 	N 1 0 	Change data for test 	  	  	TEST

The subscriber view is created like this:

SQL / PL/SQL

BEGIN
       DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
       subscription_name => 'TEST',
       source_schema     => 'SCHEMA_OWNER',
       source_table      => 'DEPT',
       column_list       => 'deptno, dname, loc',
       subscriber_view   => 'V_SUBS_DEPT');
END;
/

Oracle then creates the following view:

SQL / PL/SQL

CREATE OR REPLACE VIEW deploy.v_subs_dept
AS
SELECT operation$, cscn$, commit_timestamp$,   row_id$, rsid$, timestamp$,
  "DEPTNO", "DNAME", "LOC"
  FROM "SCHEMA_OWNER"."CLS_DEPT"
  WHERE cscn$ >= 1
  AND cscn$ <= 0 WITH READ ONLY
  /

Operation

An insert into the base table now creates a record in the change table, but that record only becomes visible in the subscriber view after the procedure extend_window has been called:

SQL / PL/SQL

Insert into DEPLOY.DEPT
   (DEPTNO, DNAME, LOC)
 Values
   (10, 'ACCOUNTING', 'NEW YORK');
COMMIT;

This produces one record in the change table CLS_DEPT.

SQL / PL/SQL

OPERATION$ 	CSCN$ 	COMMIT_TIMESTAMP$ 	RSID$ 	ROW_ID$ 	TIMESTAMP$ 	DEPTNO 	DNAME 	LOC
I 	281474976710655 	01.01.4000 	1	AAAbloAAFAAAHz2AAA 	06.09.2009 16:53:34 50	IT 	EVERYWHER

The subscription has to be activated explicitly. Once processing is finished, the subscription window can be extended.

SQL / PL/SQL

BEGIN
   DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
       subscription_name => 'TEST');
END;
/

BEGIN
   DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
       subscription_name => 'TEST');
END;

select * from DEPLOY.v_subs_dept

OPERATION$ 	CSCN$ 	COMMIT_TIMESTAMP$ 	ROW_ID$ 	RSID$ 	TIMESTAMP$ 	DEPTNO 	DNAME 	LOC
I 	10151264490285	06.09.2009 16:58:54 	AAAbloAAFAAAHz2AAA 		06.09.2009 16:53:34 	50	IT 	EVERYWHER

After the data has been processed successfully, the completed data set — the subscription window — can be purged. This is done with the PURGE_WINDOW procedure.

SQL / PL/SQL

BEGIN
   DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(
       subscription_name => 'TEST');
END;
/

Caution: this does not remove the data from the CDC change table. Only once all subscribers have consumed the data can the change table itself be purged with a separate procedure call. In that case Oracle issues a split partition statement which briefly blocks DML on the base table. This purge should therefore run very frequently — so that each run is short and the delay minimal — or outside the times when the base table is modified by DML.

SQL / PL/SQL

BEGIN
	dbms_cdc_publish.purge;
END;