oracle cdc 提取历史数据的方法 变化数据

oracle cdc 提取历史数据的方法 变化数据

CHANGE DATA CAPTURE (CDC) IN ORACLEVenki Krishnababu Senior Oracle DBA Nordstrom IT

oracle cdc 提取历史数据的方法 变化数据

AGENDACDC INTRODUCTION CDC CONCEPTS CDC CASE STUDY CDC PROCESS FLOW CDC PUBLISHER/SUBSCRIBER SETUP CDC BEST PRACTICE DEMO Q&A2

oracle cdc 提取历史数据的方法 变化数据

INTRODUCTIONCDC is an oracle tool which can help to manage data changes and capture them in consistent manner with predefined APIs. CDC is not a development solution to perform any validations or transformation or provide any application specific checks etc. CDC doesn’t require any changes to the existing data model. CDC most commonly used to capture transactional changes from an OLTP system and publish the changes to one or more subscription systems.

oracle cdc 提取历史数据的方法 变化数据

CONVENTIONAL METHOD TO CAPTURE DATA CHANGESCAPTURING DATA CHANGETable DifferencingHeavy resource intensive SQLs Intermediate change values cannot be captured Multiple changes on one transaction cannot be captured

Change Value Based on TimestampPotentially expensive queries against Source Tables. Intermediate change values cannot be captured Multiple changes on one transaction cannot be captured Possibility of missing a changed record during extract Source system have to be design giving consideration to this approach.

Custom Built TriggersCustom Development work. Cost associated with extensive development and testing. Cost proportional to the complexity of the project. If not designed properly can potentially cause performance issues to source system.

oracle cdc 提取历史数据的方法 变化数据

WHAT CDC CAN OFFERCDC 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 5 access to the changed data.

oracle cdc 提取历史数据的方法 变化数据

WHAT CDC CANNOT DO?CDC purely worked based on logged operations, so any nonlogged DML operations are not captured. CDC doesn’t support direct load insert. CDC cannot be implemented on table with TDE (Transparent Data Encryption) enabled. Asynchronous mode capture wont 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.6

oracle cdc 提取历史数据的方法 变化数据

CDC CONCEPTS PUBLISHER/SUBSRIBER MODEL

PUBLISHERTable#1 Changes# 1

SUBSCRIBER

Subscription#1 Subscription#2

Table#2

Changes# 2

oracle cdc 提取历史数据的方法 变化数据

SYNCHRONOUS CDCBased on Triggers Supported in Oracle 9i and later versions Triggers on source database captures the change immediately. Captured data is made part of the source system trans

action. Available with Standard and enterprise edition. Adds overhead to the source system during the capture time. BuiltBuilt-in triggers are automatically created by invoking the CDC APIs.8

oracle cdc 提取历史数据的方法 变化数据

ASYNCHRONOUS CDC (HOTLOG MODE)Changes are captured from redo log files after the DML transaction is completed. Changed data is not part of the source transaction. Minimal latency involved. Minimal Performance overhead to source system. Log writer records the committed transactions to online redo logs. Local Oracle Stream process reads the redo log files and captures the changes to change table.9

oracle cdc 提取历史数据的方法 变化数据

ASYNCHRONOUS CDC (AUTOLOG MODE)Changes are captured from set of redo log files managed by redo transport service. (Part of Data Guard Framework). Autolog Online Mode : Changes are captured from redo log files. Autolog Archive Mode : Changes are captured from archive log files. Changed data is not part of the source transaction. Minimal latency involved. Minimal Performance overhead to source system. If the changes are extracted to a change table in a staging the data is transferred via LAN using Oracle Net. Source and staging database should run same OS and Oracle Version.

oracle cdc 提取历史数据的方法 变化数据

CDC TERMINOLOGYCHANGE SOURCELogical representation of Source Database.

CHANGE SETLogical grouping of Change data. This grouping enables to provide transaction consistent images of multiple change tables in the same set. Change tables within a change set can be joined.

CHANGE TABLEChange data resulting of DML operation are stored in the table. This table acts a container/staging area to stage changed data. Subscription views are built based on Change table.

PUBLISHERPerson who captures and publishes changed data. DBA creates and maintains schema objects make up part of CDC. Usually one publisher per source system.11

oracle cdc 提取历史数据的方法 变化数据

CDC TERMINOLOGY (Contd..) SUBSCRIBERApplications and inpiduals who consume the changed data. Multiple applications can subscribe to the same set of changes.

STAGING DATABASEDatabase to which the captured change data is applied. Source Database can be staging database.

SUBCRIBER VIEWView that specifies the change data from a specific publication in a subscription.

SUBSCRIPTION WINDOWRange of rows in a publication that the subscriber can view through subscriber views.12

oracle cdc 提取历史数据的方法 变化数据

CDC Case StudyCapture Supplier information changes from Inventory system. Near real time Supplier information update. Average few hundred supplier information changes per day. Very little coding effort. Scope is to just capture the changes on supplier master table. CDC ImplementationMode : Synchronous Publisher : 1 Change Set :1 Subscriber :1

oracle cdc 提取历史数据的方法 变化数据

CDC Case Study (Contd..)Change Table

Oracle 9i

Final/DW Tables

Based On Trigger

Transform PL/SQL

OLTP DB

PL/SQL to extract/transform change data Publish/subscribe paradigm Parallel transformation of data Store final processed changed data in staging table. Or extract the change in a transformed form the change table

oracle cdc 提取历史数据的方法 变化数据

CDC CASE STU

DY (Contd..)POSSIBLE FUTURE ENHANCEMENTSUpgrade to Oracle 10g Release 2. Turn on Supplemental logging on Supplier Master. Perform Asynchronous mode data change capture using (Hotlog Mode). Disable synchronous mode data change capture. Implement Asynchronous CDC to establish CIM (Common Information Model) for product.

oracle cdc 提取历史数据的方法 变化数据

CDC SETUP OUTLINEPUBLISHER SETUP: Identify the source tables. Set up a publisher. Create change tables. Optionally setup dedicated publisher and subscriber accounts.

oracle cdc 提取历史数据的方法 变化数据

CDC SETUP OUTLINE (CONTD.)SUBSCRIBER ONE TIME SETUP :Set up a subscriber. Subscribe to the source tables. Activate the subscription.

CYCLIC SUBSCRIPTION PROCESS :Set up the CDC window and extend the window. Consume the changed data using subscriber views. Purge the consumed data window. Repeat the steps in cycle.17

oracle cdc 提取历史数据的方法 变化数据

CDC PROCESS FLOW (OVERVIEW)Create Change Set Create Subscription

Identify Source Table(s)

Activate Subscripition (Create Subscriber View)

Create Change Table(s)

Extend Change Window

Cyclic Process

Grant select privilege on Change Table to Subscribers

Extract Data from CDC Subscriber View

Purge Extract Window

oracle cdc 提取历史数据的方法 变化数据

SUBSCRIPTION WINDOW MOVEMENT

Window#1CSCN$=10 TO CSCN$=20

Window#2CSCN$=21 TO CSCN$=30

Window#3CSCN$=31 TO CSCN$=40

SUBSCRIBER

本文来源：https://www.bwwdw.com/article/tjiq.html