Oracle GoldenGate Demo and Data Integration Concepts

Oracle GoldenGate Demo & Data Integration Basic Concepts

Fumiko Yamashita 11th Mar, 2011

Safe Harbor Statement: The views expressed on this presentation are my own and do not necessarily reflect the views of Oracle.
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any
contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making a purchasing decision. The
development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

Agenda

• Oracle GoldenGate Demo
• UI Demo - Siebel & Database Migration
• Technical Demo - Data Transformation
• Data Integration Basic Concepts
• Operational Reporting vs Data Warehousing
• Master Data Management (MDM)
• Change Data Capture (CDC)

Why Oracle GoldenGate (OGG) & ODI now?

• Gartner’s DI research in Dec 2010 indicates:
• BI and DWH remain the predominant use case for data integration
tools
 The focus of the 22-Mar customer webcast: “Optimize Your Data
Warehouse”
• The slow economy is pushing many enterprises to seek low-
cost, "good enough" data integration capabilities
 Promote the cost efficiency and cost saving effects of OGG/ODI
 By using Oracle Data Integration products, organizations have:
 Reduced their development costs by 30% > cost
 Improved the speed of handling data by 50% > customer
satisfaction
 Reduced business process execution times by at least 70% >
revenue
• IDC sees Oracle as not just DB/OFM/Apps vendor, but DI vendor (OOW
SF Sep 2010)

SAP BO: SAP Business Objects BI: Business Intelligence DWH: Data Warehouse DI: Data Integration

What Can Oracle GoldenGate/ODI Achieve?
Customers Demand Continuous Real Time Data
While Lowering IT Costs and Gaining Operational Efficiencies
Data Quality/
Data Profiling/
Real-Time MDM
Business
OGG Operations OGG/ODI

1
Continuous Availability of Critical 2Real-Time Information Access for
Applications and Data Intelligence & Decisioning
• Continuous Uptime in event of disaster • Instantly available Data for Reporting
• No downtime during planned outage / • Access to Key Information for Analysis
migration, achieve gradual migration • Across Heterogeneous Systems
• Load balancing & Data distribution

3 Lower IT Spend & Improve Efficiencies
• Choose best system for need (heterogeneity)
• Modernize Legacy Systems
• Offload for reporting
OGG

MDM: Master Data Management

Oracle GoldenGate/ODI Use Cases & Demo Coverage
Enterprise-wide Solution for Real Time Data Needs
Covered by
1st
demo on today’s demo
migration from Disaster Recovery,
Siebel 7 & DB 10g Data Protection Standby
(Open & Active)
to Siebel 8 & 11g 1
Continuous
Zero Downtime Availability
Migration and (OGG)
Upgrades
Log Based, Real- Covered by
Time Change Data
Capture
Operational
Reporting today’s demo
Reporting
OGG
Database except
ODI/ETL
ODI/ETL 2
ODS EDW Real-time
OR/BI
ODI/ETL
(ODI/OGG)
Heterogeneous EDW
Real-time BI Covered by
Source Systems
today’s demo
Query Offloading
3
Lower IT Cost
Data Distribution
(OGG)

Process integration is
SOA Integration /Web
Services (ODI/OGG) also possible

OGG: Oracle GoldenGate ODS: Operational Data Store TCO: Total cost of ownership BI: Business Intelligence
ODI: Oracle Data Integrator EDW: Enterprise Data Warehouse ROI: Return of investment ETL: Extract-Transform-Load Tool

Oracle GoldenGate - UI Demo
Siebel/DB Migration

Siebel 7 & SQL Server Siebel 8 & DB 11g
OGG

Pump

Source System Target System
Windows 2003 SP1 Red Hat Enterprise Linux 5.3
SQL Server 2000 SP4 Oracle Database 11.1.0.6
Siebel application version 7.8.2.4 Call Siebel application version 8.1.1 Call
Center ENU Center ENU
Oracle GoldenGate version: 10.4.0.19 Oracle GoldenGate version: 10.4.0.19
Source Extract (Capture): extsieb Source Extract: ext_s81
Source Pump: pumpsieb Source Pump: pump_s81
Source Replicat (Delivery): rep_s78 Source Replicat: rep_s81

