Oracle Change Data Capture under magnifier – Async CDC with 10gR2

CDC is perceived like an aspirin in my organization, so why is that? Simply here the need is sharing and integration of information among a multitude of applications and databases and CDC make this data flow. Another variation of this need is instead of taking full extractions and calculating the change during data warehouse’s ETL process, CDC enables just to extract the changes from source databases. I guess this is why CDC is in Data Warehousing Guide as Chapter 16 :)

cdc-for-dwh.png

Sync methods like trigger or materialized view log to capture the change on tables have important performance affects on source systems. Below is a simple Sync CDC demo with a Materialized View Log.

Code Listing 211a – Sync CDC demo with Materialized View Log

Oracle Streams(Logminer+Advanced Queues(AQ)) is the technology behind Async CDC, it automatically;
– captures database changes at a source database,
– stages the changes,
– propagates the changes to one or more destination databases,
– and then applies the changes at the destination database(s).

All these are handled including both DML and DDL changes. And Oracle Streams integration can be;
– Within an Oracle database,
– Between two Oracle databases,
– Among multiple Oracle databases,
– Between an Oracle database and a non-Oracle database.

In Oracle the committed changes to data that take place in a database are recorded in the database redo log files. The Streams capture process extracts these changes from the redo log files(or archived log files) and formats each change into a logical change record(LCR). The LCRs are then stored(staged) in a queue. Next, Streams propagates LCRs from one queue(the producer queue) to another(the consumer queue) automatically and you can then apply(or consume) the LCRs from the consumer queue to the destination database with custom pl/sql applications depending on your need or want.

After 10g Release 2, Asynchronous Distributed CDC can be used against Oracle 9i Release 2 and higher sources. And there is enhanced CDC PL/SQL Control APIs(dbms_cdc_subscribe, dbms_cdc_publish) support for Async CDC setup and configuration. WE have two options after 10gR2;

Option 1 – Async Distributed Hotlog CDC Flow
a. Database Objects Record Changes
b. LGWR Log Changes to Online Redo Log
c. Capture Process Enqueue LCRs on local Streams Queue
d. LCRs are Propagated to target Streams Queue
e. Dequeue LCRs from target Streams Queue and Apply Changes to target Database Objects

Option 2 – Async Autolog CDC Flow
a. Database Objects Record Changes
b. LGWR Log Changes to Online Redo Log
c. ARCn Write Redo Data to local Archived Log File
d. Archived Log File is copied to target
e. Capture Process Enqueue LCRs to target Streams Queue from transferred Archived Log File
e. Dequeue LCRs from target Streams Queue and Apply Changes to target Database Objects

Important prerequisites for Async CDC are;
a. Source database must be in ARCHIVELOG mode,
b. the change capture table must be altered and kept logging or at database level FORCE LOGGING must be enabled for NOLOGGING operations. FORCE LOGGING on tablespace level may be preferred in some cases.
c. Supplemental Logging is enabled at database level or table level;

-- To enable minimal supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- other supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
-- to drop supplemental logging
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
-- to control supplemental logging
SELECT supplemental_log_data_min, supplemental_log_data_pk,
supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all
FROM v$database;

d. Async CDC requires at least version 9206 on source database,
e. Autolog Async option which reduces the mining and queuing performance impact on source requires Oracle and OS versions to be same,
f. some init.ora settings are mandatory, and GLOBAL_NAMES=TRUE is advised by documentation. But most probably this setting is not exceptable at source because you need to make sure the name of the database link matches the name of the target database exactly after this setting. As a workaround a database logon trigger can be used;

CREATE OR REPLACE TRIGGER cdc_trig
AFTER LOGON
ON DATABASE
DECLARE
sqlstr VARCHAR2(200) := 'ALTER SESSION SET GLOBAL_NAMES=TRUE';
BEGIN
IF (USER IN ('USR1', 'USR1')) THEN
execute immediate sqlstr;
END IF;
END cdc_trig;
/

Based on Mark Van de Wiel’s great Async CDC Cookbook here is Async Distributed Hotlog CDC option in action between a HP-UX B.11.23 U 10g Enterprise Edition Release 10.2.0.2.0 – 64bit as source and SunOS 5.10 – 10g Enterprise Edition Release 10.2.0.3.0 – 64bit as target.

Code Listing 211b – Async Distributed Hotlog CDC option in action

Before my final words here is a brief summary for the above demonstration;

PRE SETUP STEPS –
A – required database parameters are set on both databases,
B – cdc admin users are created with requested grants on both databases,
C – dblinks are created on both databases,
C – force logging and supplemental logging is enabled at source only.

SETUP STEPS –
A – create_hotlog_change_source step at target,
B – create_change_set step at target,
C – create_change_table step at target,
D – alter_change_set step at target,
E – alter_hotlog_change_source step at target,
F – create_subscription step at target.

