oracle data integration solutions goldengate new · pdf fileoracle data integration solutions...

1 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Oracle Data Integration Solutions GoldenGate New Features Summary

Valarie Bedard

Principle Sales Consultant

Tony Velardo

DIS Account Manager

Oracle Data Integration Moving Data to Transform Business


Note to Reader - March 2014

This deck represents highlights of features in OGG 11.2 and OGG 12.x

It does not represent ALL features in either of these releases

It is recommended that the reader review the Release Notes for either version of OGG to get the complete picture of new features per release

It is recommended that the reader review the OGG Install Guide, Admin Guide, and Reference Guides for either version to understand dependencies, usage and syntax for any functionality or feature mentioned in this deck.


Quick List of OGG Database Integration points

Ability to ignore triggers when applying data (OGG V 12 default)

Ability to ignore constraints (like cascade delete) when applying data

Optimized ASM capture - DBLOGREADER

Access to Oracle’s Encryption Methods

Transparent Data Encryption

Tablespace Encryption

RMAN Integration – log retention

Automatic Schema level supplemental logging

Integrated Capture - Capture compressed data including EHCC

Database Vault access

Oracle’s AES encryption algorithms for trail files

OEM Grid Control Plug-in for Monitoring, control and metrics

Exadata DBFS optimizations for GoldenGate


Oracle GoldenGate 11g Release2

• Integrated Extract

• Conflict Detection and Resolution

• Manageability and Monitoring

• Security and Performance

• Globalization

• Event Marker Enhancement

• Coherence Integration


Integrated Extract

Source database and Integrated Extract process are in the same machine

User/Application changes

Record

changes Capture

changes

Redo Logs

Capture

Integrated

Extract

Oracle Database

Trail

File …..

Source Machine

LCR1

LCR2

LCR3

….

….

….

Logical Change

Records

• Leverages Proprietary Oracle Database LCR API


Integrated Extract Benefits - Summary

Category New Features Comments

Exadata Capture support for EHCC compression Enable capture from Exadata sources

Compressions Capture support for OLTP, Segment compression

Distributed Transactions XA-RAC, PDML Support capture from XA transactions originated from multiple nodes of RAC

RAC Simplified RAC management No need to modify parameter files, if an instance is not available

New Datatypes XML OR, XML Binary

LOB Full and Partial read from REDO log

REDO Processing Multithreaded support Performance gain from parallel processing

Deployment Source and Downstream capture support Helps to offload some of the extract work from the source database;

DDL Captures tables created with column level password specification

Others Support IOT with MAPPING table option


Integrated Extract – Expanded Deployment Options


Record

changes

Redo Logs

Capture

Integrated

Extract

Oracle Database

Trail

File

Source Machine

Source database and Integrated Extract process are in different machine

Standby

Redo Logs

Oracle Database

Downstream Machine

….. Continuous REDO

Shipping

Capture

changes

LCR1

LCR2

LCR3

….

….

….

Logical Change

Records

Capture


• Conflicts occur when the timing of simultaneous changes results in one of these

out-of-sync conditions:

– a replicated insert attempts to add a row that already exists in the target.

– the before image of a replicated update does not match the current row in the target.

– a replicated delete attempts to remove a row that does not exist in the target.

Conflict Detection and Resolution


Conflict Detection and Resolution

INSERTROWEXISTS Insert

Overwrite

Ignore

Discard

USEMIN,USEMAX

UPDATEROWMISSING

Update

Overwrite

Ignore

Discard

UPDATEROWEXISTS

Overwrite

Ignore

Discard

USEMIN,USEMAX

USEDELTA

DELETEROWMISSING

Delete

Ignore

Discard

DELETEROWEXISTS Overwrite

Ignore

Discard


Performance - Streaming

• Prior to Oracle GoldenGate 11gR2 – Synchronous data communication between data pump

extract and remote collector process

– Data sent by data pump extract to remote collector

process must be followed with ACK from the remote

collector. Extract can only send out new data after ACK.

• Oracle GoldenGate 11gR2 – Acknowledgement message can be skipped for every

data sent

– Remote collector does not send an acknowledgement

to the data pump extract unless a packet contains a

flag requesting a response, typically when the data

pump extract must checkpoint or determine a write

position.


Security Enhancements: Encryption

TCP/IP

Source

Database Target

Database

GGSCI Interactive Interface

Manager Manager GGSCI Interactive Interface

LAN / WAN / INTERNET

Over TCP/IP

Parameter Files

Parameter Files

PASSWORDS

PASSWORDS

BLOWFISH, AES128, AES192,AES256

IPv6 support


• Database Object Name Enhancement

– Non US-ASCII Character Support (both DDL/DML)

• Support database object name with any characters including European accent, multi-byte,

white space and symbols as long as database supports

– Database Like Object Level Case Sensitivity Support

