Oracle GoldenGate (OGG) - DOAG Deutsche ORACLE ... und Heterogene Real-Time Replikation Zero Downtime Upgrades, Migrationen und Wartungsarbeiten Migration von Non-Oracle Datenbanken

<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


LAN / WAN /

Internet


Route: Datendistribution zu mehreren Zielen.



Delivery: Anwenden mit Transaktions-

integrität, Transformationen wie benötigt.

Capture Source Trail Target Trail

Deliver

Quell-

Datenbank

Ziel-

Datenbank

12


LAN / WAN /

Internet

Bidirektional


Route: Datendistribution zu mehreren Zielen.



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

13

OGG – Oracle Open World Flash

../Flash_With_Oracle/2009_OOW_Touchscreen_Flash-159512.exe

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::

16

Oracle Learning Library – OGG (2)

PDF-Dokument

Oracle GoldenGate

Download und Installation

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

19

E-Delivery – Download GoldenGate (1)

https://edelivery.oracle.com

https://edelivery.oracle.com/

20

E-Delivery – Download GoldenGate (2)

2 Versionen!

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



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



D:\Myfiles\17_GoldenGate\GG1111_ORAJ>ggsci


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


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

25

Systemsteuerung Tools Services

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)

Initial-Load & Instanziierung

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


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


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

Checkpoints

GoldenGate Checkpointing (1)

Source

Database

Capture, Pump, and

Delivery save

positions to a

checkpoint file so

they can recover in

case of failure


Source

Database

Capture, Pump, and

Delivery save

positions to a

checkpoint file so

they can recover in

case of failure


Capture, Pump, and

Delivery save

positions to a

checkpoint file so

they can recover in

case of failure

Target

Database

Source

Database

OGG Security

Features

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



D:\ogg_new_src>ggsci


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.


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

Weitere

GoldenGate Produkte

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

OGG – Veridata Architektur

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


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


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

Die Oracle Strategie

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


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


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


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 Data Integration Community

Informationsportal für unsere deutschsprachigen Kunden

Oracle BU ST-PCM Registration Page

http://www.oracle.com/webfolder/technetwork/de/community/platform/index.html

Oracle BU ST-PCM Data Integration - Global

http://apex.oracle.com/pls/otn/f?p=43477:1

Oracle BU ST-PCM Data Integration - Community

http://apex.oracle.com/pls/otn/f?p=9530:1

81

Oracle GoldenGate

Bidirectional

Demo Für technisch Interessierte!

Oracle GoldenGate

Parameter- & OBEY-Files

Für technisch Interessierte!

--name the Change Extract Process

EXTRACT ex2prioj

NOLIST

include .\dirmac\dbconnect.mac

LIST


#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


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


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'

'----------------------------‚


------------------------------ -------------------



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'

'----------------------------‚


------------------------------ -------------------



Connected.

GLOBAL_NAME

---------------------

ORACLEK.DE.ORACLE.COM

'----------------------------'

'Heartbeat Table from OracleK'

'----------------------------‚


------------------------------ -------------------



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