Oracle Goldengate: Real-Time Access To Real-Time Information

Oracle GoldenGate Real-time Access to Real-time Information
Gavin Soorma
Senior Principal Consultant, OnCall DBA
Introduction
Oracle buys GoldenGate -transaction closed September 3, 2009
A private company, founded in 1995 and based in San Francisco, CA

Recognized by industry analysts as a leader in real-time data integration Fortune 500 customers across several key industries including Financial Services,Communications, Healthcare, Public Sector, Retail and Utilities Extends Oracles data integration capabilities with the fastest, most scalable realtime Heterogeneous data integration solution Another Oracle High Availability solution Oracle Streams not widely accepted by the community a strategic replacement?
GoldenGate Oracle Product Positioning
Real-Time Data Integration
Continuous Low Latency Data Movement
Oracle GoldenGate
High Availability
Continuous Operations
2009 Oracle Corporation
GoldenGate is Strategic to Oracle

Best-in-class leader in real-time data solutions Complements existing Oracle products Continuous Availability for heterogeneous systems Real-time data access for Reporting & BI/EPM Zero-downtime migrations / upgrades to Oracle Database and Applications Over 500 customers with 4,000+ implementations across Fortune 500 companies: Financial Services, Communications, Healthcare, Public Sector, Retail & Utilities industries Top 3 of 5 largest commercial banks Top 3 of 3 busiest ATM networks Top 7 of 10 financial data services companies Top 4 of 5 telecommunications providers Top 3 of 5 largest food & drug stores
Some Success Stories

Supports 1.6TB of data movement per day to read-only servers Source Oracle 10g | Target MySQL
Real-time data integration from Siebel CRM on Oracle to central Teradata Warehouse
GoldenGate moves 150-200 million records per day with 1.5 second latency.
5 way Active-Active configuration provides same data across worldwide plants Zero downtime DB migration to new version using phased approach
What is Oracle GoldenGate?

Oracle GoldenGate provides low-impact capture, routing, transformation, and delivery of transactional data across heterogeneous environments in real-time
Why Oracle GoldenGate?
High Performance Non-intrusive, low-impact, sub-second latency
Extensibility & Flexibility
Open, modular architecture - Supports heterogeneous sources and targets Maintains transactional integrity - Resilient against interruptions and failures
Reliability
6 Oracle GoldenGate Real-time Access to Real-time Information
Oracle GoldenGate Heterogeneity

Supports Applications running on .
Databases
Capture:
Oracle DB2 Microsoft SQL Server Sybase ASE Teradata
O/S and Platforms

Windows 2000, 2003, XP Linux Sun Solaris HP NonStop HP-UX HP TRU64 HP OpenVMS IBM AIX IBM z/OS
Delivery:
All listed above, plus:
MySQL, TimesTen, Netezza, Greenplum, HP Neoview and any ODBC compatible databases ETL products JMS message queues
Oracle GoldenGate Products

Oracle GoldenGate GoldenGate Management Pack Golden Gate Veridata Available for download on Oracle E-Delivery web site. Available on all major platforms for Oracle versions 10g and 11g
Oracle GoldenGate Solutions

Disaster Recovery, Data Protection
Standby (Open & Active)
Microsoft SQL Server
Zero Downtime Migration and Upgrades
Oracle
Log Based, Real-Time Change Data Capture
Operational Reporting
GoldenGate
Reporting Database
Enterprise-wide Solution for Real Time Data Needs EDW

ODS Sybase ETL
Real-time BI Query Offloading
ETL
Heterogeneous Source Systems
EDW
Data Distribution
Oracle GoldenGate Topologies

Unidirectional Query Offloading Bi-Directional Live Standby or Active-Active for HA Peer-to-Peer Load Balancing, Multi-Master
Broadcast Data Distribution
Integration/Consolidation Data Warehouse
Cascading Data Marts
Integration with Data Warehousing

Integration via:
Staging tables Flat files Messaging
ETL
Integration via Staging Tables Delivery
Integration via Files
Capture
Trail File
Delivery
data data data files files
files
ETL
OLTP
Integration via Messaging Delivery
ETL
JMS / MoM Queue or Topic
Architecture
Manager process Capture Extract process Delivery Replicat process Data Pump Trail files Checkpoints Data definition files Log files, Discard files
GoldenGate Architecture
Source Database
Network (TCP/IP)
Extract
Collector
Transaction Logs
Data Pump
Replicat
Local Trail
Remote Trail
Manager
Manager Target Database

