GoldenGate and Oracle Data Integrator - A Perfect Match- Upgrade to 12c

info@rittmanmead.com www.rittmanmead.com @rittmanmead
Michael Rainey | Rittman Mead | KScope15
2
GoldenGate and Oracle Data Integrator  
A Perfect Match: Upgrade to 12c

Introduction
• Michael Rainey

- Principal Consultant

- Oracle Data Integration expert

‣ GoldenGate and Oracle Data Integrator
- Blogger, instructor, speaker

- Oracle ACE
3
@mRainey

About Rittman Mead
• Oracle Gold partner

- World leading specialist partner for technical excellence, solutions delivery
and innovation in Oracle BI

- Provide consulting, training, managed services for customers worldwide

• 100+ consultants including 1 Oracle ACE Director and 3 Oracle ACEs

- All experts in Oracle BI, DW, EPM and Analytics tech

- Skills in broad range of supporting Oracle tools: OBIEE, OBIA, ODIEE,
Essbase, Oracle OLAP, GoldenGate, Exadata, Endeca

• Blog: www.rittmanmead.com/blog Twitter: @rittmanmead
4

info@rittmanmead.com www.rittmanmead.com @rittmanmead 5
Visual Redesign Business User Training
Ongoing SupportEngagement Toolkit
Average user adoption for BI
platforms is below 25%
Rittman Mead’s User Engagement Service can help

Today’s Topic
• Upgrade Oracle Data Integrator 11g and Oracle
GoldenGate 11g to 12c

- Overview of ODI & GoldenGate

- What are the steps for upgrade of each product

- How to cleanup after the conversion to 12c

- What to watch out for when using ODI & GoldenGate
together
6

GoldenGate - Data Replication
• High-performance data replication

- Heterogeneous sources and targets

- Minimal impact on source and target systems

- JMS integration

- Conflict detection and resolution

- Password and data encryption for enhanced security

• Enhance decision-making with real-time data
7

Oracle GoldenGate
8

• Integrated Apply

• Big Data integration

- GoldenGate Adapters - load HDFS, Flume, etc

• Capture from and deliver to Oracle Exadata Storage Server

• Support for multi-tenant databases - Oracle 12c

• Enhanced performance

- coordinated mode
What’s new in GoldenGate 12c?
9

Oracle Data Integrator Concepts
• ODI is a complete and unified data integration solution

- Supports batch-based, real-time and service-based integration

• “Design Once, Run Anywhere”

- Provides reusability, flexibility and extensibility using Knowledge  
Modules (KM)

- Separates the logical from the physical implementation

• Based in an ELT architecture to leverage target platform capabilities

- Higher performance

- Platform Independent

• Combines data movement, data synchronization, data transformation, data
quality, data management and data services
10

ELT vs ETL
11

ELT vs ETL
12
• Reduce network transfer
• Reduce hardware costs
• Set-based operations
• Leverage target platform capabilities
• Referential Integrity Check

What’s new in ODI 12c?
• Flow-based Mappings

- Deployment specifications

- Component Knowledge Modules

• Debugger

• WebLogic Management Framework

• Improved performance

- Parallel target table load

- Session blueprints

• Enhanced KM and Procedure editors
13

GoldenGate and ODI - A Perfect Match
• JKM Oracle to Oracle Consistent (OGG) Knowledge Module

- Oracle Data Integrator Metadata used to generate GoldenGate parameter files  
(extract, pump, replicate) and 
configuration files

- Delivered with ODI

• ODI CDC Framework generated

- Staging table - replicate of  
source

- J$ (journal) table - change rows

• Journalized data used in  
transformations (via JV$ views)
14

Oracle Data Integrator - Journalizing (CDC)
• Oracle Data Integrator Change Data Capture (CDC) delivered via Journalizing

- Identify, capture, and deliver changes made to source data

- Journalizing Knowledge Module (JKM) performs setup and  
creates infrastructure

• ODI CDC Framework

- Capture Process - mechanism for capturing changed data from the  
source database (Ex. Oracle GoldenGate)

- Journals - tables (J$) hold references to changed records and the  
change type (insert / update / delete)

- Journalizing Views - (JV$, JV$D) provides access to changed data,  
used by IKM / LKM in mappings

- Subscribers - used to allow consumption of changed data at different intervals, for multiple
applications, etc.
15

Upgrade to 12c - The Goal
• Minimize interruption to source and target systems

• Minimize downtime
16

GoldenGate Upgrade Considerations
• GoldenGate 12c is supported by the 64-bit OS only
(Oracle installations)

• Start with simple upgrade, then implement new
features (integrated replicat, integrated extract, etc)

• Parameter file updates

- Quoted object names - use the convprm tool

- Upgrade extract (or replicat) only

- Character-Set conversion can be handled
17

Upgrading GoldenGate 11g to 12c - convprm
• Run prior to performing the upgrade on a backup of the files

18
Usage: convprm [options] <list of file names>

-h, --help: displays usage.

-v, --version: displays version information.

