oracle goldengate 101 - introduction
TRANSCRIPT
-
7/29/2019 Oracle GoldenGate 101 - Introduction
1/73
T : +44 (0) 8446 697 995 or(888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
Borkur Steingrimsson, Rittman Mead ConsultingRMOUG, Denver, CO, February 2012
Oracle GoldenGate 101:A newbie's dive in to the unknown
Monday, 27 February 12
-
7/29/2019 Oracle GoldenGate 101 - Introduction
2/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Introduction
Brkur Steingrimsson
Principal Consultant at
Rittman Mead Consulting
Based in Brussels, Belgium
Blog http://www.rittmanmead.com/blog/
15 years of dabbling in Oracle technology
Discoverer
DW
OWB / ODI OBIEE
OGG
Monday, 27 February 12
http://www.rittmanmead.com/blog/http://www.rittmanmead.com/blog/http://www.rittmanmead.com/blog/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
3/73
T : +44 (0) 8446 697 995 or(888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
About Rittman Mead
Oracle BI and DW specialized partner
World leading specialist partner for technical excellence, solutions delivery and innovation inOracle BI
50+ consultants all expert in Oracle BI and DW
Global organisation, local delivery teams
Offices in UK, US, Europe, India, Australia Skills in broad range of supporting Oracle tools:
OBIEE
OBIA
ODI
OWB
Essbase, Oracle OLAP
GoldenGate
Exadata
Exalytics
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
4/73
T : +44 (0) 8446 697 995 or(888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
About Rittman Mead
Oracles most trusted BI partner
Writing official OBIEE book
ACE Director and Oracle ACEs
Voted partner of the year by UKOUG 3 of 4years
Run the worlds first and only expert technicalBI forum in Europe and the US
Solutions delivery experience in a wide rangeof regions, industries and technologies
Services based on unparalleled experience
and world leading best practices Consulting
Training
Support
Expertise
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
5/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Agenda
Introduction to GoldenGate
Technical Things
Installation
Platforms / Services
Development Deployment
Demo
Interface; command line & GUI
DML & DDL
Log files GoldenGate and ODI
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
6/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Oracle GoldenGate
So youve heard about Oracle GoldenGate?
Whats all the Fuss about?
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
7/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Introduction to Oracle GoldenGate
Acquired in 2009 from GoldenGate
Best-of-Breed & Easy-to-Deploy product
Change Data Capture engine (CDC)
Replicate and integrate transactional data
Sub-second speed Multiple enterprise system support
Carry data between (heterogenous) systems
Oracle databases
DB/2
MSSQL ... and so on
Support for different hardware platforms
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
8/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Oracle GoldenGate
Supported Oracle Versions
9i Release 2
11g Release 2
CDC
Real-Time Log-based
Large data volume
Low-impact and footprint
DML and DDL replication
ETL - E-LT Filter
Mapping
Transformations
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
9/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Whats in the Box?
Oracle GoldenGate
Management Pack
Oracle GoldenGate Veridata
Oracle GoldenGate Application Adapters
Oracle GoldenGate for Mainframes A full Oracle Active Data Guard license
Stand-by databases
A full Oracle XStreams - Oracle Streams API - License
Oracle GoldenGate Statement of Directions tells us that
Given the strategic nature of Oracle GoldenGate, Oracle Streams willcontinue to be supported, but will not be actively enhanced. Rather, the bestelements of Oracle Streams will be evaluated for inclusion with OracleGoldenGate.
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
10/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Technical Things
Local installation of Oracle GoldenGate on each participating box
Oracle GoldenGate
Protocols: TCP/IP
Managers default listen on port 7809
Movement on data can be encrypted MS Windows can run the Manager as a service
Create scripts to start up on Unix
Increasing number of platforms supported
iOS5 support for iPad is probably in the making ;)
EXTRACT and REPLICAT groups are created and configured to move dataaround
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
11/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
From the Doco: Architecture
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
12/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Availability
Zero-downtime operations
Enable uninterrupted business operations during system upgrade, migration,and maintenance activities.
Disaster recovery and data protection
Immediate failover with up-to-the-minute data to minimize recovery time Deploy with Oracle Database across database versions or operating systems
Non-Oracle environments.
Data distribution
Synchronize data for distributed applications in real time across geographiesfor reliable access to timely data.
Query offloading
Ensure high performance for production systems while still supportingnecessary read-only activities by replicating data between heterogeneoussources and targets.
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
13/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Real-Time Data Integration
Real-time data warehousing
Provide continuous, real-time capture and delivery of the most-recent changed databetween OLTP systems and the data warehouse. Oracle GoldenGate integrates easilywith Oracle Data Integrator Enterprise Edition 11g and other extract, transform, and load(ETL) solutions. Oracle GoldenGate 11g is certified to capture from and deliver to Oracle
Exadata Storage Server to enable real-time data warehousing or data consolidationsolutions.
Operational reporting
Offload reporting activity from production databases to lower- cost secondary systemswith current data for real-time reporting. Oracle GoldenGate 11g is certified to supportmajor Oracle applications, including Oracle E-Business Suite, JD Edwards, PeopleSoftand Siebel CRM, for operational reporting solutions.
Operational data integration
Integrate operational data between OLTP systems in real time. Enable service-orientedarchitectures, including Oracle SOA Suite, to operate with real-time data by publishingchanged data via Java Message Service (JMS) using Oracle GoldenGate Application
Adapters.
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
14/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
White Paper Points
Real-time data
Immediately captures, routes, transforms, and delivers transactional data toother systems with sub-second latency. Improves organizational decision-making through enterprise-wide visibility into accurate, up-to-date information.
Heterogeneous support Supports heterogeneous databases and platforms to increase IT flexibility.
Extracts data from existing IT investments and lowers your total cost ofownership while unifying data from all enterprise systems.
Reliability
Delivers all committed records to the target, even in the event of network
outages. Moves data without requiring system interruption or outage windows. High performance with low impact
Moves thousands of transactions per second with negligible impact on sourceand target systems. Enables access to critical information in real time withoutbogging down production systems.
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
15/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
White Paper Points
Transaction integrity
Maintains transaction commit boundaries and atomicity, consistency, isolation,and durability (ACID) properties as transactions are moved between sourceand target systems. Ensures data consistency and referential integrity across
multiple masters, back-up systems, and reporting databases. Integration
Integrates with Oracle Data Integrator Enterprise Edition and complementsother ETL solutions. Via Oracle GoldenGate Application Adapters, it allows tocapture from, or deliver to, Java Message Servicebased messaging solutionssuch as Oracle WebLogic.
Flexible topology support Moves data in one-source-to-one-target, one-to-many, many-to-one, many-to-
many, cascading, and bidirectional configurations.
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
16/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
From the Doco: Supported Topologies
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
17/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
White Paper Points
Conflict detection and resolution
Enables conflict detection and resolution in multi-master configurations wheretwo systems can modify separate instances of the same table.
Event based infrastructure
Triggers immediate actions based on specific database operations capturedand stored in Trail Files
Routing and compression
Utilizes TCP/IP to send data and eliminate geographical distance constraints.
Applies additional compression to the data as it is routed.
Data encryption
Securely transmits data for domestic and international applications withvariable key length encryption.
Monday, 27 February 12
-
7/29/2019 Oracle GoldenGate 101 - Introduction
18/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
White Paper Points
Deferred apply
Applies data immediately or at a deferred time chosen by the user, withoutlosing transaction integrity.
Automated memory management
Automatically adjusts transaction memory based on the size and number oftransactions being capturing.
Bounded Recovery
Persists uncommitted operations to disk to enable fast and simple datarecovery for long running transactions in the event that the replication processis paused or interrupted.
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
19/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Having Said That ...
There is no magic!
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
20/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Having Said That ...
There is no magic!But it is quite DBA centric
- so perhaps there is some magic?
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
21/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Whats New in 11g?
Certification for operational reporting solutions on Oracle Applications such asOracle E-Business Suite, Oracle PeopleSoft, and Oracle JD Edwards.
Support for more data types and direct loads for Oracle Database, andcertification on Oracle Exadata.
Expanded heterogenous support via: Log-based capture from, and delivery to IBM DB2 v9.7,
Native delivery to Oracle TimesTen databases.
Capture from JMS-based messaging systems
MySQL v5
For the Oracle Database, simplified recovery to significantly reduce recovery
windows for long running transactions in case of process interruptions.
Increased transaction tracing flexibility to easily identify bottlenecks and tune thedata integration solution for optimum performance
Tighter integration with ODI11g (well, from ODIs side)
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
22/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Building Blocks
Extract groups
Replication groups
Trails
Maps
Files parameter files
scripts and the OBEY command
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
23/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Installation
Each node will have the Oracle GoldenGate software installed
Download the platform-specific zip files from EDelivery.oracle.com
- Operating system
- 32bit or 64 bit
- 10.4 or the new 11g release- Database vendor & version
Unzip the downloaded file in to a new directory
Create default directories to store our metadata
Configure the Manager on each node
Which port (default is 7809) Service name
Windows: Configure to run as Service & Install Event Messages
Bobs your uncle
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
24/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Configuration
The GGSCI utility (GoldenGate Command Interpreter) is our first entry point tothe installation
Command-Line interface
Configure the Manager process
Manage extractions and replications Edit parameters
- Global
- extract / replicat specific
Help command
- Often with examples
Run scripts
View statistics
... and then some
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
25/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Configuration: Create Subdirs
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
26/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Configuration: Edit Parameters
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
27/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Configuration: Edit Parameters
port 7809
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
28/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Configuration: Edit Parameters
port 7809
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
29/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Configuration: Edit Parameters
port 7809
MGRSERVNAME Oracle_GG_Mgr
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
30/73
-
7/29/2019 Oracle GoldenGate 101 - Introduction
31/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Configuration: Install Service
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
32/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Initial Load
We want to have special loads that get all the data sources & targets in synch
We can filter, transform and map the source to target or just pass the datathrough directly
SOURCEISTABLE keyword allows us to by-pass trail files on extract
SPECIALRUN keyword makes for a one-time run and not a continuous process BULKLOAD tells the replicate process to interface with SQL*Loader
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
33/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Initial Load
We want to have special loads that get all the data sources & targets in synch
We can filter, transform and map the source to target or just pass the datathrough directly
SOURCEISTABLE keyword allows us to by-pass trail files on extract
SPECIALRUN keyword makes for a one-time run and not a continuous process BULKLOAD tells the replicate process to interface with SQL*Loader
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
34/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Initial Load: Create Extract
Using the GGSCI Command-line Interpreter (on the source box)
ADD EXTRACT OGG_IEXT, SOURCEISTABLE
EDIT PARAM OGG_IEXT
extract OGG_IEXTuserid OGG_gg, password password1
discardfile E:\oracle\GG\GG11g\dirrpt\OGG_IEXT.dsc, purge
rmthost winxpvm.rmcvm.com, mgrport 7810
rmttask replicat, group OGG_IREP
table OGG_GG.BOB;table OGG_GG.DEMO_TABLE;
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
35/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Initial Load: Create Replicat
Using the GGSCI Command-line Interpreter (on the target box)
ADD REPLICAT OGG_IREP, SPECIALRUN
EDIT PARAM OGG_IREP
replicat OGG_IREPdiscardfile E:\oracle\GG\GG11g_TRG\dirrpt\OGG_IREP.dsc, purge
--BULKLOAD
assumetargetdefs
userid OGG_gg_trg, password password1
map OGG_GG.BOB, target OGG_GG_TRG.BOB, colmap (A=B,B=A);map OGG_GG.DEMO_TABLE, target OGG_GG_TRG.DEMO_TABLE, colmap
(ID=ID, DATA=DATA, ENTRY_DATE=ENTRY_DATE);
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
36/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
From the Doco: Filter Examples
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
37/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Continuous Load
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
38/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Continuous Load
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
39/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Continuous Feed: Create Extract
Using the GGSCI Command-line Interpreter (on the source box)
ADD EXTRACT OGG_EX1
ADD RMTTRAIL E:\oracle\GG\GG11g_TRG\dirdat\O1 extract OGG_EX1
EDIT PARAM OGG_EX1
extract OGG_EX1userid OGG_gg, password password1
discardfile E:\oracle\GG\GG11g_1110\dirrpt\OGG_EX1.dsc, purge
--Connect to the remote host and write to trail. Path is relevant to remote host
rmthost winxpvm.rmcvm.com, mgrport 7810
rmttrail E:\oracle\GG\GG11g_TRG\dirdat\O1table OGG_GG.*;
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
40/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Continuous Feed: Create Replicat
Using the GGSCI Command-line Interpreter (on the target box)
ADD REPLICAT OGG_REP1
EDIT PARAM OGG_REP1
replicat OGG_REP1discardfile E:\oracle\GG\GG11g_1110_TRG\dirrpt\OGG_REP1.dsc, purge
assumetargetdefs
userid OGG_gg_trg, password password1
HANDLECOLLISIONS
map OGG_GG.*, target OGG_GG_TRG.*;
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
41/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
From the Doco: Parameter Examples
Sample Extract parameter fileEXTRACT capt USERID ggs, PASSWORD *********
DISCARDFILE /ggs/capt.dsc, PURGE
RMTHOST sysb, MGRPORT 7809
RMTTRAIL /ggs/dirdat/aa
TABLE fin.*; TABLE sales.*;
Sample Replicat parameter fileREPLICAT deliv USERID ggs, PASSWORD ****
SOURCEDEFS /ggs/dirdef/defs
DISCARDFILE /ggs/deliv.dsc, PURGE
GETINSERTS
MAP fin.account, TARGET fin.acctab, COLMAP (account = acct, balance =
bal, branch = branch);
MAP fin.teller, TARGET fin.telltab, WHERE (branch = NY);
IGNOREINSERTS
MAP fin.teller, TARGET fin.telltab, WHERE (branch = LA);
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
42/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Oracle GoldenGate Director (2.0.0.3)
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
43/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Oracle GoldenGate Director Web (2.0.0.3)
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
44/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Some debugging tools
Logfiles
E:\oracle\GG\GG11g\ggserr.log
E:\oracle\diag\rdbms\winxp11g\winxp11g\trace\ggs_ddl_trace.log
SQL> select sequence#,status from v$log;
ALTER EXTRACT OGG_EX1, BEGIN NOW
ALTER REPLICAT OGG_REP1, extseqno 38
SEND EXTRACT OGG_EX1, ROLLOVER
SEND EXTRACT OGG_EX1, SKIPTRANS ...
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
45/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
GGSCI (winxpvm) 49> send extract OGG_ex1, showtrans
Sending SHOWTRANS request to EXTRACT OGG_EX1 ...
Oldest redo log file necessary to restart Extract is:
Redo Log Sequence Number 39, RBA 10782224
------------------------------------------------------------
XID: 5.28.1323
Items: 564442
Extract: OGG_EX1
Redo Thread: 1
Start Time: 2011-09-17:18:35:02
SCN: 0.1402704 (1402704)
Redo Seq: 39
Redo RBA: 10783248
Status: Running
GGSCI (winxpvm) 50> send extract OGG_ex1, skiptrans 5.28.1323 FORCE
Sending skiptrans request to EXTRACT OGG_EX1 ...
Transaction [XID 5.28.1323, Redo Thread 1, Start Time 2011-09-21:18:35:02, SCN 0
.1402704 (1402704)] skipped.
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
46/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Oracle GoldenGate 101Demo DML in Action
Text
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
47/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
DDL Support
DDL operations are written to the redo log
CREATE / DROP
ALTER
RENAME
GRANT (etc)
Database Import
Various parameters control the replication process
DDLOPTIONS
DDLERROR
DDLSUBST
PURGEDDLHISTORY
PURGEMARKERHISTORY
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
48/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
DDL Support
tables
clusters
indexes
functions
packages
procedure
tablespaces
roles
sequences
synonyms
triggers
types
views
materialized views
users
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
49/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
DDL Support
Mapping occurs between source and target
Renaming of base objects and derived objects
Only propagate certain DDL actions
- EXCLUDE | INCLUDE
- OPTYPE
- OBJTYPE
- OBJNAME
- INSTR[WORDS]
- INSTRCOMMENTS[WORDS]
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
50/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
From the Doco: DDL Parameters Syntax
DDL &
INCLUDE UNMAPPED &
OPTYPE alter &OBJTYPE table &
OBJNAME scott.tab* &
INCLUDE MAPPED OBJNAME * &
EXCLUDE MAPPED OBJNAME "scott.temp*"
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
51/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Enable DDL Logging
Edit parameters for the Extract group using the GGSCI command-line tool
edit param OGG_ex1
- DDL INCLUDE MAPPED
- DDLOPTIONS REPORT
Edit parameters for the Replicat group using the GGSCI command-line tool
edit param ogg_rep1
- DDLOPTIONS MAPDERIVED
- DDLERROR DEFAULT IGNORE
Restart each group
stop extract ogg_ex1
start extract ogg_ex1
stop replicat ogg_rep1
start replicat ogg_rep1
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
52/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
From The Doco: MAPDERIVED
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
53/73
-
7/29/2019 Oracle GoldenGate 101 - Introduction
54/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
DDL Replicated on Target
Setting current schema for DDL operation to [SYS].
Restoring current schema for DDL operation to [OGG_GG_TRG].
From the ggserr.log
From the ggs_ddl_trace.log
DDL operation [drop procedure
"OGG_GG_TRG"."GENERATE_TEST_DDL" ], sequence [30], DDL type
[DROP] PROCEDURE, real object type [PROCEDURE], validity [], object ID [],object [OGG_GG_TRG.GENERATE_TEST_DDL], real object
[OGG_GG_TRG.GENERATE_TEST_DDL], base object schema [], base
object name [], logged as [SYS]
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
55/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Setting up DDL Replication
DDL replication uses a specific schema with supporting tables to keep track ofDDL operations
Set the DDL schema in the ./GLOBALS parameter file
GGSCHEMA ogg_gg_ddl
SQL> grant execute on UTL_FILE to ogg_gg_ddl; -- Allows schema to write
trace to the USER_DUMP_DEST/ggs_ddl_trace.log Disable the Oracle RECYCLEBIN (requires database restart)
alter system set recyclebin=off scope=spfile;
Run as SYSDBA
marker_setup.sql --Creates the tables to hold the DDL actions
ddl_setup.sql --Creates the DDL trigger role_setup.sql --Creates a role, GGS_GGSUSER_ROLE, with DML grants to
the DDL objects. Grant this to all GG users
ddl_[dis|en]able.sql --Enables/Disables the DDL trigger
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
56/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
OWB and ODI Support
Oracle Warehouse Builder and Oracle Data Integrator provide knowledgemodules to interact with Oracle GoldenGate
Knowledge Modules are Code Templates in OWB lingo
These are pre-scripted templates that will auto-magically configure OGG forthe sources and targets we need
http://www.rittmanmead.com/2010/03/22/configuring-odi-10-1-3-6-to-use-oracle-golden-gate-for-changed-data-capture/
For a detailed walk-through example
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/2010/03/22/configuring-odi-10-1-3-6-to-use-oracle-golden-gate-for-changed-data-capture/http://www.rittmanmead.com/2010/03/22/configuring-odi-10-1-3-6-to-use-oracle-golden-gate-for-changed-data-capture/http://www.rittmanmead.com/2010/03/22/configuring-odi-10-1-3-6-to-use-oracle-golden-gate-for-changed-data-capture/http://www.rittmanmead.com/2010/03/22/configuring-odi-10-1-3-6-to-use-oracle-golden-gate-for-changed-data-capture/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
57/73
-
7/29/2019 Oracle GoldenGate 101 - Introduction
58/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
ODI setup for Oracle GoldenGate
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
59/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
ODI Setup for Oracle GoldenGate
LOCAL_TEMP_DIR : C:\TEMP
SRC_LSCHEMA : GG_TEST_SRC
SRC_DB_USER : ogg_gg
SRC_DB_PASSWORD : password1
SRC_OGG_PATH : C:\GOLDENGATE\GOLDENGATE_SRCSTG_HOST : winxpvm.rmcvm.com
STG_MANAGER_PORT : 7910
STG_OGG_PATH : C:\GOLDENGATE\GOLDENGATE_STG
COMPATIBLE : 10
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
60/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
ODI setup for Oracle GoldenGate
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
61/73
-
7/29/2019 Oracle GoldenGate 101 - Introduction
62/73
-
7/29/2019 Oracle GoldenGate 101 - Introduction
63/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
ODI setup for Oracle GoldenGate
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
64/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
ODI setup for Oracle GoldenGate
Monday, 27 February 12
This readme file provides a detail of the different steps to be executed manually in order tocomplete the setup of the CDC using OGG.
UPLOAD FILES TO SOURCE MACHINEThe files for the source machine have been generated in C:\TEMP/ODIS_to_ODIT1/srcThese files must be copied on the source machine to the following location C:\GoldenGate
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
65/73
These files must be copied on the source machine, to the following location C:\GoldenGate\GoldenGate_Src using the same directory structure.
For instance, files contained in C:\TEMP/ODIS_to_ODIT1/src/dirprm should end up in C:\GoldenGate
\GoldenGate_Src/dirprm
UPLOAD FILES TO STAGING MACHINEThe files for the staging machine have been generated in C:\TEMP/ODIS_to_ODIT1/stgThese files must be copied on the target machine, to the following location C:\GoldenGate\GoldenGate_Tgt using the same directory structure.
For instance, files contained in C:\TEMP/ODIS_to_ODIT1/stg/dirprm should end up in C:\GoldenGate\GoldenGate_Tgt/dirprm
RUN THE SOURCE OBEY FILEConnect to the source machine using a command line tool such as cmd on Windows or a shell onUNIX/Linux and execute the following command:C:\GoldenGate\GoldenGate_Src/ggsci paramfile C:\GoldenGate\GoldenGate_Src/diroby/ODISS.oby
GENERATE THE DEFINITION FILEConnect to the source machine using a command line tool such as cmd on Windows or a shell onUNIX/Linux and execute the following command:
C:\GoldenGate\GoldenGate_Src/defgen paramfile C:\GoldenGate\GoldenGate_Src/dirprm/ODISD.prm
COPY DEFINITION FILECopy the definition file C:\GoldenGate\GoldenGate_Src/dirdef/ODISC.def from the source machine toC:\GoldenGate\GoldenGate_Tgt/dirdef/ODISC.def on the staging machine.
RUN THE STAGING OBEY FILEConnect to the staging machine using a command line tool such as cmd on Windows or a shell onUNIX/Linux and execute the following commands:
C:\GoldenGate\GoldenGate_Tgt/ggsci paramfile C:\GoldenGate\GoldenGate_Tgt/diroby/ODIT1T.oby
Monday, 27 February 12
-
7/29/2019 Oracle GoldenGate 101 - Introduction
66/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
ODI setup for Oracle GoldenGate
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
67/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
ODI setup for Oracle GoldenGate
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
68/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Create the normal mappings in ODI
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
69/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Create the normal mappings in ODI
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
70/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Some Different Examples
Replicate datamarts and dimension tables to a dedicated query box
Takes the query load off the DW box that was being maxed out with growingclassic ETL processes
2 days of setup
Point front end to new DB instance
Can now start working on introducing OGG to the classic environment whilststill having the query box free
Point front end back to old DB when done reengineering with OGG
Keep Oracle Application Express applications in synch on dev/test/prod
APEX applications being used (not developed) on all boxes Change once and dont bother with re-deploying; Very Rapid Rollout
Monday, 27 February 12
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
71/73
T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com
Conclusion
Oracle GoldenGate offers very reliable, zero down-time movement of data
Useful for
Replication
Migration
DR
Data distribution
CDC
Relatively straight forward to set up
But highly customizable
Hundreds of parameters and settings to play with
Secure and Encrypted ... and Fun :)
Monday, 27 February 12
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
72/73
T : +44 (0) 8446 697 995 or(888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com
More Information
Thank you for attending this presentation
More information can be found at http://www.rittmanmead.com
Contact us at [email protected]
Look out for our book, Oracle Business Intelligence Developers Guide due Q1 2012
Follow-us on Twitter (@rittmanmead) or Facebook (facebook.com/rittmanmead)
Monday, 27 February 12
mailto:[email protected]:[email protected]:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected] -
7/29/2019 Oracle GoldenGate 101 - Introduction
73/73