GG BootCamp - Implementation
GG BootCamp - Implementation
GG BootCamp - Implementation
For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential
1. Oracle Corporation (Oracle) is pleased to allow its business partner (Partner) to download
and copy the information, documents, and the online training courses (collectively, Materials")
found on this Site. The use of the Materials is restricted to the non-commercial, internal training
of the Partners employees only. The Materials may not be used for training, promotion, or sales
to customers or other partners or third parties. <Insert Picture Here>
2. All the Materials are trademarks of Oracle and are proprietary information of Oracle. Partner or
other third party at no time has any right to resell, redistribute or create derivative works from
the Materials.
4. Under no circumstances shall Oracle or the Oracle Authorized Delivery Partner be liable for Technical Implementation Boot Camp
any loss, damage, liability or expense incurred or suffered which is claimed to have resulted
from use of this Site of Materials. As a condition of use of the Materials, Partner agrees to
Title of Presentation
Oracle GoldenGate
indemnify Oracle from and against any and all actions, claims, losses, damages, liabilities and Presenters Name
Presenters Title
expenses (including reasonable attorneys' fees) arising out of Partners use of the Materials.
5. Reference materials including but not limited to those identified in the Boot Camp manifest
can not be redistributed in any format without Oracle written consent.
For Oracle employees and authorized partners only. Do not distribute to third parties. For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential 2008 Oracle Corporation Proprietary and Confidential
Student Labs
For Oracle employees and authorized partners only. Do not distribute to third parties. For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential 2009 Oracle Corporation Proprietary and Confidential
1
Implementation Bootcamp Agenda
Day 3
GoldenGate
Student Labs
Questions and Answers Product Positioning and Differentiators
For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential 8
9 10
Real-time Access
11 12
2
Oracle GoldenGate Technology Differentiators Differentiator: Heterogeneity
Oracle GoldenGate provides low-impact capture, routing, transformation, and Core Infrastructure
delivery of database transactions across heterogeneous environments in real-time
Core product components are utilized for all databases
Canonical trail file format
Canonical metadata definitions
Key Differentiators:
Automatic mapping of common data types
13
13
Capture Support for Oracle RAC Capture Ability to capture from native backups
Support for ASM Coexistence with SQL Server
Archived Log Online Log Backups Transaction Log Replication
Support for index-organized tables
(IOTs) with overflow SQL Server Replication components
Support for clustered tables are not required
Support for object tables Support tables with no unique key (not
Support for object types (UDTs) supported by SQL Server Replication)
Support for DDL operations Support for computed columns
Archived log only (ALO) mode Support for identity columns
Off-platform capture (LOGSOURCE)
Capture
Commit Ordered Multi-threaded capture Capture
Commit Ordered
Trail Trail
OCI/Oracle Net ODBC
Capture Support for Multi Dimensional Capture Runs under Unix System Services
Clustered Tables (MDC) (USS)
Archived Log Online Log Support for Materialized Query Tables Archived Log Online Log Support for data sharing environments
(MQT) Automatic EBCDIC/ASCII translation
Support for data compression Support for EBCDIC, ASCII, and
(tablespace) Unicode encoded tables
DB2READLOG IFI IFCID 0306
Support for data compression
(tablespace)
3
Differentiator: Heterogeneity Differentiator: Heterogeneity
HP NonStop Capture (Enscribe and SQL/MP) HP NonStop Capture (SQL/MX)
Capture Logger used for non-TMF (non- Capture Runs under Open Systems Services
audited) enabled applications (OSS)
TMF Audit Dump TMF Audit Log ARLIB2 interface utilized by AUDSERV TMF Audit Dump TMF Audit Log ARLIB2 interface utilized by VAMSERV
process process
HP Blades Support Based on open systems core
Support for TCP/IP and EXPAND infrastructure
ARLIB2 networks ARLIB2
Fast block-based unload
Support for network distributed
transactions
AUDSERV
Support primary key updates from
Commit Ordered VAMSERV Commit Ordered
Capture Trail open systems Capture Trail
Logger
Embedded SQL Embedded SQL
Capture
Unsorted Trail Commit Ordered
Sort/Pump Trail Support for DDL operations
ODBC Support for LOB and UDT data types
Support for identity data types
Maximum Protection Mode
Decoupled architecture
Individual processes can be restarted automatically
Transaction boundaries
Tolerance to network outages (configurable)
Recorded in GoldenGate trail file
Transactions ordered in commit
sequence Recovery
Boundaries adjusted automatically Recovery ensures that no operations are skipped or
due to record filtering or trail splits duplicated after failure of any kind
Recovery of the Capture process is more involved than
Transaction integrity recovery the Delivery process
Checkpointing and recovery are Although GoldenGate processes are completely
based on transaction boundaries decoupled, the Delivery process must be aware of a
Original commit sequence is recovery performed by the Capture process or pump
maintained by the delivery process
23 24
4
Oracle GoldenGate Products and Services for
Windows, UNIX & Linux
Products
Oracle GoldenGate Transactional Data Management (TDM)
<Insert Picture Here>
Oracle GoldenGate Director
Oracle GoldenGate Veridata
Oracle GoldenGate Products TDM Integration Edition
TDM Netezza Integration
TDM Greenplum Integration
Services
Professional Services
Educational Services
For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential
Oracle GoldenGate Products and Services for What is Oracle GoldenGate Veridata?
NonStop
Products Oracle GoldenGate Veridata is a high-speed, low-impact data comparison
solution that identifies and reports data discrepancies between two databases,
Transactional Data Management (TDM) Capture without interrupting those systems or the business processes they support.
Transactional Data Management (TDM) Delivery
Logger
Syncfile
Rollback
Base 24 Add-on Modules: T24, E24, N24, D24, M24
GoldenGate Director
GoldenGate Veridata
Services
Professional Services
Educational Services
5
Oracle GoldenGate Director
Management Pack
A centralized server-based graphical application that offers an
intuitive way to define, configure, manage, and report on
GoldenGate TDM processes.
31
Oracle Differentiator
For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential
6
Value Propositions for Oracle GoldenGate
Oracle GoldenGate
37 38
Capture: committed transactions are captured (and can be Capture: committed transactions are captured (and can be
filtered) as they occur by reading the transaction logs. filtered) as they occur by reading the transaction logs.
Trail: stages and queues data for routing.
Trail
Capture Capture
LAN/WAN LAN/WAN
Internet Source Trail Internet
TCP/IP TCP/IP
Capture: committed transactions are captured (and can be Capture: committed transactions are captured (and can be
filtered) as they occur by reading the transaction logs. filtered) as they occur by reading the transaction logs.
Trail: stages and queues data for routing. Trail: stages and queues data for routing.
Pump: distributes data for routing to target(s). Pump: distributes data for routing to target(s).
Route: data is compressed,
encrypted for routing to target(s).
7
How Oracle GoldenGate Works How Oracle GoldenGate Works
Capture: committed transactions are captured (and can be Capture: committed transactions are captured (and can be
filtered) as they occur by reading the transaction logs. filtered) as they occur by reading the transaction logs.
Trail: stages and queues data for routing. Trail: stages and queues data for routing.
Pump: distributes data for routing to target(s). Pump: distributes data for routing to target(s).
Route: data is compressed, Route: data is compressed,
encrypted for routing to target(s). encrypted for routing to target(s).
Delivery: applies data with transaction Delivery: applies data with transaction
integrity, transforming the data as required. integrity, transforming the data as required.
45 46
47
8
ETL Integration Delivery to JMS (Java Message System)
OLTP
Integration via Messaging
Delivery
ETL
JMS / MoM
Queue or
Topic
For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential
For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential
Solution
Report on live data in transactional systems
Solution Real-time Data Integration No additional load on source system offload reporting
to a database instance that is synchronized in real time
Benefit
Operational Reporting OGG Enables tactical reports quickly for decision making
and business transparency
Real-time Business Intelligence
Removes impact from source systems
Avoids the political challenges of running reports on
Transactional Operational transactional systems
Applications Data Store
What products to position?
Oracle GoldenGate offload to replica. BI Publisher for
operational reporting
Accessing the data in real time using GoldenGate we can
immediately see if were profitable and if our business processes
are working. Qualifier?
- Sam Peterson, SVP Technology, Overstock.com Do you have adequate transparency into the data in your most sensitive
transactional systems?
54
9
Real Time Data
Real-Time Data Integration Integration Oracle Real Time Data Integration
Enables Real-time BI Oracles Unique Offering for Real Time Business Intelligence
55 56
57 58
Operational Reporting
Oracle Database Active Data Guard Technical Overview
Query Offloading Oracle Technology Active Data Guard
(Offloading Source)
Non-Oracle Database GoldenGate
For Oracle employees and authorized partners only. Do not distribute to third parties.
59 2009 Oracle Corporation Proprietary and Confidential
10
Online versus Batch Tasks Checkpointing - Capture
For change data capture, Extract and Replicat save checkpoints to a
A GoldenGate Online Process is designed to run indefinitely, and uses
checkpoint file so they can recover in case of failure
checkpoints for recovery
Extract maintains:
A GoldenGate Task is a batch job designed to run once, and is not 3 input checkpoints
recoverable 1 output checkpoint for each trail it writes to
Starting point in Start of oldest Last record read
Change data capture & delivery can be run either continuously (online) transaction log uncommitted from transaction log
transaction
or as a special run (task) to capture changes for a specific period of Input:
time. Transaction Log
Checkpoints Delete, TX 4
Current Read
Position
Commit Ordered
Capture Source Trail
Source
Database
64
Delete, TX 4 Delete, TX 4
Current Read Current Read
Position Position
65 66
11
Parameters, Process Groups & Commands Solutions and Architecture Discussion Points
GoldenGate Instantiation
Start change data capture on Source Database
Current source Commit Sequence Number (CSN) is 222
Oracle GoldenGate
Instantiation Current CSN is
222 CSNs 222
through
69 70
Consistent Consistent
Current CSN is as of CSN Current CSN is Deliver as of CSN
245 CSNs 222 245 356 CSNs 222 transactions 356
through through 356 after CSN
245 In Sync!
71 72
12
Zero Downtime Migration & Upgrade Operational BI
Zero Downtime OS and DB Migrations Real-time Data Integration to Support Reporting Demands
ADP Reporting = Consolidate Product Line
Business Challenges: Data to Off-load Real-Time Reporting
RIM BlackBerry has a very small monthly window to take 40+ Source Systems in ASP Environment:
downtime for upgrades, migrations, and planned Zero-Downtime OS and Database Business Challenges:
HR, Payroll, Benefits Products
maintenance Upgrade with Application Maintenance Common real-time reporting functionality
Strict SLA limits with the major Telcos!
across ADP product suites
GoldenGate Solution: Ability to do cross-product reporting
Zero-Downtime Upgrade and OS change for business- Incremental revenue opportunity and Oracle Oracle Oracle SQL Server
critical Oracle databases improved customer service
GoldenGate Veridata to ensure that all data from source
Reporting gets moved off from production
is in-synch with target and that migration has been Application Maintenance
successful databases
Provisioning Provisioning
GoldenGate Benefits: Database migration GoldenGate Solution:
Fail-back capability to reduce business risk and downtime with failback Live Reporting for real-time data consolidation
No manual intervention to reduce possibility of errors from 40+ source systems into a centralized
Assurance of database consistency with Veridata. target
Reporting:
Minimal footprint and data latency Oracle Data Mart
Oracle 10.2.0.3 Oracle 10.2.0.4 Business Objects,
HP-UX HP-UX Maintains data integrity Crystal
Waterloo Slough, U.K. Ability to support data transformations and
data security With GoldenGate it was boom, boom, boom full
speed ahead.
- Amit Mukherjee, Senior Director of Reporting
Services, ADP
73 74
For Oracle employees and authorized partners only. Do not distribute to third parties.
75 2009 Oracle Corporation Proprietary and Confidential
3. Initial Load
Capture Delivery
13
Prepare Environment: Installation - Windows
June 9, 2010
14
Prepare Environment: Installation
Oracle GoldenGate Directories
DIRECTORY CONTENTS
Dirchk Oracle GoldenGate checkpoint files
Performs system management and monitoring tasks Create the parameter file using GGSCI
Parameter file
mgr.prm file in GGS ./dirprm directory
Note: To determine which port Manager is using
Event information written to ggserr.log file
GGSCI> INFO MGR
15
Prepare Environment: Manager
Sample MGR Parameter File
PORT 7809
DYNAMICPORTLIST 8001, 8002, 95009520
LAGREPORTHOURS 1
LAGINFOMINUTES 3
LAGCRITICALMINUTES 5
USERID The user ID and password (if needed) to access the database
16
Prepare Environment: Source Database Prepare Environment: Source Database
Overview SQL Server
To prepare the SQL Server source environment for Oracle
Set up the database to: GoldenGate:
Ensure access by Oracle GoldenGate
Enable transaction logging Create the ODBC data source
Oracle GoldenGate connects to a SQL Server database through an
ODBC connection
Note: the exact steps depend on the database
Extract and Replicat require an established data source name (dsn)
17
GGSCI Starting and Help
Add an Extract group Save the commands in a text file, for example:
START MGR
GGSCI> ADD EXTRACT myext, TRANLOG, BEGIN NOW ADD EXTRACT myext, TRANLOG, BEGIN NOW
ADD EXTTRAIL /ggs/dirdat/rt, EXTRACT myext
Add a local trail START EXTRACT myext
GGSCI> ADD EXTTRAIL /ggs/dirdat/rt, EXTRACT myext Then use the GGSCI OBEY command to run the file:
GGSCI> START EXTRACT myext Note. An Obey file can have any file extension or none.
18
Running GoldenGate from OS Shell
1. What is GGSCI?
2. Where can you view GoldenGates command syntax?
3. What is an Obey file and why would you use one?
Configuring Oracle GoldenGate:
Step 2. Change Capture
June 9, 2010
19
Change Capture - Tasks Change Capture - ADD EXTRACT Command
On the source system: Add the initial Extract checkpoint with the GGSCI command ADD
EXTRACT:
Add an initial Extract checkpoint for your data
source ADD EXTRACT <group name>
, <data source>
Set up a parameter file for Extract , <starting point>
[, <processing options>]
Define Oracle GoldenGate trails or output files
The components of this command are discussed in
Start Extract subsequent slides.
Change Capture - ADD EXTRACT <data source> Change Capture - ADD EXTRACT <starting point>
<data source> Source (and when used) <starting point> Database
SOURCEISTABLE Database table (initial data load) BEGIN {NOW | <datetime> } Any
TRANLOG Transaction log (change capture) EXTSEQNO <seqno>, EXTRBA <relative byte address> Oracle, SQL/MX
[<bsds name>] [DB2 z/OS]
EXTRBA <relative byte address> DB2 z/OS
EXTFILESOURCE <file name> Extract file (data pump)
EOF | LSN <value> DB2 LUW
EXTTRAILSOURCE <trail name> Trail (data pump)
LSN <value> SQL Server, Ingres
REPORT <file name> Alternative report file name (fully Create an initial-load Extract named load.
qualified) ADD EXTRACT load, SOURCEISTABLE
20
Change Capture - Edit Extract Parameters Change Capture - Add a Local/Remote Trail
Add a local or remote trail with the GGSCI command:
Create/edit an Extract parameter file with the GGSCI
command: ADD EXTTRAIL | RMTTRAIL <trail name>
, EXTRACT <group name>
EDIT PARAMS <group name> [, MEGABYTES <n>]
Start an Extract process with the GGSCI command: GGSCI> EDIT PARAMS ODS
Source
START EXTRACT <group name> Database
EXTRACT ODS
USERID GoldenUser, PASSWORD password
Normally this also triggers the target Manager process to RMTHOST 162.111.23.49, MGRPORT 7809
start a Server Collector process with default parameters Transaction RMTTRAIL /ggs/dirdat/rt
Log TABLE SALES.ORDERS;
TABLE SALES.INVENTORY;
Users can start Server Collector statically and modify the GGSCI> ADD EXTRACT ODS, TRANLOG, BEGIN NOW
parameters, though rarely used. See the GoldenGate for Capture GGSCI> ADD RMTTRAIL /ggs/dirdat/rt, EXTRACT ODS
Windows and Unix Reference Guide. GGSCI> START EXTRACT ODS
Note: The remote trail is created on the target system
Network
(TCP/IP)
/ggs/dirdat/rt000000
Trail /ggs/dirdat/rt000001
Transaction
RMTHOST 162.111.23.49, MGRPORT 7809 Lab Exercise:
RMTTRAIL /ggs/dirdat/rt
Log TABLE SALES.ORDERS; Configure Change Capture
TABLE SALES.INVENTORY;
/ggs/dirdat/rt000000
Trail /ggs/dirdat/rt000001
21
Change Capture Discussion Points
3. Initial Load (various methods) What are the outage time constraints?
Target
Database
Initial Load: Advantages of GoldenGate Methods Initial Load: GoldenGate Methods - Queue Data
Work across heterogeneous database types and platforms
SQL*
File Loader
No application downtime required Manager
Source Target
Database Database
Read directly from source tables, without locking tables File BCP
File
Fetch data in arrays to speed performance Capture File
SSIS
22
Initial Load: GoldenGate Methods - Direct Load Initial Load: GoldenGate Methods - Direct Bulk
Load (to Oracle)
Manager
Manager Manager
Manager
Manager Manager
Source Target Source Oracle
Database Database Database Target
Capture Delivery
Trail
Target Trail
Transaction 2. Change Capture 4. Change Delivery
Logs
June 9, 2010
23
Change Delivery - Replicat Overview Change Delivery - Tasks
Perform data transformation Add your initial Replicat checkpoint into GoldenGate trails
ADD REPLICAT
Perform database operations just as your application
performed them Start the Replicat process
START REPLICAT
Change Delivery Sample Oracle Configuration Change Delivery - Avoiding Collisions with
Initial Load
Trails If the source database remains active during an
GGSCI> DBLOGIN SOURCEDB mydb USERID login PASSWORD pw
initial load, you must either avoid or handle any
Trail
GGSCI> ADD CHECKPOINTTABLE ggs.checkpt collisions when updating the target with interim
GGSCI> EDIT PARAMS REPORD changes
REPLICAT REPORD
TARGETDB dsn USERID ggsuser PASSWORD ggspass Avoiding Collisions
-- USERID ggsuser, PASSWORD ggspass
Delivery
ASSUMETARGETDEFS If you can backup/restore or clone the database at a
DISCARDFILE /ggs/dirrpt/REPORT.dsc, APPEND
MAP SALES.ORDERS, TARGET SALES.ORDERS;
point in time, you can avoid collisions by starting
MAP SALES.INVENTORY, TARGET SALES.INVENTORY; Replicat to read trail records from a specific
transaction Commit Sequence Number (CSN):
GGSCI> ADD REPLICAT REPORD, EXTTRAIL /ggs/dirdat/rt
GGSCI> START REPLICAT REPORD
START REPLICAT <group> ATCSN | AFTERCSN <csn>
Target
Database
24
Change Delivery Discussion Points Day 1 Summary
For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential
Program Day 2
Presentations
Data Pumps
<Insert Picture Here> Extract Trails and Files <Insert Picture Here>
Parameters
Oracle GoldenGate Data Mapping and Transformation
For Oracle employees and authorized partners only. Do not distribute to third parties. For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential 2009 Oracle Corporation Proprietary and Confidential
25
Data Pumps One to Many Trails Data Pumps One to Many Target Systems
Data Pump 1
Target Trail
Target Trail
EXTRACT <primary>
<login for your database>
EXTTRAIL ./dirdat/<trailid>
<table statements as required>; Workshop:
Data pumps are often configured for pass-through:
Configure a Data Pump
EXTRACT <datapump>
PASSTHRU
RMTHOST <target >, MGRPORT <port>
RMTTRAIL ./dirdat/<rmttrail>
<table statements as required>;
Add a data pump (source is the local trail from the primary
Extract )
ADD EXTRACT <datapump>, EXTTRAILSOURCE ./dirdat/<trailid>
26
Extract Trails and Files Extract Trails and Files - Overview
Reversing the sequence Extract writes checkpoints for trails during change capture:
Guarantees no data lost during restart
Multiple Replicat processes may process the same trail
158
Extract Trails and Files - Distribution Extract Trails and Files - Contents
159
27
Canonical Data Format Canonical Data Format - File Header
Each trail file has a file header that contains:
Trail file information
Compatibility level
By default, trails are formatted in the Canonical Character set
Data Format Creation time
File sequence number
File size
Each trail file has a trail file header and trail records
First and last record information
GoldenGate components only read Canonical Data Timestamp
Format Commit sequence number (CSN)
Extract information
GoldenGate version
Group name
Host name
Hardware type
OS type and version
DB type, version and character set
Identifies the trail file format by GoldenGate Identifies the sequence in which transactions were
<major>.<minor> version numbers committed:
Allows customers to use different versions of More efficient for deciding which transaction
GoldenGate Extract, trail files and Replicat together completed first than using heterogeneous database-
supplied transaction identifiers
Set in Extract EXTFILE, EXTTRAIL, RMTFILE or
RMTTRAIL parameter; for example: The CSN is based on various database identifiers;
RMTTRAIL /ggs/dirdat/ex, FORMAT RELEASE 10.0
for example:
The input and output trails of a data pump must For Oracle, the system change number (SCN)
have the same compatibility level For Teradata, the sequence ID
28
Extract Trails and Files - Alternative Formats Alternative Formats: FORMATASCII
29
Alternative Formats: FORMATSQL Syntax Alternative Formats: FORMATSQL
Sample Output
FORMATSQL B,2008-11-11:13:48:49.000000,1226440129,155,
DELETE FROM TEST.TCUSTMER WHERE CUST_CODE='JANE';
[, NONAMES ] DELETE FROM TEST.TCUSTMER WHERE CUST_CODE='WILL';
[, NOPKUPDATES ] DELETE FROM TEST.TCUSTORD WHERE CUST_CODE='JANE' AND
ORDER_DATE='1995-11-11:13:52:00' AND PRODUCT_CODE='PLANE' AND
[, ORACLE ] ORDER_ID='256';
DELETE FROM TEST.TCUSTORD WHERE CUST_CODE='WILL' AND
ORDER_DATE='1994-09-30:15:33:00' AND PRODUCT_CODE='CAR' AND
ORDER_ID='144';
INSERT INTO TEST.TCUSTMER (CUST_CODE,NAME,CITY,STATE) VALUES
('WILL','BG SOFTWARE CO.','SEATTLE','WA');
INSERT INTO TEST.TCUSTMER (CUST_CODE,NAME,CITY,STATE) VALUES
('JANE','ROCKY FLYER INC.','DENVER','CO');
INSERT INTO TEST.TCUSTORD
(CUST_CODE,ORDER_DATE,PRODUCT_CODE,ORDER_ID,PRODUCT_PRICE,P
RODUCT_AMOUNT,TRANSACTION_ID) VALUES ('WILL','1994-09-
30:15:33:00','CAR','144',17520.00,3,'100');
INSERT INTO TEST.TCUSTORD
(CUST_CODE,ORDER_DATE,PRODUCT_CODE,ORDER_ID,PRODUCT_PRICE,P
RODUCT_AMOUNT,TRANSACTION_ID) VALUES ('JANE','1995-11-
11:13:52:00','PLANE','256',133300.00,1,'100');
C,
Logdump
Viewing in Logdump
30
Logdump Starting and Getting Online Help Logdump Opening a Trail
To go to the first record, and to move from one record to Record header: contains transaction information.
I/O type Below the header is the data area.
another in sequence:
Operation
Logdump 6 > pos 0
type and
Logdump 7 > next (or just type n) time the
record was
To position at an approximate starting point and locate the written
next good header record:
Source
table
Logdump 8 > pos <approximate RBA>
Logdump 9 > scanforheader (or just type sfh) Image type:
could be a
before or Record Length of record and Record data,
after image Column data, its RBA position in the in ASCII
information in hex trail file
31
Logdump - Counting the Records in the Trail Logdump Counting Records in the Trail
(contd)
Logdump> count
TCUSTMER
Total Data Bytes 10562
LogTrail /ggs/dirdat/rt000000 has 4828 records Avg Bytes/Record 55
Total Data Bytes 334802 Delete 300
Avg Bytes/Record 69 Insert 1578
Delete 900 FieldComp 12
Insert 3902 Before Images 300
FieldComp 26 After Images 1590
Before Images 900
After Images 3928 TCUSTORD
Total Data Bytes 229178
Average of 25 Transactions Avg Bytes/Record 78
Bytes/Trans ..... 22661 Delete 600
Records/Trans ... 193
Insert 2324
Files/Trans ..... 8
Field Comp 14
Before Images 600
After Images 2338
Logdump Saving Records to a New Trail Logdump Keeping a Log of Your Session
32
Logdump Commands
Purpose Examples
Reverse - Overview
33
Reverse Overall Process Trails Discussion Points
Parameters - Overview
Extract parameters
Replicat parameters
June 9, 2010
Edit parameter files to configure GoldenGate processes GLOBALS parameters apply to all processes
Set when Manager starts
The GLOBALS parameter file is identified by its file path Reside in <GoldenGate install directory>/GLOBALS
GGSCI> EDIT PARAMS ./GLOBALS
Process parameters apply to a specific process
Manager and utility parameter files are identified by keywords (Manager, Extract, Server Collector, Replicat, Utilities)
GGSCI> EDIT PARAMS MGR
Set when the process starts
GGSCI> EDIT PARAMS DEFGEN
Override GLOBALS settings
Reside by default in the dirprm directory in files named
Extract and Replicat parameter files are identified by the
<processname>.prm
process group name
GGSCI> EDIT PARAMS <group name> Most apply to all tables processed but some can be specified at
the table level
34
GLOBALS Parameters Sample Manager Parameter File
Control things common to all processes in a GoldenGate
instance PORT 7809
DYNAMICPORTLIST 90019100
Can be overridden by parameters at the process level
AUTOSTART ER *
Must be created before any processes are started
AUTORESTART EXTRACT *, WAITMINUTES 2, RETRIES 5
Stored in <GoldenGate install directory>/GLOBALS
LAGREPORTHOURS 1
(GLOBALS is uppercase, no extension)
LAGINFOMINUTES 3
Must exit GGSCI to save
LAGCRITICALMINUTES 5
Once set, rarely changed
PURGEOLDEXTRACTS /ggs/dirdat/rt*, USECHECKPOINTS
Parameters most commonly used
MGRSERVNAME ggsmanager1
Defines a unique Manager service name on Windows systems
CHECKPOINTTABLE dbo.ggschkpt
Defines the table name used for Replicats checkpoint table
35
Extract Parameters Extract TABLE Parameter
Purpose Examples TABLE <table spec>
General SETENV, GETENV, OBEY [, TARGET <table spec>]
[, DEF <definitions template>]
Processing method BEGIN, END, PASSTHRU [, TARGETDEF <definitions template>]
[, COLMAP (<column mapping expression>)]
Database login SOURCEDB, USERID
[, {COLS | COLSEXCEPT} (<column specification>)]
Selecting and [, EVENTACTIONS <action>]
mapping data IGNOREINSERTS, GETUPDATEBEFORES, TABLE [, EXITPARAM <parameter string>]
[, FETCHBEFOREFILTER]
Routing data EXTTRAIL, RMTHOST, RMTTRAIL [, {FETCHCOLS | FETCHCOLSEXCEPT} (column specification)]
[, {FETCHMODCOLS | FETCHMODCOLSEXCEPT} (<column spec>)]
Formatting data FORMATASCII, FORMATSQL, FORMATXML, NOHEADERS
[, FILTER (<filter specification>)]
Custom processing CUSEREXIT, INCLUDE, MACRO, SQLEXEC [, KEYCOLS (<column specification>)]
[, SQLEXEC (<SQL specification>)]
Reporting REPORT, REPORTCOUNT, STATOPTIONS [, SQLPREDICATE WHERE <where clause>]
Error handling DISCARDFILE, DDLERROR [, TOKENS (<token specification>)]
[, TRIMSPACES | NOTRIMSPACES]
Tuning ALLOCFILES, CHECKPOINTSECS, DBOPTIONS [, WHERE (<where clause>)]
Maintenance PURGEOLDEXTRACTS, REPORTROLLOVER ;
Note: You must use a semicolon to terminate the TABLE statement.
Security ENCRYPTTRAIL, DECRYPTTRAIL
TRANLOGOPTIONS ARCHIVEDLOGONLY
Causes Extract to read from the archived logs exclusively.
215 216
36
Sample Replicat Parameter File Replicat Parameters
Purpose Examples
REPLICAT SALESRPT
General SETENV, GETENV, OBEY
USERID ggsuser, PASSWORD ggspass
Processing method BEGIN, END, SPECIALRUN
ASSUMETARGETDEFS
Database login SOURCEDB, USERID
DISCARDFILE /ggs/dirrpt/SALESRPT.dsc, APPEND
Selecting, converting COLMATCH, IGNOREUPDATES, MAP, SOURCEDEFS,
MAP HR.STUDENT, TARGET HR.STUDENT and mapping data ASSUMETARGETDEFS
WHERE (STUDENT_NUMBER < 400000); Routing data EXTFILE, EXTTRAIL
MAP HR.CODES, TARGET HR.CODES; Custom processing CUSEREXIT, DEFERAPPLYINTERVAL, INCLUDE,
MAP SALES.ORDERS, TARGET SALES.ORDERS, MACRO, SQLEXEC
WHERE (STATE = CA AND OFFICE = LA); Reporting REPORT, REPORTCOUNT, STATOPTIONS
Error handling DISCARDFILE, OVERRIDEDUPS, HANDLECOLLISIONS
Tuning ALLOCFILES, BATCHSQL, GROUPTRANSOPS,
DBOPTIONS
Maintenance PURGEOLDEXTRACTS, REPORTROLLOVER
Security DECRYPTTRAIL
217
220
37
Data Mapping and Transformation - Overview
Data selection and filtering
Column mapping
Functions
Data Mapping and Transformation:
SQLEXEC Data Selection and Filtering
Macros
User tokens
User exits
Sequences (Oracle only, else omitted)
GoldenGate provides the ability to select or filter out data The WHERE clause is the simplest form of selection
based on a variety of levels and conditions
Parameter / Clause Selects WHERE clause appears on either the MAP or TABLE
parameter and must be surrounded by parenthesis
TABLE or MAP Table
WHERE Row
FILTER Row, Operation, Range WHERE clause cannot:
TABLE COLS | COLSEXCEPT Columns perform arithmetic operations
refer to trail header and user token values
WHERE can perform an evaluation for: Only rows where the state column has a value of CA are
returned.
WHERE (STATE = CA);
Element Description Example
Columns PRODUCT_AMT Only rows where the amount column has a value of NULL.
Note that if amount was not part of the update, the result is
Comparison operators =, <>, >, <, >=, <=
false.
Numeric values -123, 5500.123 WHERE (AMOUNT = @NULL);
Literal strings "AUTO", "Ca"
Field tests Only rows where the amount was part of the operation and it
@NULL,@PRESENT,@ABSENT has value that is not null.
Conjunctive operators AND, OR WHERE (AMOUNT @PRESENT AND AMOUNT <> @NULL);
38
Selection FILTER Clause Data Selection FILTER Clause
Why is the example above not constructed like the one Example
below? The filter below will fail!
TABLE SALES.ACCOUNT, FILTER (@RANGE (1,3));
FILTER(NAME = JOE);
Data Selection RANGE Function Examples Data Selection RANGE Function Examples
For transaction volume beyond the capacity of a single Replicat, the
Two tables, REP and ACCOUNT, related by REP_ID, require three
example below shows three Replicat groups, each processing one- Replicats to handle the transaction volumes
third of the data
By hashing the REP_ID column, related rows will always be processed
Hashing each operation by primary key to a particular Replicat to the same Replicat
guarantees the original sequence of operations RMTTRAIL /ggs/dirdat/aa
TABLE SALES.REP, FILTER (@RANGE (1,3));
Replicat #1 TABLE SALES.ACCOUNT, FILTER (@RANGE (1,3,REP_ID));
MAP SALES.ACCOUNT,
TARGET SALES.ACCOUNT, FILTER (@RANGE (1,3));
RMTTRAIL /ggs/dirdat/bb
TABLE SALES.REP, FILTER (@RANGE (2,3));
Replicat #2
MAP SALES.ACCOUNT, TABLE SALES.ACCOUNT, FILTER (@RANGE (2,3,REP_ID));
TARGET SALES.ACCOUNT, FILTER (@RANGE (2,3));
RMTTRAIL /ggs/dirdat/cc
Replicat #3 TABLE SALES.REP, FILTER (@RANGE (3,3));
MAP SALES.ACCOUNT, TABLE SALES.ACCOUNT, FILTER (@RANGE (3,3,REP_ID));
TARGET SALES.ACCOUNT, FILTER (@RANGE (3,3));
39
Day 2 Summary
Presentations
Data Pumps
Extract Trails and Files <Insert Picture Here> <Insert Picture Here>
Parameters
Data Mapping and Transformation Oracle GoldenGate
Student Labs
Program Day 3
For Oracle employees and authorized partners only. Do not distribute to third parties. For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential 2009 Oracle Corporation Proprietary and Confidential
Program Day 3
Presentations
Data Mapping and Transformation (continued)
Configuration Options <Insert Picture Here>
For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential
Oracle GoldenGate provides the capability to map columns Syntax for the COLMAP clause:
from one table to another
Data can be transformed between dissimilar database tables MAP SOURCE.TABLE, TARGET TARGET.TABLE,
Using COLMAP to map target columns from your source columns COLMAP ( [USEDEFAULTS,]
<target field> = <source expression>
Oracle GoldenGate automatically matches source to target
column names with USEDEFAULTS [, <target field> = <source expression>]
[, ...]
Mapping can be applied either when extracting or replicating
data );
40
Column Mapping Example Column Mapping Building History
MAP HR.CONTACT, TARGET HR.PHONE, This example uses special values to build history of operations
data
COLMAP (USEDEFAULTS,
NAME = CUST_NAME, INSERTALLRECORDS
MAP SALES.ACCOUNT, TARGET REPORT.ACCTHISTORY,
PHONE_NUMBER = @STRCAT( (, AREA_CODE, ), COLMAP (USEDEFAULTS,
PH_PREFIX, -, PH_NUMBER ) ); TRAN_TIME =
@GETENV(GGHEADER,COMMITTIMESTAMP),
OP_TYPE = @GETENV(GGHEADER, OPTYPE),
BEFORE_AFTER_IND = @GETENV(GGHEADER,
BEFOREAFTERINDICATOR),
);
Using column conversion functions, you can: MAP SALES.ACCOUNT, TARGET REPORT.ACCOUNT,
COLMAP ( USEDEFAULTS,
Perform string and number conversion TRANSACTION_DATE = @DATE (YYYY-MM-DD,
Extract portions of strings or concatenate columns YY, YEAR,
Compare strings or numbers MM, MONTH,
Perform a variety of date mappings DD, DAY),
Use single or nested IF statements to evaluate numbers, AREA_CODE = @STREXT (PHONE-NO, 1, 3),
strings, and other column values to determine the PHONE_PREFIX = @STREXT (PHONE-NO, 4, 6),
appropriate value and format for target columns PHONE_NUMBER = @STREXT (PHONE-NO, 7, 10) );
41
Functions Performing Tests on Column Values Discussion Points: IF Function
Function Description
Syntax:
CASE Allows user to select a value depending on a series of value tests
@IF (<conditional expression>,
<value if expression is non-zero>,
EVAL Allows a user to select a value depending on a series of independent <value if expression is zero>)
tests
Non-zero is considered true, and zero (0) is considered false.
IF Selects one of two values depending on whether a conditional
statement returns TRUE or FALSE
1. What IF clause would you use to set the target column
COLSTAT Returns whether a column value is missing, NULL or invalid AMOUNT_COL to AMT only if AMT is greater than zero, and
otherwise return zero?
COLTEST Tests whether a column value is present, missing, NULL or invalid AMOUNT_COL = @IF (AMT > 0, AMT, 0)
42
Functions Working with Strings and Numbers Functions Working with Strings and Numbers
Function Description Function Description
COMPUTE Returns the result of an arithmetic expression STRLTRIM Trims leading spaces in a column
NUMBIN Converts a binary string into a number STRNCAT Concatenates one or more strings up to a specified number of
characters per string
NUMSTR Converts a string into a number
STRNCMP Compares two strings up to a certain number of characters
STRCAT Concatenates two or more strings
STRNUM Converts a number into a string, with justification and zero-fill
STRCMP Compares two strings to determine if they are equal, or if the first is options
less or greater than the second
RANGE Divides workload into multiple groups of data, while ensuring the same
row will always be sent to the same process. Range uses a hash against
primary key or user defined columns.
TOKEN Maps environmental values that are stored in the user token area to the
target column.
43
SQLEXEC - Overview
SQLEXEC advantages:
Execute a stored procedure or SQL query using the SQLEXEC is available for the following databases:
SQLEXEC clause of the TABLE or MAP parameter Oracle SQL Server
Teradata Sybase
DB2 ODBC
Optionally extract output parameters from the stored
procedure or SQL query as input to a FILTER or The stored procedure interface supports the following data
types for input and output parameters:
COLMAP clause using the @GETVAL function
Oracle DB2 SQL Server / Sybase / Teradata
v12+
Use SQLEXEC at the root level (without CHAR CHAR CHAR
input/output parameters) to call a stored procedure, VARCHAR2 VARCHAR VARCHAR
run a SQL query or issue a database command DATE DATETIME DATE
All Numeric types All Numeric types All Numeric types
LOBS up to 200 bytes BLOB data types
SQLEXEC Using with Lookup Stored SQLEXEC Using with Lookup Stored
Procedure Procedure (contd)
The following stored procedure performs a query to return a The following parameter entry:
description given a code:
Maps data from the ACCOUNT table to the NEWACCT table
CREATE OR REPLACE PROCEDURE LOOKUP When processing any rows from ACCOUNT, Extract
(CODE_PARAM IN VARCHAR2, DESC_PARAM OUT VARCHAR2) performs the LOOKUP stored procedure prior to executing
BEGIN the column map
SELECT DESC_COL INTO DESC_PARAM Maps values returned in desc_param to the newacct_val
FROM LOOKUP_TABLE column using the @GETVAL function
WHERE CODE_COL = CODE_PARAM;
END;
MAP HR.ACCOUNT, TARGET HR.NEWACCT,
SQLEXEC (spname lookup,
params (code_param = account_code)),
COLMAP (USEDEFAULTS, newacct_id = account_id,
newacct_val = @GETVAL(lookup.desc_param));
44
SQLEXEC Using with SQL Query SQLEXEC - Syntax within a TABLE or MAP
Statement
The following example (for Oracle) performs a SQL query When the SQLEXEC parameter is used within a TABLE or MAP
statement, the syntax is:
directly to return the description. @GETVAL is used to
retrieve the return parameter: SQLEXEC (
{ SPNAME <sp name> | QUERY <sql query> }
MAP HR.ACCOUNT, TARGET HR.NEWACCT, [, ID <logical name>]
SQLEXEC (id lookup, { PARAMS <param spec> | NOPARAMS}
[, BEFOREFILTER | AFTERFILTER]
query select desc_param from lookup_table
[, DBOP]
where code_col = :code_param,
[, EXEC <frequency>]
params (code_param = account_code)),
[, ALLPARAMS <option>]
COLMAP (USEDEFAULTS, newacct_id = account_id, [, PARAMBUFSIZE <num bytes>]
newacct_val = @GETVAL(lookup.desc_param)); [, MAXVARCHARLEN <num bytes>]
[, TRACE <option>]
[, ERROR <action>]
)
Example
45
Macros - Overview
Data Mapping and Transformation: Write once and use many times
Macros
Consolidate multiple statements
46
Macros Libraries Macros Expansion
Macros can be built in a library and referenced into Macro Processor enables tracing of macro expansion with the
CMDTRACE option
your parameter file
Syntax
EXTRACT EXTACCT
CMDTRACE [ ON | OFF | DETAIL ]
INCLUDE /ggs/dirprm/macro.lib
Default is OFF
EXTRACT EXTACCT
NOLIST INCLUDE /ggs/dirprm/macro.lib
include /ggs/dirprm/large.lib CMDTRACE ON
LIST MAP SALES.ACCOUNT, TARGET REPORT.ACCOUNT_HISTORY,
COLMAP (USEDEFAULTS,
#maptranfields () );
Data Mapping and Transformation: Set token values through a TABLE TOKENS clause and
@GETENV functions, for example:
User Tokens
TABLE SALES.PRODUCT,
TOKENS (TKN1 =
@GETENV(GGENVIRONMENT",OSUSERNAME"),
TKN2 = @GETENV(GGHEADER",COMMITTIMESTAMP") );
47
User Tokens - Using User Tokens Viewing in Logdump
logdump 2> usertoken on
Tokens are retrieved through a MAP COLMAP clause and logdump 3> usertoken detail
logdump 4> next
@TOKEN functions:
User tokens:
TKN-HOST : jemhadar
MAP SALES.ORDER, TARGET REPORT.ORDER_HISTORY, TKN-GROUP : EXTORA
COLMAP (USEDEFAULTS, TKN-BA_IND : AFTER
TKN-COMMIT_TS : 2003-03-24 17:08:59.000000
TKN_NUMRECS = @TOKEN ("TKN-NUMRECS");
TKN-POS : 3604496
TKN-RBA : 4058
MAP SALES.CUSTOMER, TARGET REPORT.CUSTOMER_HISTORY, TKN-TABLE : SOURCE.CUSTOMER
COLMAP (USEDEFAULTS, TKN-OPTYPE : INSERT
TKN-LENGTH : 57
TRAN_TIME = @TOKEN ("TKN-COMMIT-TS"),
TKN-TRAN_IND : BEGIN
OP_TYPE = @TOKEN (TKN-OP-TYPE), TKN-LAG_SEC :1
BEFORE_AFTER_IND = @TOKEN (TKN-BA-IND), TKN-LAG_MIN :0
TKN_ROWID = @TOKEN ("TKN-ROWID")); TKN-LAG_MSEC : 1229
TKN-NUMRECS :8
TKN-DBNAME : ORA901
TKN-DB_USER : GGOODRIC
TKN-DB_VER : 9.0.1.0.0
TKN-INAME : ora901
TKN-ROWID : AAABBAAABAAAB0BAAF
48
User Exits - Parameters User Exits Implementing
On Windows: create a DLL in C and create a routine to be
EXIT_CALL_TYPE indicates when, during processing, the called from Extract or Replicat
Extract or Replicat process calls the user exit: at start
processing, stop processing, begin transaction, end On UNIX: Create a shared object in C and create a routine to
transaction, process record, process marker, discard record, be called from Extract or Replicat
fatal error or call result
The routine must accept the following parameters:
EXIT_CALL_RESULT provides a response to the routine: EXIT_CALL_TYPE
OK, ignore, stop, abend or skip record EXIT_CALL_RESULT
EXIT_PARAMS
EXIT_PARAMS supplies information to the routine: calling
program path and name, function parameter, more records In the source for the DLL/shared object, include the usrdecs.h
indicator file (in the GoldenGate install directory)
You can call a user exit from Extract or Replicat by the CUSEREXIT
Sample user exit files are located in <GoldenGate parameter
installation directory>/ UserExitExamples
Syntax
CUSEREXIT <DLL or shared object name> <routine name>
Each directory contains the .c file as well as makefiles and [, PASSTHRU]
a readme.txt file. [, INCLUDEUPDATEBEFORES]
[, PARAMS "<startup string>]
Examples
CUSEREXIT userexit.dll MyUserExit
CUSEREXIT userexit.dll MyUserExit, INCLUDEUPDATEBEFORES, & PASSTHRU,
PARAMS "init.properties"
Oracle Sequences
Oracle GoldenGate supports the replication of Oracle sequence values
Use the Extract SEQUENCE parameter to extract sequence values from the
transaction log; for example:
SEQUENCE hr.employees_seq;
Data Mapping and Transformation:
Use the Replicat MAP parameter to apply sequence values to the target; for
Oracle Sequences example:
MAP hr.employees_seq, TARGET payroll.employees_seq;
Note: Change this default only if you know there will be no gaps in the
sequence updates (e.g. from a trail corruption or process failure) and you want
to improve the performance of GoldenGate
49
Configuration Options - Overview
BATCHSQL
Compression
Oracle GoldenGate Encryption
Configuration Options Bidirectional considerations
Event actions
DDL replication (Oracle, else omitted)
50
Options: Compression
COMPRESSTHRESHOLD sets the minimum byte size for which compression will
occur. Default is 1000 bytes.
Example:
Message Encryption
(Blowfish)
Trail or Extract File Encryption
GoldenGate uses 256-key byte substitution Trail or Extract File Encryption
Encrypts only the record data in a trail or extract file (Oracle GoldenGate)
51
Options: Message Encryption Options: Message Encryption (contd)
1. Run the GoldenGate KEYGEN utility to generate random hex keys
C:\GGS> RUN KEYGEN <key length> <number of keys> KEYGEN
Blowfish accepts a variable-length key from 32 to 128 bits
2. Enter key names and values in an ASCII text file named ENCKEYS (upper ENCKEYS ENCKEYS
case, no file extension) in the GoldenGate install directory
Capture Delivery
##Key name Key value
superkey 0x420E61BE7002D63560929CCA17A4E1FB
TCP/IP
secretkey 0x027742185BBF232D7C664A5E1A76B040 Network
Server Collector
3. Copy the ENCKEYS file to the source and target GoldenGate install directory Target Trail
For example:
TCP/IP
Network
GGSCI> ENCRYPT PASSWORD MyPass, ENCRYPTKEY DRKEY Server Collector
Encrypted password: AACAAAAAAAAAAAIAJFGBNEYGTGSBSHVB Target Trail
3. Paste the encrypted password in the Extract or Replicat PASSWORD Password Encryption
parameter, for example:
52
Workshop: Configuration Options:
Using Encryption Event Actions
Reports Logs Discards Chkpts Example using a separate event table to manage events:
Reports Logs Discards Chkpts
Whenever a record is written to the event table, the trail file is rolled over.
EVENT EVENT
PROCESSING Example using data values to trigger events:
PROCESSING
TCP/IP
Any record where account_no = 100 is discarded and a log message
Network written.
Transaction Target Trail
Capture Delivery
Log
53
Options: Event Actions - EVENTACTIONS Options: Event Actions Heartbeat Example
Parameter
TABLE | MAP A heartbeat table is periodically updated with the current time in the
source database:
EVENTACTIONS (
MAP source.heartbeat, TARGET target.heartbeat,
[STOP | ABORT | FORCESTOP]
FILTER ((@DATEDIFF (SS, hb_timestamp, @DATENOW() > 60
[IGNORE [TRANSACTION [INCLUDEVENT]] AND @DATEDIFF (SS, HBTIMESTAMP, @DATENOW() < 120),
EVENTACTIONS (LOG);
[DISCARD]
MAP source.heartbeat, TARGET target.heartbeat,
[LOG [INFO | WARNING]] FILTER (@DATEDIFF (SS, hb_timestamp, @DATENOW() > 120),
[REPORT] EVENTACTIONS (LOG WARNING);
[ROLLOVER]
[SHELL <command>] Info
EVENT Log
[TRACE <trace file> [TRANSACTION] [PURGE | APPEND]]
PROCESSING
[CHECKPOINT [BEFORE | AFTER | BOTH]] Warning
[, ...]
TX1
)
TX2
Heartbeat
Note: You can also use a TABLE parameter in a Replicat to trigger actions without writing
data to target tables TX4 Target Trail
Delivery
Application Application Application 2. When Replicat reads the event record, it communicates Application
to the second ETL process to start at the right point and
2. When Replicat reads the event performs checkpoints before and after the record.
1. User writes an event record at the record, it triggers an event action ETL ETL
planned outage point. This is read by run a custom script to switch the 1. When a batch load is starting, the ETL process writes an
Extract through the transaction log. application to the target database. event record. Extract reads the record and performs a
checkpoint before and after the record.
Capture Delivery
Capture Delivery
TCP/IP TCP/IP
Network Network
Source Trans Log Target Source Trans Log Target
Target Trail Target Trail
TCP/IP TCP/IP
Network Trans Log Network Trans Log
Delivery Capture
Delivery Capture
Target Trail 3. The Extract on the target, Target Trail
already configured and running, 3. When the second ETL process is completed, it generates an event record that is read by
starts capturing transactions. Extract on the target. When Replicat on the source receives the event record, it triggers a
custom script to start the application based on the status of the batch process on the source.
Transaction
Log Capture Delivery
Source Target
Target Source
TCP/IP
Network
Delivery
Target Trail
Capture Transaction
Log
54
Options: Bidirectional - Capabilities Options: Bidirectional - Issues
Available for both homogeneous and heterogeneous Loop Detection
configurations
Detect if GoldenGate or the application performed the operation
Distributed processing
Conflict Avoidance, Detection and Resolution
Both sides are live Detect if an update occurred on both the source and target before
the changes were applied by GoldenGate
Oracle GoldenGates low latency reduces the risk of conflicts
Determine business rules on how to handle collisions
The update operation is captured and sent to system B The row is inserted on system B
The row is updated on system B
The update operation is captured sent to system A The insert is captured and sent to system A
The row is updated on system A
The insert is attempted on system A, but the operation fails
with a conflict on the primary key causing synchronization
Without loop-detection, this loop continues endlessly. services to HALT
55
Options: Bidirectional - Loop Detection (contd) Options: Bidirectional - Loop Detection (contd)
Sybase NonStop SQL/MX
Do nothing and allow Replicat to use the default transaction name ggs_repl
Replicat transactions identified by the name of the checkpoint table
Or identify the Replicat transaction name in the Extract parameter: specified with TRANLOGOPTIONS option:
TRANLOGOPTIONS EXCLUDETRANS <trans name>
Or identify the Replicat user name in the Extract parameter:
TRANLOGOPTIONS FILTERTABLE <table>
TRANLOGOPTIONS EXCLUDEUSER <user name> Extract ignores transactions that include this checkpoint table
PURGEDATA operation is not supported
Teradata
You do not need to identify Replicat transactions that are applied to a
Teradata database.
c-tree
Extract automatically identifies Replicat transactions that are applied
to a c-tree database
56
Options: Bidirectional - Conflict Resolution by Options: Multi-master Oracle Sequence
Applying Net Differences Numbers
Initial balance: $500
Oracle sequence numbers are used for a variety of purposes,
Trans A: Mr. Smith deposits $75 in LA: end balance $575 such as to maintain a count, determine order of entry, or
generate a primary key value
Trans B: Mrs. Smith withdraws $20 in NY: end balance $480
Trans A replicated to NY: begin/end balance $500 / $575 GoldenGate does not support the replication of sequence
Conflict detected between trans A begin balance (500) and values in a bidirectional configuration
current NY balance (480), so apply net difference:
Trans A end balance (575) Trans A begin balance (500) = +75
In a bidirectional configuration, to ensure that the source and
resulting in NY end balance of $555
target sequence numbers are unique, assign odd and even
Trans B replicated to LA: begin/end balance $500 / $480 values
Conflict detected between trans B begin balance (500) and
current LA balance (575), so apply net difference: In a multidirectional configuration, each system must use a
Trans B end balance (480) Trans B begin balance (500) = -20
resulting in LA end balance of $555
starting value and increment based on the number of systems
Time
Options: Bidirectional - Sybase & SQL Server Options: Bidirectional - Truncate Table
Identity Data Types Operations
Similar issues to Oracle sequences Truncate Table operations cannot be detected for
loops
Set the seed and increment values so each system
is using a different range of identity values Make sure that GETTRUNCATES is ON for only one
direction
Use IGNORETRUNCATES (default) for the other
direction
Change database security so truncates can only be
issued on one system
57
Options: DDL Replication Oracle GoldenGate Options: DDL Replication - Oracle
Requirements/Restrictions Requirements/Restrictions
Identical source and target data def: ASSUMETARGETDEFS
Schema names using Oracle reserved names are ignored
Data pumps must be configured in PASSTHRU mode
The GETTRUNCATES parameter should not be used with full
WILDCARDRESOLVE must remain set to DYNAMIC (default) DDL support
Data manipulation is not supported Table name cannot be longer than 16 characters plus quotation
Oracle GoldenGate user exits are not supported for DDL activity marks for ALTER TABLE RENAME
Restrictions exist for DDL operations that involve user defined types and LOB ALTER TABLE..MOVE TABLESPACE
data
Supported when tablespace all SMALLFILE or BIGFILE
DDL on objects in TABLE or MAP statements inherit the limitations in allowed
Stop Extract before issuing a MOVE TABLESPACE
characters of those parameters
The recycle bin must be turned off
Restrictions exist for DDL operations that involve stored procedures
ALTER DATABASE and ALTER SYSTEM not captured
Oracle GoldenGate does not support bidirectional replication of DDL allow
DDL changes on one system only
DDL statements > 2MB require special handling
58
Options: DDL Replication - DDL Parameter Options: DDL Replication - String Substitution
DDL parameter enables DDL support and filters the DDLSUBST parameter substitutes strings in a DDL
operations operation
Valid for Extract and Replicat Multiple statements can be used
DDL [
{INCLUDE | EXCLUDE} DDLSUBST parameter syntax:
[, MAPPED | UNMAPPED | OTHER | ALL] DDLSUBST <search_string> WITH <replace_string>
[, OPTYPE <type>] [INCLUDE <clause> | EXCLUDE <clause>]
[, OBJTYPE <type>]
[, OBJNAME <name>] Where:
[, INSTR <string>] <search_string> is the string in the source DDL statement you
[, INSTRCOMMENTS <comment_string>] want to replace, in single quotes
] <replace_string> is the replacement string, in single quotes
[...] <clause> is an inclusion or exclusion clause using same syntax as
INCLUDE and EXCLUDE from DDL parameter
Options: DDL Replication - Error Handling Options: DDL Replication - DDLOPTIONS for
Oracle
DDLERROR parameter: default and specific error handling DDLOPTIONS parameter configures aspects of DDL
rules to handle full range of anticipated errors
processing other than filtering and string substitution
Extract syntax: DDLOPTIONS
DDLERROR [, MAPDERIVED | NOMAPDERIVED]
[, RESTARTSKIP <num skips>] [, NOCROSSRENAME]
[, REPORT | NOREPORT]
Replicat syntax:
[, ADDTRANDATA]
DDLERROR
[, DEFAULTUSERPASSWORD <password>
{<error> | DEFAULT} {<response>}
[ENCRYPTKEY DEFAULT | ENCRYPTKEY <keyname>]]
[RETRYOP MAXRETRIES <n> [RETRYDELAY <delay>]]
[, GETAPPLOPS | IGNOREAPPLOPS]
{INCLUDE <clause> | EXCLUDE <clause>}
[, GETREPLICATES | IGNOREREPLICATES]
[, IGNOREMISSINGTABLES | ABENDONMISSINGTABLES]
[, REMOVECOMMENTS {BEFORE | AFTER}]
[, RESTARTCOLLISIONS | NORESTARTCOLLISIONS]
[, REPLICATEPASSWORD | NOREPLICATEPASSWORD]
Where <response> can be IGNORE, ABEND, DISCARD
59
Managing GoldenGate - Overview
Managing: Command Level Security Overview Managing: Command Level Security - CMDSEC
File
Security rules established in the CMDSEC file Command security entries include:
Entry Examples
COMMAND NAME Such as INFO, ADD, START, STOP.
Controls which users have access to GGSCI May be any GGSCI command name or a wildcard.
commands OBJECT NAME Such as EXTRACT, REPLICAT.
May be any GGSCI command object or a wildcard.
GROUP NAME For a Windows or Unix group.
For example, certain users On Unix operating systems, you can specify:
- A numeric group ID instead of the group name
Allowed to view reports and collect status information - A wildcard to specify all groups
USER NAME For a Windows or Unix user.
Excluded from stop and delete commands On Unix operating systems, you can specify:
- A numeric user ID instead of the user name
- A wildcard to specify all users
ACCESS Granted or prohibited; YES or NO
STATUS
STATUS
REPLICAT
*
ggsgroup
ggsgroup
ggsuser
*
NO
YES
Managing Oracle GoldenGate
START REPLICAT root * YES Trail Management
START REPLICAT * * NO
* EXTRACT 200 * NO
STOP * ggsgroup * NO
STOP * ggsgroup ggsuser YES
* * root root YES
* * * * NO
Can you see the error with the two STOP lines?
60
Managing: Trail Management Overview Managing: Trail Management Allocation of
Space
Trail files are created by Extract
Allocation of space for Oracle GoldenGate trails
Plan the initial allocation of space for the trail files
Manage the ongoing number and size of the files Initially set the number and size of the trail files
Using size and number parameters
based on
Purging old files Transaction log volume
Speed of your system
Maximum anticipated outage
For example:
Syntax
Trail files AA000000, AA000001, and AA000002 exist. Replicat
has been down for four hours and has not completed
processing any of the files. SEND MANAGER
{CHILDSTATUS |
The result:
GETPORTINFO [DETAIL] |
The files have not been accessed for 4 hours so MINKEEP
GETPURGEOLDEXTRACTS |
rule allows purging, but checkpoints indicate the files have
not been processed so purge is not allowed. KILL <process name>}
61
Managing: Trail Management
GETPURGEOLDEXTRACTS Report
Example:
GGSCI > SEND MANAGER GETPURGEOLDEXTRACTS
PurgeOldExtracts Rules
Fileset MinHours MaxHours MinFiles MaxFiles UseCP
S:\GGS\DIRDAT\EXTTRAIL\P4\* 0 0 1 0 Y
S:\GGS\DIRDAT\EXTTRAIL\P2\* 0 0 1 0 Y Managing Oracle GoldenGate
S:\GGS\DIRDAT\EXTTRAIL\P1\* 0 0 1 0 Y
S:\GGS\DIRDAT\REPTRAIL\P4\* 0 0 1 0 Y Process Startup and TCP/IP Errors
S:\GGS\DIRDAT\REPTRAIL\P2\* 0 0 1 0 Y
S:\GGS\DIRDAT\REPTRAIL\P1\* 0 0 1 0 Y
OK
Extract Trails
Filename Oldest_Chkpt_Seqno IsTable IsVamTwoPhaseCommit
S:\GGS\8020\DIRDAT\RT 3 0 0
S:\GGS\8020\DIRDAT\REPTRAIL\P1\RT 13 0 0
S:\GGS\8020\DIRDAT\REPTRAIL\P2\RT 13 0 0
S:\GGS\8020\DIRDAT\REPTRAIL\P4\RT 13 0 0
S:\GGS\8020\GGSLOG 735275 1 0
S:\GGS\8020\DIRDAT\EXTTRAIL\P1\ET 14 0 0
S:\GGS\8020\DIRDAT\EXTTRAIL\P2\ET 14 0 0
S:\GGS\8020\DIRDAT\EXTTRAIL\P4\ET 14 0 0
62
Managing: Reporting - Overview Managing: Reporting Set up with Parameters
Generate interim runtime statistics using:
Set up hourly or daily interval reports via parameters
REPORT REPORT
REPORTCOUNT REPORT AT 14:00
REPORTROLLOVER
REPORT ON FRIDAY AT 23:00
63
Managing: Statistics STATOPTIONS
Parameter
REPORTDETAIL | NOREPORTDETAIL
Report statistics for collisions (Replicat)
REPORTFETCH | NOREPORTFETCH
Managing Oracle GoldenGate
Monitoring
Report statistics on row fetching (Extract)
RESETREPORTSTATS |
NORESETREPORTSTATS
Resets statistics when a new report file is create by the
REPORTROLLOVER parameter
System Time
Write to Trail
LAGINFOHOURS <hours>
System Time
Write to Trail
LAGCRITICAL
Extract lag Set lag threshold to force a warning message to the error log
LAGCRITICALSECONDS <seconds>
Pump lag LAGCRITICALMINUTES <minutes>
LAGCRITICALHOURS <hours>
Replicat lag
end-to-end latency
64
Managing: Monitoring - Setting Email Alerts in
Director
Within Director, you can configure email alerts:
when specific messages appear in the Oracle GoldenGate error log
when a specific lag threshold has been exceeded
Oracle GoldenGate
Troubleshooting
Troubleshooting
INFO ALL shows status and lag for all Manager, Extract, and Replicat
GoldenGate reports and logs
processes on the system
Process reports
Event/Error log
INFO <group>, DETAIL shows process status, datasource,
Discard file checkpoints, lag, working directory, files containing processing
System logs information
Both Extract and Replicat restart after a failure at their last read
INFO <group>, SHOWCH provides detailed checkpoint information
checkpoint
SEND EXTRACT STATUS command reports when Extract is
Extract maintains the following read checkpoints:
recovering
Startup starting position in data source (transaction log or trail)
Checkpoint information is updated during the recovery stage allowing
Recovery position of oldest unprocessed transaction in data you to monitor the progress with the INFO command
source
If an error prevents Replicat from moving forward in the trail, you can
Current position of last record read in data source restart Replicat after the bad transaction:
and one write checkpoint:
Current - current write position in trail START REPLICAT <group>
SKIPTRANSACTION | ATCSN <csn> |AFTERCSN <csn>
Replicat maintains the following read checkpoints:
Startup starting position in trail To determine the CSN to use, view the Replicat report file with the
Current position of last record read in trail VIEW REPORT <group> command or view the trail with the
Logdump utility
65
Troubleshooting - Process Report Troubleshooting - Event Log (ggserr.log)
Each Extract, Replicat, and Manager has its own report file that Oracle GoldenGate Event Log provides:
shows: History of GGSCI commands
Banner with startup time Processes that started and stopped
Parameters in use Errors that occurred
Table and column mapping Informational messages
Database and Environmental information
Runtime messages and errors Shows events leading to an error. For example, you might discover:
Someone stopped a process
The report provides initial clues, such as invalid parameter, A process failed to make a TCP/IP or database connection
data mapping errors, or database error messages. A process could not open a file
For Oracle employees and authorized partners only. Do not distribute to third parties.
2009 Oracle Corporation Proprietary and Confidential
66