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
Real-Time Business
Operations
What Can Oracle GoldenGate/ODI Achieve?Customers Demand Continuous Real Time DataWhile Lowering IT Costs and Gaining Operational Efficiencies
Continuous Availability of CriticalApplications and Data
•Continuous Uptime in event of disaster•No downtime during planned outage / migration, achieve gradual migration
•Load balancing & Data distribution
1
Lower IT Spend & Improve Efficiencies
• Choose best system for need (heterogeneity)• Modernize Legacy Systems
• Offload for reporting
3
Real-Time Information Access for Intelligence & Decisioning
• Instantly available Data for Reporting• Access to Key Information for Analysis
• Across Heterogeneous Systems
2
OGG/ODIOGG
OGG
Data Quality/ Data Profiling/
MDM
MDM: Master Data Management
Oracle GoldenGate/ODI Use Cases & Demo CoverageEnterprise-wide Solution for Real Time Data Needs
Log Based, Real-Time Change Data Capture
Heterogeneous Source Systems
EDWODS
EDW
Disaster Recovery, Data Protection
Zero Downtime Migration and Upgrades
Operational Reporting
Real-time BI
Standby(Open & Active)
ReportingDatabaseOGG
ODI/ETL
ODI/ETL
Query Offloading
Data Distribution
OGG: Oracle GoldenGate ODS: Operational Data Store TCO: Total cost of ownership BI: Business IntelligenceODI: Oracle Data Integrator EDW: Enterprise Data Warehouse ROI: Return of investment ETL: Extract-Transform-Load Tool
Covered by today’s demo
1Continuous Availability(OGG)
2Real-time OR/BI (ODI/OGG)
3Lower IT Cost(OGG)
Covered by today’s demo
1st demo on migration from
Siebel 7 & DB 10g to Siebel 8 & 11g
Covered by today’s demo
except ODI/ETL
SOA Integration /Web Services (ODI/OGG)
Process integration is also possible
Oracle GoldenGate - UI Demo Siebel/DB Migration
Source SystemWindows 2003 SP1SQL Server 2000 SP4Siebel application version 7.8.2.4 Call Center ENUOracle GoldenGate version: 10.4.0.19 Source Extract (Capture): extsieb Source Pump: pumpsieb Source Replicat (Delivery): rep_s78
Target SystemRed Hat Enterprise Linux 5.3Oracle Database 11.1.0.6Siebel application version 8.1.1 Call Center ENUOracle GoldenGate version: 10.4.0.19 Source Extract: ext_s81 Source Pump: pump_s81 Source Replicat: rep_s81
Siebel 7 & SQL Server Siebel 8 & DB 11gOGG
Pump
7
Siebel & DB Migration – Account Creation
Siebel 7 & SQL Server (Source) Siebel 8 & DB 11g (Target)
The account created in the
source system is replicated in the target system immediately.
Can be described as the production site in the DR scenario
Can be described as the DR site identical to the
Prod site in the DR scenario
Can be the source site in the replication scenario
Can be the target site in the replication scenario
Can be the original system which used to handle large data load and very slow in the offloading scenario
Can be the offloading target system (usually less expensive HW) to reduce the load on the original system in the offloading scenario
Let’s watch the demo now!
8
Siebel & DB Migration– Account Phone Number Change
The phone number updated in the target system is changed in
the source system immediately.
Siebel 7 & SQL Server (Source) Siebel 8 & DB 11g (Target)
Can be bi-directional
9
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"), @COLSTAT(NULL), PR_DEPT_OU_ID) , OPTY_CD = @IF(@STREQ(OPTY_CD,"Design Registration"), "Design Opportunity", OPTY_CD));
2. Primary Account ID
1. Opportunity Type
If (source)Then (target)Else (target)
If (source)Then (target)Else (target)
10
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 = @IF(@STREQ(OPTY_CD,"Design Registration"), "Design Opportunity", OPTY_CD));
Opportunity Type
If (source)Then (target)Else (target)
11
Data Transformation – Concealing Internal Data
Main System (Source) Offloaded System (Target)
PR_DEPT_OU_ID = @IF(@STREQ(OPTY_CD,"Design Project"), @COLSTAT(NULL), PR_DEPT_OU_ID) ,
Primary Account ID
If (source)Then (target)Else (target)
When the Opportunity Type is changed to “Design Project”, the Account is nullified only on the target side.
Opportunity Type
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)
OGG Technical Demo: Data Transformation - Overview
Source DBNode B: Chicago
Target DBNode C: Boston
Data Replication with Simple Data
Transformation
OGG Technical Demo: Data Transformation - Architecture
Source DBNode B: Chicago
la2334
Target DBNode C: Boston
la2335
Data Replication with Simple
Data Transfor-mation
Swingbench NodeLoad Generator/Benchmarking
Management Node fa2333
15
OGG Technical Demo: Data Transformation – Module Configuration
TCP/IP
Database
ExtractSource Trail
Manager
Data Pump
Source System
Port 7809
Database
Target TrailReplicat
Manager
Collector
Target System
Port 7809
Capture Delivery
GGS_EXT GGS_PUMP GGS_REP
et00001, 2, ...
Host Name: la2335.oracleads.comHost Name: la2334.oracleads.comRMTHOSTparam
Let’s watch the demo now!
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 MappingMAP GGS.customers, target GGS2.non_us_customers,
COLMAP (USEDEFAULTS, 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,COLMAP (USEDEFAULTS, contactfirst = @STREXT (contactname, 1, @STRFIND (contactname," ") - 1),contactlast = @STREXT (contactname, @STRFIND (contactname," ") + 1, @STRLEN (contactname)))WHERE (country = "USA");
• A graphical enterprise application that offers a visual and intuitive way to define, configure, manage, and report on all GoldenGate transactional data synchronization processes
Synchronize Synchronize
SynchronizeSynchronizeDirector
GoldenGate GoldenGate
GoldenGate
GoldenGate
GoldenGate
Mgt Pack for GoldenGate (Director) – Overview
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
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)
Oracle GoldenGate/ODI Use Cases & Demo CoverageEnterprise-wide Solution for Real Time Data Needs
Log Based, Real-Time Change Data Capture
Heterogeneous Source Systems
EDWODS
EDW
Disaster Recovery, Data Protection
Zero Downtime Migration and Upgrades
Operational Reporting
Real-time BI
Standby(Open & Active)
ReportingDatabaseOGG
ODI/ETL
ODI/ETL
Query Offloading
Data Distribution
1Continuous Availability(OGG)
2Real-time OR, BI /DWH, MDM(ODI/OGG)
3Lower IT Cost(OGG)
What’s the difference between
Operational Reporting &
Data Warehouse/BI
SOA Integration /Web Services (ODI/OGG)
OGG: Oracle GoldenGate ODS: Operational Data Store TCO: Total cost of ownership BI: Business IntelligenceODI: Oracle Data Integrator EDW: Enterprise Data Warehouse ROI: Return of investment ETL: Extract-Transform-Load Tool
What’s Master Data Management?
24
What is Operational Reporting, DWH/BI, & MDM?
Oracle Data Integrator
ODS Schema DW Star Schema
On-Disk Logs
OracleGoldenGate
OracleGoldenGate
OBI EE Suite/ MDM SuiteODI (Data Profile/Quality)
• 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)
Cust
FACT
Prod
Prod CatTerritory
Order, Call Log
On-Disk Logs
Cust B
CRM
Territory
Call Log
Prod B
Cust A
ERP Financial
Prod A
Order
Prod Cat
Cust AProd A
Prod Cat
Order
Cust BProd B
TerritoryCall Log
MDMGenerates consolidated BI reportsGenerates simple
operational reports
Dimension tables
25
ODI or ETL Tools
DW Star Schema
On-Disk Logs
OracleGoldenGate
OracleGoldenGate
OBI EE Suite/ MDM SuiteODI (Data Profile/Quality)
or third-party tools
Cust
FACT
Prod
Prod CatTerritory
Order, Call Log
On-Disk Logs
Cust B
CRM
Territory
Call Log
Prod B
Cust A
ERP Financial
Prod A
Order
Prod Cat
Cust AProd A
Prod Cat
Order
Cust BProd B
TerritoryCall Log
MDM
Offload operational reporting work to outside the OLTP systems to speed up the OLTP system
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
Staging Area
ODS Schema
Real-time data replication is the key for achieving the fast real-time ORs > OGG
26
ODI or ETL Tools
DW Star Schema
On-Disk Logs
OBI EE Suite/ MDM SuiteODI (Data Profile/Quality)
or third-party tools
Cust
FACT
Prod
Prod CatTerritory
Order, Call Log
On-Disk Logs
Cust B
CRM
Territory
Call Log
Prod B
Cust A
ERP Financial
Prod A
Order
Prod Cat
Cust AProd A
Prod Cat
Order
Cust BProd B
TerritoryCall Log
MDM
View data in operational reports real time
Many customers are still seeing yesterday’s data in Operational Reports
Viewing yesterday’s data in operational reports
ODS SchemaNightly Batch Process
Nightly Batch Process
OracleGoldenGate
OracleGoldenGate
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)
28
Oracle Data Integrator
ODS Schema DW Schema
OracleGoldenGate
OracleGoldenGate
OBI EE Suite/ MDM SuiteODI (Data Profile/Quality)
Cust
FACT
Prod
Prod CatTerritory
Order, Call Log
Cust AProd A
Prod Cat
Order
Cust BProd B
TerritoryCall Log
MDM
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.
On-Disk Logs
Cust AERP Financial
Prod A
Order
Prod Cat
RDBMS
On-Disk Logs
Cust B
CRM
Territory
Call Log
Prod B
RDBMS
29
Oracle Data Integrator
ODS Schema DW Schema
On-Disk Logs
OBI EE Suite/ MDM SuiteODI (Data Profile/Quality)
Cust
FACT
Prod
Prod CatTerritory
Order, Call Log
On-Disk Logs
Cust B
CRM
Territory
Call Log
Prod B
Cust AERP Financial
Prod A
Order
Prod Cat
Cust AProd A
Prod Cat
Order
Cust BProd B
TerritoryCall Log
MDM
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
RDBMS
SQLs:select * from customers …;
SQLs:select * from customers …;
OracleGoldenGate
OracleGoldenGate
What happens without real-time log-based CDC & Timestamp?• Huge data traffic – all records need to be copied to the DWH repository
Customer Total Timestamp
Optus $100,000 10-Mar-2011, 3:00 PM
Telstra $150,000 9-Mar-2011, 3:00 AM
ANZ $120,000 8-Mar-2011, 3:00 AM
CBA $40,000 10-Mar-2011, 9:00 AM
With Timestamp:Last Refresh Date
10-Mar-2011, 3:00 AM
OLTP Order Table:Refresh History Table:
√
√
Check new records DWH Fact Table:
Customer Total Timestamp
Telstra $150,000 9-Mar-2011, 3:00 AM
ANZ $120,000 8-Mar-2011, 3:00 AM
Copy new records only
since last refresh Optus $100,000 10-Mar-2011, 3:00 PM
CBA $40,000 10-Mar-2011, 9:00 AM
Customer Total Timestamp
Optus $100,000 10-Mar-2011, 3:00 PM
Telstra $150,000 9-Mar-2011, 3:00 AM
ANZ $120,000 8-Mar-2011, 3:00 AM
CBA $40,000 10-Mar-2011, 9:00 AM
Without Timestamp:OLTP Order Table:
DWH Fact Table:
Customer Total
Telstra $150,000
ANZ $120,000
Copy ALL records to the staging area
Optus $100,000
CBA $40,000
Customer Total
Optus $100,000
Telstra $150,000
ANZ $120,000
CBA $40,000
Staging Customer Table:
DW Schema
Customer Total
Telstra $150,000
ANZ $120,000
Without Timestamp with log-based CDC:
Optus $100,000
CBA $40,000
Copy as a new record is created
DWH Fact Table:Customer Total
Telstra $150,000
ANZ $120,000
DW Schema
Optus $100,000
CBA $40,000Copy as a new record is created
Use some
complex logics to identify
new records
CDC helps customers to replicate data WITHOUT changing anything on both source & target DBs!
It’s hard to change the data structure to add the timestamp!
Appendix• Customer References
• Bombay Stock Exchange Ltd• Sabre Holdings• RIM BlackBerry
32
Customer Success Story (OGG):Bombay Stock Exchange Processes Online Transactions 10% to 15% Faster
“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
ProfileEstablished in 1875, Bombay Stock Exchange (BSE) is the oldest stock exchange in Asia. Today, around 4,397 companies with a total market capitalization of US$1.06 trillion (as of July 2009) are listed on BSE’s bourse, more than any other stock exchange in the world. The BSE Index, SENSEX, is India’s most popular stock market benchmark index and includes 30 stocks representing 12 market sectors.
• Business Challenges• Deploy a data replication tool that would capture transactions at the company’s main data
centre and deliver them quickly to many locations running multiple target databases• Meet regulations that required transactional data to be accessible online
• Oracle’s Solution• Capture transactional data from its core system, deliver it to multiple target databases at
several locations and make this data available online for analysis• 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
CHALLENGES / OPPORTUNITIES• Optimize OLTP system performance –
offload all query activity• Reduce TCO via platform changes and
segment “lookers” from “bookers”• Handle growing data volumes and
support heterogeneous systems over life-cycle of Air Travel Shopping Engine (ATSE)
• Maintain data integrity across all systems
GoldenGate PROVIDES• Supports 1.6TB of data movement per day
to read-only servers• Source– Oracle 10g | Target – MySQL• Current plans to migrate to Oracle 11g on
both source and target.
RESULTS• 80% TCO Reduction – Millions $$ saved
COMPANY OVERVIEWA world leader in the travel marketplace, Sabre Holdings merchandises and retails travel products and provides distribution and technology solutions for the travel industry
Query Offloading Case StudyQuery Offloading Saves Millions $
“Bookers vs Lookers”
Query OffloadingDatabase Tiering: Save Millions and Drive Revenue
Business Challenges: Ensure continuous availability of Air Travel Shopping
Engine (ATSE) throughout upgrades and migrations Optimize system performance to handle growth and
scale, while reducing TCO via platform changes Low latency data movement between OLTP
(“bookers”) and search database (“lookers”) GoldenGate Solutions: Zero Database Downtime during hardware and
database migration for ATSE Database Tiering insures high availability, top
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 Higher customer satisfaction - improved response
times and availability
“GoldenGate…provided the glue to move the data acrosssystems, evolving our overall approach in parallel with thefuture of online travel commerce.”- James Harding, VP of Systems Planning & Performance, Sabre
156 MySQL Databases on Linux
Database Tiering Enables Top Performance, Unlimited Scalability
Schedule & Availability Updates
Fare & Rule Updates
Oracle Databases
“Lookups”
GoldenGate Transaction UpdatesUp to 800,000 p/hour, 1.6 TB/day
Air Travel Shopping
Schedule & Availability Changes Accounts
& Billing
• 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.
35
Value PropositionReduce IT Costs
Oracle GoldenGate can enable cost savings of 80%
Query Offloading
COMPANY OVERVIEWResearch In Motion (RIM) is a leading designer, manufacturer and marketer of innovative wireless solutions for the worldwide mobile communications market.
CHALLENGES / OPPORTUNITIES• Experiencing exponent growth and
stringent carrier SLA’s to meet growing demand for Blackberry devices
• Maintain continuous availably of critical data across all global data centers (4) for manufacturing plants
• All plants see same data and can move devices based on regional demand
GoldenGate PROVIDES• Active-Active configuration provides same
data across worldwide plants• Improved system performance & response
times• Zero downtime DB migration to new version
using phased approach
RESULTS• All of manufacturing from VP to
engineer on plant floor can access most up to date info.
Continuous Availability Case StudyHigh Availability of Manufacturing Application Meets Stringent SLAs
Zero Downtime Migration & UpgradeZero Downtime OS and DB Migrations
Business Challenges:• RIM BlackBerry has a very small monthly window to
take downtime for upgrades, migrations, and planned maintenance
• Strict SLA limits with the major Telcos!
GoldenGate Solution:• Zero-Downtime Upgrade and OS change for
business-critical Oracle databases• GoldenGate Veridata™ to ensure that all data from
source is in-synch with target and that migration has been successful
GoldenGate Benefits:• Fail-back capability to reduce business risk and
downtime• No manual intervention to reduce possibility of
errors• Assurance of database consistency with Veridata.
Zero-Downtime OS and Database Upgrade with Application Maintenance
Provisioning
Oracle 10.2.0.3HP-UX
Waterloo
Oracle 10.2.0.4HP-UX
Slough, U.K.
Application Maintenance
ProvisioningDatabase migration
with failback