Siebel & DB Migration – Account Creation
Siebel 7 & SQL Server (Source) Siebel 8 & DB 11g (Target)
Can be described as Can be described as the
the production site The account DR site identical to the
in the DR scenario Prod site in the DR
created in the scenario
source system is
replicated in the
Can be the original target system
system which used to immediately. Can be the offloading
handle large data load target system (usually
and very slow in the less expensive HW) to
offloading scenario reduce the load on the
original system in the
offloading scenario

Can be the source
site in the Can be the target
replication site in the
scenario replication
scenario

Let’s watch the
demo now!
7

Siebel & DB Migration– Account Phone Number Change

Siebel 7 & SQL Server (Source) Siebel 8 & DB 11g (Target)

The phone number
updated in the target
system is changed in
the source system
immediately.

Can be bi-directional

8

Data Transformation – Configuration File

MAP "dbo.S_OPTY" TARGET oltp_sia.S_OPTY
keycols (row_id),
COLMAP (USEDEFAULTS,
PR_DEPT_OU_ID
= @IF(@STREQ(OPTY_CD,"Design Project"), If (source)
@COLSTAT(NULL), Then (target)
2. Primary Account ID Else (target)
PR_DEPT_OU_ID) ,
OPTY_CD
= @IF(@STREQ(OPTY_CD,"Design Registration"), If (source)
"Design Opportunity", Then (target)
OPTY_CD)); Else (target)
1. Opportunity Type

9

Data Transformation – Data Change

Main System (Source) Offloaded System (Target)

The Opportunity Type is changed
from “Design Registration” to
“Design Opportunity” while being
transferred.

OPTY_CD Opportunity Type
= @IF(@STREQ(OPTY_CD,"Design Registration"),
If (source)
"Design Opportunity",
Then (target)
OPTY_CD));
Else (target)

10

Data Transformation – Concealing Internal Data

Main System (Source) Offloaded System (Target)

When the Opportunity
Type is changed to
“Design Project”, the
Account is nullified only
on the target side.
Primary Account ID
PR_DEPT_OU_ID
= @IF(@STREQ(OPTY_CD,"Design Project"),
If (source)
@COLSTAT(NULL), Opportunity Type
Then (target)
PR_DEPT_OU_ID) ,
Else (target)

11

OGG Technical Demo: Data Transformation - Overview

Source DB Data Replication with Target DB
Simple Data Node C: Boston
Node B: Chicago Transformation

OGG Technical Demo: Data Transformation - Architecture

Management Node fa2333 Swingbench Node
Load Generator/Benchmarking

Source DB Target DB
Node B: Chicago Node C: Boston
la2334 la2335

Data
Replication
with Simple
Data
Transfor-
mation

OGG Technical Demo: Data Transformation
– Module Configuration

Port 7809 Port 7809

Let’s watch the
demo now!

15

Mappings – CUSTOMERS

• CUSTOMERS table split into US an
non-US customers using a FILTER
• CONTACTNAME column split into
CONTACTFIRST and CONTACTLAST
using built-in @STR functions

CUSTOMER Mapping
MAP GGS.customers, target GGS2.non_us_customers,
contactfirst = @STREXT (contactname, 1,
@STRFIND(contactname," ") - 1),
contactlast = @STREXT (contactname,
@STRFIND(contactname," ") + 1,
@STRLEN(contactname)))
WHERE (country <> "USA");

MAP GGS.customers, target GGS2.us_customers,
contactfirst = @STREXT (contactname, 1, @STRFIND
(contactname," ") - 1),
contactlast = @STREXT (contactname, @STRFIND
(contactname," ") + 1, @STRLEN (contactname)))
WHERE (country = "USA");

Mgt Pack for GoldenGate (Director) – Overview
• A graphical enterprise application that offers a visual and intuitive way
to define, configure, manage, and report on all GoldenGate
transactional data synchronization processes

GoldenGate GoldenGate

Director
Synchronize Synchronize

GoldenGate GoldenGate GoldenGate
Synchronize Synchronize

Oracle GoldenGate Veridata
• A high-speed, low impact data comparison solution
• Identifies and reports data discrepancies between two databases without
interrupting those systems or the business processes they support
• Supports Oracle, Teradata, SQL Server, NonStop SQL/MP and Enscribe
• Supports homogeneous and heterogeneous compares

