Creating Oracle GoldenGate Parameter Files for the Source Database

This section describes the parameter files that you need to create and modify manually for the source database. Create these files in the dirprm directory of your source Oracle GoldenGate installation. For example, OGG_HOME/dirprm.

The file samples given here include editing instructions.

  • sourceaddtrndata.oby — The file defines the data to be transferred to the target database. Oracle GoldenGate will transfer changes to the tables defined in the file from the source to the target database.

  • configure_source.oby — Records the access user ID and defines the extract.

Creating sourceaddtrndata.oby for Data Definition

Create a file named sourceaddtrndata.oby and add the following:

##########################################################################################
-- ADD Trandata Obey file for Primary
-- This file defines the tables which we are interested in having OGG capture changes from the Transaction logs.
-- We initially specify an add TRANDATA EMDBO.* with wildcard to capture all tables.
-- We then direct OGG to ignore specific table trandata.  In our case all of the PeopleSoft type ‘7’ temp tables.
-- This is done by appending the output from the PSGGgeneratetrandatadeletes.txt (eg. just the generated 
-- DELETE -- TRANDATA statements) after the ADD TRANDATA EMDBO.* statement
-- ##########################################################################################
-- 
-- Edit and modify 'OGGUSER' to Oracle GoldenGate Admin Userid and PW
--  
-- Edit and modify 'EMDBO' to PSACCESSID
-- 
-- Append the output from the PSGGgeneratetrandatadeletes.txt (eg. just the generated 
-- DELETE TRANDATA       -- statements) after the ADD TRANDATA EMDBO .* statement
--
dblogin useridalias <alias>
ADD TRANDATA SYSADM.*
-- ##########################################################################################
-- This section lists the generated DELETE TRANDATA  statements 
-- ##########################################################################################
--<Copy in the generated DELETE TRANDATA statements after the preceding ADD TRANDDATA statement.>
.
.
.
-- ##########################################################################################
-- This section lists the required static DELETE TRANDATA statements 
-- ##########################################################################################
DELETE TRANDATA EMDBO.PSLOCK
DELETE TRANDATA EMDBO.PSRECDEFN 
DELETE TRANDATA EMDBO.PS_SERVERMONITOR
DELETE TRANDATA EMDBO.PS_SERVERACTVTY
DELETE TRANDATA EMDBO.PS_PRCSSEQUENCE
DELETE TRANDATA EMDBO.PS_MESSAGE_LOGPARM
DELETE TRANDATA EMDBO.PS_MESSAGE_LOG
DELETE TRANDATA EMDBO.PS_AETEMPTBLMGR
DELETE TRANDATA EMDBO.PS_AERUNCONTROLPC
DELETE TRANDATA EMDBO.PS_AERUNCONTROL
DELETE TRANDATA EMDBO.PS_AELOCKMGR
DELETE TRANDATA EMDBO.PSWEBPROFHIST
DELETE TRANDATA EMDBO.PSSERVERSTAT
DELETE TRANDATA EMDBO.PSQRYTRANS
DELETE TRANDATA EMDBO.PSPRCSJOBSTATUS
DELETE TRANDATA EMDBO.PSOPRDEFN
DELETE TRANDATA EMDBO.PSIBSUBSLAVE
DELETE TRANDATA EMDBO.PSIBPUBSLAVE
DELETE TRANDATA EMDBO.PSIBFOLOCK
DELETE TRANDATA EMDBO.PSIBFAILOVER
DELETE TRANDATA EMDBO.PSIBBRKSLAVE
DELETE TRANDATA EMDBO.PSACCESSLOG
DELETE TRANDATA EMDBO.PS_PTFP_ACCESS_LOG
DELETE TRANDATA EMDBO.PS_PTFP_OPTIONS
DELETE TRANDATA EMDBO.PSIBPROFILESYNC
DELETE TRANDATA EMDBO.PSIBLOGHDR
DELETE TRANDATA EMDBO.PSIBLOGERR
DELETE TRANDATA EMDBO.PSIBLOGERRP
DELETE TRANDATA EMDBO.PSIBLOGDATA
DELETE TRANDATA EMDBO.PSIBLOGIBINFO
DELETE TRANDATA EMDBO.PSQASRUN
DELETE TRANDATA EMDBO.PSPRCSRQST
DELETE TRANDATA EMDBO.PSPRCSQUE
DELETE TRANDATA EMDBO.PSPRCSRQSTFILE
DELETE TRANDATA EMDBO.PSPRCSPARMS
DELETE TRANDATA EMDBO.PSPRCSRQSTTEXT
DELETE TRANDATA EMDBO.PSPRCSRQSTTEXT2
DELETE TRANDATA EMDBO.PS_CDM_LIST
DELETE TRANDATA EMDBO.PS_CDM_TRANSFER
DELETE TRANDATA EMDBO.PS_CDM_AUTH
DELETE TRANDATA EMDBO.PS_BAT_TIMINGS_LOG
DELETE TRANDATA EMDBO.PS_BAT_TIMINGS_DTL
DELETE TRANDATA EMDBO.PS_AE_TIMINGS_LG
DELETE TRANDATA EMDBO.PS_AE_TIMINGS_DT
DELETE TRANDATA EMDBO.PS_BAT_TIMINGS_FN
DELETE TRANDATA EMDBO.PSQRYFAVORITES
DELETE TRANDATA EMDBO.PSQRYSTATS
DELETE TRANDATA EMDBO.PSFILE_ATTDET
DELETE TRANDATA EMDBO.PSPTFILE_REF
DELETE TRANDATA EMDBO.PSPTFILE_WART
DELETE TRANDATA EMDBO.PS_PTSF_SCHED_STAT
DELETE TRANDATA EMDBO.PSPGVIEWOPT
DELETE TRANDATA EMDBO.PSPGCHARTOPT
DELETE TRANDATA EMDBO.PSPGCHRTFLRSOPT
DELETE TRANDATA EMDBO.PSPGCHTFLRSLANG
DELETE TRANDATA EMDBO.PSPGDISPOPT
DELETE TRANDATA EMDBO.PSPGGRIDOPT
DELETE TRANDATA EMDBO.PSPGQRYPROMPT
DELETE TRANDATA EMDBO.PSPGQRYPROMPLNG
DELETE TRANDATA EMDBO.PSPGCHARTOPTLNG
DELETE TRANDATA EMDBO.PSPGVIEWOPTLANG
DELETE TRANDATA EMDBO.PSPGVIEWOPTPERS
DELETE TRANDATA EMDBO.PSPGAXISPERS
DELETE TRANDATA EMDBO.PSPGAXISPERSLNG
DELETE TRANDATA EMDBO.PSPGCHARTOPTPER
DELETE TRANDATA EMDBO.PSPGQRYPRMPTPER
DELETE TRANDATA EMDBO.PSPGGRIDOPTPERS
DELETE TRANDATA EMDBO.PSPGCHTOPTPERLN
DELETE TRANDATA EMDBO.PSPGQRYPRMPTPLN
DELETE TRANDATA EMDBO.PSPGVWOPTPERLN
DELETE TRANDATA EMDBO.PSPGCORE
DELETE TRANDATA EMDBO.PSPGCORELANG
DELETE TRANDATA EMDBO.PSPGMODEL
DELETE TRANDATA EMDBO.PSPGMODELLANG
DELETE TRANDATA EMDBO.PSPGSETTINGS
DELETE TRANDATA EMDBO.PSPGAXIS
DELETE TRANDATA EMDBO.PSPGTHRESHOLDLN
DELETE TRANDATA EMDBO.PSPGNUIOPT
DELETE TRANDATA EMDBO.PSPGNUIDIMOPT
DELETE TRANDATA EMDBO.PSPGSAVEHIST 
DELETE TRANDATA EMDBO.PSPTFILE_PRCS
DELETE TRANDATA EMDBO.PSUSEROBJTYPE
DELETE TRANDATA EMDBO.PSUSERSRCHDEFN
DELETE TRANDATA EMDBO.PSUSERPRSNLOPTN
DELETE TRANDATA EMDBO.PSVERSION
DELETE TRANDATA EMDBO.PSUSRTAPAGECUST
DELETE TRANDATA EMDBO.PSBATCHAUTH
DELETE TRANDATA EMDBO.PSBATCHAUTHLONG
DELETE TRANDATA EMDBO.PSPRCSCHLDINFO
DELETE TRANDATA EMDBO.PS_PTNVSLYTQRY
DELETE TRANDATA EMDBO.PSTREESELNUM
DELETE TRANDATA EMDBO.PSTREESELCTL
DELETE TRANDATA EMDBO.PSNVSBATCHRSTRT
DELETE TRANDATA EMDBO.PSNVSDRILLQRY
DELETE TRANDATA EMDBO.PSNVSDRLPROMPTS
DELETE TRANDATA EMDBO.PS_CDM_TEXT
DELETE TRANDATA EMDBO.PS_PRCSRQSTDIST
DELETE TRANDATA EMDBO.PSTREESELECT01
DELETE TRANDATA EMDBO.PSTREESELECT02
DELETE TRANDATA EMDBO.PSTREESELECT03
DELETE TRANDATA EMDBO.PSTREESELECT04
DELETE TRANDATA EMDBO.PSTREESELECT05
DELETE TRANDATA EMDBO.PSTREESELECT06
DELETE TRANDATA EMDBO.PSTREESELECT07
DELETE TRANDATA EMDBO.PSTREESELECT08
DELETE TRANDATA EMDBO.PSTREESELECT09
DELETE TRANDATA EMDBO.PSTREESELECT10
DELETE TRANDATA EMDBO.PSTREESELECT11
DELETE TRANDATA EMDBO.PSTREESELECT12
DELETE TRANDATA EMDBO.PSTREESELECT13
DELETE TRANDATA EMDBO.PSTREESELECT14
DELETE TRANDATA EMDBO.PSTREESELECT15
DELETE TRANDATA EMDBO.PSTREESELECT16
DELETE TRANDATA EMDBO.PSTREESELECT17
DELETE TRANDATA EMDBO.PSTREESELECT18
DELETE TRANDATA EMDBO.PSTREESELECT19
DELETE TRANDATA EMDBO.PSTREESELECT20
DELETE TRANDATA EMDBO.PSTREESELECT21
DELETE TRANDATA EMDBO.PSTREESELECT22
DELETE TRANDATA EMDBO.PSTREESELECT23
DELETE TRANDATA EMDBO.PSTREESELECT24
DELETE TRANDATA EMDBO.PSTREESELECT25
DELETE TRANDATA EMDBO.PSTREESELECT26
DELETE TRANDATA EMDBO.PSTREESELECT27
DELETE TRANDATA EMDBO.PSTREESELECT28
DELETE TRANDATA EMDBO.PSTREESELECT29
DELETE TRANDATA EMDBO.PSTREESELECT30
DELETE TRANDATA EMDBO.PSFP_FEED
DELETE TRANDATA EMDBO.PSFP_FEED_LANG
DELETE TRANDATA EMDBO.PSFP_SETTINGS
DELETE TRANDATA EMDBO.PSFP_ADMN_PREF
DELETE TRANDATA EMDBO.PSFP_USER_PREF
DELETE TRANDATA EMDBO.PSFP_SECURITY
DELETE TRANDATA EMDBO.PSFP_PUB_SITES
DELETE TRANDATA EMDBO.PSFP_PARMS
DELETE TRANDATA EMDBO.PSFP_PARMS_LANG
DELETE TRANDATA EMDBO.PSFP_PVALS
DELETE TRANDATA EMDBO.PSFP_PVALS_LANG
DELETE TRANDATA EMDBO.PSFP_ATTRS
DELETE TRANDATA EMDBO.PSFP_ATTRS_LANG
DELETE TRANDATA EMDBO.PSFP_FEED_DEL
DELETE TRANDATA EMDBO.PSAPMSGPUBHDR
DELETE TRANDATA EMDBO.PSAPMSGPUBDATA
DELETE TRANDATA EMDBO.PSAPMSGIBATTR
DELETE TRANDATA EMDBO.PSAPMSGSEGATTR
DELETE TRANDATA EMDBO.PSAPMSGPUBSYNC

