|
| CDC | Streams |
| Definition | Change Data Capture is database functionality that enables capturing incremental changes with predefined API against an Oracle Database and to make the change data available for further use. | Streams provides a flow mechanism in which, the database changes flow in a streamed manner, hence the name Streams. It is a very broad and flexible way of capturing changes, moving them, and applying them. |
| Purpose | CDC is for monitoring changes in a database.
You can create subscriptions for applications so they can see only certain changed data. | For Information Sharing/Distribution
Streams are for replicating data between databases for more high availability. The stream routes published information to subscribed destinations. You can route the flow of data through multiple databases without having the changes applied at each intermediate server. |
| Mechanism | Source TABLE => Change Table => Subscriber view =>TARGET system
Change data capture does not depend on intermediate flat files to stage the data outside of the relational database. It captures the change data resulting from INSERT, UPDATE, and DELETE operations made to user tables. The change data is then stored in a database object called a change table, and the change data is made available to applications in a controlled way. | CATPURE => Staging => APPLY
Streams, keeps track of the entire database changes. It then stages those changes into queues and later moves them to the destination queues where they are applied to the destination database objects.You make changes to the data before it is applied at the destination. Database objects and data are kept synchronized at all of the databases in the replication environment. |
| DML and DDL changes | You can capture only DML changes CDC is *NOT* supported for doing DDL operation | You can also capture DDL changes.Note 238457.1 What DML and DDL is Captured by Streams |
| Usage | CDC most commonly used to capture transactional changes from an OLTP system and publish the changes to one or more subscription systems use Change Data Capture to simplify ET L (extraction and transportation of relational data) in data warehouse applications. The advantage of CDC is that it is intended for extracting data in a data-warehousing environment so you can process only the changed data not entire tables and makes the change data available for further use. | Used for the following:
Replicating data from one database to another
Message Queuing
Data Warehouse Loading.
Event management and notification.
Data provisioning in a grid environment
High availability during database upgrade, platform migration, and application upgrade.
Allows data to be transparently shared between both Oracle and non-Oracle data stores. |
| How does it work | Synchronous Change Data Capture uses triggers on the source database to capture change data.
Asynchronous Change Data Capture uses the redo log files. Change data is captured after a SQL statement that performs a DML operation is committed. In this mode, change data is not captured as part of the transaction that is modifying the source table, and therefore has no effect on that transaction. Asynchronous Change Data Capture is available with Oracle Enterprise Edition only not the standard edition. |
Streams works by scanning through the redo logs and capturing changes that have been made to specified objects or schema's.
|
|
Oracle and OS/platform compatibility
| The internal Change Data Capture (CDC) tasks of Streams Capture and Apply are
decoupled, thereby enabling a heterogeneous CDC setup with different operating
systems and Oracle versions. The propagation between source and target database
is a pure Streams implementation, enabling asynchronous CDC to leverage any existing Oracle9i Release 2 system as a source.
Asynchronous Change Data Capture (CDC) no longer requires the same operating
system for source and target. Furthermore, distributed asynchronous CDC
capabilities expands the reach of CDC source systems to include Oracle9i Release 2.
In case of Asynchronous AutoLog Mode the source database and the staging database must be running on the same hardware,operating system, and Oracle version.
The Distributed HotLog mode of Change Data Capture supports the use of different hardware platforms or operating systems (or both) for the source and staging databases.. | For bidirectional/unidirectional Streams replication does not have any restriction on Oracle version or OS .
Both source and target can be on different platforms as well different Oracle base releases..
Operational Requirements for Downstream Capture
The following are operational requirements for using downstream capture:
■ The source database must be running at least Oracle Database 10g and the
downstream capture database must be running the same release of Oracle as the
source database or later.
■ The downstream database must be running Oracle Database 10g Release 2 to
configure real-time downstream capture. In this case, the source database must
be running Oracle Database 10g Release 1 or later.
■ The operating system on the source and downstream capture sites must be the
same, but the operating system release does not need to be the same. In
addition, the downstream sites can use a different directory structure from the source site.
■ The hardware architecture on the source and downstream capture sites must be the same. For example, a downstream capture configuration with a source
database on a 32-bit Sun system must have a downstream database that is
configured on a 32-bit Sun system. Other hardware elements, such as the number of CPUs, memory size, and storage configuration, can be different between the source and downstream sites. |
| With respect to Non Oracle Databases |
Does not support changed tables and subscriber view to be present on non-Oracle Databases
|
Allows data to be shared between oracle and non-oracle data stores.
|
| With respect to logical Standby |
CDC Async Distributed Hotlog Mode is *NOT* supported on LOGICAL STANDBY
Logical standby as the source of Async CDC (in any mode)/Streams is not supported in any releases 9.2, 10.1, 10.2 and 11.1
| Streams cannot be used in 9i and 10g with Logical Standby as logical standby cannot run jobs BUT in 11g you can use Streams with Logical Standby only in Combined Capture and Apply mode |
|
Benefits
| CDC offers cost savings by simplifying the extraction of change data from database as its part of Oracle 9i database and later versions.
CDC Captures change data resultant of DML operations including the before and after update values of an update operation.
Data changes are captured automatically to change table.Very friendly simple to use APIs to publish and subscribe to the changes.Can be scripted with very little effort.
Asynchronous CDC captures data with very little performance impact. Best of both worlds.Automatic purge of consumed or obsolete change data captured in change table.
CDC ensures that every subscriber sees all changes.Efficient tracking of multiple subscribers and provides a shared access to the changed data.
Transactional consistency for changes across multiple source tables is guaranteed. Transparently coordinates sharing of change data across users and applications | Integrated Feature of Oracle9i Database
No additional software to install. No special commands to learn.Takes advantage of reliability and security provided with Oracle9i Database.
Manageable
Provides maximum flexibility for configuration and administration with Oracle-supplied PL/SQL packages.
Provides wizards and monitoring capabilities for ease of use with Streams tool in Oracle Enterprise Manager.
Keeps users informed about their environment through Data dictionary view.
Performance
Hot mining of the online redo log reduces the latency of data capture.
Parallel capture and apply processes ensure maximum throughput for concurrent events |
| LIMITATION | Asynchronous mode CDC purely worked based on logged operations, so any non-logged DML operations are not captured.
Synchronous mode CDC does not support direct load insert.
CDC cannot be implemented on table with TDE (Transparent Data Encryption) enabled.
Asynchronous mode capture will not work without supplemental logging.
Although direct select is possible on change table but the extraction of the changed data is valid/supported only via subscriber views.
CDC is not a development solution to perform any validations or transformation or provide any application specific checks. | TDE columns are not supported by Oracle Streams in 9i and 10g but supported in 11g
TDE is supported is 11g for Asyncronous CDC as specified in the Datawarehouse guide:
Note that you cannot use any table that uses transparent data encryption as a source table for synchronous Change Data Capture. Asynchronous Change Data Capture supports transparent data encryption if both the source and staging databases have COMPATIBLE set to 11 or higher.
Change Data Capture will not encrypt the data in the change table. A user who wants to encrypt a column in the change table can manually use an ALTER TABLE statement to encrypt the column in the change table.
Oracle Streams provides database support for a wide variety of data types, but does not provide native support for data movement of some advanced data types. However, by using Extended Data type Support (EDS), you can take advantage of the flexibility of Streams to accommodate several more advanced data types. See
http://www.oracle.com/technology/deploy/availability/pdf/maa_edtsoverview.pdf
Note 238455.1 Streams DML Types Supported and Supported Data types http://download.oracle.com/docs/cd/B19306_01/server.102/b14229/strms_over.htm
|
| Reference | Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) Part Number B14223-02
Chapter 16 Change Data Capture
http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_cdc_cookbook_0206.pdf
http://www.oracle.com/technology/oramag/oracle/03-nov/o63tech_bi.html http://download.oracle.com/docs/cd/B19306_01/server.102/b14223.pdf
|
|
No comments:
Post a Comment