Oracle Goldengate

References

Execute the following DDLs and permissions inside the Oracle database

SQL> alter database add supplemental log data;
SQL> alter system switch logfile;
SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, FORCE_LOGGING from v$database;
SQL> selet supplemental_log_data_min from v$database;

SQL> archive log list;
SQL> select log_mode from v$database;

SQL> -- create user ggs identified by ggs default tablespace users temporary tablespace temp;
SQL> -- grant create session, connect, resource, unlimited tablespace to ggs;
SQL> -- grant execute on utl_file to ggs;
SQL> -- grant select any dictionary, select any table to ggs;
SQL> -- grant select on dba_clusters to ggs;
SQL> -- grant alter any table to ggs;
SQL> -- grant flashback any table to ggs;
SQL> -- grant execute on DBMS_FLASHBACK to ggs;
SQL> -- grant select any transaction to ggs;
SQL> -- alter system to ggs;
SQL> -- EXEC DBMS_GoldenGate_AUTH.GRANT_ADMIN_PRIVILEGE('ggs');
SQL> -- GRANT SELECT ON SYS.V_$DATABASE TO ggs;

SQL> -- grant insert anytable to ggs;
SQL> -- grant delete any table to ggs;
SQL> -- grant update any table to ggs;

SQL> -- or just  grant dba to ggs;

SQL> purge dba_recyclebin;
SQL> alter system set recyclebin=off

Install Oracle Golden Gate

-- Install Oracle goldengate is just unzip and untar the software 
unzip xxx.tar.zip or gunzip xxx.tar.gz  or gzip -d xxx.tar.gz
tar -xovf xxx.tar

Create GoldenGate Default Sub Directories

ggsci
GGSCI> create subdirs
GGSCI> exit

Help and Informational Commands

GGSCI> HELP
GGSCI> HELP ALL
GGSCI> HELP ADD EXTRACT
GGSCI> HELP ADD EXITRAIL

GGSCI> set editor emacs
GGSCI> set editor vi

GGSCI> HISTORY

-- View the GoldenGate application log (view GGS Event )
-- Oracle GoldenGate error log (ggserr.log file)
GGSCI> VIEW GGSEVT

-- View a brief informational summary of all processes
GGSCI> -- INFO ALL [TASKS | ALLPROCESSES]
GGSCI> INFO ALL
GGSCI> info all tasks 
GGSCI> info all allprocesses

GGSCI> status extract initext
GGSCI> INFO EXTRACT INITEXT
GGSCI> INFO EXTRACT INITEXT,DETAIL
GGSCI> info extract extora, showch,detail,tasks,allprocesses
GGSCI> info extract initext,showch,detail,tasks,allprocesses

GGSCI> VIEW REPORT INITEXT

GGSCI> send extract extora, report
GGSCI> view report extora

GGSCI> stats <group>
GGSCI> stats <group>, TABLE *, TOTALSONLY *

GGSCI> send extract extora, rollover

Basic Setup 1

Shell> cd <install location>
Shell> ggsci
GGSCI> EDIT PARAMS MGR

-- GoldenGate Manager parameter file
-- PORT <port>
PORT 7809 

-- Start the Manager
GGSCI> START MGR

-- Verify that the Manager has started
GGSCI> INFO MGR
-- MGR and MANAGER interchangable
GGSCI> INFO MANAGER

-- Add Supplemental logging in GoldenGate

GGSCI> DBLOGIN USERID <login>, PASSWORD <password>
GGSCI> list tables *
GGSCI> -- dblogin userid myusername, password myusername_pass
GGSCI> ADD TRANDATA <owner/schema>.TCUSTMER
GGSCI> ADD TRANDATA <owner/schema>.TCUSTORD

-- Verify that supplemental logging has been turned on for these tables
GGSCI> INFO TRANDATA <owner/schema>.TCUST*
GGSCI> -- info trandata *

Prepare Target environment
GGSCI> EDIT PARAMS MGR

-- GoldenGate Manager Parameter File
-- PORT <port>
PORT 7809 

GGSCI> START MANAGER
GGSCI> INFO MANAGER

Initial Data Load using Direct Load Method
1): Inital data Capture

-- Add the initial load capture batch task group
-- on the <source> system, to create an Extract named EINI<unique id>
GGSCI> ADD EXTRACT EINI<unique id>, SOURCEISTABLE

-- Verify the result:
GGSCI> INFO EXTRACT *, TASKS

2): Configure the initial load capture parameter file
-- on the <source> system
GGSCI> EDIT PARAMS EINI<unique id>

--file content inside the text editor
--
-- GoldenGate Inital Data Capture
-- for TCUSTMER and TCUSTORD
-- 
EXTRACT EINI<unique id>
USERID <userid>, PASSWORD "<password>"
RMTHOST <target>, MGRPORT <port>
RMTTASK REPLICAT, GROUP RINIT<unique id>
TABLE <owner/schema<.TCUSTMER;
TABLE <owner/schema<.TCUSTORD;

-- here E for Extract, R for Replicat,  INIT for initial data load, ORA for Oracle database

3): Initial data delivery, Add the inital load delivery batch task
-- Execute the following commands on the <target> system.
GGSCI> ADD REPLICAT RINI<unique id>, SPECIALRUN

-- Verify the results
GGSCI> INFO REPLICAT *, TASKS


4): -- Configure the intial load delivery parameter file
GGSCI> EDIT PARAMS RINI<unique id>

