☎ +91 965 246 5533  +1 512 808 5399 ✉ [email protected]  www.netsoftmate.

com

ORACLE GOLDENGATE POCKET GUIDE

DATABASES | CLOUD | MSP| ENGINEERED SYSTEMS | REPLICATION | CONSULTING | INFRASTRUCTURE | SECURITY

Oracle GoldenGate Processes Oracle GoldenGate Architecture

Manager Network
It is required for Stop and Start GoldenGate Extract, Pump and Replicat processes
Mgr Mgr
Source Target
Extract DB DB
It reads database from online redo log files and optionally from archive log files and write
data to local or remote trail files Extract Pump Colle Replicat
Ext ctor

Data Pump Extract

It reads data from local trail file and writes data to remote trail over the TCP/IP network.
It is an optional process but highly recommended

Collector This diagram explains the logical architecture of an Oracle GoldenGate online replication.
It receives data from Pump Extract and writes data to remote trail files
GoldenGate Topologies
Replicat Oracle GoldenGate modular architecture provides the ability to extract DML & DDL
It reads data from remote trail files and write to the database one transaction at a time changes across variety of Topologies.

Trails and Files

Trails files are required for continuous data replication. These files are canonical
structured files which only GoldenGate process can read

Checkpoints
It provides the transaction recovery in case GoldenGate processes stopped abnormally.

Wallet
It is a secure common storage for User IDs and Passwords. It is highly recommended to
use wallet for secure credentials

Oracle GoldenGate Utilities Oracle GoldenGate 11g/12c Installation

GGSCI Download latest Oracle GoldenGate 11g/12c from https://support.oracle.com
GoldenGate Software Command Line Interface (GGSCI) is used to add, stop, start and - Oracle GoldenGate 11g Installation
manage GoldenGate Processes. Login as OGG software owner
$ cd /ggs/home
DEFGEN $ $ p18918679_1121017_Linux-x86-64.zip
This utility produces file containing a definition of the layouts of the source tables $ tar -xvof *.tar
$ ./ggsci
LOGDUMP GGSCI> create subdirs
It enables you to search or display information stored in the GoldenGate trails and file GGSCI> info all
- Oracle GoldenGate 12c Installation
REVERSE Login as OGG software owner
It reorders operations within GoldenGate trail files in reverse sequence $ unzip p27111516_122022_Linux-x86-64.zip
$ cd fbo_ggs_Linux_x64_shiphome/Disk1/response/
KEYGEN $ cp oggcore.rsp /u01/stage/
It is used to generate random hex keys. Needed only if you do not use wallet $ cd /u01/stage/

INITIAL LOAD Open the response and edit the following parameters
- File to Replicat $ vi oggcore.rsp
Extract process write data to trail files and Replicat loads data using SQL on target DB INSTALL_OPTION=ORA11g
- File to Database Utility SOFTWARE_LOCATION=/u01/app/oracle/product/ogg/12.2.0.22/gghome
Extract process write data to text files and Replicat loads data using DB utility on target START_MANAGER=true
- Direct Load MANAGER_PORT=7809
Extract process gives data directly to Replicat & Replicat loads data using SQL on target DATABASE_LOCATION=/u01/app/oracle/product/12.2.0/dbhome
- Direct Bulk Load INVENTORY_LOCATION=/u01/app/oraInventory
Extract process gives data directly to Replicat & Replicat loads data using DB utility UNIX_GROUP_NAME=oinstall

$ ./runInstaller -silent -responseFile /u01/stage/oggcore.rsp

Netsoftmate IT Solutions Private Limited

#8-2-695/E/4, Plot No. 195, Rock House, First Floor, Road No. 12, Banjara Hills, Hyderabad - 500034, Telangana, India.
 ☎ +91 962 246 5533  +1 512 808 5399 ✉ [email protected]  www.netsoftmate.com

Oracle GoldenGate One-Way Replication Setup – Classic Oracle GoldenGate One-Way Replication Setup – Integrated (12c)
High-levels to configure Oracle GoldenGate one-way replication 11.2.0.4 and above
- Install OGG software on source and Target SQL> alter system set ENABLE_GOLDENGATE_REPLICATION=true;

- Configure OGG Manager process on source and target Create Datastore

GGSCI> Edit params mgr GGSCI> create wallet
PORT 7809 GGSCI> add credentialstore
PURGEOLDEXTRACTS /ggs/home/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2 GGSCI> alter credentialstore add user ggs
GGSCI> info credentialStore
- Enable DB Supplemental logging on GGSCI> dblogin useridalias ggs
SQL> alter database add supplemental log data;
SQL> select supplemental_log_data_min from v$database; GGSCI> add trandata SCOTT.* ALLCOLS

- Add Trandata on tables Integrated Extract