• Benefits:
• Reduce financial/legal risk exposure
• Speed and simplify IT work in
comparing data sources
• No disruption to business systems
• Improved failover to backup systems
• Confident decision-making and
reporting

Oracle GoldenGate Veridata – In-Sync

Oracle GoldenGate Veridata – Out-Of-Sync

Oracle GoldenGate/ODI Use Cases & Demo Coverage
Enterprise-wide Solution for Real Time Data Needs

Disaster
Recovery, Data Standby
(Open & Active)
Protection
1
Continuous
Zero Downtime Availability
Migration and (OGG) What’s the difference
Upgrades between
Log Based, Real-
Time Change Data
Operational Reporting
Operational
Capture Reporting
Reporting Database
&
OGG
Data Warehouse/BI
ODI/ETL 2
ODS EDW Real-time OR,
BI /DWH, MDM
ODI/ETL
(ODI/OGG)
Heterogeneous EDW
Real-time BI
Source Systems What’s Master Data
Management?
Query Offloading
3
Lower IT Cost
Data Distribution
(OGG)

SOA Integration /Web
Services (ODI/OGG)

OGG: Oracle GoldenGate ODS: Operational Data Store TCO: Total cost of ownership BI: Business Intelligence
ODI: Oracle Data Integrator EDW: Enterprise Data Warehouse ROI: Return of investment ETL: Extract-Transform-Load Tool

What is Operational Reporting, DWH/BI, & MDM?
• MDM is a practice to consolidate & clean up master data
• Master Data: Customers, departments, employees, products, product categories, & territories
(Dimensions) – data is fairly constant, used in the transactional data
• Transactional Data: Orders, call logs, & opportunities (Fact table) – increases very fast
• Operational Reporting represents simple reports w/o data massaging (i.e. all opps)
• DWH/BI requires complex multi-step data massaging (i.e. corporate BI reports)
ERP Financial
OBI EE Suite/ MDM Suite
Cust A Prod A Prod Cat ODI (Data Profile/Quality)
MDM
Generates
consolidated
Order Generates simple Oracle Data BI reports
operational
reports Integrator

On-Disk Logs
Cust Prod
CRM Cust A Cust B
Cust B Prod B Territory Oracle Prod A Prod B FACT
GoldenGate Prod Cat Territory Order, C
24 Call Log all Log
Order
Territory Prod Cat

Call Log

Oracle ODS Schema DW Star Schema
GoldenGate
On-Disk Logs Dimension tables

Many customers are still running Operational Reports (ORs)
on their production OLTP Applications
Generates ORs on the
transactional systems,
slowing down the system for
al employees/customers
ERP Financial ODI (Data Profile/Quality)
Offload operational reporting or third-party tools
Cust A Prod A Prod Cat
work to outside the OLTP
systems to speed up the MDM
OLTP system

Order
ODI or ETL Tools

Real-time data replication is
On-Disk Logs the key for achieving the fast
real-time ORs > OGG Cust Prod
CRM Cust A Cust B
GoldenGate Prod Cat Territory Order, C
25 Call Log all Log
Order
Territory Prod Cat

Call Log

Oracle Staging Area
DW Star Schema
GoldenGate ODS Schema
On-Disk Logs

Many customers are still seeing yesterday’s data in
Operational Reports

Cust A Prod A Prod Cat or third-party tools
MDM

Viewing yesterday’s data in
Order operational reports
ODI or ETL Tools

View data in operational
On-Disk Logs reports real time
Cust Prod
Nightly Batch Process
CRM Cust A Cust B
GoldenGate Prod Cat Territory Order,
26 Call Log Call Log
Order
Territory Prod Cat

Call Log
Nightly Batch Process
ODS Schema DW Star Schema
Oracle
On-Disk Logs GoldenGate

What is CDC (Change Data Capture)?
• CDC is a method to capture data changes (delta) in the source and send to the target.
• Real-Time Log-Based Light-Weight CDC: Reads data from the DB logs instead of reading via DBMS
processes. > It doesn’t put load on the RDBMS in the memory. > GoldenGate does this.
• Slow Log-based CDC: Reads data from the DB log files, but using slow technology. Low performance.
• Heavy Intrusive CDC: Reads data from the RDBMS. Slows down the source system. Might depend on
timestamps to identify the delta.
RDBMS OBI EE Suite/ MDM Suite
MDM

