oracle goldengate 101 - a newbie's dive into the unknown

76
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com Borkur Steingrimsson, Rittman Mead Consulting UKOUG, Birmingham, December 2011 Oracle GoldenGate 101: A newbie's dive in to t he unknown Saturday, 10 December 11

Upload: alina-anchidin

Post on 14-Apr-2018

215 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 1/76

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com

Borkur Steingrimsson, Rittman Mead ConsultingUKOUG, Birmingham, December 2011

Oracle GoldenGate 101:A newbie's dive in to the unknown

Saturday, 10 December 11

Page 2: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 2/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Introduction

• Börkur 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

Saturday, 10 December 11

Page 3: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 3/76

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

Saturday, 10 December 11

Page 4: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 4/76

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com

About Rittman Mead

• Oracle’s 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 world’s 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

Saturday, 10 December 11

Page 5: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 5/76

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com

Contact us to hear more about our services including

• Consulting

‣ Full BI capability delivery

‣ Implementations

‣ Upgrades

- OBIEE 11g Upgrade Assessment

‣ Migrations

‣ Expert technical services

‣ Strategic BI consulting

• Support‣ Premium technical support

‣ Flexible subscription model - HoursBank

• Training

‣ OBIEE Bootcamp

‣ BI Apps Bootcamp

‣ ODI Bootcamp

Public Training - new schedule for 2012

- 16th-20th January - OBIEE

- 6th-10th February - ODI

- 19th-23rd March - BI Apps

- www.rittmanmead.com/training

• Coming soon

‣ Exalytics

‣ Rittman Mead Cloud Based BIservice

Saturday, 10 December 11

Page 6: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 6/76

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com

Finding Rittman Mead at the conference and beyond

• Drop in a business card

‣ Get access to all of Rittman Mead’s presentations and white papers from theOUG and also Openworld and other global community events

‣ Latest insight into Exalytics

• One-to-one sessions

‣ Find Morgan McCarthy or one of our team at the back of the room to schedulein a time

• Tweet us

‣ @rittmanmead

• Mail us

[email protected]

•  And socially at the conference

‣  At All Bar One Tuesday evening after the exhibition

Saturday, 10 December 11

Page 7: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 7/76

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

Saturday, 10 December 11

Page 8: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 8/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Oracle GoldenGate

• So you’ve heard about Oracle GoldenGate?

•What’s all the Fuss about?

Saturday, 10 December 11

Page 9: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 9/76

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

Saturday, 10 December 11

Page 10: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 10/76

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

Saturday, 10 December 11

Page 11: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 11/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

What’s 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.”

Saturday, 10 December 11

Page 12: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 12/76

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

Saturday, 10 December 11

Page 13: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 13/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

From the Doco: Architecture

Saturday, 10 December 11

Page 14: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 14/76

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.

Saturday, 10 December 11

Page 15: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 15/76

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.

Saturday, 10 December 11

Page 16: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 16/76

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. Impr oves 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 of ownership 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.

Saturday, 10 December 11

Page 17: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 17/76

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 acrossmultiple 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 Service–based 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.

Saturday, 10 December 11

Page 18: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 18/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

From the Doco: Supported Topologies

Saturday, 10 December 11

Page 19: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 19/76

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.

Saturday, 10 December 11

Page 20: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 20/76

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 of transactions 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.

Saturday, 10 December 11

Page 21: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 21/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Having Said That ...

 There is no magic!

Saturday, 10 December 11

Page 22: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 22/76

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?

Saturday, 10 December 11

Page 23: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 23/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

What’s 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 ODI’s side)

Saturday, 10 December 11

Page 24: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 24/76

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

Saturday, 10 December 11

Page 25: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 25/76

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

• “Bob’s your uncle”

Saturday, 10 December 11

Page 26: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 26/76

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

Saturday, 10 December 11

Page 27: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 27/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Configuration: Create Subdirs

Saturday, 10 December 11

Page 28: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 28/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Configuration: Edit Parameters

Saturday, 10 December 11

Page 29: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 29/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Configuration: Edit Parameters

port 7809

Saturday, 10 December 11

Page 30: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 30/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Configuration: Edit Parameters

port 7809

Saturday, 10 December 11

Page 31: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 31/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Configuration: Edit Parameters

port 7809

MGRSERVNAME Oracle_GG_Mgr

Saturday, 10 December 11

Page 32: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 32/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Configuration: Install Service

Saturday, 10 December 11

Page 33: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 33/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Configuration: Install Service

Saturday, 10 December 11

Page 34: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 34/76

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 

Saturday, 10 December 11

Page 35: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 35/76

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 

Saturday, 10 December 11

Page 36: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 36/76

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_IEXT userid OGG_gg, password password1

discardfile E:\oracle\GG\ GG11g\dirrpt\OGG_IE XT.dsc, purge

