Transcript Database Monitoring Services

Jos van Lammeren
[email protected]
www.LamkoDB.com
www.MonitorMyDatabase.com 1


Background
GoldenGate
•
•
•
•
•
•

Overview
Setup
Operations
Troubleshooting
Monitoring
Challenges
Q&A
www.LamkoDB.com
www.MonitorMyDatabase.com
2


Using Oracle GoldenGate since 2010
At clients
• Oracle 9i, 10g near-zero downtime migrations to 11g RAC
• Oracle 11g RAC setup for DR and EDW load
• Upgrade OGG 10.4 to 11.2 for Oracle 11g clusters (no app
downtime)
• Oracle 9i, 10g, 11g EDW load
• SQL Server 2008 to Oracle 11g EDW load

At LamkoDB
• Oracle 10g, 11g, 12c, SQL Server 2008, 2012, MySQL tests with
OGG 11g and 12c
www.LamkoDB.com
www.MonitorMyDatabase.com
3

What GoldenGate does
•
•
•
•
•
•
•
•

Continuous data replication from one database to another
Source and target databases can be of different vendors/releases
Non-intrusive, low-impact, sub-second latency
Maintains transactional integrity - Resilient against interruptions
and failures
Data transformation on the fly
Few or many tables in a schema
DDL replication if desired
Used for EDW feeds, DR, DB version upgrades, DB migrations
What GoldenGate does not
• Is not a replacement for Data Guard
www.LamkoDB.com
www.MonitorMyDatabase.com
4
www.LamkoDB.com
www.MonitorMyDatabase.com
5

Use of replication
www.LamkoDB.com
www.MonitorMyDatabase.com
6

Source Databases/Platforms supported
www.LamkoDB.com
www.MonitorMyDatabase.com
7

Target Databases/Platforms supported
www.LamkoDB.com
www.MonitorMyDatabase.com
8



Processes/Files involved
Classic Capture or Integrated Capture mode
Nonintegrated Replicat or Integrated Replicat
www.LamkoDB.com
www.MonitorMyDatabase.com
9

Observations
Not all data types are supported
Need data type translation in heterogeneous environments
Encryption of data in files and across network
Optional compression of data copied by pump process
Cryptic names for processes: 8 characters
Cryptic names of trail files e1xxxxxx (2 characters followed by
incrementing number)
• Administrator must like command line tool (similar to sqlplus)
•
•
•
•
•
•
www.LamkoDB.com
www.MonitorMyDatabase.com 10

Installation GG software (source & target server)
• Download software from Oracle site. Software to use is Oracle
DBMS release specific.
• On Linux/Unix create account that owns the software & add to
dba group
• Create profile to set $ORACLE_HOME, $PATH, etc. for the account
• Unzip & untar software or use Oracle Universal Installer
• Configure GLOBALS and mgr.prm files

Installation in DB
•
•
•
•
DB in archive log mode (at least source instance)
Turn off recyclebin (in source instance for DDL replication)
Put DB in supplemental logging mode (source instance)
Run OGG supplied installation scripts (source & target)
www.LamkoDB.com
www.MonitorMyDatabase.com 11






How many schemas/tables to replicate & how much
data will be replicated?
Have tables the same structure in source and target
database or do target tables have additional columns?
Do tables have PKs?
Are there referential constraints to deal with if not all
tables are being replicated?
Delete Cascade constraints
When bi-directional replication
• Sequences
• Triggers firing on either database
www.LamkoDB.com
www.MonitorMyDatabase.com 12

Setup replication flow & initial load of data
•
•
•
•
Create/start Extract on source database
Create/start Pump process on source database
Do initial data load from source to target database
Create/start Replicat on target database
www.LamkoDB.com
www.MonitorMyDatabase.com 13

Extract
• Extract process E1ORA1
• E1ORA1 extracts data from MMDBORA1 (Oracle) to local trail files
in directory/gg/product/11.2.1_11g/ggs/dirdat/MMDBORA1/e1
www.LamkoDB.com
www.MonitorMyDatabase.com 14
EXTRACT
E01ORA1.obey
ADD EXTRACT E01ORA1, TRANLOG, BEGIN NOW, params
/gg/product/11.2.1_11g/ggs/dirprm/MMDBORA1/E01ORA1.prm
ADD EXTTRAIL /gg/product/11.2.1_11g/ggs/dirdat/MMDBORA1/e1, EXTRACT E01ORA1
E01ORA1.prm
EXTRACT E01ORA1
. . .
USERID ggate, PASSWORD abc
EXTTRAIL /gg/product/11.2.1_11g/ggs/dirdat/MMDBORA1/e1 megabytes 100
. . .
TABLE CHINOOK.*;
start E01ORA1
www.LamkoDB.com
www.MonitorMyDatabase.com 15