GGSCI> dblogin userid ggs, password xxxxx
GGSCI> Add trandata SCOTT.* SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee =>
'GGUSER');
- Configure Extract & Pump Extract Processes on source
GGSCI> edit params ext $ cd $GGS_HOME
EXTRACT intext $ ./ggsci
USERID ggs, PASSWORD xxxxx GGSCI> EDIT PARAMS intext
EXTTRAIL ./dirdat/et EXTRACT intext
TABLE SCOTT.*; dblogin useridalias ggs
RMTHOST 192.168.10.1, MGRPORT 7809
GGSCI> add extract test, tranlog, begin now TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100)
GGSCI> add exttrail /ggs/home/dirdat/ex2c157a/et, extract ext, megabytes 100 RMTTRAIL ./dirdat/ie
GGSCI> start ext TABLE SCOTT.*;
GGSCI> ADD EXTRACT intext, INTEGRATED TRANLOG, BEGIN NOW
GGSCI> edit params pext GGSCI> dblogin useridalias ggs
EXTRACT pext GGSCI> REGISTER EXTRACT intext, DATABASE
RMTHOST 192.168.10.1, MGRPORT 7809 GGSCI> ADD RMTTRAIL ./dirdat/ie, EXTRACT intext
RMTTRAIL /ggs/home/dirdat/rt GGSCI> start extract intext
PASSTHRU
TABLE SCOTT.*; Login to the source database and query the $GOLDENGATE_CAPTURE view to get the
information about the integrated extract.
GGSCI> add extract pext, exttrailsource /ggs/home/dirdat/et SQL> select sid, serial#,CAPTURE_NAME,STATE, SGA_USED from
GGSCI> add rmttrail /ggs/home/dirdat/rt, extract ptest, megabytes 100 V$GOLDENGATE_CAPTURE;
GGSCI> start pext
Integrated Replicat
- Perform Initial using your desired method (Expdp/impdp, RMAN, and so on)
$ expdp userid = ggs/xxxx flashback_scn = xxxxx directory = EXP_DIR dumpfile = $ cd $GGS_HOME
expdp_src.dmp logfile = expdp_src.log status=60 schemas = SCOTT $ ./ggsci

$ impdp userid = ggs/xxxx directory = IMP_DIR dumpfile = expdp_src.dmp logfile = GGSCI> dblogin useridalias ggs
impdp_tgt.log status=60 remap_schemas = SCOTT:SCOTT GGSCI> add checkpointtable ggs.chkpoint_table
GGSCI> edit params ./GLOBALS
- Create checkpoint table CHECKPOINTTABLE GGS.CHKPOINT_TABLE
$ ./ggsci
GGSCI> exit
GGSCI> dblogin userid ggs, password xxxx
GGSCI> add checkpointtable ggs.chkpoint_table GGSCI> Edit Param rint
GGSCI> edit params ./GLOBALS Replicat rint
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
CHECKPOINTTABLE GGS.CHKPOINT_TABLE assumetargetdefs
discardfile ./dirrpt/rint.dsc, Purge
GGSCI> exit dblogin useridalias ggs
Map scott.*, target scott.*;
- Configure Replicat on Target
GGSCI> edit params rep GGSCI> dblogin useridalias ggs
REPLICAT REP GGSCI> add Replicat rint Integrated exttrail ./dirdat/ie
USERID ggs, PASSWORD xxxxxx GGSCI> Start Replicat rint
ASSUMTARGETDEFS GGSCI> info all
MAP SCOTT.*, TARGET SCOTT.*;
SQL> connect / as sysdba
GGSCI> dblogin userid ggs, password xxxxx SQL> column replicat_name format a30
GGSCI> add replicat rep, exttrail /ggs/home/dirdat/rt SQL> column server_name format a30
GGSCI> start replicat SQL> select replicat_name,server_name from DBA_GOLDENGATE_INBOUND;
GGSCI> info rep

Netsoftmate IT Solutions Private Limited

#8-2-695/E/4, Plot No. 195, Rock House, First Floor, Road No. 12, Banjara Hills, Hyderabad - 500034, Telangana, India.
 ☎ +91 962 246 5533  +1 512 808 5399 ✉ [email protected]  www.netsoftmate.com

Oracle GoldenGate Patching (12c) Oracle GoldenGate Upgrade (12c)

It is assumed that base OGG software version 12.2.0.1.0 or 12.2.0.1.1 or 12.1.2.1.0 or It is assumed that we are upgrade OGG from 12.2.0.1.0 to 12.2.0.1.1
12.1.2.1.1 is already installed.
Steps:
Steps: Download and unzip patch in staging area
Download and unzip patch in staging area $ cd /stage
$ cd /stage $ unzip *.zip  unzip patch
$ unzip *.zip  unzip patch
$ cd $GG_HOME
$ cd $GG_HOME $ ./ggsci
$ ./ggsci GGSCI> stop er *
GGSCI> version GGSCI> stop mgr!