After modifying and saving the file, use Admin Client to verify the standard output from running sourceaddtrndata.oby.

Creating configure_source.oby for Extraction Definition

Create a file named configure_source.oby and add the following.

################################################################################
-- 
-- Edit and modify useridalias and extract name
--

dblogin useridalias ggprime
ADD EXTRACT primecap INTEGRATED TRANLOG, BEGIN NOW
add exttrail pp, extract primecap
REGISTER EXTRACT primecap DATABASE 

Create a file named primecap.prm and add the following:

########################################################################################## 
-- 
-- Edit and modify useridalias
-- 
-- Edit and modify 'EMDBO' to PSACCESSID
--  
-- Copy the output from the PSGGgeneratetableexcludes.txt (eg. just the generated TABLEEXCLUDE  
-- statements) between the WILDCARDRESOLVE DYNAMIC statement and before the TABLE EMDBO.*
-- statement
--
-- ##########################################################################################

EXTRACT primecap
useridalias ggprime
EXTTRAIL pp
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TRANLOGOPTIONS USE_ROOT_CONTAINER_TIMEZONE

-- ##########################################################################################
-- This section lists the generated TABLEEXCLUDE statements 
-- ##########################################################################################

-- <Copy in the generated TABLEEXCLUDE statements after the preceding WILDCARDRESOLVE DYNAMIC 
-- statement.>
.
.
.
.

