Understanding Oracle GoldenGate

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
A RedStack Technology Company
Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014
Understanding
Oracle GoldenGate
BGOUG, June 2014
Sandanski

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
“The more I learn, the more I realize how much I
don't know.” - Albert Einstein
So Many Oracle Manuals, So Little Time – Iggy
Fernandez blog

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Pictures VS Bullets

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Who Am I
• (Sve)toslav Gyurov
• Principal consultant at e-DBA
• Working mainly on Exadata and migrations
• GTD and 7 Ps (Proper Planning and Preparation Prevents Piss Poor Performance
)
• I’m tall – 202cm (6 feet 717⁄32
inches)
• Futurama and Friends big fan
• I blog – http://sve.to and tweet - @sgyurov

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Customers
Some of our
Customers

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Introduction

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Introduction
• GoldenGate Software founded in 1995
• Acquired by Oracle in September 2009
• Now part of the Oracle Fusion Middleware
product family
• Near real-time transactional data replication in a
heterogeneous computing environment

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Introduction
• Supported topologies:
– Unidirectional (reporting or migration)
– Bi-directional (active-passive or active-active)
– Broadcast (data distribution)
– Consolidation (DWH consolitation)

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Supported
databases
• Oracle Database 9.2i, 10g, 11g, 12c
• TimesTen 7.0.5
• MySQL 4.1, 5.0
• IBM DB2 8.1, 8.2, 9.1, 9.5
• Microsoft SQL Server 2000, 2005, 2008
• Teradata V2R5, V2R6
• Sybase ASE 12.5.4, 15.0

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Architecture

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Oracle GoldenGate for Oracle Database

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
System
requirements
• Supported platforms
• Memory and disk requirements
• Network configuration
• Database version
• Supported data types and objects

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Supported version
• Oracle GoldenGate 11g supports:
– Oracle Database Oracle 10.2.0.4+, Oracle 11.1.0.6+,
Oracle 11.2.0.1+
• Oracle GoldenGate 12c supports:
– Oracle Database 11.1.0.6+, 11.2.0.1+, 12.1.0.1+
• Source and target GG must be same versions!

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Components
• Extract (capture)
• Data pump (routing)
• Replicat (delivery)
• Trails or extract files
• Checkpoints
• Manager
• Collector

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Components
• Extract
– Capture mechanism of Oracle GoldenGate
– Initial load or change synchronization
– Extract captures DML and DDL operations
– Only captures committed transactions
– Writes to trail files

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Components
• Extract continued
– Log-based
– Filtering
– Bounded Recovery – 4H
– CACHEMGR
– Classic capture (trigger based)
– Integrated capture (11.2.0.4)

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Configuration
options

View Slide

