Data Replication with Advanced Replication Oracle Streams John

Data Replication with Advanced Replication & Oracle Streams John Abrahams Technology Sales Consultant Oracle Nederland

What is Replication Ÿ Multiple copies of data at different sites Ÿ Increased availability Ÿ Manual data replication implementations – – – Export/Import CREATE TABLE AS SELECT FROM REMOTETABLE COPY

Oracle 9 i Features for Information Sharing Ÿ Features Introduced In Prior Releases – – Data Guard – Physical Standby Database Advanced Queuing -- Message Queuing Advanced Replication -- Replication of Data Change Data Capture -- DW Loading Ÿ Features available in Oracle 9 i. R 2 – – Oracle Streams -- a comprehensive information sharing solution Data Guard -- Logical Standby Databased on Oracle Streams

Oracle Advanced Replication

Oracle 9 i Replication Technology Ÿ Oracle 9 i provides built-in technology to create and manage replicated environments – Integrated, no add-ons, no special commands – Managed with Oracle Enterprise Manager Ÿ Advanced data replication technology – Bi-directional, all copies potentially updatable – Automatic conflict detection and resolution – Tables and supporting objects – Full copies or subsets – DDL (schema changes) as well as DML (transactions) – Continuous or on demand replication

Architectural Overview Single master replication l master materialized view Single, updatable master Multiple updatable or read-only materialized views (snapshots) – l l Full copies or subsets of master All conflicts resolved at parent site of materialized view Oracle 9 i standard edition

Architectural Overview Multiple master replication M View master Master M View master l M View Support for multiple, n-way connected, updatable masters l Improves scalability and availability l Oracle 9 i Enterprise Edition

Master vs. Materialized View Replication Multimaster Materialized View • Server-to-Server • Mass Deployment • HQ and regional offices • Large number of small remote sites • Continuous, near realtime data propagation • Periodic Bulk Transfer • Updates per transaction • Final Values of Changed Rows Only • Full Copies • Subsets or Full Copies

Advanced Replication Key Features Ÿ Near real-time replication (multimaster) – – Parallel data propagation Multiple, pre-defined conflict resolution methods Ÿ Mass deployment (materialized views) – – – subquery materialized views deployment templates multitier materialized views Ÿ Oracle Enterprise Manager for configuration and administration Ÿ Specialized options: procedural and synchronous replication

Near Real-time Replication Ÿ Benefits: – availability, scalability, failover Ÿ Uses: – Telesales, support Ÿ Requirements: – – Efficient data capture and storage Efficient data propagation Continuous data propagation Automatic resolution of conflicting updates

Architectural Overview Multimaster l l master Groups of related schema objects kept in synch at multiple locations Sites communicate by broadcasting changes to all other sites

Replication Objects Ÿ Database object replication to multiple servers Ÿ The following database objects can be replicated – – – Tables, Indexes, Views, Synonyms, Triggers Packages, Procedures, Functions Advanced Data Types Ÿ User-Defined Types, Indextypes Ÿ Tables with column objects, object tables Ÿ Nested Tables, Varrays

Efficient Data Capture and Storage Internal Trigger Updates Ÿ Committed changes are added to queue for later propagation to remote sites – – Source tables Advanced Queue – Enqueued using advanced queueing mechanism Captured and applied using internal C code Minimum data needed to apply change is captured

Efficient Data Propagation If. Ifupdate… If. Ifdelete… If. Ifinsert. . . internal procedure background process Ÿ Queued changes are pushed to remote sites in parallel for improved performance – – – Single parallel stream Maintains transactional consistency Automatically detects transaction dependencies

Parallel Data Propagation Ÿ Dependencies – – Transaction “B” is dependent on transaction “A” if “B” accesses data “A” has updated Dependency detection is dynamic and lightweight Ÿ Ordering – – Dependent transactions are propagated in dependency order All other transactions are propagated in parallel

Continuous Propagation Ÿ Changes can be continuously propagated or at a fixed interval, fixed time, or on demand Ÿ Different intervals can be used for each location Ÿ Different intervals can be used for each group Ÿ Dynamic Views to monitor propagation activity and throughput

Automatic Conflict Resolution Ÿ Automatic conflict detection with userselectable conflict resolution routines – latest timestamp, earliest timestamp, maximum or minimum value, overwrite, priority group, discard, site priority, average, or additive Ÿ User-definable resolution routines Ÿ Detection and resolution based on column groups