• Oracle, DB2 and SQL/MX case sensitive object name

• Case sensitive from/to case insensitive mapping

• Partial Character Set Conversion

– Implicit Character Set Conversion Support by replicat

• Between two different character set CHAR/VARCHAR/CLOB columns

• CHAR/VARCHAR/CLOB from/to NCHAR/NVARCHAR/NCLOB columns

Globalization


Event Marker System 11gR2

Target Database

Source Database


Transaction Logs

LAN / WAN / INTERNET

Over TCP/IP

ACTIONS

TRACE

LOG

CHECKPOINTBEFORE

IGNORE

DISCARD

ROLLOVER

REPORT

ABORT

CHECKPOINTAFTER

FORCESTOP/STOP

SHELL with parameters

SUSPEND/RESUME

DDL

Event

record

Event

record

Event

record


Coherence HotCache

• Coherence/GoldenGate Integration

– Capture changes from DB

– Pump the data to coherence node,

using Java

– Toplink maps relational changes to

the objects

– Updates the cache

– User will see the latest data in their

cache in “Real-Time” after the

database is updated


Oracle GoldenGate 12c Highlights

Optimized for Oracle Database 12c

Non-consolidated, Multitenant and Cloud-based Real-Time Replication

Three part naming convention, New data types (Large VARCHAR)

Integrated Delivery for the Oracle Database

Leveraging high-speed, lightweight Streaming API built Exclusively for OGG to take

advantage of database parallel apply server for auto dependency aware parallel apply

Coordinated Apply for Non-Oracle Databases

Orchestrates the High-Speed Apply Processes & Simplifies Setup and Management

Expanded Heterogeneity

12c Brings Support for New Databases and Enhancements to Existing Supported Platforms

Improved Ease of Use

Automatic Discard File, Enhanced Debugging, Schema Wildcarding, and Improved Security

Enhanced High Availability for Zero Data Loss

Integration with Data Guard and FSFO for Automated & Transparent failover of Components

Expanded Oracle Application and Technology Support

Active/Active ATG, Low Downtime E-Business Suite Migrations and Coherence Integration


Oracle GoldenGate 12.1.2 New Functionality

16

• New with OGG 12.x

– Oracle Installer

– Oracle database Version 12 CDB support

– 3 part object name support

– Automatic Discard

– Improved initial load with added data type support for remote task

– Improved SHOWSYNTAX

– Schema Wildcard Support

– Credential store for userid/(encrypted)password >> Alias

– Source time zone in trail, replicat sets its session to this

– Extract start at CSN ( Oracle SCN)

– Oracle dbms_goldengate_auth.grant-admin_priviledge Package


17

Integrated Replicat

• Integrated Replicat for Oracle target databases only

– 12.1.0.1 and 11.2.0.4

• Leverages database parallel apply servers for automatic dependency

aware parallel apply

• Minimal changes to replicat configuration

– Single replicat parameter file for all tables

– Single Replicat, no need to use @RANGE or THREAD or other

splitting

Overview


Oracle Database

Integrated Replicat Architecture Diagram

Lightweight

Streaming API

Replicat

Receiver Preparer Coordinator

Applier 1

Applier N

Replicat

• Reads the trail file

• Constructs logical change

records (LCRs)

• Transmits LCRs to Oracle

Database via the Lightweight

Streaming API

Inbound Server (Database Apply Process)

• Receiver: Reads LCRs

• Preparer: Computes the dependencies between the transactions (primary key, unique

indexes, foreign key) , grouping transactions and sorting in dependency order.

• Coordinator: Coordinates transactions, maintains the order between applier processes.

• Applier: Performs changes for assigned transactions, including conflict detection and

error handling.

Inbound Server


Integrated Replicat - Dependency Aware Apply

• Source Database Supplemental Logging

Supplemental logging of scheduling columns (PK, UI, FK) needed for dependency aware

apply processing at target

New default of ADD TRANDATA is to log all unique indexes and foreign key columns .

If Primary Key, Unique Index, Foreign Key columns are same at both source and

target, ADD TRANDATA provides requisite information. If target has different

scheduling columns, then ADD TRANDATA [table] COLS …format should be

used to identify any additional columns

For old behavior specify NOSCHEDULINGCOLS option to only log primary key

ADD TRANDATA [table], NOSCHEDULINGCOLS

ADD SCHEMATRANDATA [schema], NOSCHEDULINGCOLS

• Scheduling columns must be present in trail file

Extract parameter LOGALLSUPCOLS

Includes any supplementally logged column in trail file

Automatically enables GETUPDATEBEFORES and NOCOMPRESSDELETES

19


Converting to Integrated Replicat

• Convert to integrated mode from existing nonintegrated replicat

GGSCI> STOP REPLICAT repdb

GGSCI> ALTER REPLICAT repdb, INTEGRATED

– Updates checkpoint file to indicate INTEGRATED