-i, --follow-include: also converts files included through an OBEY or INCLUDE directive.

-n, --no-backup: does not create a copy of the original file.

-s, --silent: does not display status messages.

-q, --quotes: performs quote conversion (active by default).

-b, --before-function: converts from BEFORE.column syntax to @BEFORE(column) (active by default).

-d, --dry-run: does not change the input or create a backup file. It only prints out what would happen.

•Start with the dry run
19
[oracle@ODIGettingStarted test]$ ./convprm -d ../sogg/dirprm/*
*** Conversion started for file ../sogg/dirprm/EXTC.prm.
extract EXTC
userid ggsrc, password oracle
exttrail /home/oracle/sogg/dirdat/EXToc/oc
TABLE SUGARCRM.ACCOUNTS;
TABLE SUGARCRM.ACCOUNTS_CONTACTS;
TABLE SUGARCRM.ACCOUNTS_OPPORTUNITIES;
TABLE SUGARCRM.MEETINGS_LEADS;
TABLE SUGARCRM.MEETINGS;
TABLE SUGARCRM.MEETINGS_CONTACTS;
--- Conversion complete for file ../sogg/dirprm/EXTC.prm.

•Run the full parameter file conversion
20
[oracle@ODIGettingStarted togg]$ ./convprm ./dirprm/*
*** Conversion started for file ./dirprm/jagent.prm.
--- Conversion complete for file ./dirprm/jagent.prm.
*** Conversion started for file ./dirprm/mgr.prm.
--- Conversion complete for file ./dirprm/mgr.prm.
*** Conversion started for file ./dirprm/REPSA1.prm.
+ string literal to ANSI SQL quotes at 26,29; length = 8.
...
--- Conversion complete for file ./dirprm/REPSA1.prm.
*** 3 files have been converted.

•Run the full parameter file conversion
20
[oracle@ODIGettingStarted togg]$ ./convprm ./dirprm/*
*** Conversion started for file ./dirprm/jagent.prm.
--- Conversion complete for file ./dirprm/jagent.prm.
*** Conversion started for file ./dirprm/mgr.prm.
--- Conversion complete for file ./dirprm/mgr.prm.
*** Conversion started for file ./dirprm/REPSA1.prm.
...
--- Conversion complete for file ./dirprm/REPSA1.prm.
*** 3 files have been converted.
[oracle@ODIGettingStarted dirprm]$ diff REPSA1.prm REPSA1.prm.bck
26c26
< WINDOW_ID = @STRCAT(@GETENV('RECORD', 'FILESEQNO'),
@STRNUM(@GETENV('RECORD', 'FILERBA'), RIGHTZERO, 10))
---
> WINDOW_ID = @STRCAT(@GETENV("RECORD", "FILESEQNO"),
@STRNUM(@GETENV("RECORD", "FILERBA"), RIGHTZERO, 10))
...

Upgrade GoldenGate 11g to 12c Steps
• Ensure all transactions have completed on source database and extract

- Source system outage is necessary only when SQL or DDL operations are
performed

• Record current SCN

• Stop all process groups and managers on source & target

• Install GoldenGate 12c

• Upgrade the supplemental logging on the source (ulg.sql)

• Set the extract, pump, and replicat to start at the recorded SCN

• Start the processes!
21

Upgrade GoldenGate 11g to 12c - Source Preparation
• Check for long-running transactions

INFO EXTRACT group, SHOWCH
• If needed, restore archive logs to checkpoint…

• Or…”clear” them using FORCETRANS or SKIPTRANS or wait for completion

22

• Record the current SCN

SQL> select current_scn from v$database;
• Check the extract to determine if it has
completed processing transactions

23

• If not upgrading the replicat, add:

- exttrail /u01/sogg/dirdat/EXToc/oc, format release 11.2

• Stop the extract group(s)
24

Upgrade GoldenGate 11g to 12c - Target Preparation
• Ensure the replicat has completed processing all transactions, then stop the process

• Stop the source and target manager processes
25

Upgrade GoldenGate 11g to 12c - Installation
Install GoldenGate 12.1.2 into the current source and target directories
26

• If the source or target is Oracle 11.2.0.4 or 12.1.0.2+, set the ENABLE_GOLDENGATE_REPLICATION
initialization parameter to TRUE

• Run the ulg.sql (update log group) script on the source system to update the supplemental logging

- Supplemental log groups must have been created via Add Trandata

Upgrade GoldenGate 11g to 12c - Update Logging
27

• If the source or target is Oracle 11.2.0.4 or 12.1.0.2+, set the ENABLE_GOLDENGATE_REPLICATION
initialization parameter to TRUE
• Run the
- Supplemental log groups must have been created via Add Trandata

Upgrade GoldenGate 11g to 12c - Update Logging
27

Upgrade GoldenGate 11g to 12c - Finalize Upgrade
• Alter the extract (and pump) processes to write a new sequence number

• Issue the command info extract groupname, detail to get the new sequence number
28
GGSCI (ODIGettingStarted) 3> alter extract EXTC ETROLLOVER
2014-12-03 19:13:52 INFO OGG-01520 Rollover performed. For each affected output trail
of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for
that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new
trail file; it will not happen automatically.
EXTRACT altered.
GGSCI (ODIGettingStarted) 4> alter extract REPSP ETROLLOVER
2014-12-03 19:14:07 INFO OGG-01520 Rollover performed. For each affected output trail
of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for
that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new
trail file; it will not happen automatically.
EXTRACT altered.

Upgrade GoldenGate 11g to 12c - Finalize Upgrade
• Alter the extract (pump) and replicat processes to begin at the new sequence number

• Start the processes on source and target

29
GGSCI (ODIGettingStarted) 7> alter extract REPSP, extseqno 14, extrba 0
EXTRACT altered.
GGSCI (ODIGettingStarted) 1> alter replicat REPSA1, extseqno 14, extrba 0
REPLICAT altered.

Oracle Data Integrator Upgrade Considerations
• Can be completed before or after GoldenGate upgrade

• Interfaces will be converted to Mappings using the  
DataSet component

- Further work necessary to fully utilize flow-based  
Mappings

• Agent upgrade

- If possible, install new agent. (WLS must be upgraded to 12c anyway).

- If not…follow upgrade procedure.

• Upgrade key - used to migrate additional objects later
30

Upgrade Oracle Data Integrator 11g to 12c Steps
• Backup environment and clone repositories

• Install ODI 12c

• Run RCU to add new supporting 12c schemas

• Run the Upgrade Assistant
31

Upgrade ODI 11g to 12c - Preparation
• Backup the existing environment and clone
the repositories for upgrade

- Ensure the table schema_version_registry has an
ODI entry (Support Doc ID 1622194.1)

- Set the work repository schema JDBC URL to the
cloned version

32

Upgrade ODI 11g to 12c - Installation
• Install ODI 12c

- Use JDK 1.7.xx

- Choose a new Oracle Home

33

Upgrade ODI 11g to 12c - Run RCU
• Run the RCU to create additional schemas necessary

- AS Common Schemas (Common Infrastructure Services)

34

Upgrade ODI 11g to 12c - Upgrade Assistant
• Stop all services (agents, close ODI Studio)

- [oracle@ODIGettingStarted bin]$ ./agentstop.sh -NAME=ODIAGENT 
2014-12-04 19:25:56.075 NOTIFICATION OracleDI: Stopping Agent ODIAGENT…
• Run the Upgrade Assistant

- [oracle@ODIGettingStarted bin]$ pwd 
/home/oracle/odi12c/oracle_common/upgrade/bin 
[oracle@ODIGettingStarted bin]$ ./ua
35

Upgrade ODI 11g to 12c - Upgrade Assistant
36

Upgrading ODI 11g to 12c - Post Upgrade Steps
37

Review the Upgraded Mappings
38

Reusable Mappings - Cleanup Required
• Temporary Interfaces are now Reusable Mappings

• 3 Mappings are generated: 1 Mapping & 2 Reusable
Mappings

• Cleanup required to remove the unused mappings
39

Inline Views - What to Watch For
40

Inline Views - What to Watch For
40
AND

Convert to Flow
• Automatically convert the Dataset component to
flow based components within the Mapping

- Immediately benefit from the upgrade to 12c
41

Convert to Flow
42

Convert to Flow? Groovy-tize it!
43

Convert to Flow? Groovy-tize it!
44

ODI and GoldenGate Integration
• Journalizing is still enabled
and running

- Still using the 11g JKM

45

Move to ODI 12c Journalizing Knowledge Module
• Import the JKM

• Configure and start
JAgent on source/target

• Setup the GoldenGate
Data Server/Physical
Schemas (Online)

• Change the JKM in the
Model and set the
Options
46

Summary
• Upgrade both GoldenGate and ODI at the same time or individually

- Backup and clone environment

- Use tricks, such as “Convert to Flow”, to finalize migration to 12c

- Journalizing integration will still work after upgrade of ODI

• Minimize interruption of source and target

- Capture source SCN before upgrade and restart GoldenGate at
that point in the transaction logs

• Oracle documentation is good!
47

Questions?
48

Questions?
• Blogs:

- www.rittmanmead.com/2014/05/goldengate-odi-perfect-match-12c-1

- www.rittmanmead.com/blog

- blogs.oracle.com/dataintegration/

• Contact:

- info@rittmanmead.com

- michael.rainey@rittmanmead.com

• Twitter

- @rittmanmead

- @mRainey
49

GoldenGate and Oracle Data Integrator - A Perfect Match- Upgrade to 12c

Recommended

More Related Content

What's hot (20)

Viewers also liked (13)

Similar to GoldenGate and Oracle Data Integrator - A Perfect Match- Upgrade to 12c (20)

More from Michael Rainey (8)

Recently uploaded (20)

GoldenGate and Oracle Data Integrator - A Perfect Match- Upgrade to 12c