Transcript Boeing Template For PowerPoint 2000

Page 1
Session #: 36637
Data Integration Using
Oracle Streams
A Case Study
Steven Melton
Sr. Oracle DBA
Database Design and System Support
Information and Data Services
Boeing Commercial Airplane
Data Integration Issues
• Different architectures and data models
• Disparate and autonomous nature of
applications
• Data distribution among different geographic
locations
Streams Overview
•
•
•
•
Page 5
A stream is a flow of information either within
a database or from one database to another.
Streams is a set of processes and database
structures that allow sharing of data and
messages in a data stream.
The unit of information put into a stream is
called an event.
• Data or DML changes, formatted as an LCR
• User-created messages
Queues are used to stage and propagate
events.
Streams Basic Elements
Capture
Staging
Consumption
Logical Change Record
(LCR)
• An LCR is an object with a specific format that
describes a database change. LCRs are of two
types: row LCRs and DDL LCRs
• A row LCR describes a change to the data in a
single row or a change to a single LOB column
• A DDL LCR describes a data definition
language change
Page 7
Logical Change Record
(LCR) cont’d
• Each LCR (DDL or DML) contain the following
main information
• The name of the source database where the change
•
•
•
•
Page 8
occurred
The type of DDL/DML statement: Insert/Update/Alter
table
The schema owner name
The database object name
The SCN when the change was written to the redo
log
Rules
• Control which information is to be shared
• Control where to share information
• Evaluated by a rules engine (built-in)
• Grouped together into rule sets
• A rule can be in one rule set, multiple rule sets,
or no rule sets.
• User application & Oracle features (Streams)
can be clients of the rules engine
Rule Condition
• A rule condition combines one or more
expressions and operators and returns a
Boolean value (TRUE, FALSE, or NULL).
Examples:
department_id = 30 OR job_id = ‘PR_REP’
is_manager(:employee_id) = ‘Y’
Streams Transformation
• Transformations can be performed
• as events enter the staging area
• as events leaving the staging area
• As events propagate between staging areas
• Transformation examples
• Change format, data type, column name, table
name, schema owner name, add columns, remove
columns
Existing Environment
• The target database is part of a 24x7 B2B
portal
• 5x8 source database
• Different database schemas
MANUAL CODE
(TBL_MANUAL_
CODE)
MODEL
(TBL_MODEL)
MODEL /
MANUAL
(TBL_MODEL_
MANUAL)
DOCUMENT
(TBL_
DOCUMENT)
MANUAL CODE
(PRODUCT_
TITLE)
MODEL
(MODEL)
SALES ORDER
(TBL_NOTICE_
SALES_ORDER)
SALES ORDER
(SO_DATA)
PURCHASE
ORDER
(TBL_NOTICE_
PURCHASE_
AUTHORITY)
PURCHASE
ORDER
(PO_DATA)
DOCUMENT
(PRODUCT)
CUSTOMER
DISTRIBUTION
(TBL_SHIP_
COMMIT)
CUSTOMER
DISTRIBUTION
(S_INFO)
HIERARCHY
DISTRIBUTION
PLAN
(TBL_NOTICE)
Parent
Child
TRANSACTION
DISTRIBUTION
PLAN
DETAILS
(TBL_NOTICE_
ITEM)
IDENTIFICATION
REFERENCE
DISTRIBUTION
PLAN
(LINE_ITEM)
MANUAL CODE
(TBL_MANUAL_
CODE)
MODEL
(TBL_MODEL)
MODEL /
MANUAL
(TBL_MODEL_
MANUAL)
DOCUMENT
(TBL_
DOCUMENT)
MANUAL CODE
(PRODUCT_
TITLE)
MODEL
(MODEL)
SALES ORDER
(TBL_NOTICE_
SALES_ORDER)
SALES ORDER
(SO_DATA)
PURCHASE
ORDER
(TBL_NOTICE_
PURCHASE_
AUTHORITY)
PURCHASE
ORDER
(PO_DATA)
DOCUMENT
(PRODUCT)
CUSTOMER
DISTRIBUTION
(TBL_SHIP_
COMMIT)
CUSTOMER
DISTRIBUTION
(S_INFO)
HIERARCHY
DISTRIBUTION
PLAN
(TBL_NOTICE)
Parent
Child
TRANSACTION
DISTRIBUTION
PLAN
DETAILS
(TBL_NOTICE_
ITEM)
IDENTIFICATION
REFERENCE
DISTRIBUTION
PLAN
(LINE_ITEM)
Business Constraints
• Target databases contain only the completed
publication distribution requirement.
• Cannot maintain target tables in source
database.
• Cannot maintain source tables in target
database.
• Cannot apply changes at target database
directly from the source database.
Good News
• Set of common identification elements
• Relatively low change volume from daily
operations
Project Objectives
• Provide near real-time data to TMT by
eliminating nightly batch file
• Implement Oracle Streams to capture, modify
and propagate specific DML changes
• Ensure that Streams can handle the
occasional data cleanup sweep
Transformation Scenarios
• One-to-one match at table level.
• Data from two source tables have to be
combined to correspond to one target table
• Data from one source table has to be spread
among two target tables
• A master-detail combination reversed at the
target
• Create LCR
Solution
•
•
Configure source database to -
• capture row LCRs into a queue
• use DML_Handlers to modify the required LCRs
•
– add/delete the necessary columns
enqueue the modified LCRs
Configure target
•
•
•
employ apply to de-queue
set appropriate instantiation SCN
use DML_Handlers to re-modify the required
LCRs to match target data model
Source Environment
• Two streams queues
• Log based capture
• User enqueued modified LCRs
• One capture process
• One apply process
• Set of DML Handlers for required LCR
modifications – add/delete required columns
• A function to check for existence of detail rows
Source
Capture
Staging
LCR Queue
UE Queue
Apply
strmadmin.capture_queue
Apply Process
Capture Process
table_dml_handler
PL/SQL Procedure
strmadmin.user_queue
Page 23
Customizations
• Master-Detail modifications
• Need to use a DML Handler
• DML Handlers to add/drop columns
• Existence of detail rows
• A function that checks for the required data
• A rule that evaluates to TRUE on the master
• Tracking table
• Procedure to create LCR
• LCR enqueue procedure
Target Environment
• Set the instantiation SCN
• SET_TABLE_INSTANTIATION_SCN=‘1’
• One apply process
• Associated DML_Handlers for structure
modifications
• Ensure constraints are set as deferrable
Target
Staging
Queue
Apply
strmadmin.streams_queue
Apply Process
table_dml_handler
Table
Page 27
PL/SQL Procedure
Helpful Suggestions
• Analyze and record the necessary rule
conditions and potential handlers.
• Develop a checklist of all the elements that
need to be completed
• Supplemental Logging
• DML/DDL Handlers
• Re-location of Log Miner tables
• Rules, Rule sets
• Etc.
Page 28
Reminder –
please complete the
OracleWorld online session
survey
Thank you.
Page 29