$ export ORACLE_HOME=/u01/app/oracle/product/ogg/12.2.0.1.1/ogghome1 $ cd /u01/app/oracle/product/ogg/12.2/ogghome1

$ export PATH=$PATH:/u01/app/oracle/product/12.2.0/orcl/OPatch $ cd ..
$ opatch version $ cp -pR ogghome1 ogghome1_bkp
$ opatch lsinventory
$ cd /stage/xxx/Disk1
$ ./ggsci $ ./runInstaller  follow the on screen details to complete the installation
GGSCI> STOP ER *
GGSCI> STOP MANAGER $ cd $GG_HOME
$ cd /stage/<patch_number> $ ./ggsci -v

$ opatch apply $ cd /u01/app/oracle/product/ogg/12.2/ogghome1_bkp

cp -pR dir* $GG_HOME
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/orcl
$ ./ggsci
$ ./ggsci GGSCI> START MANAGER
GGSCI> START MANAGER GGSCI> START ER *
GGSCI> START ER *

Oracle GoldenGate Initial Load Methods

Direct Load Method File to Replicat Method

- Create initial data load extract - Create initial data load extract
$ cd $GG_HOME $ cd $GG_HOME
$ ./ggsci $ ./ggsci
GGSCI> ADD EXTRACT ELOAD, SOURCEISTABLE
GGSCI> INFO EXTRACT ELOAD, TASKS GGSCI> Edit Param eload
SOURCEISTABLE
GGSCI> EDIT PARAMS ELOAD USERID ggs, PASSWORD ggs
EXTRACT ELOAD RMTHOST 10.10.10.2, MGRPORT 7809
USERID ggs, PASSWORD ggs -- Below rmtfile command will create 50 files of size 2GB each on target server
RMTHOST 10.10.10.2, MGRPORT 7809 RMTFILE ./dirdat/el, maxfiles 50 MEGABYTES 2048
RMTTASK REPLICAT, GROUP RLOAD TABLE SCOTT.*;
TABLE SCOTT.*; GGSCI> exit

GGSCI> ADD EXTRACT ELOAD, SOURCEISTABLE $ ./extract paramfile dirprm/eload.prm reportfile dirrpt/eload.rpt
- Create initial data Replicat process on target $ ps -ef|grep eload
$ cd $GG_HOME $ cat dirrpt/eload.rpt | more
$ ./ggsci - Create initial data Replicat process on target
GGSCI> ADD REPLICAT RLOAD, SPECIALRUN $ cd $GG_HOME
GGSCI> INFO RLOAD, TASKS $ ./ggsci
GGSCI> add rep rload, exttrail ./dirdat/el, nodbcheckpoint
GGSCI> EDIT PARAMS RLOAD
REPLICAT RLOAD GGSCI> EDIT PARAMS rload
USERID ggs, Password ggs Replicat RLOAD
DISCARDFILE ./dirdat/rustarc.DSC, PURGE USERID ggs, Password ggs
BATCHSQL DISCARDFILE ./dirdat/rustarc.DSC, PURGE
MAP SCOTT.*, TARGET SCOTT.*; DBOPTIONS SUPPRESSTRIGGERS
DBOPTIONS USEREPLICATIONUSER
GGSCI> ADD REPLICAT RLOAD, SPECIALRUN BATCHSQL
MAP SCOTT.*, TARGET SCOTT.*;
- Start Initial Load on source only
GGSCI> start eload GGSCI> start replicat rload
GGSCI> info eload
GGSCI> info rload  check on target $ ps -ef|grep rload
$ cat dirrpt/rload.rpt | more

Netsoftmate IT Solutions Private Limited

#8-2-695/E/4, Plot No. 195, Rock House, First Floor, Road No. 12, Banjara Hills, Hyderabad - 500034, Telangana, India.
 ☎ +91 962 246 5533  +1 512 808 5399 ✉ [email protected]  www.netsoftmate.com

Useful Oracle GoldenGate Parameter files