Pump
• Pump process P1ORA1 copies local trail file contents to remote
trail files on another server in directory
C:\gg\dirdat\MMDBORA1\p1
www.LamkoDB.com
www.MonitorMyDatabase.com 16
PUMP
P01ORA1.obey
ADD EXTRACT P01ORA1, EXTTRAILSOURCE
/gg/product/11.2.1_11g/ggs/dirdat/MMDBORA1/e1, params
/gg/product/11.2.1_11g/ggs/dirprm/MMDBORA1/P01ORA1.prm
ADD RMTTRAIL
C:\gg\dirdat\MMDBORA1\p1, EXTRACT P01ORA1, MEGABYTES 100
P01ORA1.prm
EXTRACT P01ORA1
RMTHOST 192.168.21.94, MGRPORT 7809
RMTTRAIL C:\gg\dirdat\MMDBORA1\p1, megabytes 100
PASSTHRU
TABLE CHINOOK.*;
start P01ORA1
www.LamkoDB.com
www.MonitorMyDatabase.com 17

Initial load of data
•
•
•
•
•
•
•
Export/Import
Data pump
RMAN
Load data over a DB link
bcp
OGG utilities
many more …
www.LamkoDB.com
www.MonitorMyDatabase.com 18

Replicat
• Replicat process R01ORA1 (SQL Server 2012) reads the remote
trail file contents and applies rows to the database
www.LamkoDB.com
www.MonitorMyDatabase.com 19
REPLICAT
R01ORA1.obey
DBLOGIN SOURCEDB GGATE, USERID ggate, PASSWORD abc
add replicat R01ORA1, exttrail C:\gg\dirdat\MMDBORA1\p1 params
C:\gg\dirprm\CHINOOK\R01ORA1.prm
R01ORA1.prm
REPLICAT R01ORA1
SOURCEDB GGATE, USERID ggate, PASSWORD abc
. . .
MAP CHINOOK.ALBUM,
TARGET dbo.ALBUM;
MAP CHINOOK.ARTIST,
TARGET dbo.ARTIST;
MAP CHINOOK.CUSTOMER,
TARGET dbo.CUSTOMER;
MAP CHINOOK.TRACK,
TARGET dbo.TRACK;
start R01ORA1
www.LamkoDB.com
www.MonitorMyDatabase.com 20

Replication flow complete
www.LamkoDB.com
www.MonitorMyDatabase.com 21

ggsci command line interface
•
•
•
•
•
•
•
•
•
info all
info * or info E*, etc
info E01ORA1
info E01ORA1 detail
start E01ORA1
stop E01ORA1
stats E01ORA1
send E01ORA1 status
many more …
www.LamkoDB.com
www.MonitorMyDatabase.com 22
ggsci commands
GGSCI (MMDBLNXDB01) 1> info all
Program
Status
Group
Lag at Chkpt
Time Since Chkpt
MANAGER
EXTRACT
EXTRACT
EXTRACT
EXTRACT
REPLICAT
RUNNING
RUNNING
RUNNING
RUNNING
RUNNING
RUNNING
E01ORA1
E02ORA1
P01ORA1
P02ORA1
R01SQL1
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00
00:00:04
00:00:04
00:00:09
00:00:00
00:00:04
GGSCI (MMDBLNXDB01) 2> info E01ORA1
EXTRACT
E01ORA1
Checkpoint Lag
Log Read Checkpoint
Last Started 2014-02-20 15:49
Status RUNNING
00:00:00 (updated 00:00:02 ago)
Oracle Redo Logs
2014-03-13 21:52:56 Seqno 3726, RBA 25596928
SCN 0.127734271 (127734271)
www.LamkoDB.com
www.MonitorMyDatabase.com 23

Network issues causing pump to crash
• Usually restart pump
• Use AUTORESTART in mgr.prm
www.LamkoDB.com
www.MonitorMyDatabase.com 24

Network issues causing pump to crash
• Usually restart pump
• Use AUTORESTART in mgr.prm

Server reboot and processes not starting
• Use AUTOSTART in mgr.prm
www.LamkoDB.com
www.MonitorMyDatabase.com 25

Network issues causing pump to crash
• Usually restart pump
• Use AUTORESTART in mgr.prm

Server reboot and processes not starting
• Use AUTOSTART in mgr.prm

Extract performance bad
• Use dedicated volumes where redo/archived logs are stored
• Have separate volumes for local trail files
• Use multiple extracts
www.LamkoDB.com
www.MonitorMyDatabase.com 26

Network issues causing pump to crash
• Usually restart pump
• Use AUTORESTART in mgr.prm

Server reboot and processes not starting
• Use AUTOSTART in mgr.prm

Extract performance bad
• Use dedicated volumes where redo/archived logs are stored
• Have separate volumes for local trail files
• Use multiple extracts

Pump performance bad
• Compress data when sending data across
• Have separate volumes for local/remote trail files
• Use multiple extracts/pumps
www.LamkoDB.com
www.MonitorMyDatabase.com 27

Replicat performance bad
•
•
•
•
Check for tables with missing PKs
Use batchsql in prm file of replicat
Have separate volumes for remote trail files
Use multiple replicats
www.LamkoDB.com
www.MonitorMyDatabase.com 28

Replicat performance bad
•
•
•
•