– DB checkpoint table is not maintained when using integrated replicat

• Consolidate the parameter files into a single file

• Start replicat with proper parameter file

– GGSCI> START REPLICAT repdb

• Registers replicat repdb1 database

• Starts and attaches to inbound server

• INFO REPLICAT repdb will show INTEGRATED in output

20


Coordinated Replicat

• Applicable to all databases

• Full barrier coordination across events requiring ordering

– DDL (Oracle only)

– Primary Key Update

– EMI

– SQLEXEC

• Ease of Use

– Simplify parameter file manageability (fewer param files)

– Simplify error recovery


Unified Parameter File (THREAD)

MAP sales.acct1, TARGET sales.acct1;



MAP sales.acct1, TARGET sales.acct1, THREAD(1); MAP sales.acct2, TARGET sales.acct2, THREAD(2); MAP sales.acct,3 TARGET sales.acct3, THREAD(3);


Unified Parameter File (THREADRANGE)

MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (1, 3, ID));



MAP sales.acct, TARGET sales.acct, THREADRANGE(1-3, ID));


Key Concepts Coordinated vs. Integrated Replicat

24

Coordinated Replicat

Integrated Replicat

User Specified Partitioning Automatic Scheduling

Split transaction Semantics

Does not split transactions

All databases Oracle specific with version requirements

Continues to employ SQL generation and execution outside the database.

Apply is in the database server. Tight integration with Oracle database.


Integrated Extract

• Integrated Extract was introduced in Oracle GoldenGate 11.2

• Oracle GoldenGate 12.1.2 enhances the feature.

• With Oracle GoldenGate 12.1.2

– By default, all newly created extracts will be integrated extract

– Upgrading from old IE you can use TRANLOGOPTION _LCRCAPTUREPROTOCOL

V2

– _LCRCAPTUREPROTOCOL V2 provides better performance by

• Streamlined for improved performance (V2 as fast or faster than Classic Extract)

• Drastic improvement in LOB performance

• Reduced overhead on database

• Available when the mining server is 11.2.0.3 BP 14 and higher or Oracle 12c

Overview

25


Integrated Extract

• Requirements:

– Database must be running Oracle Database 12c (12.1.0.1), 11.2.0.4 and later versions.

– DDL Trigger Removal is required for CDB.

• Behavior

– Integrated Extract running against Oracle Database 12c or 11.2.0.4 will automatically run in this

mode

• No need to run DDL setup scripts

• DDL trigger can remain enabled

– Integrated Extract running against Oracle Database 11g Release 11.2.0.3 will get metadata

gathered from DDL trigger

– Classic Extract will continue to require existence of DDL trigger

DDL Trigger Removal


Integrated Extract and Replicat Review

• Integrated Extract

– Oracle GoldenGate 12.1.2: the source database can be 11.1 or 11.2 or above. ... the MINING

database has to be version newer than the source database, but must be 11.2.0.3 BP 6 or higher.

Same as OGG 11.2.1.

– If the source database is Oracle DB 12.1, then OGG 12.1.2 must be used.

– For Oracle Database 12c with CDB setup, integrated extract is required.

• Integrated Replicat

– Target database has to be Oracle Database 12.1.0.1 and 11.2.0.4.

– The source database (Extract) can be other databases but have to include required supplemental

logging information for dependency calculation.

• Oracle GoldenGate 11.1.1 and 11.2.1 versions or non Oracle source databases, use 2 parameters: GETUPDATEBEFORES and NOCOMPRESSDELETES, these are required.

• For Oracle GoldenGate 12.1.2 use LOGALLSUPCOLS and UPDATERECORDFORMAT COMPACT. This works for classic and integrated extract only.

Oracle Database Only Support


Oracle GoldenGate 12c Database Support

• Oracle Database

– Oracle Database 12c

– Multitenant Container Database (CDB)

– More Datatypes: Large VARCHAR2

• Other Databases

– MySQL 5.6 & NDB 7.x Clusters

– Sybase 15.7

– DB2/LUW 10.1

28


Join the Data Integration Community Twitter or Facebook twitter.com/ORCLGoldenGate facebook.com/OracleDataIntegration

Oracle GoldenGate YouTube channel youtube.com/oraclegoldengate

Oracle.com/goto/dataintegration

LinkedIn “Oracle Data Integration”

Oracle’s Data Integration blog blogs.oracle.com/dataintegration

Oracle Technology Network

Oracle’s Public Help Forum http://forums.oracle.com/forums/forum.jspa?forumID=860&start=0

Oracle Technology Network

OTN: GoldenGate http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

Oracle Learning Library http://apex.oracle.com/pls/apex/f?p=44785:1:0::NO

MOS Knowledgebase http://support.oracle.com/

oracle data integration solutions goldengate new · pdf fileoracle data integration solutions...

Documents