Manager Parameter File Data Pump Extract Parameter File
PORT 7809 EXTRACT PUMP_TEST
DYNAMICPORTLIST 7810-7830 PASSTHRU
USERID ggs, PASSWORD abcbd!@#$1234, & ENCRYPTKEY DEFAULT RMTHOST 192.168.10.1, MGRPORT 7809, COMPRESS, TCPBUFSIZE 900000
PURGEOLDEXTRACTS /ggs/home/dirdat/* , USECHECKPOINTS, MINKEEPHOURS 48 TCPFLUSHBYTES 900000
AUTOSTART ER * EOFDELAYCSECS 10
AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 720 FLUSHCSECS 10
DOWNREPORTMINUTES 15 RMTTRAIL /ggs/home/dirdat/rt
DOWNCRITICAL TABLE SCOTT.*;
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0 Replicat Parameter File
LAGREPORTMINUTES 15 REPLICAT REP_TEST
SETENV (ORACLE_HOME = "/u01/app/oracle/product/12.0.1/db")
Extract Parameter File SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
EXTRACT EXT_TEST USERID ggs, PASSWORD "abcd!@#$1234", & ENCRYPTKEY DEFAULT
SETENV (ORACLE_HOME = "/u01/app/oracle/product/12.0.1/db") ASSUMETARGETDEFS
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") DISCARDFILE /ggs/home/dirrpt/rep_test.dsc, megabytes 100 append
USERID ggs, PASSWORD "abcd!@#$1234", & ENCRYPTKEY DEFAULT DISCARDROLLOVER AT 01:00 ON SUNDAY
EXTTRAIL /ggs/home/dirdat/et DBOPTIONS DEFERREFCONST
DISCARDFILE /ggs/home/dirrpt/ext_test.dsc, megabytes 100 append BATCHSQL
DISCARDROLLOVER AT 02:00 ON SUNDAY GROUPTRANSOPS 2000
REPORTCOUNT EVERY 10000 RECORDS, RATE EOFDELAYCSECS 10
STATOPTIONS REPORTFETCH APPLYNOOPUPDATES
STATOPTIONS RESETREPORTSTATS REPORTCOUNT EVERY 100000 RECORDS, RATE
REPORT ON SUNDAY AT 01:55 REPORT ON SUNDAY AT 00:55
REPORTROLLOVER AT 01:00 ON SUNDAY REPORTROLLOVER AT 01:00 ON SUNDAY
TRANLOGOPTIONS EXCLUDEUSER ggs STATOPTIONS RESETREPORTSTATS
GETUPDATEBEFORES REPERROR (DEFAULT, EXCEPTION)
DDL INCLUDE MAPPED OBJNAME SCOTT.* REPERROR (DEFAULT2, ABEND)
DBOPTIONS ALLOWUNUSEDCOLUMN REPERROR (-1, DISCARD)
EOFDELAYCSECS 10 REPERROR (1403, IGNORE)
TABLE SCOTT.*; DDL INCLUDE MAPPED
TABLEEXCLUDE SCOTT.TEST MAP SCOTT.*, TARGET SCOTT.*;
SEQUENCE SCOTT.*; MAPEXCLUDE SCOTT.TEST

Oracle GoldenGate Monitoring, Troubleshooting and Tuning

Monitoring and Troubleshooting Tuning
GGSCI> info all $ view ./dirrpt/einit.dsc dynamicresolution
GGSCI> info mgr Use this parameter to add a table’s attributes to the object record the first time its
GGSCI> info extract ext showsyntax object ID enter the transaction log
GGSCI> info extract pump Shows Replicat SQL Statement wildcardresolve
GGSCI> info replicat rep checkparams Use this parameter if you are using wildcard names
Use to verify syntax flushsecs
GGSCI> info extract ext, detail Control the point at which Extract Flushes data from buffer to target
GGSCI> info extract pump, detail logdump: It enables you to search or tcpflushbytes
GGSCI> info replicat rep, detail display information stored in the Control the point at which Extract Flushes data from buffer to target
GoldenGate trails and file tcpbufsize
GGSCI> send extract ext, status Controls the size of the TCP socket buffer
GGSCI> send extract pump, status $ logdump eofdelay
GGSCI> send replicat rep, status Logdump> ghdr on Control how often Extract reads the transaction log or trail file
Logdump> detail on eofdelaycsecs
GGSCI> stats extract ext Logdump> detail data Control how often Extract reads the transaction log or trail file
GGSCI> stats replicat rep Logdump> reclen 200 checkpointsecs
Logdump> POS 0 Adjust checkpoint for Exadata & Replicat
GGSCI> status extract * Logdump> n grouptransops
GGSCI> status replicat * Controls how many operations are grouped into one transaction
autostart
GGSCI> info extract int, showch Control Auto start of the process when manager process starts
GGSCI> info replicat rint, showch bootdelayminutes
Delays Manager start of processing activities on Windows system
GGSCI> lag extract ext passthru
GGSCI> lag replicat rep Use this in Pump process if no conversion or filtering is used
batchsql
GGSCI> view report eint batches similar SQL statement into arrays
GGSCI> view report rep maxtransops
To split large transaction into smaller ones on the target
GGSCI> view GGSEVT maxsqlstatements : Replicat maintains cursors for caching SQL statement

Netsoftmate IT Solutions Private Limited

#8-2-695/E/4, Plot No. 195, Rock House, First Floor, Road No. 12, Banjara Hills, Hyderabad - 500034, Telangana, India.