Check for tables with missing PKs
Use batchsql in prm file of replicat
Have separate volumes for remote trail files
Use multiple replicats
Disk full for local and/or remote trail files
• Occasionally need to setup replication again, with or without
initial load
• Monitor disk space usage by storage group or sysadmins
• Have separate volumes for local and remote trail files
• Use PURGEOLDEXTRACTS in mgr.prm
www.LamkoDB.com
www.MonitorMyDatabase.com 29

Crashing replicats when tables added/changed
• Add/modify tables on target database, possibly update data
definition file, and restart replicat
• Make extract process stop when it notices DDL
www.LamkoDB.com
www.MonitorMyDatabase.com 30

Crashing replicats when tables added/changed
• Add/modify tables on target database, possibly update data
definition file, and restart replicat
• Make extract process stop when it notices DDL

Extract process does not stop
• Commit open transactions in DB
• Kill session having open transaction
• Force stop of Extract process if not many archived logs to re-read
at next Extract startup
www.LamkoDB.com
www.MonitorMyDatabase.com 31

Crashing replicats when tables added/changed
• Add/modify tables on target database, possibly update data
definition file, and restart replicat
• Make extract process stop when it notices DDL

Extract process does not stop
• Commit open transactions in DB
• Kill session having open transaction
• Force stop of Extract process if not many archived logs to re-read
at next Extract startup

Crashing replicats because of manual data changes in
target database
• Do more manual changes to fix the data
• Possibly reload affected tables
• Educate users
www.LamkoDB.com
www.MonitorMyDatabase.com 32

Where to look for information
•
•
•
•

Report file of process (view report <process>)
Discard file
OGG Error log
Oracle alert log / SQL Server ERRORLOG
Compare source and target data after fixing issue
•
•
•
•
Veridata
select data from source table MINUS select data from target table
Oracle DBMS_COMPARISON
Other tools (TOAD, …)
www.LamkoDB.com
www.MonitorMyDatabase.com 33

Setup Heartbeat in replication flow
• Document ID: 1299679.1
OGG_Best_Practice_-_heartbeat_table_using_DBMS_SCHEDULER-_V11_0_ID1299679.1.pdf
• Update heartbeat record in source database (every minute)
• Extract picks up record in source database, adds information
and write to local trail file
• Pump picks up record, adds information and write to remote
trail file
• Replicat picks up record, adds information and writes to target
database (as an insert)
www.LamkoDB.com
www.MonitorMyDatabase.com 34

Monitor using Oracle tool (Director)
• Need to install Java agent in every GG instance & maintain bunch
of servers to keep tool running (Weblogic, …)

(Remote) Monitor using own developed tools
• Monitor status of processes
• Monitor number of rows replicated
• Report on Heartbeat data
• Queries against heartbeat table in target database
• Graphs generated based on data in heartbeat table (lag times)
• ggsci-like GG Dashboard for all GG instances monitored
www.LamkoDB.com
www.MonitorMyDatabase.com 35

OGG Management Pack
www.LamkoDB.com
www.MonitorMyDatabase.com 36

LamkoDB Monitoring
www.LamkoDB.com
www.MonitorMyDatabase.com 37

Historical stats for Extract process
www.LamkoDB.com
www.MonitorMyDatabase.com 38

Lag times for Extract/Pump/Replicat
www.LamkoDB.com
www.MonitorMyDatabase.com 39

GG Dashboard
www.LamkoDB.com
www.MonitorMyDatabase.com 40

GG process detail information
www.LamkoDB.com
www.MonitorMyDatabase.com 41

GG process relationships
www.LamkoDB.com
www.MonitorMyDatabase.com 42

Who supports GoldenGate?
• DBA
• Middleware group
www.LamkoDB.com
www.MonitorMyDatabase.com 43

Who supports GoldenGate?
• DBA
• Middleware group

What to do about table structure changes
• software releases need to be evaluated/tested/coordinated
www.LamkoDB.com
www.MonitorMyDatabase.com 44

Who supports GoldenGate?
• DBA
• Middleware group

What to do about table structure changes
• software releases need to be evaluated/tested/coordinated

What do developers, support, clients know about OGG?
• Educate personnel
www.LamkoDB.com
www.MonitorMyDatabase.com 45

Who supports GoldenGate?
• DBA
• Middleware group

What to do about table structure changes
• software releases need to be evaluated/tested/coordinated

What do developers, support, clients know about OGG?
• Educate personnel

Monitor what is running and what is broken
• Have proper monitoring in place with alerting
www.LamkoDB.com
www.MonitorMyDatabase.com 46

Who supports GoldenGate?
• DBA
• Middleware group

What to do about table structure changes
• software releases need to be evaluated/tested/coordinated

What do developers, support, clients know about OGG?
• Educate personnel

Monitor what is running and what is broken
• Have proper monitoring in place with alerting

Keeping track of new, existing and changing replication
flows. Number of processes quickly becomes a
spaghetti bowl.
• Have automated tool to show relationships
www.LamkoDB.com
www.MonitorMyDatabase.com 47

Questions?
Jos van Lammeren
[email protected]
www.LamkoDB.com
www.MonitorMyDatabase.com 48