Migration Oracle Integration Solutions DOAG Regionaltreffen Südbayern/München 14.05.2007 gsehrt(at)datamirror.com Georg Sehrt
Agenda
Migration Beispiele Szenarien Lösungen
DataMirror iReflect for Oracle DataMirror Transformation Server V6 for Oracle
www.datamirror.com
2
Migration
Migration (Informationstechnik) aus Wikipedia, der freien Enzyklopädie Unter Migration versteht man im Rahmen der Informationstechnik den Umstieg eines wesentlichen Teils der eingesetzten Software beziehungsweise den Transfer von Daten aus einer Umgebung in eine andere, sowie die Umstellung von Hardware einer alten Technologie in neue Technologien unter weitgehender Nutzung vorhandener Infrastrukturen. Die häufig eng miteinander zusammenhängenden Prozesse lassen sich in Softwaremigration und Datenmigration aufteilen. Diese Umstellung kann Probleme bereiten. Voraussehbare Folgen werden als missbilligt (engl: deprecated) markiert.
www.datamirror.com
Unterscheidung
1 Softwaremigration Betriebssystem Datenbankengine Anwendung
2 Datenmigration Zeichensätze Strukturänderungen
3 Hardwaremigration Plattformwechsel
4 Umstellung auf neuere Schnittstellen und Techniken DW, BI, SOA etc.
www.datamirror.com
4
Ziele
1:1 Umsetzung Datenveränderungen Strukturveränderungen Minimierung der Unterbrechung Test / Abnahme im laufenden Betrieb Audit - Protokoll
www.datamirror.com
5
Ziele >Lösungen
innerhalb Oracle permanente Spiegelung der Änderungen
Transformationen Strukturveränderungen heterogenes Umfeld (non Oracle) biderektionale Spiegelung
www.datamirror.com
6
Beispiel Migration / Rolling Upgrade
„ALT“ -System
„NEU“ -System
1. Refresh…………………….. 2. Delta sammeln…………… spiegeln……………………… 3. 4. www.datamirror.com
Umschalten
nach erfolgreichem Anlauf Spiegelung stoppen
* 7
Beispiel Migration UNIX > LINUX
„ALT“ -System
1. Refresh 2. Delta spiegeln 3. 4. nach erfolgreichem Anlauf Spiegelung stoppen
www.datamirror.com
„NEU“ -System
Umschalten
8
Beispiel UPGRADES (CPU) @ Zerodowntime
1. Refresh 2. 3. 4.
Delta spiegeln Platten Umschalten nach Anlauf der Anwendung Spiegelung stoppen
www.datamirror.com
9
ABLAUF
Refresh Delta spiegeln, kont. Spiegelung Umschalten nach erfolgreichem Anlauf Spiegelung stoppen ANWENDUNG kurze Unterbrechung für Umschaltung
www.datamirror.com
10
Architektur
www.datamirror.com
11
Architektur
www.datamirror.com
12
Flexible Implementation
Feature: Extreme Scalability www.datamirror.com
Business Benefits: Non-intrusive / Flexible Arch., Geographic Dispersion 13
Workload Balancing
Feature: Workload Distribution www.datamirror.com
Business Benefit: Extended Resource Capabilities 14
Selectable Replication
Replicate All…Or Selective
Feature: Selective Data / Object Replication www.datamirror.com
Business Benefit: Flexible to Business Needs 15
GUI Administration
Feature: Event Driven JAVA GUI
www.datamirror.com
Business Benefits: Easier / More Efficient Admin. 16
Java-Based Admin GUI
Feature: Event Driven Administrator
www.datamirror.com
Business Benefits: Easier / More Efficient Admin.
17
Digital Graphical Monitor
Feature: Digital Monitor
www.datamirror.com
Business Benefits: Easier / More Efficient Monitoring 18
Feature Summary Architected for Oracle 9i ^ Log Based Transaction Aware Replication Real Time Auto Registration DDL & DML Support Refresh and Active Mirroring Modes Fault Tolerant Architecture Resilient Communications Infrastructure Selectable Replication Supports Master to Master Data and Definitional Sync Checks
www.datamirror.com
Request Point Exits Secondary System Remains Open Manual & Auto Fail-Over Alarms, Alerts & Thresholds Java Based GUI Administrator One-View Workflow Monitor
19
PAUSE Georg Sehrt
Szenarien
www.datamirror.com
21
Beispiel Migration UNIX > LINUX
„ALT“ -System
1. Refresh 2. Delta spiegeln 3. 4. nach erfolgreichem Anlauf Spiegelung stoppen
www.datamirror.com
„NEU“ -System
Umschalten
22
Beispiel Anwendungs & Plattform- Migration
1. Refresh 2.
Delta spiegeln Gegenrichtung spiegeln 3. Umschalten 4. nach erfolgreicher Migration Spiegelung stoppen
www.datamirror.com
23
DataMirror Transformation Server High performance, peer-to-peer data integration solution Captures, transforms, and flows data in real-time between DB2 UDB, Microsoft SQL Server, Oracle, Sybase, Teradata, and XML, across multiple computing platforms Creates live data flows that enable a range of business applications:
business intelligence customer relationship management e-Business enterprise application integration business activity monitoring
www.datamirror.com
24
Replication Modes: Refresh
Replication Log Continuous (Real Time)
Refresh
Push Engine
Net Change (Periodic)
Database Table Refresh (Full Copy)
www.datamirror.com
25
Replication Modes: Continuous Mirroring
Replication Log Continuous (Real Time)
Scrape
Push Engine
Net Change (Periodic)
Database Table Refresh (Full Copy)
www.datamirror.com
26
What is Transformation Server?
0110
1010
www.datamirror.com
27
Transformation Server Components
Multi-Platform Support www.datamirror.com
28
Transformation Server Architecture
www.datamirror.com
29
TS Source Architecture (Detail)
www.datamirror.com
30
Transformation Server Target Architecture Optional Parallel Processes For Scalability
Standard DML Apply Audit Apply Adaptive Apply Row Summarization Row Consolidation
www.datamirror.com
*Refresh Can be SQL*LDR Direct Path, Conventional Path Load, or Standard SQL DML Operation
31
Modes of Replication
www.datamirror.com
32
Filtering CUST_NO
L_NAME
F_NAME
PHONE
REP_NO
58699
Smith
John
404-555-3874
45
37283
Duggan
Ira
613-555-8367
25
89863
Quinn
Fran
905-555-1296
11
89732
Muntz
Muntz
704-555-2738
25
ROW SELECT
Row filtering allows you to select rows
REP_NO = 25
Column filtering allows you to select columns
www.datamirror.com
CUST_NO
L_NAME
F_NAME
REP_NO
37283
Duggan
Ira
25
89732
Muntz
Josie
25
33
Transformations EMP
LAST
FIRST
HIRE_DATE
STAT
SALARY
MAX
1234
Moreiro
Nicole
01/05/97
A
$55,000
$60,000
2345
Ellison
Val
04/12/97
I
$40,000
$50,000
Increase Field Size
Concatenation
Century Dates
Transform Fields
Derived Fields
EMP_ID
FULL_NAME
HIRE_DATE
STATUS
%SALARYMAX
001234
Nicole Moreiro
01/05/1997
Active
92%
002345
Val Ellison
04/12/1997
Inactive
80%
www.datamirror.com
34
Joins at Publisher CUSTOMER TABLE
PUBLISHER ORDER TABLE Order Number
Order Date
Transporter Code
Customer Code
ORD001
12/3/99
TRA001
CUS001
Cus_ID
Customer Name
Customer Address
CUS001
ABC Ltd.
London
TRANSPORTER TABLE Courier Code
Transporter Name
TRA001
AK Road Masters
SUBSCRIBER ORDER TABLE
www.datamirror.com
Order Number
Order Date
Transporter Name
Customer Name
Customer Address
ORD001
12/3/99
TRA001
ABC Ltd.
London
35
Derived Expressions
JOURNAL CONTROL COLUMNS
DERIVED EXPRESSIONS
----------------------------------------------------------------------&CCID An identifier for the transaction with the update. &CNTRRN Source table relative record number &CODE Always “U” for refresh. Always “R” for mirror. &ENTTYP Indicates the type of update. &JOB The name of the source job that made the update. &JOBNO The operating system user Id of the update process. &JOBUSER The operating system user at the time of the update. &JOURNAL The name of the journal, as described in Properties. &JRNFLG Indicates if before image is present &JRNLIB The name of the journal schema. &LIBRARY The source table schema or its alias. &MEMBER The source table name or its alias. &PROGRAM The name of source program that made the update. &OBJECT The source table name or its alias. &SEQNO The sequence number of this update in the journal. &SYSTEM The hostname of the source system &TIMSTAMP Time of the update or refresh. &USER The user ID which made the update.
------------------------------------------------------%BEFORE Net change (before image) %CURR Current image %CONCAT Concatenation %REPLACE Character substitution %SUBSTRING Substring %LOWER Lower case character conversion %UPPER Upper case character conversion %PROPER Proper case character conversion %LTRIM Left Trim blank characters %RTRIM Right Trim blank characters %TOCHAR Convert to character %TONUMBER Convert to number %TODATE Convert date format %TOTIME Convert time format %CENTURY Add a 2 digit century to your date %IF Conditional %VAR Initialise a result variable %USER Call user exit program %GETCOL Get a column from another table %STRPRC Call user exit stored procedure
www.datamirror.com
36
Complex Transformations Summarization Real-time addition and subtraction of roll-ups
Row Consolidation Used for merging rows in different tables together or for applying changes based on a lookup table
Adaptive Apply Used where source data may not be consistent with target data
Derived Fields Based on data in many columns from many tables, perform mathematical operations or concatenation
www.datamirror.com
37
User Exits
www.datamirror.com
38
Enterprise Administrator
Java-based web-enabled GUI Single unified point of administration Standardize and manage your metadata Open Application Programming Interface (API) Command-line Interface for Scheduled and Batch executions Integrated support for alarms and alerts EA Monitor Remote Administration
www.datamirror.com
39
EA Monitor Visualize complex networks through user-defined network diagrams Evaluate the health of the network through operational status metrics Troubleshoot issues and finetune performance using latency metrics
www.datamirror.com
40
Vielen Dank Fragen … gerne gsehrt(at)datamirror.com Georg Sehrt