POST SETUP –
A – change test at source,
B – capture control at target.

And my final notes are as follows;
a. CDC performance and restrictions are mostly related to Streams feature,
b. Direct path load and nologging statements on the operational source database is a threat which is usually a rare situation on a traditional OLTP environment,
c. There are documented bugs and patches on metalink, so testing Async CDC for your own need is highly recommended;
Bug 4285404 – Streams propogation from 9206
Bug 4649767 – High system commit numbers(SCNs)
Bug 5024710 – Queue tables in System Tablespace
d. do not throw a mountain to kill a scorpion :) If you are able to easily capture change information through your application(some modification date columns for example) I advice you to go for them first.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

References Used :

Asynchronous Change Data Capture Cookbook by Mr.Mark Van de Wiel – Feb 2006
Asynchronous Autolog Change Data Capture Cookbook by Mr.Mark Van de Wiel – Jan 2007
Mark Rittman’s article
Julian Dyke’s Streams and Supplemental Logging presentations
Streams and CDC demos at psoug.org
Streams related Oracle magazine article

Oracle Information Integration Part 1

I am working on the change data capture feature of Oracle for our Extract Transform Load(ETL) process. With this feature we may be able to only apply the daily changes from our operational databases to our data warehouse’s staging area. But before that I must negotiate with the operational database’s admins on how much cost this feature will bring to their databases.

In order to do that I plan to produce statspack reports(also 10046 trace files if needed) and compare before and after setup conditions, as statspack and 10046 are standard all excepted performance tools I think decision between doing this extract-transform-load process daily with whole tables and calculating the differences from the previous days whole data on the staging area or doing it with this change data capture feature will be less debatable.

So I started to do some researches on Oracle’s Streams Advanced Queuing(AQ) option since Logminer and AQ is the two most important Oracle features behind this technology. As a starting point I wanted to compare the performance of a Do It Yourself(DIY) queue to Oracle’s Advanced Queue(AQ). AQ has a long history and a much more stable product compared to other options, also has lots of great configure and use features like timeout, delay, multi-consumer and priority. But if you really do not need these features and only your need will be to push and pull without any of these options, is AQ still a good choice for you?

I did a similar research for this question on 8iR2 several years ago and we choosed to use DIY table(an index organized table(IOT)) from the performance results. So the time has come to redo this comparison on 10gR2. Below you will find the setup scripts for both aq and diy-iot queue tables and packages. Also functional and benchmark test scripts are provided, so please try them and give me feedback if I am missing something;

Code listing 69a : DIY queue with IOT setup
Code listing 69b : AQ Setup
Code listing 69c : Responce time and latching results SINGLE based
Code listing 69d : Responce time and latching results ARRAY based

In order to eliminate the caching affect I repeated the tests for several times;
For single runstats AQ for 1000 enqueue and dequeue operations finished within 336 hsecs where as
for single runstats DIY-IOT-Q for 1000 enqueue and dequeue operations finished within 166 hsecs.
~202% of response time.

Also when we look at the latching activity of the two options which is very critical in a concurrent environment;
Again for single runstats DIY-IOT-Q for 1000 enqueue and dequeue operations against AQ had
~154% of latching amount. Dominant difference comes from these three Oracle events; undo change vector size, redo size, IMU undo allocation siz

So the second comparison was on array(bulk) enqueueing and dequeueing;
For array runstats AQ for 1000 enqueue and dequeue operations of a 10 rows array finished within 1242 hsecs where as
for single runstats DIY-IOT-Q for 1000 enqueue and dequeue operations of a 10 rows array finished within 1412 hsecs.
~88% of response time.

Also again when we look at the latching activity of the two options which is very critical in a concurrent environment;
Again for array runstats DIY-IOT-Q for 1000 enqueue and dequeue operations of a 10 rows array against AQ had
~81% of latching amount. This time dominant difference comes from these Oracle events; session uga memory, session pga memory max, session pga memory, IMU Redo allocation siz, undo change vector size, IMU undo allocation siz, redo size

So what are the next steps, first of all I hate do it yourself(diy) approaches since using what is already available, tested and paid a lot is much more efficient. Also there is no need to reinvent a dumper wheel, Oracle gives support for its AQ and there are a lot of great features you may easily configure and use depending on your customers changing needs.

From my primitive loop testings still AQ with bulk option is better, but this decision must be made on the results that are to be monitored under load, not just a primitive isolated database test. So here is a good starting article on how to load Oracle to follow up. Also doing some 10046 research on how to decrease the waits found on bulk AQ option should be very useful.

I always try NOT to be one of those “Question Authorities” you may easily find from a search engine, so please use the test cases provided and contribute to this peer group review.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :
http://www.psoug.org/reference/bulk_collect.html
Oracle® Streams Advanced Queuing User’s Guide and Reference 10g Release 2 (10.2)