<Insert Picture Here>
Oracle GoldenGate (OGG) Plattformübergreifende Datenreplikation
Joachim Jaensch, Principal Sales Consultant, Business Unit ST-PCM
13.12.2011 – DOAG Reginaltreffen Hamburg / Nord
2
Oracle Replikation – Rückblick
OGG – Einsatzgebiete und Einsatz-Szenarien
Oracle GoldenGate Architektur (OOW Flash File)
OGG – Download und Installation
GoldenGate Initial-Load und Instanziierung
Oracle GoldenGate Checkpoints
OGG – Security Features
Oracle Veridata
Oracle Management Pack for OGG
Die Oracle Strategie
Fragen & Antworten
Agenda
Oracle Replikation – Rückblick
Oracle 7.1 – Advanced Replication (Snapshots, Multimaster Replication)
Oracle 8.1 – Materialized Views ersetzen Snapshots
Oracle 9.2 – Oracle Streams (Replication, Message Queuing)
Oracle 10.2 – Autotuning Streams Pool,
Buffered Messaging, EM Support
Simplified APIs, Deklarative Transformation
Oracle 11.1 – Combined Capture & Apply Streams Advisor & Topology Sychronous Capture Transparent Data Encryption Support Split and Merge of a Streams Destination LCR Tracking
Oracle 11.2 – Compressed Tables Capture & Apply SecureFile LOBs, Statement DML Handlers, Keep Column Declarative Rule- Based Transformation, Automatic Split and Merge of a Streams Destination
„Oracle Streams will continue to be supported, but will not be actively enhanced.“
OGG 10.4 – Oracle on SUSE Linux System z EXCLUDEUSER / ...ID Oracle Spatial Objects Cluster Tables
OGG 11.1 – DDL Support Enhanced Embedded XML in UDTs Extract API for Oracle ASM TDE & TSE New Monitor
Oracle Streams vs. Oracle GoldenGate
* Wenige Fremddatenbanken, nur über Gateway zur Fremddatenbank (Uni-Direktional)
Netzverbindung Quelle-Ziel
Oracle Streams Oracle GoldenGate
DML Änderungen ja ja
DDL Änderungen ja ja
immer
Architektur Oracle Database Server
Prozesse
ja
Flexibilität hoch hoch
in speziellen Fällen
Parallelisierung ja ja
Merkmal
Betriebssystem Prozesse
Prozesse ständig aktiv und kommunizieren
zeitlich voneinander entkoppelt
Heterogene Replikation nein*
Streams und GoldenGate Begriffe
Prozeß Oracle Streams Oracle GoldenGate
Erfassen von Änderungen
Capture
Extract
Ablegen (Zwischenspeichern)
Staging Queues
Trails, Files
Weiterleiten
Propagation
Data Pump
Anwenden der Änderungen
Apply
Replicat
(Prozeß-)Koordinator
Oracle Datenbank
Manager
Datensammler
-
Collector
GoldenGate auch: Extract = Capture und Replicat = Delivery
Oracle – “Excellent Product Viability”
Weight
-ing IBM Oracle Inform-
atica iWay SAP SAS
Micro-
soft
Bulk data movement 55% 5.0 4.5 5.0 4.1 4.6 4.3 4.3
Federated views 15% 3.9 3.0 3.9 3.7 4.2 3.4 2.2
Message-oriented
movement 10% 4.5 3.7 3.3 4.1 3.2 2.0 3.7
Data replication and
synchronization 20% 4.6 5.0 4.5 2.9 4.0 3.0 2.9
Total Rating 18 16.2 16.7 14.8 16 12.7 13.1
Weighted Rating 4.705 4.295 4.565 3.8 4.28 3.675 3.645
Source: Gartner Critical Capabilities for Data Integration Tools: Common Data Delivery Styles, Dec 2010
(showing top 7 of 11 vendors in chart above)
Oracle GoldenGate Gartner 5.0 out of 5.0
Clearly the Industry Leader in
Data Replication and Synchronization
Oracle GoldenGate 11g
New DB/HW/OS/APP
Fully Active Distributed DB
Reporting Database
Data Warehouse
Global Data Centers
ODS Data Integrator
Zero Downtime Upgrade & Migration
Query Offloading, Disaster Recovery
Data Synchronization across the Enterprise
Real-time BI, Operational
Reporting, MDM
Event Driven Architecture,
SOA
Highly Available / Disaster Recovery
Log-based, changed data
Database
Message Bus
Legacy
Message Bus
Low-Impact Real-Time Data Integration & Transactional Replication
Unidirectional Query Offloading
Zero-Downtime Migration
Bi-Directional Hot Standby or
Active-Active for HA
Peer-to-Peer Load Balancing
Multi-Master
Broadcast Data Distribution
Integration/Consolidation Data Warehouse
BPM
BAM
CEP
Data Distribution via Messaging
OGG – Einsatz-Szenarien
Homogen und Heterogene Real-Time Replikation
Zero Downtime Upgrades, Migrationen und Wartungsarbeiten
Migration von Non-Oracle Datenbanken zu Oracle 11gR2 & Exadata
Upgrade Oracle Datenbanken 8i, 9i, 10g to 11gR2
Upgrade/Migration Datenbank Server und/oder Betriebssystem
Datenbank Wartungsarbeiten
AppliKations-Upgrade (Siebel CRM und JD Edwards)
Disaster Recovery für Non-Oracle Databases und zwischen Oracle Datenbank Versionen
7*24 Verfügbarkeit durch Active-Active Konfiguration
Off-Load Reporting für Legacy Systems auf Oracle Datenbank(en) für maximale Leistung und Kostenreduzierung
Oracle GoldenGate – Einsatzgebiete
10
OGG – Architektur (1)
LAN / WAN /
Internet
Quell-
Datenbank
Ziel-
Datenbank
Capture: Auslesen der bestätigten Transaktionen
(optional gefiltert) wie sie im Transaktionslog erscheinen.
Capture Source Trail
Trail files:Speichern der Daten für Weiterleitung.
11
OGG – Architektur (2)
LAN / WAN /
Internet
Trail files:Speichern der Daten für Weiterleitung.
Route: Datendistribution zu mehreren Zielen.
Capture: Auslesen der bestätigten Transaktionen
(optional gefiltert) wie sie im Transaktionslog erscheinen.
Delivery: Anwenden mit Transaktions-
integrität, Transformationen wie benötigt.
Capture Source Trail Target Trail
Deliver
Quell-
Datenbank
Ziel-
Datenbank
12
OGG – Architektur (3)
LAN / WAN /
Internet
Bidirektional
Trail files:Speichern der Daten für Weiterleitung.
Route: Datendistribution zu mehreren Zielen.
Capture: Auslesen der bestätigten Transaktionen
(optional gefiltert) wie sie im Transaktionslog erscheinen.
Delivery: Anwenden mit Transaktions-
integrität, Transformationen wie benötigt.
Capture Source Trail Target Trail
Source Trail Target Trail
Deliver
Deliver Capture Quell-
Datenbank
Ziel-
Datenbank
14
Databases O/S and Platforms
Oracle GoldenGate Capture:
Oracle
DB2 for v 9.7
DB2 for v 10 on z/OS
Microsoft SQL Server for 2008 R1, R2
Sybase ASE, 15.5
Teradata
Enscribe
SQL/MP
SQL/MX
MySQL
JMS message queues
Oracle GoldenGate Delivery:
All listed above, plus:
TimesTen, IBM System I, Netezza &
Greenplum
ETL product
Linux
Sun Solaris
Windows 2000, 2003, XP, 2008
HP NonStop
HP-UX
IBM AIX
IBM z Series
zLinux
14
OGG 11g – Unterstützte Plattformen
15
Oracle Learning Library – OGG (1)
http://apex.oracle.com/pls/apex/f?p=44785:2:260111171332531::NO:RIR::
18
Installation – Oracle Environment
1. Set Environment Variables
set ORACLE_HOME = D:\oracle\...
set ORACLE_SID = xxxx
2. Install Oracle GoldenGate
Repeat for
each Database
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\jjaensch>set ORACLE_SID
ORACLE_SID=oraj
C:\Documents and Settings\jjaensch>set ORACLE_HOME
ORACLE_HOME=d:\Oracle\V1101
C:\Documents and Settings\jjaensch>
3. Check Settings
Oracle GoldenGate Builds
Can I use an OGG build for Oracle 10G to extract / replicate from /to Oracle 9i or oracle 11G database?
For an extract - The database version should match exactly with the Oracle GG build
Scenarios
GG oracle 10G Build – Can extract from Oracle 10.1 or Oracle 10.2 and not from any other Oracle version
GG oracle 10.1 build – Can extract only from Oracle 10.1 and not from any other Oracle version
GG oracle 10.2 build – Can extract Only from Oracle 10.2 and not from any other Oracle version
For a replicat – The major database version should match with the Oracle GG build
Scenarios
GG oracle 10G Build – Can replicate to Oracle 10.1 or Oracle 10.2 and not to any other Oracle version
GG oracle 10.1 build – Can replicate to Oracle 10.1, Oracle 10.2 and not to any other Oracle version
GG oracle 10.2 build – Can replicate to Oracle 10.1, Oracle 10.2 and not to any other Oracle version
The case is same with Oracle GG 9i build or GG 11g build.
The reason behind these requirements is that the data dictionary calls may be different from one version
of the database to the other and our code is database version specific.
To clarify it differently, OGG always refers the Oracle DB software version and not the COMPATIBLE parameter,
which might point to older versions for backward compatibility.
Compatibility Between OGG Builds and Oracle Database Versions
Support Note: 1086154.1:
22
Installationsschritte
Create Sub-Directories: GGSCI CREATE SUBDIRS
Specifying a non-default Manager Name: EDIT PARAMS ./GLOBALS
MGRSERVNAME <name>
Install Service: INSTALL ADDEVENTS ADDSERVICE MANUALSTART
Manually: Create Directory: dirmac
1.
2.
3.
23
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
D:\Myfiles\17_GoldenGate\OGG_V11111>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Windows (optimized), Oracle 11g on Apr 22 2011 02:56:40
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (jjaensch-lap) 1> create subdirs
Creating subdirectories under current directory D:\Myfiles\17_GoldenGate\OGG_V11111
Parameter files D:\Myfiles\17_GoldenGate\OGG_V11111\dirprm: created
Report files D:\Myfiles\17_GoldenGate\OGG_V11111\dirrpt: created
Checkpoint files D:\Myfiles\17_GoldenGate\OGG_V11111\dirchk: created
Process status files D:\Myfiles\17_GoldenGate\OGG_V11111\dirpcs: created
SQL script files D:\Myfiles\17_GoldenGate\OGG_V11111\dirsql: created
Database definitions files D:\Myfiles\17_GoldenGate\OGG_V11111\dirdef: created
Extract data files D:\Myfiles\17_GoldenGate\OGG_V11111\dirdat: created
Temporary files D:\Myfiles\17_GoldenGate\OGG_V11111\dirtmp: created
Veridata files D:\Myfiles\17_GoldenGate\OGG_V11111\dirver: created
Veridata Lock files D:\Myfiles\17_GoldenGate\OGG_V11111\dirver\lock: created
Veridata Out-Of-Sync files D:\Myfiles\17_GoldenGate\OGG_V11111\dirver\oos: created
Veridata Out-Of-Sync XML files D:\Myfiles\17_GoldenGate\OGG_V11111\dirver\oosxml: created
Veridata Parameter files D:\Myfiles\17_GoldenGate\OGG_V11111\dirver\params: created
Veridata Report files D:\Myfiles\17_GoldenGate\OGG_V11111\dirver\report: created
Veridata Status files D:\Myfiles\17_GoldenGate\OGG_V11111\dirver\status: created
Veridata Trace files D:\Myfiles\17_GoldenGate\OGG_V11111\dirver\trace: created
Stdout files D:\Myfiles\17_GoldenGate\OGG_V11111\dirout: created
GGSCI (jjaensch-lap) 2>
OGG – Create Sub-Directories
24
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
D:\Myfiles\17_GoldenGate\GG1111_ORAJ>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Windows (optimized), Oracle 11 on Jul 28 2010 17:20:29
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights
reserved.
GGSCI (jjaensch-lap) 1> edit params ./GLOBALS
GGSCI (jjaensch-lap) 2>
MGRSERVNAME GG_ORAJ
File: GLOBALS
OGG – Manager und Windows Service
D:\Myfiles\17_GoldenGate\GG1111_ORAJ>install addevents addservice manualstart
Oracle GoldenGate messages installed successfully.
Service 'GG_ORAJ' created.
Install program terminated normally.
D:\Myfiles\17_GoldenGate\GG1111_ORAJ>
Über GoldenGate Software Command Interface (GGSCI) File GLOBAL editieren
Über Windows Command Line einen GoldenGate Service definieren
26
Objekt Zweck Default Name
DDL Marker Tabelle DDL Informationen (nur Inserts)
GGS_MARKER
Sequence auf Marker
Tabelle
Für eine Spalte der
Marker Tabelle
GGS_DDL_SEQ
DDL History Tabelle Metadata History (Inserts, Updates, Deletes)
GGS_DDL_HIST
Objekt ID History Tabelle Objekt IDs der
konfigurierten DDL Objekte GGS_DDL_HIST_ALT
DDL Trigger Feuert bei jeder DDL Operation.
Schreibt Infos in Marker und
History Tabelle
(Zusätzliche Packages)
GGS_DDL_TRIGGER_BEFORE
DDL Schema DDL Synchronisations
Objekte
keiner,
siehe GLOBAL File
Oracle DDL Support – DB Objekte
GoldenGate Installation 1 GoldenGate Installation 2
Zugriff auf primäre DB: ORAJ Zugriff auf sekundäre DB: ORAK
OGG – 1 Installation pro Datenbank
ORAK
Bi-directional
ORAJ
OGG – 1 Manager pro Installation
ex2prioj.prm re2secok.prm
re2prioj.prm ex2secok.prm
1 Tabelle:
GG_HEARTBEAT_TABLE
Manager Port: 7810 Manager Port: 7811
(.\dirprm\mgr.prm) (.\dirprm\mgr.prm)
Extract ORAJ Replicat ORAK
(.\dirprm\ex2prioj.prm) (.\dirprm\re2secok.prm)
Replicat ORAJ Extract ORAK
(.\dirprm\re2prioj.prm) (.\dirprm\ex2secok.prm)
30
GoldenGate – Aufbau einer Replikation
Change-Extract (hier als Change-Capture bezeichnet) muß immer zuerst laufen
Change-Extract läuft hier als Primary-Extract und speichert in lokales Trail
Data-Pump (Secondary-Extract) überträgt dann von Local- in Remote-Trail
Die verschiedenen Initial-Load Methoden werden im Anschluß vorgestellt
Start Change Data Capture auf der Quell-Datenbank
Momentane Commit Sequence Number (CSN) der Quelle ist 222
OGG – Instanziierung (1)
31
Commit Ordered
Source Trail
Commit Ordered
Target Trail
Quell-Datenbank Ziel-Datenbank
Momentane CSN ist 222 CSNs 222
bis …
Bei Oracle DB entspricht die CSN der Oracle SCN!
GoldenGate kennt Commit Sequence Number (CSN)
in allen unterstützten Datenbanken!
Capture Pump
Warten auf Abschluß etwaiger offener Transaktionen
32
Commit Ordered
Source Trail
Commit Ordered
Target Trail
Ziehen eines Backup von
CSN 245
Einspielen des Backup von
CSN 245
Konsistent von CSN 245
OGG – Instanziierung (2)
Quell-Datenbank Ziel-Datenbank Capture Pump
Momentane CSN ist 222 CSNs 222
bis …
Kopieren der Quelle auf das Ziel mittels Backup
(oder Export/Import) von einem konsistenten Zustand Momentane CSN auf der Quelle ist 245
Sobald Delivery bei der aktuellen CSN der Quelle ankommt,
sind Quelle und Ziel synchron
33
Capture Delivery Pump Commit Ordered
Source Trail
Commit Ordered
Target Trail
Deliver Transaktionen nach CSN 245
Momentane CSN ist 356
Konsistent von CSN 356
Synchron!
Ziehen eines Backup von
CSN 245
Einspielen des Backup von
CSN 245
OGG – Instanziierung (3)
Quell-Datenbank Ziel-Datenbank
Start GG Delivery aller Transaktionen nach der Backup
CSN (245 in diesem Fall) Momentane (CSN) auf der Quelle ist 356
Instanziierungsvarianten für die Ziel-DB
Cross Platform:
Transportable Database (v$db_transportable_platform)
Transportable Tablespace (v$_transportable_platform)
Import Data Pump über Database Link
CTAS (Create Table As Select)
Expdb / Impdb (Export/Import)
Backup / Restore (Homogene Instanziierung)
GoldenGate (Heterogene Instanziierung von non-Oracle DBs)
u.a. ...
Instanziierung über Export / Import
Start: Change Extract
Start: Export mit
Ende: Export
Konsistent!
t1
t2
t3
FLASHBACK_SCN= 5847739263594
Source DB
1. Start OGG Change Capture
2. Export mit FLASHBACK_SCN (konsistent!)
Start: Change Replicat mit
Start: Import
Ende: Import
t4
t5
t6
NOHANDLECOLLISIONS
AFTERCSN 5847739263594
Target DB
3. Import des konsistenten Export-File
4. Start OGG Change Replicat
GoldenGate Checkpointing (1)
Source
Database
Capture, Pump, and
Delivery save
positions to a
checkpoint file so
they can recover in
case of failure
GoldenGate Checkpointing (2)
Source
Database
Capture, Pump, and
Delivery save
positions to a
checkpoint file so
they can recover in
case of failure
GoldenGate Checkpointing (3)
Capture, Pump, and
Delivery save
positions to a
checkpoint file so
they can recover in
case of failure
Target
Database
Source
Database
Welche Möglichkeiten hat der Nutzer?
2. Datenverschlüsselung in Extract Files oder Trail Files
3. Verschlüsseln der Datenbank-Passwörter
4. Verschlüsselung des TCP/IP Datenverkehrs
1. Datenbank-Passwörter nicht in Parameter Files
5. “User-Level Permissions” für GGSCI Kommandonutzung
6. Verbindungsaufbau vom Zielsystem aus
Datenbank-Passwörter hinterlegen
Datenbank Connect-Informationen im Macro
- Passwörter nicht sichtbar in Parameter- und Report-Files
- “dirmac” Sub-Directory im Installationsverzeichnis notwendig
White Paper “Oracle GoldenGate for Linux, UNIX, and Windows” August 2010
...
MACRO #oracle_connect_demo1
BEGIN
USERID demo1, PASSWORD DEMO1
END;
...
Beispiel
...
NOLIST
include .\dirmac\dbconnect.mac
LIST
--Database Connection Information
#oracle_connect_demo1()
...
Process Parameters
.dirmac\dbconnect.mac
Datenbank-Passwörter verschlüsseln
GGSCI Kommando: ENCRYPT PASSWORD
ENCRYPT PASSWORD <password> ENCRYPTKEY <keyname>
ENCRYPT PASSWORD <password>
1. Benutzung der Standard Verschlüsselung
2. Benutzung eines eigenen Schlüssels (ENCKEYS-File notwendig)
DBLOGIN USERID demo1 PASSWORD <generated PW> ENCRYPTKEY <keyname>
3. Benutzung des verschlüsselten Passworts
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
D:\ogg_new_src>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Windows (optimized), Oracle 11g on Apr 22 2011 02:56:40
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (jjaensch-lap) 1> encrypt password DEMO1
No key specified, using default key...
Encrypted password: AACAAAAAAAAAAAFAMEIDEJNATJYESDHI
GGSCI (jjaensch-lap) 2> dblogin userid demo1 password DEMO1
Successfully logged into database.
GGSCI (jjaensch-lap) 3> dblogin userid demo1 password AACAAAAAAAAAAAFAMEIDEJNATJYESDHI encryptkey default
Successfully logged into database.
GGSCI (jjaensch-lap) 8>
Beispiel: ENCRYPTKEY DEFAULT
GGSCI Umgebung
GGSCI – GoldenGate Software Command Interface
Verschlüsselungsmethoden
- Verschlüsselung auf Basis “256-key Byte Substitution”
- Verschlüsselte Übertragung und Speicherung
Extract Files und Trail Files
- Verschlüsselung “Blowfish” (variable-length key: 32 – 128 bits)
TCP/IP Datenverkehr
- Passwörter und Daten
Die Kombination beider Methoden ist möglich.
Trail / File Ver- und Entschlüsselung
Extract verschlüsselt
Quelle Ziel
Replicat entschlüsselt
Mapping, Filtering, Transformations bei verschlüsselten Daten nicht möglich
EXTRACT Trail / File Verschlüsselung
Extract Parameter: ENCRYPTTRAIL
- Default
Extract Parameter: NOENCRYPTTRAIL
- Nur für Data Pump (Secondary Extract)
Extract Parameter: DECRYPTTRAIL
- Entschlüsseln für Mapping, Filtering, Transformation usw
- Danach kann wieder verschlüsselt werden (ENCRYPTTRAIL)
- Gilt für alle folgenden Trail / File Anweisungen
- Gilt für alle folgenden Trail / File Anweisungen
REPLICAT Trail / File Entschlüsselung
- Wenn Trail / File durch Extract verschlüsselt wurde
Replicat Parameter: DECRYPTTRAIL
TCP/IP Datenstrom (1)
- ENCKEYS-File muß auf Quell- und Zielseite identisch sein.
- Nicht notwendig, wenn Trail Files bzw. Extract Files verschlüsselt sind und verschlüsselte Passwörter verwendet werden
- Generieren von nutzer-spezifischen Keys mit GoldenGate KEYGEN Kommando:
KEYGEN <key length> <n> <n> - Anzahl der Schlüssel
- ENCKEYS-File aufbauen:
## Key Name Key Value
Superkey 0x420E61BE7002D63560929CCA17A4E1FB
Secretkey 0x027742185BBF232D7C664A5E1A76B040
Superkey1 0x42DACD1B0E94539763C6699D3AE8E200
Superkey2 0x0343AD757A50A08E7F9A17313DBAB045
TCP/IP Datenstrom (2)
RMTHOST sys1, MGRPORT 7840, ENCRYPT BLOWFISH, KEYNAME superkey
EXTRACT Parameter:
RMTHOSTOPTIONS ENCRYPT BLOWFISH, KEYNAME secretkey
Aktiver EXTRACT:
Passiver EXTRACT:
Kommando-Autorisierung
CMDSEC-File
<command name> is a GGSCI command name or a wildcard, for example
START or STOP or *.
<command object> is any GGSCI command object or a wildcard, for example
EXTRACT or REPLICAT or MANAGER.
<OS group> is the name of a Windows or UNIX user group.
On a UNIX system, you can specify a numeric group ID instead
of the group name. You can use a wildcard to specify all groups.
<OS user> is the name of a Windows or UNIX user.
On a UNIX system, you can specify a numeric user ID instead of
the user name. You can use a wildcard to specify all users.
<YES | NO> specifies whether access to the command is granted or prohibited.
<command name> <command object> <OS group> <OS user> <YES | NO>
Verbindungsaufbau vom Zielsystem aus
- Passiv Extract auf Quellseite
- Gestartet durch Replicate auf Zielseite (Trusted Site!)
- Zielsystem über Firewall gesichert
OGG – Veridata
http://www.oracle.com/us/products/middleware/data-integration/goldengate-veridata/index.html
OGG Veridata – Installation
Pro Installation: Veridata Server Nutzer - VDSERVER
Pro Installation: Veridata Web Nutzer - VDWEBADM
Pro Datenbank: Veridata Agent Nutzer - VDAGENT
Nutzer-IDs:
TCP/IP-Default-Ports:
Pro Installation: Veridata Server Port – 4150
Pro Installation: Veridata Shutdown Port – 8820
Pro Installation: Veridata Web Nutzer – 8830
Pro Datenbank: Veridata Agent Nutzer – 7850
http://<hostname>:8830/veridata/prepareWelcome.jsf
Browser Page:
OGG – Veridata-Merkmale
Keine Unterbrechung - führt Vergleiche aus, während Datenquellen
online sind und während die Replikation(en) weiterlaufen
Datenbank übergreifender Vergleich zwischen Oracle, SQL Server,
Teradata, HP SQL/MP und HP Enscribe Datenbanken.
Geeignet für große Datenvolumina
Selektive Vergleichsmöglichkeiten
Besonders schnell und effizient
Anwender wählt Tabellen/Schemata aus Quell- und Zieldatenbank
Der Vergleich wird gestartet aus der Veridata webbasierten Oberfläche
oder von Kommandozeile
Während die Dateninhalte sich weiterhin ändern, meldet GoldenGate Veridata:
Bestehend bleibende Diskrepanzen
Vorübergehende Datendiskrepanzen (konfigurierbar)
Web Interface kommuniziert mit dem Veridata Server via Web Services
Veridata-Service-Prozess behandelt alle Ressourcen, Daten,
Vergleiche, multi-threading, etc.
Konfiguration, Status & Berichte in Datenbank gespeichert
OGG – Veridata Arbeitsweise
Details laufender Vergleiche
Anzeige des Status jedes Vergleichspaares
Anzeige der Details jedes Vergleichspaares
Anzeige der Performanzmessungen
Ansicht der Out-of-Sync Datensätze
Details der Differenzen werden
formatiert basierend auf Vorlagen
Vordefinierte Ansichten
Selbstdefinierte Ansichten
Dieselben Ergebnisse können
auf mehrfache Weise angesehen
werden
Management Pack for GoldenGate (1)
http://www.oracle.com/us/products/middleware/data-integration/management-pack/index.html
Management Pack for GoldenGate (2)
1. Oracle GoldenGate Director Administrator:
Definieren von Nutzerrechten und Instanzen einer Oracle GoldenGate Umgebung.
2. Oracle GoldenGate Director Server:
Komponente zum Sammeln von Daten der einzelnen Oracle GoldenGate Prozesse.
3. Oracle GoldenGate Director Client:
Client-Interface zur Verbindung mit dem Oracle GoldenGate Director.
4. Oracle GoldenGate Director Web:
Browser-Interface zum Oracle GoldenGate Director (Keine zusätzliche Software nötig!).
Grafisches Nutzer-Interface zum Monitoring und zum Management der Oracle GoldenGate Oracle GoldenGate Prozesse.
Der Oracle GoldenGate Director besteht aus diesen Komponenten:
Alter Name: GoldenGate Director
Management Pack for GoldenGate (3)
Zwei Produkte Ein Preis:
1. Oracle GoldenGate Director:
Basiskonfiguration, Management, Monitoring, und Alerting.
Basiert auf vorhandener Infrastruktur in allen GoldenGate Instanzen.
2. Oracle GoldenGate Monitor:
Erweitertes Monitoring, Alerting (SNMP support), Lag Graphs und
History Repository.
Basierend auf neuer Agent Infrastruktur, die die Grundlage für alle
zukünftigen Erweiterungen bildet. Verfügbar mit OGG 11.1.1.1.1+
OGG Monitor oder OGG Director ?
Erweitertes Monitoring OGG Monitor
Infos über SNMP, Emails und CLI Alerts; Statistikreports für alle Monitor-Punkte;
Status- und Summary-Informationen, Dynamische End-To-End Diagramme;
Verfügbar mit OGG 11.1.1.1.1+;
Oracle Enterprise Manager Grid Control Plug-In ist in Arbeit und wird demnächst
verfügbar sein!
Konfiguration und Prozeßüberwachug OGG Director
ADD, ALTER, DELETE, START und STOP von OGG Prozessen; Editieren von
OGG Paramater Files; Unterstützt ältere OGG Versionen.
65
OGG – Support Master Notes
https://support.oracle.com
Komponente Note Number
Oracle GoldenGate (OGG) 1313280.1
OGG - Veridata 1307285.1
OGG - Management Pack (Director) 1307305.1
OGG - Certification Matrix 976287.1
Comparing GoldenGate & Streams
67
GoldenGate is Oracle’s strategic
replication solution
Lead with GoldenGate
• Highly customizable and flexible
• Optimized for Oracle Database
• Few large references
Oracle Streams
• Out-of-the-box solutions
• Broad heterogeneous support
• Excellent reference base
Oracle GoldenGate
Question: I already have Streams for free? Why buy GoldenGate?
• Better ROI overall: GoldenGate is lower cost to implement and maintain
• Enterprise-wide solution: GoldenGate easily expands to new use cases
• Support for Streams continues: Continue to use Streams where it’s deployed
Streams continues to be a
supported Oracle Database feature
Die Zukunft von Oracle Streams (?!)
...
Oracle GoldenGate
...
Oracle GoldenGate is the strategic replication solution for Oracle Database and for
heterogeneous databases, with proven success in a wide range of demanding industries
and mission critical use cases.
Oracle Streams
Oracle Streams is a built-in feature of the Oracle Database that allows information
sharing among multiple Oracle databases. With Oracle Streams, transactional changes
and events are captured, propagated and applied within an Oracle database or between
Oracle databases, creating a flexible replication solution for a homogeneous all-Oracle
environment.
Given the strategic nature of Oracle GoldenGate, Oracle Streams will continue to be
supported, but will not be actively enhanced. Rather, the best elements of Oracle
Streams will be evaluated for inclusion with Oracle GoldenGate.
Current customers depending on Oracle Streams will continue to be fully supported, and
Oracle Streams customers should continue using the feature wherever it is deployed
today.
...
Quelle: Oracle – GoldenGate Statement of Direction, Page 4
Oracle Data Integration Solution Best-in-class Heterogeneous Platform for Data Integration
MDM Applications
SOA Platforms
Oracle Applications
Business Intelligence
Activity Monitoring
Custom Applications
Oracle GoldenGate
Log-based CDC
Bi-directional Replication
Real-time Data
SOA Abstraction Layer
Service Bus Process Manager Data Services
Oracle Data Integrator
ELT/ETL
Data Transformation
Bulk Data Movement
OLTP System
Flat Files Data Warehouse/ Data Mart
OLAP Cube Web 2.0 Web and Event Services, SOA
Storage
Data Verification
Oracle Data Quality
Data Profiling
Data Parsing
Data Cleansing
Data Federation
Data Lineage Match and Merge
Comprehensive Data Integration Solution
Key Data Integration Products
• Comprehensive Integration
• ELT/ETL for Bulk Data
• Service Bus
• Process Orchestration
• Human Workflow
• Data Grid
• Business Data / Metadata
• Statistical Analysis
• Time Series Reporting
• Integrated Data Quality
• Cleansing & Parsing
• De-duplication
• High Performance
• Integrated w/ODI
• Heterogeneous E-LT & ETL
• High-speed Transformations
• OLAP Data Loading
• Data Warehouse Loading
• Real Time Data Replication
• Changed Data Capture
• DBMS High Availability
• Disaster Tolerance
• Data Service Modeling
• XQuery Data Federation
• Data Security/Redaction
• XA Compliance
Transactional
RDBMS
Source Systems ODI Staging & Target
Source DB’s ODI J$ Tables Target EDW
Replicated Source Tables
Target Tables
Replicated Source Tables
Source Tables
J$
ODI-EE Integration with GoldenGate Non-invasive Data Capture combined with ODI ELT strengths
Key Benefits:
1. Eliminate Overhead no need for DB API overhead on the Source, or the invasiveness of the ODI J$ objects on the Source system,
2. Automate GoldenGate automation of GG deployment directly from ODI GUI
3. Provide Common DW Pattern supplies a common pattern for mini-batch style (non-real-time) DW aggregate loads
Generate all GG deployment files
Generate all ODI CDC infrastructure Execute end-to-end CDC
ODI CDC Framework
ODI
Oracle’s Data Integration Joint Solution Best-of-Breed and Proven
Performance
Extensible & Flexible
Enterprise
Technology Differentiators:
• E-LT architecture for best performance of high data volume transformations
• Knowledge Module architecture for extensibility and flexible connectivity
• SOA-native, integrated with Fusion MW to fit future enterprise architectures
• Lowest latency and highest throughput; non-invasive, low overhead
• De-coupled architecture; multiple deployment styles; open and extensible
• Maintain transactional integrity; resilient against interruptions and failures
Oracle Data Integrator
Enterprise Edition Oracle GoldenGate
• Fastest real-time solution
• Sub-second latency for real-time feeds
• Guaranteed delivery eliminates data loss
• Eliminates down-time for migration and upgrades
• Least intrusive to source systems
Oracle GoldenGate
• Fastest E-LT Solution
• Optimized SET-based transformation for high volume transformations
• Data lineage for improved manageability
• Integrates to Data Quality
Oracle Data Integrator
Zusammenwirken von OGG und ODI
• Solution
• Using OGG for log-based capture of database
transactions from source to minimize overhead
• Load to target with sub-second latency
• Transformation performed on the database
using E-LT in mini-batches
• Fast ETL/E-LT handled by database processing
• Benefits
• No resource / performance impact to OLTP
• Live data available for better decision making
• Get double-duty from database investment by
using it for transformations
• Maximizes availability of source systems and
DW due to smaller batch windows.
Business Intelligence in Real-Time
Oracle Data Integrator
EMP DEPT
DIM
FACT
DIM
DIM DIM
ODS Schema DW Schema
Oracle
GoldenGate
Journalize
Read from CDC Source
Load
From Sources to Staging
Check
Constraints before Load
Integrate
Transform and Move to Targets
Service
Expose Data and Transformation
Services
Reverse
Engineer Metadata
• Leverage Database Optimizations:
Native SQL; Native Functions; Native Loads; Native Journaling / CDC
• Tailor to an organization’s existing best practices
• Ease administration work
• Reduce cost of ownership
Reverse
Journalize
Load
Check
Integrate Services
Pluggable Knowledge Module Architecture
CDC
Sources
Staging Tables
Error Tables
Target Tables
WS WS
WS
Benefits
75
Overview of the ODI KM Framework
Overview of the Integration Using ODI & OGG Together
Transactional RDBMS
Source Tables
Staging DB
Replicated
Source Tables
ODI CDC
Framework
Target DB
Target Tables
WAN
ODI
Interfaces
Extract
Source trail
files
Staging trail
files
Datapump
Replicat
Oracle BU ST-PCM Registration Page
http://www.oracle.com/webfolder/technetwork/de/community/platform/index.html
--name the Change Extract Process
EXTRACT ex2prioj
NOLIST
include .\dirmac\dbconnect.mac
LIST
--Database Connection Information
#oracle_connect_ggadmin()
-- Exclude GG-User
TRANLOGOPTIONS EXCLUDEUSER ggadmin
--send data to remote host
RMTHOST localhost, MGRPORT 7811
--send data to remote trail named rt
RMTTRAIL d:\ogg_new_tar\dirdat\tstact\rt
--get trail file data for these tables
TABLE TSTACT.GG_HEARTBEAT_TABLE; --name the process
REPLICAT re2prioj
--Databse Connection Information
USERID GGADMIN@oraj, PASSWORD GGADMIN
--throw error records to discard file
DISCARDFILE .\dirrpt\re2prioj.dsc, purge
--1:1 mapping - no Source Definition File is needed
ASSUMETARGETDEFS
--simple like-to-like mapping
MAP TSTACT.GG_HEARTBEAT_TABLE, TARGET TSTACT.GG_HEARTBEAT_TABLE;
REPLICAT / EXTRACT für ORAJ
ex2prioj
re2prioj
--name the process
EXTRACT ex2secok
--Database Connection Information
userid ggadmin@orak, password GGADMIN
-- Exclude GG-User
TRANLOGOPTIONS EXCLUDEUSER ggadmin
--send data to remote host
RMTHOST localhost, MGRPORT 7810
--send data to remote trail named rt
RMTTRAIL d:\ogg_new_src\dirdat\tstact\rt
--get trail file data for these tables
TABLE TSTACT.GG_HEARTBEAT_TABLE;
--name the process
REPLICAT re2secok
--Database Connection Information
USERID GGADMIN@orak, PASSWORD GGADMIN
--throw error records to discard file
DISCARDFILE .\dirrpt\re2secok.dsc, purge
--1:1 mapping - no Source Definition File is needed
ASSUMETARGETDEFS
MAP TSTACT.GG_HEARTBEAT_TABLE, TARGET TSTACT.GG_HEARTBEAT_TABLE;
REPLICAT / EXTRACT für ORAK
re2secok
ex2secok
SQL> @GG_Heartbeat_Select_Table.sql
Connected.
GLOBAL_NAME
---------------------
ORACLEJ.DE.ORACLE.COM
'----------------------------'
'Heartbeat Table from OracleJ'
'----------------------------‚
DB_NAME Current-Time
------------------------------ -------------------
ORACLEJ.DE.ORACLE.COM 28.11.2011,19.20.00
ORACLEK.DE.ORACLE.COM 03.11.2011,14.54.00
Connected.
GLOBAL_NAME
---------------------
ORACLEK.DE.ORACLE.COM
'----------------------------'
'Heartbeat Table from OracleK'
'----------------------------‚
DB_NAME Current-Time
------------------------------ -------------------
ORACLEJ.DE.ORACLE.COM 03.11.2011,14.54.01
ORACLEK.DE.ORACLE.COM 28.11.2011,19.19.00
Heartbeat-Tables – nur lokal aktuell
Heartbeat-Tables – aktuell
SQL> @GG_Heartbeat_Select_Table.sql
Connected.
GLOBAL_NAME
---------------------
ORACLEJ.DE.ORACLE.COM
'----------------------------'
'Heartbeat Table from OracleJ'
'----------------------------‚
DB_NAME Current-Time
------------------------------ -------------------
ORACLEJ.DE.ORACLE.COM 28.11.2011,20.35.00
ORACLEK.DE.ORACLE.COM 28.11.2011,20.35.00
Connected.
GLOBAL_NAME
---------------------
ORACLEK.DE.ORACLE.COM
'----------------------------'
'Heartbeat Table from OracleK'
'----------------------------‚
DB_NAME Current-Time
------------------------------ -------------------
ORACLEJ.DE.ORACLE.COM 28.11.2011,20.35.00
ORACLEK.DE.ORACLE.COM 28.11.2011,20.35.00
OBEY – Files für OGG Setup ORAJ
-- ********************************************
-- * Statements to Replicat from ORAJ to ORAK *
-- ********************************************
-- log in to create supplemental log groups
DBLOGIN USERID ggadmin@oraj PASSWORD GGADMIN
-- add table level supplemental logging for Heartbeat table (primary key)
add TRANDATA TSTACT.GG_HEARTBEAT_TABLE
-- verify supplemental log groups were created
info TRANDATA *
-- Change Extract Process
-- register log based extract to the manager starting now
add EXTRACT ex2prioj, TRANLOG, BEGIN now
-- register the remote trail
add RMTTRAIL d:\ogg_new_tar\dirdat\tstact\rt, EXTRACT ex2prioj
-- start change extract ex2prioj
start EXTRACT ex2prioj
-- *************************************************
-- * Statements to Replicat back ORAK to ORAJ *
-- *************************************************
-- Login to Primary Database
dblogin userid GGADMIN@oraj, password GGADMIN
-- Adds Checkpoint Table
add checkpointtable GGADMIN.GG_CHKPT1
-- Adds the change replicat process
add REPLICAT re2prioj, EXTTRAIL .\dirdat\tstact\rt, CHECKPOINTTABLE GGADMIN.GG_CHKPT1
-- Start change replicat process later with command
start REPLICAT re2priok
-- info
info ALL
-- stats change extract
stats EXTRACT ex2prioj
-- stats change replicat
stats REPLICAT re2prioj
OBEY – Files für OGG Setup ORAK
-- ********************************************
-- * Statements to Replicat from ORAJ to ORAK *
-- ********************************************
-- Login to Secondary Database
dblogin userid GGADMIN@orak, password GGADMIN
-- Adds Checkpoint Table
add checkpointtable GGADMIN.GG_CHKPT1
-- Adds the change replicat process
add REPLICAT re2secok, EXTTRAIL .\dirdat\tstact\rt, CHECKPOINTTABLE GGADMIN.GG_CHKPT1
-- Start change replicat process
start REPLICAT re2secok
-- *************************************************
-- * Statements to Replicat BACK from ORAK to ORAJ *
-- *************************************************
-- log in to create supplemental log groups
dblogin USERID GGADMIN@orak PASSWORD GGADMIN
-- add table level supplemental logging for Heartbeat table (primary key)
add TRANDATA TSTACT.GG_HEARTBEAT_TABLE
-- verify supplemental log groups were created
info TRANDATA *
-- register log based extract to the manager starting now
add EXTRACT ex2secok, TRANLOG, BEGIN now
-- Register the remote trail
add RMTTRAIL d:\ogg_new_src\dirdat\tstact\rt, EXTRACT ex2secok
-- start change extract ex2secok
start EXTRACT ex2secok
--info
info ALL
-- stats replicat re2secok
stats REPLICAT re2secok
-- stats change extract
stats EXTRACT ex2secok