rmthost winxpvm.rmcvm.com, mgrport 7810

rmttask replicat, group OGG_IREP

table OGG_GG.BOB;table OGG_GG.DEMO_TABLE;

Saturday, 10 December 11

Page 37: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 37/76

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);

Saturday, 10 December 11

Page 38: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 38/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

From the Doco: Filter Examples

Saturday, 10 December 11

Page 39: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 39/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Continuous Load

Saturday, 10 December 11

Page 40: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 40/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Continuous Load

Saturday, 10 December 11

Page 41: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 41/76

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.*;

Saturday, 10 December 11

Page 42: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 42/76

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.*;

Saturday, 10 December 11

Page 43: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 43/76

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”);

Saturday, 10 December 11

Page 44: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 44/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Oracle GoldenGate Director (2.0.0.3)

Saturday, 10 December 11

Page 45: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 45/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Oracle GoldenGate Director Web (2.0.0.3)

Saturday, 10 December 11

Page 46: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 46/76

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 ...

Saturday, 10 December 11

GGSC ( ) OGG

Page 47: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 47/76

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.

Saturday, 10 December 11

Page 48: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 48/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Oracle GoldenGate 101

Demo DML in Action

Saturday, 10 December 11

Page 49: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 49/76

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

Saturday, 10 December 11

Page 50: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 50/76

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

Saturday, 10 December 11

Page 51: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 51/76

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]

Saturday, 10 December 11

Page 52: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 52/76

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*"

Saturday, 10 December 11

Page 53: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 53/76

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

Saturday, 10 December 11

Page 54: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 54/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

From The Doco: MAPDERIVED

Saturday, 10 December 11

Page 55: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 55/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

DDL Operation Detected on Source

DDL operation included [INCLUDE MAPPED], optype [GRANT], objtype [OBJECT

PRIVILEGE], objowner [OGG_GG], objname [OGG_TEST_DDL].

Writing DDL operation to extract trail file.

DDL found, operation [drop procedure ogg_gg.generate_test_ddl (size 40)], start SCN

[1316328], commit SCN [1316351] instance [winxp11gvm]

DDL operation included [INCLUDE MAPPED], optype [DROP], objtype [PROCEDURE],

objowner [OGG_GG], objname [GENERATE_TEST_DDL].

Writing DDL operation to extract trail file.

SQL> grant insert on ogg_gg.ogg_test_ddl to public;

SQL> drop procedure ogg_gg.generate_test_ddl;

Saturday, 10 December 11

Page 56: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 56/76

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 [], ob ject 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]

Saturday, 10 December 11

Page 57: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 57/76

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 of DDL operations

• Set the DDL schema in the ./GLOBALS par ameter 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 

Saturday, 10 December 11

Page 58: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 58/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Oracle GoldenGate 101

Demo DDL in Action

Saturday, 10 December 11

Page 59: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 59/76

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 for the 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

Saturday, 10 December 11

Page 60: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 60/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

ODI setup for Oracle GoldenGate

Saturday, 10 December 11

Page 61: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 61/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

ODI setup for Oracle GoldenGate

Saturday, 10 December 11

Page 62: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 62/76

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

Saturday, 10 December 11

Page 63: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 63/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

ODI setup for Oracle GoldenGate

Saturday, 10 December 11

Page 64: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 64/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

ODI setup for Oracle GoldenGate

Saturday, 10 December 11

Page 65: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 65/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

ODI setup for Oracle GoldenGate

Saturday, 10 December 11

Page 66: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 66/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

ODI setup for Oracle GoldenGate

Saturday, 10 December 11

Page 67: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 67/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

ODI setup for Oracle GoldenGate

Saturday, 10 December 11

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

Page 68: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 68/76

p , g\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

Saturday, 10 December 11

Page 69: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 69/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

ODI setup for Oracle GoldenGate

Saturday, 10 December 11

Page 70: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 70/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

ODI setup for Oracle GoldenGate

Saturday, 10 December 11

Page 71: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 71/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Create the normal ‘mappings’ in ODI

Saturday, 10 December 11

Page 72: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 72/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Create the normal ‘mappings’ in ODI

Saturday, 10 December 11

S “Diff ” E l

Page 73: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 73/76

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 growing“classic” 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 don’t bother with re-deploying; Very Rapid Rollout

Saturday, 10 December 11

C l i

Page 74: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 74/76

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 :)

Saturday, 10 December 11

M I f ti

Page 75: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 75/76

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)

Saturday, 10 December 11

Thank You For Your Attention

Page 76: Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

7/29/2019 Oracle GoldenGate 101 - A Newbie's Dive Into the Unknown

http://slidepdf.com/reader/full/oracle-goldengate-101-a-newbies-dive-into-the-unknown 76/76

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Thank You For Your Attention

Questions?

Saturday, 10 December 11