Oracle Goldengate

I am in the process of putting my notes into web pages, please keep checking in later for more contents

References

oracle-to-Oracle Tutorial  
top   prev   next  

In 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 connect, resource, unlimited tablespace to ggs;
SQL> -- grant execute on utl_file to ggs;
SQL> -- grant select any dictionary, select anytable table 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 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



top   prev   next  

Install Oracle Golden Gate

unzip xxx.tar.zip or gunzip xxx.tar.gz  or gzip -d xxx.tar.gz
tar -xovf xxx.tar

top   prev   next  

Create GoldenGate Default Sub Directories

ggsci
GGSCI>create subdirs
GGSCI>exit

top   prev   next  

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 
GGSCI> stats , TABLE *, TOTALSONLY *

GGSCU> send extract extora, rollover


top   prev   next  

Basic Setup 1

Shell>cd 
Shell> ggsci
GGSCI> EDIT PARAMS MGR

-- GoldenGate Manager parameter file
-- 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 , PASSWORD 
GGSCI> list tables *
GGSCI> -- dblogin userid myusername, password myusername_pass
GGSCI> ADD TRANDATA .TCUSTMER
GGSCI> ADD TRANDATA .TCUSTORD

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

Prepare Target environment
GGSCI> EDIT PARAMS MGR

-- GoldenGate Manager Parameter File
-- 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  system, to create an Extract named EINI
GGSCI> ADD EXTRACT EINI, SOURCEISTABLE

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

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

--file content inside the text editor
--
-- GOldenGate Inital Data Capture
-- for TCUSTMER and TCUSTORD
-- 
EXTRACT EINI
USERID , PASSWORD ""
RMTHOST , MGRPORT 
RMTTASK REPLICAT, GROUP RINIT
TABLE .TCUSTMER;
TABLE .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  system.
GGSCI> ADD REPLICAT RINI, SPECIALRUN

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


4): -- COnfigure the intial load delivery parameter file
GGSCI> EDIT PARAMS RINI

-- Text File content inside the text editor
--
-- GoldenGate Inital Load Delivery
--
REPLICAT RINIT
ASSUMETARGETDEFS
USERID , PASSWORD 
DISCARDFILE ./dirrpt/RINIT.dsc, PURGE
MAP .*, TARGET .*;

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

-- on  system
GGSCI> VIEW REPORT RINIT

-- -----------------------------------------
-- Configure Change Capture
-- -----------------------------------------
1): Add the Extract group
-- On  system to add an Extract group named EORA.

GGSCI> ADD EXTRACT EORA, TRANLOG, BEGIN NOW, THREADS 
GGSCI> INFO EXTRACT EORA

2): Create the Extract parameter file
GGSCI> EDIT PARAM EORA
--
-- Change Capture parameter file to capture
-- TCUSTMER and TCUSTORD Changes
--
EXTRACT EORA
USERID , PASSWORD 
RMTHOST , MGRPORT 
RMTTRAIL ./dirdat/
TABLE .TCUSTMER;
TABLE .TCUSTORD;

-- note: Record the two characters selected for your , YOu will need this when you setup the Replicat.


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

GGSCI> stop extract 
GGSCI> alter rmttrail ./dirdat/, MEGABYTES 100, EXTRACT 
GGSCI> start extract 

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

-- Verify the results:
GGSCI> INFO EXTRACT EORA, DETAIL
GGSCI> VIEW REPORT EORA

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

-- ./GLOBALS
CHECKPOINTTABLE .ggschkpt

GGSCI> DBLOGIN USERID , PASSWORD 
GGSCI> ADD CHECKPOINTTABLE

GGSCI> ADD REPLICAT RORA, EXTTRAIL ./dirdat/
GGSCI> edit PARAM RORA

-- 
-- RORA.prm
--
REPLICAT RORA
USERID , PASSWORD 
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORA.DSC, PURGE
MAP .tcustmer,  TARGET .tcustmer;
MAP .tcustord,  TARGET .tcustord;

GGSCI> START REPLICAT RORA
GGSCI> INFO REPLICAT RORA

-- turn off handlecollisions
GGSCI> SEND REPLICAT RORA, NOHANDLECOLLISIONS

GGSCI> SEND REPLICAT RORA, REPORT
GGSCI> view report RORA

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

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


top   prev   next  

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  [, SHOWCH []] [, 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


top   prev   next  

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;