+44 (0) 844 811 3600
[email protected]dba.com
www.e-dba.com
Classic capture
• Was the original capture method
• Trigger based
• Restricted support for the complex data types
• Supports any database except multitenant
container database
• Redolog/archive log mining

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Integrated capture
• Integrated within the database
• Interacts directly with a database logmining server
• Supports more data and storage types
• Only mode that supports multitenant db
• Support for Basic, OLTP, EHCC compression
• Compatibility must be set to 11.2.0.4

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Components
• Data pump
– Not Oracle Datapump
– Just another extract process
– Reads trail and send it over network (TCP/IP)
– Transfer is always ASYNCHRONOUS
– Compression and encryption are optional
– pass-through mode

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Components
• Replicat
– Runs on the target
– Reads trail and reconstructs DML or DDL
– Performs data filtering, mapping, and conversion
– Apply can be delayed
– COMMIT NOWAIT - starting v11.1.1.1
– Integrated and nonintegrated replicat (trigger based)

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Components
• Trail
– Local and remote trail
– Extract writes to trail
– Data pump and Replicate reads from trail
– Stored in ./dirdat/ by default
– Names are automatically generated (lt) and (rt)
– Unique six-digit sequence number from 000000 to 999999
– The data in this trail file is in commit order sequence!

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Components
• Manager
– Controls Golden Gate
– Runs on each system
– Maintain port numbers
– Performs trail management

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Components
• Checkpoint
– Store current read or write position
– Extract – checkpoint file
– Replicate – checkpoint table

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Components
• Collector
– Communicates with remote Extract and Manger
– Receive extracted db changes
– Writes changes to the trail
– Uses TCP/IP

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Supported Data
Types
• Data types supported by integrated and classic capture mode:
– NUMBER
– BINARY FLOAT
– BINARY DOUBLE
– CHAR
– VARCHAR2
– LONG
– NCHAR
– NVARCHAR2
– RAW
– LONG RAW
– DATE
– TIMESTAMP
– CLOB
– NCLOB
– BLOB
– SECUREFILE and BASICFILE

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Supported Data
Types
• The following data types are no supported:
– REFs
– ANYDATA
– ANYDATASET
– ANYTYPE
– BFILE
– MLSLABEL
– ORDDICOM
– TIMEZONE_ABBR
– URITYPE
– UROWID (classic capture)

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
RAC support
• RAC is supported
– Limited support with classic capture
– Fully supported with integrated capture
• Shared storage support:
– DBFS
– ACFS
– OCFS

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
High Availability
• Oracle GI integration
– Oracle Grid Infrastructure Bundled Agent (starting
11.2.0.3)
– Manually create agent script (prior 11.2.0.3)
• Golden Gate AUTOSTART of groups
– Upon start Manager will start Extract, Data Pump and
Replicat

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Security
• Oracle GoldenGate security options:
– Trail File encryption (AES)
– Password encryption (no clear-text passwords)
– TCP/IP encryption (AES or Blowfish)
– Trusted connection (low sec to high sec)
– Command authentication

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Configuration

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Installation
• Environment:
– OEL 6.4
– Oracle Database 11.2.0.4
– Oracle GoldenGate 12.1.2.0.0
– GoldenGate Integrated capture and apply!
SQL> desc sve.orders;
Name Null? Type
---------------------------- -------- ----------------------------
ORDER_ID NOT NULL NUMBER
ORDER_DATE DATE
CUSTOMER_NAME VARCHAR2(64)

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Installation
• Latest version – 12.1.2 and just 326 MB!
unzip -q 121200_fbo_ggs_Linux_x64_shiphome.zip
cd fbo_ggs_Linux_x64_shiphome/Disk1/
./runInstaller

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Installation

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Pre checks
• Oracle GoldenGate database Complete Database
Profile check script for Oracle DB (All Schemas)
(Doc ID 1298562.1)
• Oracle GoldenGate database Schema Profile check
script for Oracle DB (Doc ID 1296168.1)

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Database
requirements
• Configure logging properties
– Archivelog mode
– Force logging
– Minimal supplemental logging
– Table-level logging (supplemental log groups)
– Enable Flashback
– 11.2.0.4
• ENABLE_GOLDENGATE_REPLICATION=true

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Configure
Database
SQL> alter database add supplemental log data;
SQL> alter database force logging;
SQL> create tablespace ggs_data datafile '/oracle/oradata/orcl/orcl_ggs_data_01.dbf' size 128m
autoextend on next 16m maxsize 4g;
SQL> create user ggs_owner identified by Welcome1 default tablespace ggs_data temporary tablespace
temp;
SQL> grant connect, resource, dba to ggs_owner;
SQL> alter system set enable_goldengate_replication=true;
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE( -
grantee => 'ggs_owner', privilege_type => 'capture', grant_select_privileges=> true, do_grants => TRUE);

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Configure
GoldenGate
• Create extract param file vi
vi $GG_HOME/dirprm/orcle1.prm
EXTRACT ORCLE1
USERID ggs_owner PASSWORD Welcome1
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
DDL INCLUDE MAPPED
EXTTRAIL /oracle/product/12.1.2.GG/dirdat/lt
TABLE sve.*;

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Configure
GoldenGate
• Create pump param file using ggsci
[[email protected] 12.1.2.GG]$ ./ggsci
GGSCI (oel2) 2> edit params orclp1
EXTRACT ORCLP1
RMTHOST my-remote-host.net, MGRPORT 7809
RMTTRAIL /oracle/product/12.1.2.GG/dirdat/rt
PASSTHRU
TABLE sve.*;

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Configure
GoldenGate
• Create replicat param file
GGSCI (oel1) 2> edit params orclr1
REPLICAT ORCLR1
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
ASSUMETARGETDEFS
MAP sve.*, TARGET sve.*;

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Configure
GoldenGate
• Add supplemental logging and register extract
GGSCI (oel2) 1> DBLOGIN USERID ggs_owner password Welcome1
Successfully logged into database.
GGSCI (oel2) 2> ADD TRANDATA sve.*
Logging of supplemental redo data enabled for table SVE.ORDERS.
TRANDATA for scheduling columns has been added on table 'SVE.ORDERS'.
GGSCI (oel2) 3> register extract orcle1 database
Extract ORCLE1 successfully registered with database at SCN 962593.

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Behind the scenes
Thu Jun 12 11:22:07 2014
Logminer Bld: Build started
ALTER SYSTEM SWITCH ALL LOGFILE start (orcl)
Thu Jun 12 11:22:25 2014
Logminer Bld: Done
2014-06-12 11:22:43.735000 +01:00
GoldenGate CAPTURE CP01 for OGG$CAP_ORCLE1 with pid=30, OS id=4272 is in combined capture and
apply mode.
Capture OGG$CAP_ORCLE1 is handling 1 applies.
-- capture is running in apply-state checkpoint mode.
2014-06-12 11:22:47.259000 +01:00
Starting persistent Logminer Session with sid = 1 for GoldenGate Capture OGG$CAP_ORCLE1

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Are we there yet ?
• Checklist:
– Database
– Extract process
– Data pump process
– Replicat process
• Start the processes and enjoy life!

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Start capture
GGSCI (oel2) 22> start extract orcle1
Sending START request to MANAGER ...
EXTRACT ORCLE1 starting
GGSCI (oel2) 23> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ORCLE1 00:00:10 00:00:02
• Start Extract

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Trail files
SQL> insert into orders (order_id, order_date, customer_name) values (1,to_date('2014-06-11', 'YYYY-
MM-DD'), 'Sve');
SQL> commit;
[[email protected] 12.1.2.GG]$ strings /opt/oracle/product/11.2.1.GG/dirdat/lt000000 | tail -6
SVE.ORDERS
2014-06-11:00:00:00
SveT
AAAVVDAAEAAAACvAAA
9862636
3.18.926Z

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Oracle Database 12c

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Oracle Database
12c
• Extract
– capture from multiple pluggable dbs to single trail
– Must operate in integrated capture mode
– Must connect to root container
• Replicat
– Apply only to one pluggable db
– Integrated delivery

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Configure
GoldenGate
cat > $GG_HOME/dirprm/orclp1.prm
EXTRACT ORCLE1
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
DDL INCLUDE MAPPED
EXTTRAIL /oracle/product/12.1.2.GG/dirdat/lt
SOURCECATALOG pdb1
TABLE sve.*;
SEQUENCE sve.seq*;
SOURCECATALOG pdb2
TABLE scott.*;
SEQUENCE scott*.seq*;
• Extract in 12c

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Trail files, logdump and
GoldenGate records

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Trail file
FileHeader and
TrailInfo
MachineInfo
DatabaseInfo
Commited
transaction
ProducerInfo

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Logdump
• Mainly used by Oracle Support
• Swiss army knife of troubleshooting GoldenGate
• Enables search, read and save data in a trail file
• Identify a transaction for a given SCN in Oracle
• Identify the before images of a row
• Count the number of changes table

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
GoldenGate
record
Operation
and time of
record
Object
Before or
after image
Record data
in HEX
Length of record
and RBA in trail
Column
information
with data
Record data in
ASCII
Header,
transaction
information

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Monitoring

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Monitoring
• Command line – ggssi
– info, status, lag, view report
– Monitoring lag
– Process report
– Error log
– Discard file

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Monitoring
GGSCI (oel2) 12> info orcle1
EXTRACT ORCLE1 Last Started 2014-06-12 11:40 Status RUNNING
Checkpoint Lag 00:00:09 (updated 00:00:08 ago)
Process ID 4449
Log Read Checkpoint Oracle Integrated Redo Logs
2014-06-12 13:54:10
SCN 0.986714 (986714)
GGSCI (oel2) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ORCLE1 00:00:10 00:00:02

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Monitoring
• Management Pack
for Oracle
GoldenGate
• Oracle GoldenGate
Plug-in on OEM 12c
Cloud Control

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Monitoring
• SQL Developer – Bobby Curtis
http://dbasolved.com/

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Best Practices

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
• Run it in your environment and find the best
solution for you, but keep in mind:
– Monitor the processes
– Purge the logs
– Keep enough archivelogs
– Use integrated modes where possible

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Good news everyone

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
License
• Of course it is licensed
• And yes, both databases must be licensed
• Processor license cost - $17500
• The Oracle GoldenGate product includes license
cover for Active Data Guard.

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Statement of
direction
Oracle Streams continues to be supported but will not be enhanced to support any new
database features in future releases. This means that Streams will continue to support
exactly what it supports today (11.2) in future releases. It also means that any new
database features, data types, or capabilities will not be supported by Streams.
Customers who want to take advantage of the new data types and features will need to
license Oracle GoldenGate.

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Bottom line
• It worked for me!
• Integrated capture/apply vs nonintegrated
• Same version on source and target
• Remember the 7Ps rule!

View Slide

+44 (0) 844 811 3600
[email protected]
www.e-dba.com
Thank you!

View Slide

Understanding Oracle GoldenGate

Understanding Oracle GoldenGate

Svetoslav Gyurov

More Decks by Svetoslav Gyurov

Other Decks in Technology

Featured

Transcript