-- Text File content inside the text editor
--
-- GoldenGate Inital Load Delivery
--
REPLICAT RINIT<unique id>
ASSUMETARGETDEFS
USERID <login>, PASSWORD <password>
DISCARDFILE ./dirrpt/RINIT<unique id>.dsc, PURGE
MAP <owner/schema>.*, TARGET <owner/schema>.*;

5): Execute the initial load process
-- on <source> system
GGSCI> START EXTRACT EINI<unique id>
GGSCI> VIEW REPORT EINIT<unique id>

-- on <target> system
GGSCI> VIEW REPORT RINIT<unique id>

-- -----------------------------------------
-- Configure Change Capture
-- -----------------------------------------
1): Add the Extract group
-- On <source> system to add an Extract group named EORA<unique id>.

GGSCI> ADD EXTRACT EORA<unique id>, TRANLOG, BEGIN NOW, THREADS <instances>
GGSCI> INFO EXTRACT EORA<unique id>

2): Create the Extract parameter file
GGSCI> EDIT PARAM EORA<unique id>
--
-- Change Capture parameter file to capture
-- TCUSTMER and TCUSTORD Changes
--
EXTRACT EORA<unique id>
USERID <login>, PASSWORD <password>
RMTHOST <target>, MGRPORT <port>
RMTTRAIL ./dirdat/<trail id>
TABLE <owner/schema>.TCUSTMER;
TABLE <owner/schema>.TCUSTORD;

-- note: Record the two characters selected for your <trail id>, 
         You will need this when you setup the Replicat.

3): Define the GoldenGate trail
-- on the <source>to add the trail that will store the changes on the target
GGSCI>ADD RMTTRAIL ./dirdat/<trail id> EXTRACT EORA<unique id> MEGABYTES 5
GGSCI>INFO RMTTRAIL *

GGSCI>stop extract <group>
GGSCI>alter rmttrail ./dirdat/<traid id> MEGABYTES 100, EXTRACT <group>
GGSCI>start extract <group>

4): Start the capture process
GGSCI>START EXTRACT EORA<unique id>

-- Verify the results:
GGSCI>INFO EXTRACT EORA<unique id> DETAIL
GGSCI>VIEW REPORT EORA<unique id>

5): On Target system
GGSCI>EDIT PARAMS ./GLOBALS

-- ./GLOBALS
CHECKPOINTTABLE <owner/schema>.ggschkpt

GGSCI>DBLOGIN USERID <login> PASSWORD <password>
GGSCI>ADD CHECKPOINTTABLE

GGSCI>ADD REPLICAT RORA<unique id> EXTTRAIL ./dirdat/<trail id>
GGSCI>edit PARAM RORA<unique id>

-- 
-- RORA<unique id>.prm
--
REPLICAT RORA<unique id>
USERID <login> PASSWORD <password>
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORA<unique id>DSC, PURGE
MAP <owner,schema>.tcustmer,  TARGET <owner.schema>.tcustmer;
MAP <owner,schema>.tcustord,  TARGET <owner.schema>.tcustord;

GGSCI> START REPLICAT RORA<unique id>
GGSCI> INFO REPLICAT RORA<unique id>

-- turn off handlecollisions
GGSCI> SEND REPLICAT RORA<unique id>, NOHANDLECOLLISIONS

GGSCI> SEND REPLICAT RORA<unique id>, REPORT
GGSCI> view report RORA<unique id>

GGSCI> -- send extract <group>, rollover
GGSCI> -- send er *, report

GGSCI> EXTRACT *, SAVE 3
GGSCI> CLEANUP REPLICAT *, SAVE 3

ex1

status extract initext
stop extract initext
start extract initext

info all
info extract *
INFO EXTRACT EXTORA
INFO EXTRACT INITEXT
info extract extora,showch,detail,tasks,allprocesses
help info extract
INFO EXTRACT <group name> [, SHOWCH [<n>]] [, DETAIL] [, TASKS | ALLPROCESSES]

VIEW REPORT INITEXT

ADD EXTRACT EXTORA, TRANLOG, BEGIN NOW
ALTER EXTRACT EXTORA, TRANLOG, BEGIN NOW

ADD RMTTRAIL ./dirdat/rt, EXTRACT EXTORA, MEGABYTES 10

ADD REPLICAT REPORA, EXTTRAIL ./dirdat/rt

parameter files

edit params ./GLOBALS

-- GGMGR is the default
mgrservname GGMGR 

edit params mgr  /* extract side mgr.prm */
PORT 7809
DYNAMICPORTLIST 7810-7910
AUTOSTART er *
-- AUTORESTART extract *. waitminutes 2, retries 5
PURGEOLDEXTRACTS *

edit params extora /* extract side directory load */
EXTRACT orcl1_e
-- dynamicresolution
USERID system@orcl, PASSWORD oracle
-- setenv(ORACLE_SID=ORCL)
-- exttrail /u01/app/oracle/product/GlodenGate/10.4/dirdat/et
RMTHOST 192.168.92.158, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/GlodenGate/10.4/dirdat/tr
TABLE test.employees;

edit params extora /* extract side using data pump */
EXTRACT orcl1_e
-- dynamicresolution
USERID system@orcl, PASSWORD oracle
-- setenv(ORACLE_SID=ORCL)
-- RMTHOST 192.168.92.158, MGRPORT 7809
-- RMTTRAIL /u01/app/oracle/product/GlodenGate/10.4/dirdat/tr
exttrail /u01/app/oracle/product/GlodenGate/10.4/dirdat/et
TABLE test.employees;


edit params repora /* replicat side replicat group parameter repora.prm */
REPLICAT rep
USERID system@orcl2, PASSWORD oracle
ASSUMETARGETDEFS
MAP test.employees, TARGET test.employees;