Mass Deployment Replication Ÿ Benefits: – disconnected, updatable Ÿ Uses: – field sales, field service Ÿ Requirements: – – – Easily define unique subsets Easily deploy to 100’s of sites Support mobile users Ÿ refresh on demand

Architectural Overview Materialized Views l Updatable or read-only copy of a table, or portion of a table, at a point in time SELECT… FROM. . . – Full transactional consistency – Efficient, batch-oriented refresh l Master table Materialized View log – network l Scheduled or on demand Refresh groups preserve master-detail relationships Updatable materialized views use deferred transactions to push changes to master

Managing Advanced Replication

Specialized Options Ÿ Synchronous Replication – – always up to date, no conflicts slower response, network dependent Ÿ Procedural Replication – – – faster for batch processing must be serialized, best done in off hours useful for purging

Replication Summary Ÿ Full and subset Ÿ Near realtime or on demand Ÿ Graphical administration tool Ÿ Sophisticated Functionality – – – – Efficient data capture and storage Parallel data propagation Automatic conflict detection and resolution Subquery subsetting Deployment templates Multitier materialized views Specialized options

Oracle Streams

Oracle Streams – Unified Messaging and Data Movement Standby Oracle Streams Update Standby Oracle Advanced Intelligent, Unified, Queuing (AQ) Time-Ordered Information Stream Oracle Advanced Replication Feed Data Warehouse Messaging Replication Data Warehousing

Oracle Streams Ÿ A new solution for information sharing Ÿ Provides a unified architecture for all information sharing solutions – – uniquely flexible replication message queuing data warehouse loading event management and notification Ÿ The foundation of Data Guard Logical Standby Database

Streams Basic Elements Capture Staging Consumption Ÿ Three basic elements in each database – – – Capture Staging Consumption (apply)

Multi-Database Streams Capture Staging Consumption Ÿ A stream can contain multiple elements from multiple databases Ÿ Events flow between staging areas

Capture Ÿ Streams captures events – – Implicitly: log-based capture of DML and DDL Explicitly: Direct enqueue of user messages Ÿ Captured events are published in the staging area

Log-Based Change Capture Ÿ Low overhead, low latency change capture – – – Changes to the database are written to the online redo log Oracle Streams can extract changes from the log as it is written (mining the active log) Changes are formatted as a Logical Change Record (LCR), a SQL like representation of the change

Direct Enqueue Capture Ÿ User applications can explicitly enqueue user messages into the staging area – – – Multiple open interfaces supported: JMS, C, PLSQL, SOAP (XML/HTTP), XML/SMTP Allows applications to communicate at a higher level Allows users to introduce events into the stream from non-Oracle systems

Staging Ÿ Streams publishes captured events into a staging area – Implemented as a queue – Supports for new self-describing type “any” datatype allows a single staging area to hold any type of data – All events, LCRs and user-messages, can be staged in the same queue – Messages remain in staging area until consumed by all subscribers

Staging Area Propagation Ÿ Other staging areas can subscribe to events Staging – Propagation – Staging in same database in a remote database Ÿ Events can be routed through a series of staging areas

Transformations Staging Ÿ Transformations can be performed – – – as events enter the staging area as events leave the staging area as events propagate between staging areas Ÿ Transformation examples – change format, data type, column name, table name

Consumption Ÿ Staged events are consumed by subscribers – Implicitly: Apply Process Ÿ Default Apply Ÿ User-Defined Apply – Explictly: Application dequeue via open interfaces Ÿ JMS, C, PLSQL, SOAP (XML/HTTP), XML/SMTP

Default Apply Consumption Ÿ The default apply engine will directly apply the DML or DDL represented in the LCR – – apply to local Oracle table apply via DB Link to non-Oracle table Ÿ Automatic conflict detection with optional resolution – unresolved conflicts placed in exception queue Ÿ Parallel apply maximizes concurrency

User-defined Apply Consumption Ÿ User-written custom apply functions Ÿ Written in PL/SQL, Java, C, C++ Ÿ Uses: – – custom transformations column subsetting normalizing or denormalizing data populating related fields or tables

Rule-based Subscription Ÿ Consumers subscribe to published events Ÿ Content-based subscriptions limit delivered events to those meeting the subscription criteria Ÿ Rules govern capture, staging, and consumption Publish UPDATE EMP. . . Rules Engine WHERE Subscribe OBJECT = ‘EMP’ UPDATE EMP. . . Staging Area

