1 / 31

Oracle Change Data Capture

Oracle Change Data Capture. Jack Raitto, Development Manager Oracle NEDC NYOUG Long Island SIG October 7, 2004. Capture your change data for FREE!*. * Zero additional license cost over Oracle10g EE Virtually zero source system processing cost. What is Oracle CDC?.

jana
Download Presentation

Oracle Change Data Capture

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Change Data Capture Jack Raitto, Development Manager Oracle NEDC NYOUG Long Island SIGOctober 7, 2004 Oracle Corporation

  2. Capture your change data for FREE!* * Zero additional license cost over Oracle10g EE Virtually zero source system processing cost Oracle Corporation

  3. What is Oracle CDC? • Captures change data from operational system(s) as it occurs • Part of Extract / Transform / Load (ETL) process for DSS / Data warehouse, potentially other applications • Optimizes the extract phase • Unleashes SQL power for transformations • Provides management framework for change data Oracle Corporation

  4. How was it done before (old way)? Oracle Corporation

  5. CDC Advantages • Built in, custom fit, evolves with the database • Delivers change data when you need it, where you need it • Offers several tradeoffs between timely change delivery vs. source system overhead (sync, async hotlog, async autolog, etc.) • Assumes complete change management responsibility Oracle Corporation

  6. CDC Advantages (concl.) • Captures all change data along with transaction information – see all changes a given transaction made and who made them • Transactional consistency for changes across multiple source tables is guaranteed • Transparently coordinates sharing of change data across users and applications • You don’t need rocket scientists on your staff! Oracle Corporation

  7. CDC Configurations Oracle Corporation

  8. How CDC Works: Sync CDC • Uses internal triggers to capture before and/or after images of new and updated rows • Has the same performance implications as capture via user triggers • Delivers change data in real-time • Uses the same interface as async CDC Oracle Corporation

  9. Synchronous CDC HotLog Combined Source / Operational BI System CDC Change Tables ETL Process Upsert to Load Dimension Tables Customer Triggers Direct Path Insert to load Fact Tables CDC Order Oracle Corporation

  10. How CDC Works: Async CDC • Relational interface to Streams • Prepackaged Streams application • Asynchronously captures change data from redo/archive logs • Presents relational interface to change data stream • Can operate on source system (hot log) or staging system (auto log) Oracle Corporation

  11. Foundations of Async CDC Change capture Change management Warehouse loading Async CDC Replication Message queuing Warehouse loading Event notification Data protection Streams Redo log inspection Debugging Auditing Reversing transactions LogMiner Oracle Corporation

  12. Asynchronous CDC HotLog Combined Source / Operational BI System CDC Change Tables ETL Process Upsert to Load Dimension Tables Customer LogMiner Active Redo Log Streams Direct Path Insert to load Fact Tables CDC Order Oracle Corporation

  13. Asynchronous CDC AutoLog Data Warehouse / Staging System Source Database CDC Change Tables ETL Process Upsert to Load Dimension Tables Customer LogMiner Streams Redo Logs Direct Path Insert to load Fact Tables CDC Order Arch Process Archived Redo Logs Oracle Corporation

  14. Using CDC: Publish/Subscribe • Publisher supplies, subscribers consume change data • Model allows sharing of change data across users and applications • Coordinates retention / purge of change data • Prevents application from accidentally processing change data more than once • Guarantees transactional consistency of change data across source tables via change sets Oracle Corporation

  15. Using CDC: Publish/Subscribe Subscriber 1 Subscription Publisher Change Data Publication Subscriber 2 Subscription Oracle Corporation

  16. Publisher Concepts • Change source • Defines the source system to CDC • Change set • Collection of source tables for which transactionally consistent change data is needed • Change table • Container to receive change data • Is published to subscribers Oracle Corporation

  17. Publisher Concepts Source Database: HQ Staging Database: DW Change Source: HQ_SRC Source table: sh.salesPROD_IDCUST_IDPROMO_IDAMOUNT_SOLD QUANTITY_SOLD Change Set: SH_SET Change table: sales_ctPROD_IDCUST_IDPROMO_IDAMOUNT_SOLD Source table: sh.promotionsPROMO_IDPROMO_SUBCATPROMO_CAT PROMO_COST Change table: promo_ctPROMO_IDPROMO_SUBCATPROMO_CAT Oracle Corporation

  18. Publish Package DBMS_CDC_PUBLISH CREATE / ALTER / DROP_AUTOLOG_CHANGE_SOURCE CREATE / ALTER / DROP_CHANGE_SET CREATE / ALTER / DROP_CHANGE_TABLE PURGE PURGE_CHANGE_SET PURGE_CHANGE_TABLE DROP_SUBSCRIPTION Oracle Corporation

  19. Using Change Data: Subscribers • The subscriber creates a subscription from an available publication • The subscription provides a moving window (view) to the change data • Subscriptions go against a single change set and are therefore transactionally consistent • When all subscribers have advanced past old change data, CDC automatically and efficiently purges Oracle Corporation

  20. Subscriber Concepts Staging Database: DW Subscription: sales_promo_list Change Set: SH_SET Publication on : sh.salesPROD_IDCUST_IDPROMO_IDAMOUNT_SOLD Subscriber view: spl_sales Publication on: sh.promotionsPROMO_IDPROMO_SUBCATPROMO_CAT Subscriber view: spl_promos Oracle Corporation

  21. Subscriber View Subscriber view: spl_sales Insert Updatebefore Updateafter Insert Insert Delete Insert Insert Oracle Corporation

  22. Subscriber Package DBMS_CDC_SUBSCRIBE CREATE_SUBSCRIPTION SUBSCRIBE ACTIVATE_SUBSCRIPTION EXTEND_WINDOW PURGE_WINDOW DROP_SUBSCRIPTION Oracle Corporation

  23. Security • Sync publisher must have SELECT access to the source table • Async publisher must have EXECUTE_CATALOG_ROLE privilege • Publisher uses GRANT and REVOKE on change tables to control subscriber access Oracle Corporation

  24. Performance Benchmark* • Objectives: • Determine impact on transaction time • Determine latency • Source system: Oracle 10g R1 Beta, SunFire 4800 SMP 8x900Mhz/16GB w/striped 8 x Sun StorEdge T3 arrays (9X36.4MB each) • Customer insurance quote OLTP application run at Oracle, 250 concurrent users / 175 TPS, system “warmed up” (steady state) • Mixture of Inserts, Updates, Deletes, Singleton Selects, Cursor Fetches, Rollbacks / Commits, savepoints • Capture changes on all tables * Your mileage will vary! Oracle Corporation

  25. Transaction Performance Transaction elongated by 10% Relative impact varies depending on other overhead Oracle Corporation

  26. Transaction Performance Transaction elongated by 8% Can reduce elongation by adding RAC nodes / CPUs Oracle Corporation

  27. Transaction Performance Transaction elongation virtually eliminated Change capture processing moved off system Oracle Corporation

  28. HotLog Latency Performance About ½ the change data arrived in 1 second Virtually all the change data arrived in 2 seconds Oracle Corporation

  29. Summary • CDC assumes the burden of change capture for you • Change data is guaranteed consistent and complete • Change data can be shared across users and applications effortlessly • CDC delivers change data where you need it, when you need it, and with minimal overhead Oracle Corporation

  30. For More Information • Oracle Data Warehousing Guide, 10gR1, Chapter 16 • Oracle PL/SQL Packages and Types Reference, 10gR1, packages DBMS_CDC_* • http://www.oracle.com/technology/oramag/oracle/03-nov/o63tech_bi.html • http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_dss_ontime_etl_10gr1_0304.pdf • http://www.rittman.net/archives/000901.html • http://www.nyoug.org/cdc.pdf (Oracle9i) Oracle Corporation

  31. Questions? ? Oracle Corporation

More Related