Jerry Held

1
(No Transcript)
2
Data Replication with Advanced Replication
Oracle Streams John Abrahams Technology
Sales Consultant Oracle Nederland
3
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

4
Oracle9i 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 Oracle9iR2
• Oracle Streams -- a comprehensive information
  sharing solution
• Data Guard -- Logical Standby Database based on
  Oracle Streams

5
Oracle Advanced Replication
6
Oracle9i Replication Technology

• Oracle9i 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

7
Replication Usage Examples

• Move data (such as price lists) locally for
  improved response times
• Offload queries from master site
• Only subsets of data need to be replicated

Information Dissemination
8
Replication Usage Examples

• Same data available at all sites
• Very useful for balancing usage
• Viable failover strategy if one sites fails,
  others remain available
• Updates can be done anywhere

Call Centers
9
Replication Usage Examples

• Data is located close to users at local sites
• Data is consolidated at central site for
  processing and rollups
• Only subsets of data need to be replicated at
  each site
• Updates can be done anywhere

Branch Office Automation
10
Replication Usage Examples

• Mobile users must be able to operate even when
  disconnected from central sites
• Mobile users can replicate only data they need to
  their laptop
• Synchronization can be done when re-connected

Mass Deployment
11
Architectural OverviewSingle master replication

• Single, updatable master
• Multiple updatable or read-only materialized
  views (snapshots)
• Full copies or subsets of master
• All conflicts resolved at parent site of
  materialized view
• Oracle9i standard edition

12
Architectural OverviewMultiple master replication

• Support for multiple, n-way connected, updatable
  masters
• Improves scalability and availability
• Oracle9i Enterprise Edition

13
Master vs. Materialized View Replication
Multimaster
Materialized View
14
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

15
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

16
Architectural OverviewMultimaster

• Groups of related schema objects kept in synch at
  multiple locations
• Sites communicate by broadcasting changes to all
  other sites

17
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

18
Efficient Data Capture and Storage

• Committed changes are added to queue for later
  propagation to remote sites
• Enqueued using advanced queueing mechanism
• Captured and applied using internal C code
• Minimum data needed to apply change is captured

Internal Trigger
Updates
Advanced Queue
Source tables
19
(No Transcript)
20
(No Transcript)
21
Efficient Data Propagation
internal procedure

• Queued changes are pushed to remote sites in
  parallel for improved performance
• Single parallel stream
• Maintains transactional consistency
• Automatically detects transaction dependencies

22
Parallel Data Propagation

• Dependencies
• Transaction B is dependent on transactionA
  if B accesses data A has updated
• Dependency detection is dynamic and light-weight
• Ordering
• Dependent transactions are propagatedin
  dependency order
• All other transactions are propagated in parallel

23
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

24
Automatic Conflict Resolution

• Automatic conflict detection with user-selectable
  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

25
Mass Deployment Replication

• Benefits
• disconnected, updatable
• Uses
• field sales, field service
• Requirements
• Easily define unique subsets
• Easily deploy to 100s of sites
• Support mobile users
• refresh on demand

26
Architectural OverviewMaterialized Views

• Updatable or read-only copy of a table, or
  portion of a table, at a point in time

• Full transactional consistency
• Efficient, batch-oriented refresh
• Scheduled or on demand
• Refresh groups preserve master-detail
  relationships
• Updatable materialized views use deferred
  transactions to push changes to master

SELECT FROM...
Master table
Materialized View
log
network
27
Managing Advanced Replication
28
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

29
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

30
Oracle Streams
31
Oracle Streams Unified Messaging and Data
Movement
Update Standby
Oracle Advanced Queuing (AQ)
Oracle Advanced Replication
Feed Data Warehouse
32
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

33
Streams Basic Elements
Consumption
Staging
Capture

• Three basic elements in each database
• Capture
• Staging
• Consumption (apply)

34
Multi-Database Streams

• A stream can contain multiple elements from
  multiple databases
• Events flow between staging areas

Consumption
Staging
Capture
Consumption
Staging
Capture
35
Capture
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

36
Log-Based Change Capture
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

37
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

38
Staging
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

39
Staging Area Propagation

• Other staging areas can subscribe to events
• in same database
• in a remote database
• Events can be routed through a series of staging
  areas

Staging
Propagation
Staging
40
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

41
Consumption
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

42
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

43
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

44
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

WHERE OBJECT EMP
Subscribe
Publish
UPDATE EMP...
UPDATE EMP...
45
Directed Networks
INSERT VALUES (EUROPE, ITALY)
NY (master)

• Propagation independent of Apply
• Rules-based subscription determine if event is
  locally applied
• London applies UK only
• WAN Friendly
• Send once, fan out
• NY--gtLondon, London--gtMilan,London--gtParis

EUROPE
London (subset)
FRANCE
ITALY
Milan (subset)
Paris (subset)
46
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

Message Gateway
Gateway
Sybase
MQ Series
47
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

48
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

49
Replication

• Benefits
• No quiesce for DDL
• Lower overhead on production system
• Reduced network traffic
• Flexible configurations

Log-based Capture
Default Apply
Stage
Propagation
Log-based Capture
Default Apply
Stage
50
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

51
Data Warehouse Loading
Production Database

• Benefits
• low overhead
• automatic transformation
• near real-time loading of operation data stores

Log-based Capture
Stage
Propagation
User-defined Apply
Stage
Staging Table or ODS
52
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

53
Event Notification

• Benefits
• scalable
• reduced custom development

Log-based Capture
Explicit Dequeue
Stage
54
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

55
Message Queuing
Source Database

• Benefits
• Reduced development costs
• Easy database integration
• Single development, operational, security model
• Reliability and integrity of database

Explicit Enqueue
Stage
Propagation
Explicit Dequeue
Stage
Destination Database
56
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

57
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
Default Apply
Log-based Capture
Logical Standby Database
58
Other Oracle9i Information Sharing Features

• Advanced Replication
• provides compatible replication with Oracle 8,
  8i, and 9i databases
• Migration path to Oracle Streams in future
  release
• Advanced Queuing
• Compatible with Oracle Streams
• Most functionality offered in Streams
• APIs retained for compatibility
• Migration path to Oracle Streams in future release

59
Other Oracle9i 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

60
Summary
Consumption
Staging
Capture

• 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

61
(No Transcript)