Boeing Template For PowerPoint 2000

1
(No Transcript)
2
Data Integration Using Oracle Streams
Session 36637

• A Case Study

3
Steven MeltonSr. Oracle DBA Database Design and
System Support Information and Data
Services Boeing Commercial Airplane
4
Data Integration Issues

• Different architectures and data models
• Disparate and autonomous nature of applications
• Data distribution among different geographic
  locations

5
Streams Overview

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

6
Streams Basic Elements
7
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

8
Logical Change Record(LCR) contd

• Each LCR (DDL or DML) contain the following main
  information
• The name of the source database where the change
  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

9
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

10
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
11
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

12
(No Transcript)
13
Existing Environment

• The target database is part of a 24x7 B2B portal
• 5x8 source database
• Different database schemas

14
(No Transcript)
15
(No Transcript)
16
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.

17
Good News

• Set of common identification elements
• Relatively low change volume from daily operations

18
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

19
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

20
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

21
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

22
Source
LCR Queue
UE Queue
23
(No Transcript)
24
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

25
Target Environment

• Set the instantiation SCN
• SET_TABLE_INSTANTIATION_SCN1
• One apply process
• Associated DML_Handlers for structure
  modifications
• Ensure constraints are set as deferrable

26
Target
Queue
27
Table
28
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.

29
Reminder please complete the OracleWorld
online session surveyThank you.
30
Q

A
31
(No Transcript)