=========================
General
=========================
Useful reference for Oracle Golden Gate.
=========================
Short reference
=========================
Golden Gate has these Processes:
Extract
AKA CAPTURE Process
The Extract process is the extraction (capture) data from source system.
Extract runs on the source system.
Extract can be either Initial Load or Ongoing.
For Initial Load, Extract extracts (captures) a current, static set of data directly from their source objects, i.e. from Tables.
For Ongoing, Extract captures DML and DDL operations after the initial synchronization has taken place, i.e. from Database recovery logs or transaction logs (such as the Oracle redo logs or SQL/MX audit trails).
Replicat
AKA APPLY Process
The Replicat process is Running on the Target Database.
The Goldengate Replicat process duty is to apply the changes to Target Database.
It reads the data from a file known an EXTTRAIL file which contains data changes made on the source.
In addition, Replicat also posts its exact location of the trail file while processing records.
This is used for recovery from crashes, allowing it to start from the exact point where it left off, without any data loss.
=========================
How to check OGG version:
=========================
When running ggcgi versions command, the version displayed is actually the version of the database.
OGG version, is displayed in the header of login info.
/software/ogg/1212/ggcgi
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (my_server) 1> versions
Operating System:
Linux
Version #1 SMP Sun Nov 10 22:19:54 EST 2013, Release 2.6.32-431.el6.x86_64
Node: esp-tel-1-dbu-2
Machine: x86_64
Database:
ERROR: Not logged into database, use DBLOGIN.
GGSCI (esp-tel-1-dbu-2) 3> DBLOGIN userid ogg password oggpass
Successfully logged into database.
GGSCI (esp-tel-1-dbu-2) 4> VERSIONS
Operating System:
Linux
Version #1 SMP Sun Nov 10 22:19:54 EST 2013, Release 2.6.32-431.el6.x86_64
Node: esp-tel-1-dbu-2
Machine: x86_64
Database:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
=========================
Golden Gate CLI Command Summary:
=========================
/software/ogg/1212/ggcgi
GGSCI Command Summary:
Object: Command:
SUBDIRS CREATE
ER INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP
EXTRACT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND,
START, STATS, STATUS, STOP
EXTTRAIL ADD, ALTER, DELETE, INFO
GGSEVT VIEW
MANAGER INFO, REFRESH, SEND, START, STOP, STATUS
MARKER INFO
PARAMS EDIT, VIEW
REPLICAT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND, START, STATS, STATUS, STOP
REPORT VIEW
RMTTRAIL ADD, ALTER, DELETE, INFO
TRACETABLE ADD, DELETE, INFO
TRANDATA ADD, DELETE, INFO
CHECKPOINTTABLE ADD, DELETE, CLEANUP, INFO
=========================
Golden Gate CLI
=========================
To see open Golden Gate CLI:
su - oracle
cd $OGG_HOME
./ggsci
=========================
See Info about Extract:
=========================
GGSCI (my_server) 2> INFO EXTRACT DPM_I_03
EXTRACT DPM_I_03 Last Started 2016-12-12 15:08 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:22:07 ago)
Log Read Checkpoint File /software/ogg/1212/dirdat/03/out/ei000000
First Record RBA 0
INFO EXTRACT DPM_I_03 SHOWCH
INFO EXTRACT DPM_I_03 DETAIL
GGSCI (my_server) 4> INFO EXTRACT DPM_I_03 DETAIL
EXTRACT DPM_I_03 Last Started 2016-12-12 15:11 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:24:35 ago)
Log Read Checkpoint File /software/ogg/1212/dirdat/03/out/ei000000
First Record RBA 0
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/software/ogg/1212/dirdat/03/in/ei 0 0 100 EXTTRAIL
Extract Source Begin End
/software/ogg/1212/dirdat/03/out/ei000000 * Initialized * First Record
/software/ogg/1212/dirdat/03/out/ei000000 * Initialized * First Record
/software/ogg/1212/dirdat/03/out/ei000000 * Initialized * First Record
/software/ogg/1212/dirdat/03/out/ei000000 * Initialized * First Record
/software/ogg/1212/dirdat/03/out/ei000000 * Initialized * First Record
/software/ogg/1212/dirdat/03/out/ei000000 * Initialized * First Record
/software/ogg/1212/dirdat/03/out/ei000000 * Initialized * First Record
/software/ogg/1212/dirdat/03/out/ei000000 * Initialized * First Record
/software/ogg/1212/dirdat/03/out/ei000000 * Initialized * First Record
/software/ogg/1212/dirdat/03/out/ei000000 * Initialized * First Record
Current directory /software/ogg/1212
Report file /software/ogg/1212/dirrpt/DPM_I_03.rpt
Parameter file /software/ogg/1212/dirprm/dpm_i_03.prm
Checkpoint file /software/ogg/1212/dirchk/DPM_I_03.cpe
Process file /software/ogg/1212/dirpcs/DPM_I_03.pce
Error log /software/ogg/1212/ggserr.log
=========================
Data Dictionary
=========================
DBA_CAPTURE
- capture_name (OGG$CAP_EXT_I_03)
- status (ENABLED/DISABLED)
- client_name (EXT_I_03)
- client_status (ATTACHED/DETACHED)
DBA_CAPTURE_PARAMETERS
~70 paramaters per each capture_name (OGG$CAP_EXT_I_03)
DBA_CAPTURE_EXTRA_ATTRIBUTES
~5 additional parameters per each capture_name (OGG$CAP_EXT_I_03)
DBA_CAPTURE_PREPARED_DATABASE
The smallest timestamp when the local database was prepared for instantiation.
DBA_CAPTURE_PREPARED_SCHEMAS
A list of SCHEMA and their smallest scn and timestamp
DBA_CAPTURE_PREPARED_TABLES
A list of SCHEMA.TABLE and their smallest scn and timestamp
DBA_HIST_STREAMS_CAPTURE
History runs, each entry for one snap_id.
V$STREAMS_CAPTURE
Session related information (sid, serial#) for OGG capture_name.
=========================
Golden gate health checks
=========================
What to check:
- Extract parameter files from $OGG_HOME/dirprm/<extract name>.prm
- *.rpt files from $OGG_HOME/dirrpt/<extract name>.rpt)
- ggserr.log: GoldenGate Error Log
- Oracle GoldenGate healthcheck report for your database release.
This is extracted by following technote:
GoldenGate Integrated Capture and Integrated Replicat Healthcheck Script (Doc ID 1448324.1)
- "Output of the Oracle GoldenGate OS Info Script"
- in case of a core file, stack_xxx.txt" containing the stack trace from the failing process
=========================
Golden gate spool output
=========================
Golden Gate does not have spool option.
To spool output to a file, need to use Linux script command.
script /to/some/path/my_log.log
ggsci
OGGSCI >info extract *. detail
OGGSCI >exit
exit
=========================
Golden gate Parameter files
=========================
Parameter files are located under /software/ogg/1212/dirprm/
oracle@my_server:/software/ogg/1212/dirprm>% less ext_p_15.prm
EXTRACT ext_p_15
setenv (ORACLE_SID="igt")
setenv (ORACLE_HOME="/software/oracle/112")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID ogg, PASSWORD AACAAAAAAAAAAAIAZEDCPHICXGPEQCED ENCRYPTKEY DEFAULT
EXTTRAIL /software/ogg/1212/dirdat/15/out/ep
CACHEMGR CACHESIZE 256M
-- Bi-direction replication
TRANLOGOPTIONS EXCLUDEUSER ogg
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 256)
include ./dirprm/PROVtables_15.inc
oracle@my_server:/software/ogg/1212/dirprm>% less ext_i_15.prm
EXTRACT ext_i_15
setenv (ORACLE_SID="igt")
setenv (ORACLE_HOME="/software/oracle/112")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID ogg, PASSWORD AACAAAAAAAAAAAIAZEDCPHICXGPEQCED ENCRYPTKEY DEFAULT
EXTTRAIL /software/ogg/1212/dirdat/15/out/ei
CACHEMGR CACHESIZE 256M
-- Bi-direction replication
TRANLOGOPTIONS EXCLUDEUSER ogg
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 256)
--TRANLOGOPTIONS EXCLUDETAG 0180
include ./dirprm/SFItables_15.inc
oracle@esp-tel-1-dbu-2:/software/ogg/1212/dirprm>% less dpm_i_15.prm
EXTRACT dpm_i_15
setenv (ORACLE_SID="igt")
setenv (ORACLE_HOME="/software/oracle/112")
setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
USERID ogg, PASSWORD AACAAAAAAAAAAAIAZEDCPHICXGPEQCED ENCRYPTKEY DEFAULT
RMTHOST esp-tel-2-ora-1, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL /software/ogg/1212/dirdat/15/in/ei
PASSTHRU
TABLE USER_A.*;
dpm_i_15.prm (END)
=================
rep_X_99.prm
=================
oracle@esp-tel-1-dbu-2:/software/ogg/1212/dirprm>% less rep_p_12.prm
REPLICAT rep_p_12
setenv (ORACLE_SID="igt")
setenv (ORACLE_HOME="/software/oracle/112")
setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
ASSUMETARGETDEFS
USERID ogg, PASSWORD AACAAAAAAAAAAAIAZEDCPHICXGPEQCED ENCRYPTKEY DEFAULT
REPERROR (-1, IGNORE)
CACHEMGR CACHESIZE 256M
DISCARDFILE /software/ogg/1212/dirdat/12/in/disc12p.txt, append,
include ./dirprm/mapPROVtables_12.inc
To reload mgr.prm without restart to ggsci
GGSCI (qanfv-1-dbs-1b) 2> view param mgr
PORT 7809
SYSLOG NONE
AUTOSTART EXTRACT *
AUTOSTART REPLICAT *
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60
AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60
DOWNREPORTMINUTES 5
LAGCRITICALMINUTES 5
purgeoldextracts /software/ogg/191/dirdat/01/in/*, USECHECKPOINTS, MINKEEPHOURS
3
purgeoldextracts /software/ogg/191/dirdat/01/out/*, USECHECKPOINTS, MINKEEPHOURS
4
GGSCI (qanfv-1-dbs-1b) 3> refresh mgr
Sending REFRESH request to MANAGER ...
Mgr Params Updated
Oracle Support: Oracle GoldenGate Subdirectories
Burlson: GoldenGate directory structure tips
Start, Stop, Report, Altering Extract
dirdat is Huge (400Gb)
$OGG_HOME/ is 100% full
The reason is $OGG_HOME/dirdat/01/in/ and $OGG_HOME/dirdat/01/out/ got huge and old es<SEQ> files.
SEND MANAGER PURGEOLDEXTRACTS does not work.
After manually deleting old files (more than 1 month old, the REPLICAT processes are in ABENDED state.
Now do STOP EXTRACT XXX and CLEANUP EXTRACT XXX
Use CLEANUP EXTRACT to delete run history for the specified Extract group. The cleanup keeps the last run record intact so that Extract can resume processing from where it left off. Before using this command, stop Extract by issuing the STOP EXTRACT command.
Oracle GoldenGate error log
ggserr.log
=========================
Golden Gate File Structure
=========================
Use the GGSCI SHOW command to display the instance directory structure.
GGSCI (esp-tel-1-dbu-2) 1> show
Parameter settings:
SET SUBDIRS ON
SET DEBUG OFF
Current directory: /software/ogg/1212
Using subdirectories for all process files
Editor: vi
Reports (.rpt) /software/ogg/1212/dirrpt
Parameters (.prm) /software/ogg/1212/dirprm
Replicat Checkpoints (.cpr) /software/ogg/1212/dirchk
Extract Checkpoints (.cpe) /software/ogg/1212/dirchk
Process Status (.pcs) /software/ogg/1212/dirpcs
SQL Scripts (.sql) /software/ogg/1212/dirsql
Database Definitions (.def) /software/ogg/1212/dirdef
Dump files (.dmp) /software/ogg/1212/dirdmp
Masterkey wallet files (.wlt) /software/ogg/1212/dirwlt
Credential store files (.crd) /software/ogg/1212/dircrd
dirprm directory
This directory is the default location for parameter files associated with group names.
When an object created such as extract, data pump extract and replicat, a parameter file is looked at for the group name.
Using non-default location is referenced by the parameter PARAMS <parameter file>. Regardless of the parameter file location, the parameter file name and the group name must match.
dirrpt directory
This directory is the default location for reports and discarded records.
Whenever a process ABENDED, a report file is created on this directory.
The report file indicates the reasons for failure, and if the failure is database related, it also indicate the Oracle error code.
Using non-default location is referenced by the parameter REPORT <report file>.
The Goldengate dirchk directory
This directory is for storing extract, data pump and replicat checkpoint files.
Depending on the object type, a file is created using the group name with proper a relevant extension.
For extract process, the checkpoint file name is <group name>.cpe.
For replicat process, the checkpoint file name is <group name>.cpr.
dirpcs directory
This directory stores Oracle GoldenGate process identifier (PID).
It's the PID assigned by the operating system to running program, which is associated with an Oracle GoldenGate process.
To obtain more details, use the operating system command ps -edf | grep <group name>.
dirsql directory
This directory is default location to store SQL Script used by Oracle GoldenGate.
dirdef directory
This directory is used to store definition files.
Definition files are required when the source and the target tables are not identical.
Mostly used for heterogeneous environment when mapping tables across dissimilar platforms.
dirdmp directory
Golden Gate process dump files
Below command delete dmp files older than 30 days:
find /software/ogg/1212/dirdmp/ -type f -name "*.dmp" -mtime +30 -exec rm {} \;
dirdat directory
This directory is used to start trail files.
The trail files are identified by a two character prefix, followed by 6 digits.
An extract or replicat processes trail files are designated by the parameter EXTTRAIL followed by the trial file location and name.
For example, EXTTRAIL ./dirdat/sa.
dirtmp directory
This directory is the default location for Oracle GoldenGate temporary files due to capturing large transactions that goes beyond default cache size used by the extract process.
The location and size is controlled by the CACHEMGR sub-parameters CACHESIZE, CACHEDIRECTORY and CACHEPAGEOUTSIZE.
Also, it's always recommended to dedicate a directory location to host the temporary files, avoiding contention with other type of files.
=========================
References
=========================
Introduction
Nice Tutorial
GoldenGate Reference for Oracle GoldenGate for Windows and UNIX
Administering Oracle GoldenGate for Windows and UNIX
Oracle GoldenGate - Basic Configuration