Manager
Manager process runs on source and target One manager per GoldenGate installation Supports multiple Extract and Replicat processes Starts other GoldenGate processes Performs trail file management purge old extracts Responsible for Event and Error reporting
Extract
Extract process runs on the source system Capture mechanism of GoldenGate Configure extract for
Initial Data Load Online Change Synchronization
Multiple Extract processes can operate on multiple database objects
Data Pump
Secondary extract group within source GoldenGate configuration Optional When not used, Extract sends data over network to remote trail on destination When used, Extract process writes to local trail. Data Pump reads this trail and writes to the remote trail Data pump isolates the primary extract process from TCP/IP activity Performs data filtering, mapping and conversion Protects against network and target failures
Replicat
Runs on target system Reads and extracts data changes (and DDL changes when configured) Configured for initial load or online change synchronization. Multiple replicat processes to increase throughput Configured with a apply delay if required
Trail Files
Series of files stored on disk to enable continuous extraction and propagation of changes Can exist on the source (local trail), target (remote trail) or on intermediary systems By default 10 MB in size Files are named at configuration time with a two letter identifier and are appended with an unique 6 digit serial number GoldenGate writes to trail files in Universal Data Format a proprietary format This proprietary format enables the heterogeneous data integration Checkpoints provide known position in trail from where to start in case of unexpected failure Records of checkpoints maintained in file on disk or database checkpoint table
How Oracle GoldenGate Works

Capture: Committed changes are captured (and can be filtered) as they occur by reading the transaction logs. Trail files: Universal data format enables heterogeneity. Route: No distance constraints via TCP/IP. Compression & encryption. Delivery: Applies transactional data with guaranteed integrity.
Redo/archived log
Capture
Source Trail
LAN / WAN / Internet
Target Trail
Deliver
Source Oracle Database
Target Trail
Deliver
Source Trail
Capture
Bi-directional
Target Database
GoldenGate and Oracle Data Source

Capture
Archived Log Online Log
Capture OCI/Oracle Net
Commit Ordered Trail
Support for Oracle RAC Support for ASM Support for index-organized tables (IOTs) with overflow Support for clustered tables Support for object tables Support for object types (UDTs) Support for DDL operations Archived log only (ALO) mode Off-platform capture (LOGSOURCE) Multi-threaded capture
GoldenGate and Microsoft SQL Server

Capture
Backups
Ability to capture from native Microsoft SQL ServerCapture

Transaction Log
Capture ODBC
Commit Ordered Trail
backups Coexistence with SQL Server Replication SQL Server Replication components are not required Support tables with no unique key (not supported by SQL Server Replication) Support for computed columns Support for identity columns
Zero-Downtime Upgrades, Migrations and Maintenance

Eliminate planned downtime during:
Database Upgrades: Oracle 8i or 9i 11g Database Migrations (e.g. DB2 to Oracle) Database Server Upgrades
Database Maintenance (e.g. de-fragmentation, table re-orgs, dechaining rows, etc.)

Application Upgrades (validated for Siebel CRM)
Oracle 8i or 9i to 10/11g Upgrades/Migrations

What GoldenGate TDM offers:
Minimal impact and continuous availability during the project Technology solution for eliminating database downtime Improved capabilities for data integrity, accuracy Fail-back solution in the event of unexpected issues/downtime
Proven with customers in production
Cross Platform Migration

9i Clone 10g
9i Solaris
2 3,4 1
Solaris
1. 2. 3. 4.
Start GoldenGate TDMs Capture process Set up Clone database, then Upgrade to 10g Cross platform transportable tablespaces metadata export Use a full database NOROWS export (Views, Packages, etc)

9i Clone 10g
9i Solaris
2 3,4 1 6,7
Solaris
10g
5
1. 2. 3. 4. 5. Start GoldenGate TDMs Capture process Set up Clone database, then Upgrade to 10g Cross platform transportable tablespaces metadata export Use a full database NOROWS export (Views, Packages, etc) Set up a new 10g vanilla target 6. 7.
Linux
Cross platform transportable tablespaces metadata import Full import with IGNORE option