Oracle Data
Order
Integrator

On-Disk Logs
Cust Prod
RDBMS Oracle
Cust B
CRM GoldenGate Cust A Prod B FACT
Cust B Prod B Territory Prod A Territory Order, C
Prod Cat Call Log all Log

Order Territory Prod Cat

Call Log

Oracle ODS Schema DW Schema
GoldenGate
On-Disk Logs
28

What happens without real-time log-based CDC?
• Heavy load to the RDBMS
• Slow down all transactions > Low performance
 GoldenGate achieves low-impact light-weight update capture from DB logs

RDBMS
ERP Financial OBI EE Suite/ MDM Suite
ODI (Data Profile/Quality)
SQLs: MDM
select * from customers …;

Order Oracle Data
Integrator
Oracle
On-Disk Logs
GoldenGate
Cust Prod
RDBMS
Cust A Cust B
CRM
SQLs: Prod A Prod B FACT
Cust B Prod B Territory select * from customers …; Prod Cat Territory Order,
29 Call Log Call Log
Order
Territory Prod Cat

Call Log

Oracle ODS Schema DW Schema
GoldenGate
On-Disk Logs

What happens without real-time log-based CDC & Timestamp?
• Huge data traffic – all records need to be copied to the DWH repository
With Timestamp: Refresh History Table:
OLTP Order Table:
Check new
records DWH Fact Table:

√
Copy new
records only
since last
√ refresh

Without Timestamp:
DW Schema
OLTP Order Table:
Staging Customer Table: DWH Fact Table:
Copy ALL
records to the Use
staging area some
complex
logics to
identify
It’s hard to change the data new
structure to add the timestamp! records

Without Timestamp with log-based CDC: DW Schema DWH Fact Table:

CDC helps customers to replicate
data WITHOUT changing anything
on both source & target DBs!

Copy as a new record is created

Copy as a new record is created

Appendix

• Customer References
• Bombay Stock Exchange Ltd
• Sabre Holdings
• RIM BlackBerry

Customer Success Story (OGG):
Bombay Stock Exchange Processes Online Transactions 10% to 15% Faster

