Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle CDC Tips

Oracle Tips by Burleson Consulting

December 14, 2011


Question:  I've read the documentation of configuring Oracle Streams Data Capture and I would like to a more detailed understanding of Oracle Streams change data capture.

Answer:  Configuring Oracle Streams change data capture is quite straightforward.  The ?Oracle Streams? book has a complete step-by-step guide for Streams CDC propagation.

Oracle Change Data Capture was introduced with Oracle 9i, and provided the ability to track changes to tables and store them in a change table, for further consumption by an ETL process. Oracle Change Data Capture worked by creating triggers on the source tables, transferring data synchronously but creating a processing overhead and requiring access to the structure of the source tables. Because of the effect that the triggers had on the underlying tables, many warehouse projects did without change data capture and used other methods to capture changes.

Oracle 10g introduced Asynchronous Change Data Capture, which instead of using triggers uses the database log files to capture changes and apply them to collection tables. Asynchronous Change Data Capture therefore doesn't require changes to the table structure and doesn't impact on database performance.

Oracle9i Database introduced the Change Data Capture (CDC) feature. Oracle Streams Change Data Capture captures all the inserts, updates, and deletes made to user tables. These changes are stored in a database object called a change table, and the change data is made available to applications in a controlled way through what is called a subscriber view.

Prior to Oracle 10g, the change data was synchronously fed from the source. In Oracle Database 10g, it becomes possible to asynchronously feed the change data, and there are multiple options for how this feed is obtained and managed. Asynchronous data capture reads the changed data from Oracle redo log files. As a result of this, dependence on the transaction activity is avoided and performance overhead is reduced. Oracle Streams Methodology is used to capture change data from redo log files.

Asynchronous change data capture requires a streams configuration for each CDC change set. These configurations include the streams capture and apply processes that read change data from redo log files and inserts it into change data capture change tables.

Asynchronous change data capture requires both the source and staging databases to be Oracle Database 10g.

The Purpose of Streams Change Data Capture

From the inherent data structure perspective, change data capture can either occur at the data level or at the application level. At the data level, a table in the target database is regarded as a remote snapshot of a table in the origin database. At whichever level capturing and propagation is taking place, it is imperative to note that there is always an increase in the workload on the source database. However, with oracle 10g, additional overhead is a forgotten issue.

Asynchronous change data capture is now adopted, in which change data is extracted from the redo logs without any negative performance implication on the source database. Furthermore, asynchronous change data capture can be described as a lightweight technology targeted towards change extraction and propagation in a data warehousing system and in which changes to the source tables are viewed as relational data for onward consumption by subscribers.

There is therefore no gain saying that asynchronous change data capture has greatly enhanced parallel log file processing and data transformation.

The purpose of Oracle Database 10g change data capture is to efficiently identify and capture data that has been added to, updated, or removed from Oracle relational tables, and make the change data available for use by applications.

In order to facilitate the movement of data from source databases to data warehouses, the extraction and transportation of relational data from one or more source databases must be optimized. The Oracle Database 10g change data capture feature is used for optimizing this type of data transfer activity.

Using a change table, the change data capture feature eliminates the need for an intermediate flat file to stage data outside of the relational database. Instead, the change data resulting from INSERT, UPDATE, and DELETE operations is captured into the change data table inside the database. The change data is then made available to applications in a controlled way.

Asynchronous Oracle Streams Change Data Capture

When a SQL statement that performs a DML operation is committed, change data is captured by taking it from the redo log files. Using this technique, the change data is not captured as part of the modifying transaction, and therefore has no dilatory effect on that transaction. Asynchronous Change Data Capture is available with Oracle Database 10g Enterprise Edition only.

Asynchronous Change Data Capture is based on, and provides a relational interface to, Oracle Streams.  The following are the two methods of capturing change data asynchronously:

  • HotLog Mode

  • AutoLog Mode

HotLog Mode

In the HotLog mode, change data is captured from the current active redo log file on the source database. The change tables are populated automatically as new committed transactions arrive. No data is recorded until the transaction commits. There will be a minor delay between the commit of the source table transactions and the arrival of change data to the change table.

In the HotLog mode, there is a single, predefined HotLog change source, identified as the hotlog_source, that represents the location of the current redo log files of the source database. There is only one source for the HotLog change. Once defined, this cannot be altered or dropped without a database restart.

AutoLog Mode

In the AutoLog mode, change data is captured from a set of redo log files managed by the log transport services of Data Guard. The publisher of the change data (source database) must configure the Data Guard log transport services to copy the filled redo log files from the source database system to the staging database system, and then to automatically register the redo log files. Change sets are then populated automatically as new redo log files arrive. The amount of delay depends on the frequency of redo log switches in the source database.

There is no requirement for a predefined AutoLog change source. The publisher form the source database provides information about the source database to create the AutoLog change source.

More details on Oracle Streams Asynchronous Change Data Capture can be found in the book "Oracle Streams", which also details improvements to the transportable tablespaces feature in Oracle Database 10g.

Streams Data Change Capture Example

BEGIN
 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
 dbms_output.put_line ('Instantiation SCN : ' || iscn ) ;
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@DNYOIP20.world (
    source_schema_name    => 'ny2',
    source_database_name  => 'DNYTST10.world',
    instantiation_scn     => iscn ,
    recursive => true
    );
END;
/

Start both the Streams Change Apply and Capture processes at the destination and source databases, respectively:

connect STRMADM/STRMADM@dnyoip20

PROMPT Connected to Destination (DNYOIP20)
PROMPT Starting the Apply Process at Destination
begin
dbms_apply_adm.start_apply (apply_name => 'LN2_APPLY ' ) ;
end ;
 

11g new Streams CDC Features

Oracle 11g introduced synchronous change data capture, a real-time way of capturing CDC information for databases with low-DML rates (Under 20 updates per second).

Oracle 11g also introduced 11g the "apply only" option and advanced queuing enhancements to improve re-synchronization after network loss.

More on Oracle Streams Change Data Capture:


 


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.