9i Clone 10g
9i Solaris
2 3,4 1 8 6,7
Solaris
10g
5
1. 2. 3. 4. 5. Start GoldenGate TDMs Capture process Set up Clone database, then Upgrade to 10g Cross platform transportable tablespaces metadata export Use a full database NOROWS export (Views, Packages, etc) Set up a new 10g vanilla target 6. 7. 8. 9.
Linux
Cross platform transportable tablespaces metadata import Full import with IGNORE option Start GoldenGate TDM Apply process at target **SWITCHOVER** (not depicted)

9i Clone 10g
9i Solaris
2 3,4 1 8 6,7
Solaris
11
Failback contingency
1. 2. 3. 4. 5. Start GoldenGate TDMs Capture process Set up Clone database, then Upgrade to 10g Cross platform transportable tablespaces metadata export Use a full database NOROWS export (Views, Packages, etc) Set up a new 10g vanilla target
10g
10
6.
Linux
Cross platform transportable tablespaces metadata import 7. Full import with IGNORE option 8. Start GoldenGate TDM Apply process at target 9. Verify data (optional) 10. **SWITCHOVER** (not depicted) 11. Start TDM Capture at target (new source) 12. Start TDM Apply at target (old source)
GoldenGate Management Pack
Graphical interface for designing, configuring, managing, monitoring,

and reporting on the Oracle GoldenGate components.
Golden Gate Director Server GoldenGate Director Client

GoldenGate Web Director
GoldenGate Veridata
GoldenGate Veridata is a high-speed, low impact data comparison solution
that identifies and reports data discrepancies between two databases, without interrupting those systems or the business processes they support.
High-speed data comparison solution Identifies and reports on data discrepancies between heterogeneous databases with minimal impact on infrastructure Companies can audit and verify large volumes of data across different business applications with certainty It enables users to research root causes of the discrepancies and determine how to remedy the problem and avoid future risk.
GoldenGate Veridata
GoldenGate Veridata: How it Works

The user chooses tables or files on the source and target databases The comparison is initiated from the GUI, command line or batch As the databases continue to change, GoldenGate Veridata reports: Persistent discrepancies In-flight data discrepancies (user configurable)
GoldenGate Director
GoldenGate Director
GoldenGate Director Web
GoldenGate Demo
Create the Manager, Extract and Replicat Process

./ggsci GGSCI (linux01.oncalldba.com) 1> edit params mgr PORT 7809 USERID ggs_owner, PASSWORD ggs_owner
Source Extract Process

GGSCI (linux01.oncalldba.com) 1> ADD EXTRACT ext1, TRANLOG, BEGIN NOW EXTRACT added.
GGSCI (linux01.oncalldba.com) 2> ADD RMTTRAIL /home/oracle/goldengate/dirdat/rt, EXTRACT ext1

RMTTRAIL added.
Target Replicat Process

GGSCI (linux03.oncalldba.com) 1> ADD REPLICAT rep1, EXTTRAIL /home/oracle/goldengate/dirdat/rt REPLICAT added.
Start Manager (Source)