Directed Networks. INSERT … VALUES (‘EUROPE’, ’ ITALY’) NY (master) Ÿ Propagation independent of Apply Ÿ Rules-based subscription determine if event is locally applied – – London (subset) London applies UK only Ÿ WAN Friendly – EUROPE Send once, fan out NY-->London, London-->Milan, London-->Paris ITALY Milan (subset) FRANCE Paris (subset)

Heterogeneous Support. LCR or user message Ÿ Oracle to non-Oracle apply via gateway – Apply process on Oracle node applies change Ÿ Non-Oracle to Oracle change capture supported via explicit enqueue of LCRs Ÿ Message Gateways – – MQ Series Tibco Gateway Message Gateway Sybase MQ Series

Streams Deployments Ÿ Streams can be deployed to meet a variety of information sharing requirements – – – Replication Data Warehouse Loading Event Notification Message Queuing Data Guard Logical Standby Database

Replication Ÿ Streams asynchronously maintains multiple copies of objects via automatic apply – Identical objects – Related via a transformation or function Ÿ Streams automatically captures, propagates, and applies DML and DDL changes – Detects and optionally resolves conflicts Ÿ Supports flexible data movement and subsetting Ÿ Gateways and APIs for heterogeneous support Ÿ Compatible with Materialized Views

Replication Ÿ Benefits: – No quiesce for DDL – Lower overhead on production system – Reduced network traffic – Flexible configurations Log-based Capture Stage Default Apply Propagation Log-based Capture Stage Default Apply

Data Warehouse Loading Ÿ Streams can load data warehouse staging areas and Operational Data Stores – – Updates captured from a production system Messages and business events from a process flow Ÿ Supports continuous or batch loading Ÿ Automatically transforms data to appropriate format and schema during Operational Data Store load

Data Warehouse Loading Production Database Ÿ Benefits: – – – low overhead Log-based Stage Capture automatic transformation Propagation near real-time loading of operation data User-defined Stage stores Apply Staging Table or ODS

Event Notification Ÿ Streams can notify subscribers that events of interest have occurred – – – Pager notification of flight delays (Orbitz) Notification of price drops (CNET Shopper) Notification to sales manager of Gold Customer purchase (CRM App) Ÿ Streams can evaluate DML events and send notifications to applications that send emails, page users, etc – Users get information they want

Event Notification Ÿ Benefits: – – scalable reduced custom development Log-based Capture Stage Explicit Dequeue

Message Queuing Ÿ Streams can be deployed as an enhanced database integrated message queuing solution – – – Point-to-point messaging, publish and subscribe Single data, security and transactional model for database and message queuing operations Centrally managed and multi-consumer queues to simplify configuration Content-based subscriptions, internet access Automatic dequeue to server-run user function Automatic transform DML/DDL into messages

Message Queuing Ÿ Benefits: – Reduced development costs – Easy database integration – Single development, operational, security model – Reliability and integrity of database Source Database Explicit Enqueue Stage Propagation Stage Explicit Dequeue Destination Database

Data Guard Logical Standby Database Ÿ Multiple copies of data protects from human and data errors, and disasters Ÿ Special case of replication – – Entire database (by default) One direction only Ÿ Streams supports reporting from standby as updates are applied Ÿ Data Guard adds higher level interface, tailored GUIs and broker

Data Guard Logical Standby Database Production Database Ÿ Benefits: – Open while protecting data – Support near real-time reporting from standby – Protects from physical corruptions – Additional indexes and materialized views Remote Logging Log-based Capture Default Apply Logical Standby Database

Other Oracle 9 i Information Sharing Features Ÿ Advanced Replication – provides compatible replication with Oracle 8, 8 i, and 9 i databases – Migration path to Oracle Streams in future release Ÿ Advanced Queuing – Compatible with Oracle Streams – Most functionality offered in Streams – API’s retained for compatibility – Migration path to Oracle Streams in future release

Other Oracle 9 i Information Sharing Features Ÿ Data Guard Physical Standby Database – – Uses media recovery mechanism to apply changes to database Creates an exact copy of the production database Ÿ block-for-block copy Ÿ Same version of Oracle, same hardware/software architecture – – Supports very high transaction workloads Will coexist with Data Guard Logical Standby Database

Summary Capture Staging Consumption Ÿ Oracle Streams unifies all enterprise information into a single Stream – Unifies database, messaging, replication, publish/subscribe APIs and capabilities Ÿ Allows deployment of a variety of solutions Ÿ Provides a single, unified solution to the problem of Information Sharing