-- ##########################################################################################
-- This section lists the required static TABLEEXCLUDE statements 
-- ##########################################################################################
TABLEEXCLUDE EMDBO.PSLOCK;
TABLEEXCLUDE EMDBO.PSRECDEFN;
TABLEEXCLUDE EMDBO.PS_SERVERMONITOR;
TABLEEXCLUDE EMDBO.PS_SERVERACTVTY;
TABLEEXCLUDE EMDBO.PS_PRCSSEQUENCE;
TABLEEXCLUDE EMDBO.PS_MESSAGE_LOGPARM;
TABLEEXCLUDE EMDBO.PS_MESSAGE_LOG;
TABLEEXCLUDE EMDBO.PS_AETEMPTBLMGR;
TABLEEXCLUDE EMDBO.PS_AERUNCONTROLPC;
TABLEEXCLUDE EMDBO.PS_AERUNCONTROL;
TABLEEXCLUDE EMDBO.PS_AELOCKMGR;
TABLEEXCLUDE EMDBO.PSWEBPROFHIST;
TABLEEXCLUDE EMDBO.PSSERVERSTAT;
TABLEEXCLUDE EMDBO.PSQRYTRANS;
TABLEEXCLUDE EMDBO.PSPRCSJOBSTATUS;
TABLEEXCLUDE EMDBO.PSOPRDEFN;
TABLEEXCLUDE EMDBO.PSIBSUBSLAVE;
TABLEEXCLUDE EMDBO.PSIBPUBSLAVE;
TABLEEXCLUDE EMDBO.PSIBFOLOCK;
TABLEEXCLUDE EMDBO.PSIBFAILOVER;
TABLEEXCLUDE EMDBO.PSIBBRKSLAVE;
TABLEEXCLUDE EMDBO.PSACCESSLOG;
TABLEEXCLUDE EMDBO.PS_PTFP_ACCESS_LOG;
TABLEEXCLUDE EMDBO.PS_PTFP_OPTIONS;
TABLEEXCLUDE EMDBO.PSIBPROFILESYNC;
TABLEEXCLUDE EMDBO.PSIBLOGHDR;
TABLEEXCLUDE EMDBO.PSIBLOGERR;
TABLEEXCLUDE EMDBO.PSIBLOGERRP;
TABLEEXCLUDE EMDBO.PSIBLOGDATA;
TABLEEXCLUDE EMDBO.PSIBLOGIBINFO;
TABLEEXCLUDE EMDBO.PSQASRUN;
TABLEEXCLUDE EMDBO.PSPRCSRQST;
TABLEEXCLUDE EMDBO.PSPRCSQUE;
TABLEEXCLUDE EMDBO.PSPRCSRQSTFILE;
TABLEEXCLUDE EMDBO.PSPRCSPARMS;
TABLEEXCLUDE EMDBO.PSPRCSRQSTTEXT;
TABLEEXCLUDE EMDBO.PSPRCSRQSTTEXT2;
TABLEEXCLUDE EMDBO.PS_CDM_LIST;
TABLEEXCLUDE EMDBO.PS_CDM_TRANSFER;
TABLEEXCLUDE EMDBO.PS_CDM_AUTH;
TABLEEXCLUDE EMDBO.PS_BAT_TIMINGS_LOG;
TABLEEXCLUDE EMDBO.PS_BAT_TIMINGS_DTL;
TABLEEXCLUDE EMDBO.PS_AE_TIMINGS_LG;
TABLEEXCLUDE EMDBO.PS_AE_TIMINGS_DT;
TABLEEXCLUDE EMDBO.PS_BAT_TIMINGS_FN;
TABLEEXCLUDE EMDBO.PSQRYFAVORITES;
TABLEEXCLUDE EMDBO.PSQRYSTATS;
TABLEEXCLUDE EMDBO.PSFILE_ATTDET;
TABLEEXCLUDE EMDBO.PSPTFILE_REF;
TABLEEXCLUDE EMDBO.PSPTFILE_WART;
TABLEEXCLUDE EMDBO.PS_PTSF_SCHED_STAT;
TABLEEXCLUDE EMDBO.PSPGVIEWOPT;
TABLEEXCLUDE EMDBO.PSPGCHARTOPT;
TABLEEXCLUDE EMDBO.PSPGCHRTFLRSOPT;
TABLEEXCLUDE EMDBO.PSPGCHTFLRSLANG;
TABLEEXCLUDE EMDBO.PSPGDISPOPT;
TABLEEXCLUDE EMDBO.PSPGGRIDOPT;
TABLEEXCLUDE EMDBO.PSPGQRYPROMPT;
TABLEEXCLUDE EMDBO.PSPGQRYPROMPLNG;
TABLEEXCLUDE EMDBO.PSPGCHARTOPTLNG;
TABLEEXCLUDE EMDBO.PSPGVIEWOPTLANG;
TABLEEXCLUDE EMDBO.PSPGVIEWOPTPERS;
TABLEEXCLUDE EMDBO.PSPGAXISPERS;
TABLEEXCLUDE EMDBO.PSPGAXISPERSLNG;
TABLEEXCLUDE EMDBO.PSPGCHARTOPTPER;
TABLEEXCLUDE EMDBO.PSPGQRYPRMPTPER;
TABLEEXCLUDE EMDBO.PSPGGRIDOPTPERS;
TABLEEXCLUDE EMDBO.PSPGCHTOPTPERLN;
TABLEEXCLUDE EMDBO.PSPGQRYPRMPTPLN;
TABLEEXCLUDE EMDBO.PSPGVWOPTPERLN;
TABLEEXCLUDE EMDBO.PSPGCORE;
TABLEEXCLUDE EMDBO.PSPGCORELANG;
TABLEEXCLUDE EMDBO.PSPGMODEL;
TABLEEXCLUDE EMDBO.PSPGMODELLANG;
TABLEEXCLUDE EMDBO.PSPGSETTINGS;
TABLEEXCLUDE EMDBO.PSPGAXIS;
TABLEEXCLUDE EMDBO.PSPGTHRESHOLDLN;
TABLEEXCLUDE EMDBO.PSPGNUIOPT;
TABLEEXCLUDE EMDBO.PSPGNUIDIMOPT;
TABLEEXCLUDE EMDBO.PSPGSAVEHIST;
TABLEEXCLUDE EMDBO.PSPTFILE_PRCS;
TABLEEXCLUDE EMDBO.PSUSEROBJTYPE;
TABLEEXCLUDE EMDBO.PSUSERSRCHDEFN;
TABLEEXCLUDE EMDBO.PSUSERPRSNLOPTN;
TABLEEXCLUDE EMDBO.PSVERSION;
TABLEEXCLUDE EMDBO.PSUSRTAPAGECUST;
TABLEEXCLUDE EMDBO.PSBATCHAUTH;
TABLEEXCLUDE EMDBO.PSBATCHAUTHLONG;
TABLEEXCLUDE EMDBO.PSPRCSCHLDINFO;
TABLEEXCLUDE EMDBO.PS_PTNVSLYTQRY;
TABLEEXCLUDE EMDBO.PSTREESELNUM;
TABLEEXCLUDE EMDBO.PSTREESELCTL;
TABLEEXCLUDE EMDBO.PSNVSBATCHRSTRT;
TABLEEXCLUDE EMDBO.PSNVSDRILLQRY;
TABLEEXCLUDE EMDBO.PSNVSDRLPROMPTS;
TABLEEXCLUDE EMDBO.PS_CDM_TEXT;
TABLEEXCLUDE EMDBO.PS_PRCSRQSTDIST;
TABLEEXCLUDE EMDBO.PSTREESELECT01;
TABLEEXCLUDE EMDBO.PSTREESELECT02;
TABLEEXCLUDE EMDBO.PSTREESELECT03;
TABLEEXCLUDE EMDBO.PSTREESELECT04;
TABLEEXCLUDE EMDBO.PSTREESELECT05;
TABLEEXCLUDE EMDBO.PSTREESELECT06;
TABLEEXCLUDE EMDBO.PSTREESELECT07;
TABLEEXCLUDE EMDBO.PSTREESELECT08;
TABLEEXCLUDE EMDBO.PSTREESELECT09;
TABLEEXCLUDE EMDBO.PSTREESELECT10;
TABLEEXCLUDE EMDBO.PSTREESELECT11;
TABLEEXCLUDE EMDBO.PSTREESELECT12;
TABLEEXCLUDE EMDBO.PSTREESELECT13;
TABLEEXCLUDE EMDBO.PSTREESELECT14;
TABLEEXCLUDE EMDBO.PSTREESELECT15;
TABLEEXCLUDE EMDBO.PSTREESELECT16;
TABLEEXCLUDE EMDBO.PSTREESELECT17;
TABLEEXCLUDE EMDBO.PSTREESELECT18;
TABLEEXCLUDE EMDBO.PSTREESELECT19;
TABLEEXCLUDE EMDBO.PSTREESELECT20;
TABLEEXCLUDE EMDBO.PSTREESELECT21;
TABLEEXCLUDE EMDBO.PSTREESELECT22;
TABLEEXCLUDE EMDBO.PSTREESELECT23;
TABLEEXCLUDE EMDBO.PSTREESELECT24;
TABLEEXCLUDE EMDBO.PSTREESELECT25;
TABLEEXCLUDE EMDBO.PSTREESELECT26;
TABLEEXCLUDE EMDBO.PSTREESELECT27;
TABLEEXCLUDE EMDBO.PSTREESELECT28;
TABLEEXCLUDE EMDBO.PSTREESELECT29;
TABLEEXCLUDE EMDBO.PSTREESELECT30;
TABLEEXCLUDE EMDBO.PSFP_FEED;
TABLEEXCLUDE EMDBO.PSFP_FEED_LANG;
TABLEEXCLUDE EMDBO.PSFP_SETTINGS;
TABLEEXCLUDE EMDBO.PSFP_ADMN_PREF;
TABLEEXCLUDE EMDBO.PSFP_USER_PREF;
TABLEEXCLUDE EMDBO.PSFP_SECURITY;
TABLEEXCLUDE EMDBO.PSFP_PUB_SITES;
TABLEEXCLUDE EMDBO.PSFP_PARMS;
TABLEEXCLUDE EMDBO.PSFP_PARMS_LANG;
TABLEEXCLUDE EMDBO.PSFP_PVALS;
TABLEEXCLUDE EMDBO.PSFP_PVALS_LANG;
TABLEEXCLUDE EMDBO.PSFP_ATTRS;
TABLEEXCLUDE EMDBO.PSFP_ATTRS_LANG;
TABLEEXCLUDE EMDBO.PSFP_FEED_DEL;
TABLEEXCLUDE EMDBO.PSAPMSGPUBHDR;
TABLEEXCLUDE EMDBO.PSAPMSGPUBDATA;
TABLEEXCLUDE EMDBO.PSAPMSGIBATTR;
TABLEEXCLUDE EMDBO.PSAPMSGSEGATTR;
TABLEEXCLUDE EMDBO.PSAPMSGPUBSYNC;
TABLE EMDBO.*;

After modifying and saving the file, connect to Admin Client, and verify the standard output.