• Profile
Business Challenges
Establisheddata replication tool that would capture transactions at thestock
• Deploy a in 1875, Bombay Stock Exchange (BSE) is the oldest company’s main data
exchangeandAsia. Today, around to many locations running total market databases
centre in deliver them quickly 4,397 companies with a multiple target
capitalization of US$1.06 trilliontransactional2009)to belisted on BSE’s bourse,
• Meet regulations that required (as of July data are accessible online
• more than any other stock exchange in the world. The BSE Index, SENSEX, is
Oracle’s Solution
India’s most popular stock market benchmark index and to multiple30 stocks
• Capture transactional data from its core system, deliver it includes target databases at
representing 12 market make this data available online for analysis
several locations and sectors.
• Allowed the stock exchange to meet regulations set out by the Securities and Exchange
Board of India that required daily stock transactions to be monitored online in real time
• Provided continuous data synchronization across mixed environments and integrated data
from these systems immediately to avoid the need for expensive data migration
• Enabled transactions to be processed 10% to 15% faster and ensured these transactions
were based on consistent information
• Enabled administration staff to easily move thousands of transactions per second with little
to no impact on source and target systems

“Oracle GoldenGate facilitates faster replication of transactional data to
heterogeneous databases at different locations, and ensures this data is secure and
always available online for analysis.”
– Dilip Oak, GM, System Operations, Bombay Stock Exchange

32

Query Offloading Case Study
Query Offloading Saves Millions $

COMPANY OVERVIEW GoldenGate PROVIDES
A world leader in the travel marketplace, • Supports 1.6TB of data movement per day
Sabre Holdings merchandises and retails to read-only servers
travel products and provides distribution and • Source– Oracle 10g | Target – MySQL
technology solutions for the travel industry
• Current plans to migrate to Oracle 11g on
both source and target.

CHALLENGES / OPPORTUNITIES RESULTS
• Optimize OLTP system performance – • 80% TCO Reduction – Millions $$ saved
offload all query activity
• Reduce TCO via platform changes and
segment “lookers” from “bookers”
• Handle growing data volumes and “Bookers vs Lookers”
support heterogeneous systems over life-
cycle of Air Travel Shopping Engine
(ATSE)
• Maintain data integrity across all systems

Query Offloading
Database Tiering: Save Millions and Drive Revenue

Business Challenges:
Database Tiering Enables Top
 Ensure continuous availability of Air Travel Shopping
Performance, Unlimited Scalability
Engine (ATSE) throughout upgrades and migrations
Schedule &
 Optimize system performance to handle growth and Air Travel
Availability Changes
Accounts
scale, while reducing TCO via platform changes Shopping & Billing
Oracle Databases
 Low latency data movement between OLTP
(“bookers”) and search database (“lookers”)
GoldenGate Solutions:
Schedule & Fare & Rule Updates
 Zero Database Downtime during hardware and Availability Updates
database migration for ATSE
GoldenGate Transaction Updates
 Database Tiering insures high availability, top Up to 800,000 p/hour, 1.6 TB/day
performance and lower costs
 Real-Time Data Integration for “lookers” query activity
on lower cost platform
Results:
 80% TCO reduction – millions of dollars in savings “Lookups”
 Higher customer satisfaction - improved response 156 MySQL Databases on Linux
times and availability

“GoldenGate…provided the glue to move the data across
systems, evolving our overall approach in parallel with the
future of online travel commerce.”
- James Harding, VP of Systems Planning & Performance, Sabre

34

Query
Value Proposition Offloading
Reduce IT Costs

Oracle GoldenGate can enable cost savings of 80%

• Sabre Holdings owns Travelocity, the world’s
largest online travel reservation system

• Sabre’s IT savings of 80% IT came from:

1. Routing travel searches (read-only) away from the transactional travel
reservation systems to a bank of MySQL databases kept in sync in real-
time using GoldenGate.

2. Migrating the reservation transaction system from HP Non-stop to
Oracle with RAC on Linux. GoldenGate enabled Sabre to migrate with
no downtime, which was the only real option.

Continuous Availability Case Study
High Availability of Manufacturing Application Meets Stringent
SLAs
COMPANY OVERVIEW GoldenGate PROVIDES
Research In Motion (RIM) is a leading • Active-Active configuration provides same
designer, manufacturer and marketer of data across worldwide plants
innovative wireless solutions for the • Improved system performance & response
worldwide mobile communications times
market.
• Zero downtime DB migration to new version
using phased approach

CHALLENGES / OPPORTUNITIES
• Experiencing exponent growth and RESULTS
stringent carrier SLA’s to meet growing • All of manufacturing from VP to engineer
demand for Blackberry devices on plant floor can access most up to
• Maintain continuous availably of critical date info.
data across all global data centers (4)
for manufacturing plants
• All plants see same data and can move
devices based on regional demand

Zero Downtime Migration & Upgrade
Zero Downtime OS and DB Migrations

Business Challenges: Zero-Downtime OS and Database
• RIM BlackBerry has a very small monthly window to Upgrade with Application Maintenance
take downtime for upgrades, migrations, and
planned maintenance
• Strict SLA limits with the major Telcos!

GoldenGate Solution:
• Zero-Downtime Upgrade and OS change for Application Maintenance
business-critical Oracle databases
• GoldenGate Veridata™ to ensure that all data from Provisioning Provisioning
source is in-synch with target and that migration has Database migration
been successful with failback

GoldenGate Benefits:
• Fail-back capability to reduce business risk and Oracle 10.2.0.3 Oracle 10.2.0.4
downtime HP-UX HP-UX
• No manual intervention to reduce possibility of Waterloo Slough, U.K.
errors
• Assurance of database consistency with Veridata.

37

Oracle GoldenGate Demo and Data Integration Concepts

Recommended

More Related Content

What's hot (20)

Viewers also liked (19)

Similar to Oracle GoldenGate Demo and Data Integration Concepts (20)

More from Fumiko Yamashita (9)

Recently uploaded (20)

Oracle GoldenGate Demo and Data Integration Concepts

Editor's Notes