[oracle@linux01 goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle Version 10.4.0.19 Build 002 Linux, x86, 32bit (optimized), Oracle 10 on Sep 17 2009 23:49:42
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI (linux01.oncalldba.com) 1> start manager Manager started.
GGSCI (linux01.oncalldba.com) 2> info all Program MANAGER Status RUNNING Group Lag Time Since Chkpt
Start Manager (Target)

[oracle@linux03 goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle Version 10.4.0.19 Build 002 Linux, x86, 32bit (optimized), Oracle 11 on Sep 29 2009 08:50:50
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI (linux03.oncalldba.com) 1> start manager Manager started.
GGSCI (linux03.oncalldba.com) 2> info all Program MANAGER Status RUNNING Group Lag Time Since Chkpt
Source Start Extract Process

GGSCI (linux01.oncalldba.com) 4> view params ext1
EXTRACT ext1 USERID ggs_owner@oncalldb.world,password ggs_owner RMTHOST linux03, MGRPORT 7809 RMTTRAIL /home/oracle/goldengate/dirdat/rt DDL INCLUDE ALL TABLE ggs_owner.myobjects; ; GGSCI (linux01.oncalldba.com) 5> start extract ext1 Sending START request to MANAGER ... EXTRACT EXT1 starting
GGSCI (linux01.oncalldba.com) 6> info extract ext1 EXTRACT EXT1 Checkpoint Lag Log Read Checkpoint Last Started 2010-08-13 13:12 Status RUNNING 03:00:32 (updated 00:00:00 ago) Oracle Redo Logs 2010-08-13 10:12:23 Seqno 4, RBA 19823616
Target Start Replicat Process

GGSCI (linux03.oncalldba.com) 3> view params rep1 REPLICAT rep1 ASSUMETARGETDEFS USERID ggs_owner, PASSWORD ggs_owner discardfile /home/oracle/goldengate/dirout/discard.txt,append DDL INCLUDE MAPPED MAP ggs_owner.myobjects, TARGET ggs_owner.myobjects; GGSCI (linux03.oncalldba.com) 4> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting
GGSCI (linux03.oncalldba.com) 5> info replicat rep1 REPLICAT REP1 Checkpoint Lag Log Read Checkpoint Last Started 2010-08-13 13:15 Status RUNNING 00:00:00 (updated 00:00:04 ago) File /home/oracle/goldengate/dirdat/rt000010 2010-08-13 13:12:55.105247 RBA 1030
Test DDL Replication

Create a table and index on source database
[oracle@linux01 ~]$ sqlplus ggs_owner/ggs_owner SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 13 13:09:38 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table myobjects as select * from dba_objects where 1=2; Table created. SQL> alter table myobjects add constraint pk_myobjects 2 primary key (object_id);
Table altered.
Test DDL Replication

On Target database MYOBJECTS table and index created
SQL> desc myobjects Name Null? ----------------------------------------- -------OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID NOT NULL DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY Type ---------------------------VARCHAR2(30) VARCHAR2(128) VARCHAR2(30) NUMBER NUMBER VARCHAR2(19) DATE DATE VARCHAR2(19) VARCHAR2(7) VARCHAR2(1)
SQL> select index_name from user_indexes where table_name='MYOBJECTS';
INDEX_NAME -----------------------------PK_MYOBJECTS
Test Online Change Synchronization

INSERT rows on source tables
SQL> !date Fri Aug 13 13:24:14 WST 2010 SQL> insert into myobjects select * from dba_objects where owner='SYSMAN'; 1341 rows created. SQL> commit; Commit complete
SQL> !date Fri Aug 13 13:27:01 WST 2010 SQL> insert into myobjects select * from dba_objects where owner='SYS'; 22937 rows created. SQL> commit; Commit complete.
Test Online Change Synchronization

Check target tables to see that all the rows have been inserted
SQL> !date Fri Aug 13 13:24:44 WST 2010 SQL> select count(*) from myobjects; COUNT(*) ---------1341
SQL> !date Fri Aug 13 13:27:14 WST 2010

SQL> select count(*) from myobjects;
COUNT(*) ---------24278
Monitor Replicat Progress

GGSCI (linux03.oncalldba.com) 8> stats replicat rep1 Start of Statistics at 2010-08-13 13:35:39. DDL replication statistics: *** Total statistics since replicat started Operations Mapped operations Unmapped operations Other operations Excluded operations Errors Retried errors Discarded errors Ignored errors *** 2.00 2.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Replicating from GGS_OWNER.MYOBJECTS to GGS_OWNER.MYOBJECTS: *** Total statistics since 2010-08-13 13:24:33 *** Total inserts 24278.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 24278.00
Cleanup some more DDL Replication

SOURCE SQL> truncate table myobjects; Table truncated. TARGET SQL> select count(*) from myobjects;
COUNT(*) ---------0 SOURCE SQL> drop table myobjects; Table dropped. TARGET SQL> select count(*) from myobjects; select count(*) from myobjects * ERROR at line 1: ORA-00942: table or view does not exist
gavin.soorma@oncalldba.com.au http://www.gavinsoorma.com http://www.oncalldba.com.au


Oracle Goldengate: Real-Time Access To Real-Time Information

Uploaded by

Copyright:

Available Formats

Oracle Goldengate: Real-Time Access To Real-Time Information

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Oracle Goldengate: Real-Time Access To Real-Time Information

Uploaded by

Copyright:

Available Formats

What is Oracle GoldenGate?

What is Oracle GoldenGate?

What are some key features of Oracle GoldenGate?

What are some key features of Oracle GoldenGate?

Oracle GoldenGate Real-time Access to Real-time Information