I decided to write a bit about a few Oracle GoldenGate features that seem to be vastly under utilized (at least in the implementations I see each week or so), and especially in implementations with lots of processes (say 10-100 pumps or replicats), or in implementations with many sources or targets with roughly the same configuration (only few things change per process).
I tend to use these features over and over again, even in the smallest of implementations. I start out by using them (have a zip file that lays out all the directories under /dirprm/ with all the features preconfigured) to save much configuration time because they only make the configuration process more standardized and much more efficient to configure initially, as well as when future changes are needed to the configuration.
However, using these features together, or independently, do tend to make the configuration a bit more complex. Once they are understood, customers seem to love them in the end, as it makes changes easy and quick to implement. Especially customers with large implementations (many processes) or many instances (source and/or targets) in the implementation. And, many future changes are inevitable unless you are doing a migration implementation (even then I use them because it it so incredibly efficient to configure and make any changes even during those quick implementations)
The Oracle GoldenGate features I am referring to, and will briefly cover are:
1) Include files
2) Variables
3) Macros
- create macro libraries to store common mapping types
In this blog, we will briefly cover each and give some brief examples of each, as well as combining all three together to make a very clean and easy configuration to change or automate at run time or deployment time.
INCLUDE/OBEY files
What is an INCLUDE file? An include file is a file that contains Oracle GoldenGate parameters/settings, just as they would in a regular PRM file, but are in a separate file.This file is then included in the main process PRM file with an INCLUDE parameter/statement.
These are very similar to OBEY files in Oracle GoldenGate, if you have used them before (very common for GoldenGater technical folks to write process/trail add/delete/alter commands in OBEY files for easy recall/reuse and to keep a record of the commands used).
Using INCLUDE files makes a configuration more modular, and certainly more efficient by eliminating redundancy when a lot of processes are configured in the exact same way in the instance (referring to global parameters used by the processes and not the individual TABLE/MAP statements or other parameters that are required to be different between say 40 replicats). And, variables/macros can be used in Include files as well. More on those in the later sections.
1) Database environment
- DB name and SID, login info and any SETENV related to the DB that many processes use
- can include TRANSLOGOPTIONS, FETCH options or DBOPTIONS if the same across all
2) Discard file, report files, STATS and ROLLOVER options
- read how to use with variables to make them process specific using the same command for
3) TABLE/MAP mappings/statement
- read further down to see how we use with macros to streamline
4) RMTHOST settings
- remote host settings which are common to many pumps
5) Global parameters needed in different processes but specific to process types (extract vs replicat)
- FETCHOPTIONS, REPERROR, DDLERROR, TRANSLOGPTIONS, DBOPTIONS
6) Macro libraries
- we will discuss using macros and putting them into macro libraries later in this blog
Below are two examples of Include files. I use many include files but these are two real examples. Both use variables in them as well (which is required if you use one Include file for all processes). The variables localize the parameters for each process.
One is for the database login for a replicat, and the other Include file is for all processes and handles all the Discard, report file and STATS options .
I usually put all Include files under the ./dirprm/ directory, because they are part of the configuration even though in separate files. GoldenGate parses the PRM
Sample INCLUDE file for database information (for all replicats):
---> db_login_DBM.inc
----------- start of INCLUDE file ------------
COMMENT Database Connection Info and Parameters
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
-- USERID ogg@?OGG_DB PASSWORD guess_me_if_you_can
USERID OGG@?OGG_DB PASSWORD AACAAAAAXXXXXXAAIAWGVAABXXXXBIDPEHB ENCRYPTKEY DEFAULT
DBOPTIONS DEFERREFCONST
DBOPTIONS SUPPRESSTRIGGERS
----------- end of INCLUDE file ------------
---> global_parms.inc.
----------- Start of Global INCLUDE file ------------
COMMENT Global Parameters Include file (for all processes)
DISCARDROLLOVER AT 00:01 ON SUNDAY
STATOPTIONS RESETREPORTSTATS, REPORTFETCH, REPORTDETAIL
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORT AT 00:00
REPORTROLLOVER AT 00:02 ON SUNDAY
ROLLOVER AT 00:00
WARNLONGTRANS 4H, CHECKINTERVAL 6H
----------- End of Global INCLUDE file ------------
Now that we have seen what is in each Include file, it is time to make them part of the configuration. To do this, we need to include this information in each process's PRM file. We do this by adding the INCLUDE parameter with the path to the Include file in each PRM file. I highly recommend to use relative paths, it just makes life easier to base all paths from the GGS_HOME.
Replicat #1 Parameter file:
REPLICAT rep1
.....
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_DBM.inc
-- Global Parameters (for all processes)
INCLUDE ./dirprm/global_parms.inc
.....
Replicat #2 Parameter file:
REPLICAT rep2
.....
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_DBM.inc
-- Global Parameters (for all processes)
INCLUDE ./dirprm/global_params.inc
.......
REPLICAT rep3
.....
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_DBM.inc
-- Global Parameters (for all processes)
INCLUDE ./dirprm/global_parms.inc
.....
REPLICAT rep4
.....
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_DBM.inc
-- Global Parameters (for all processes)
INCLUDE ./dirprm/global_parms.inc
.....
Variables
Variables in GoldenGate are used in the same way operating systems use variables. When you set a variable in GoldenGate (via SETENV), you are setting an environment variable in the shell for the process whether it be extract or pump or replicat to access.
Variables are used to localize the configuration or the PRM template for the local environment and the process type. We may have include files that are process type specific and we need each process to know which ones to include.
Below is an example of how to make all configurations cookie cutter and only a few lines need to be changed per process (once the process has been added in "ggsci".
The example below shows how to set variables in a parameter file to localize it for the process name and type.
-- Start of Replicat parameter file
REPLICAT rep1
SETENV (OGG_GROUP = "rep1")
SETENV (OGG_DB = "DBM")
SETENV (OGG_NUM = "1")
SETENV (OGG_TYPE = "replicat")
-- All configuration below is based on variables and is standardized
.....
-- End of Replicat parameter file
Replicat #1 Parameter file:
-- --- Start of replicat PRM --------
REPLICAT rep1
SETENV (OGG_GROUP = "rep1")
SETENV (OGG_DB = "ksdev")
SETENV (OGG_NUM = "1")
SETENV (OGG_TYPE = "replicat")
-- -------- No need to edit below this line -----------
-- All parameters/Include files below are based on variables set above
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_?OGG_DB.inc
-- Global Parameters (for all processes)
INCLUDE ./dirprm/global_parms.inc
INCLUDE ./dirprm/global_error_handling.?OGG_TYPE.inc
-- Global DDLOptions
INCLUDE ./dirprm/global_ddloptions.?OGG_TYPE.inc
-- Marco Include file (for replicat)
INCLUDE ./dirprm/macros/macros_?OGG_TYPE.inc
-- Include Map/Target statements
INCLUDE ./dirprm/objects_?OGG_DB.?OGG_GROUP.?OGG_NUM.inc
----- End of replicat PRM --------
Replicat #2 Parameter file:
-- --- Start of replicat PRM --------
REPLICAT rep2
SETENV (OGG_GROUP = "rep2")
SETENV (OGG_DB = "DBM")
SETENV (OGG_NUM = "2")
SETENV (OGG_TYPE = "replicat")
-- -------- No need to edit below this line -----------
-- All parameters/Include files below are based on variables set above
INCLUDE ./dirprm/db_target_?OGG_DB.inc
-- Global Parameters (for all processes)
INCLUDE ./dirprm/global_parms.inc
INCLUDE ./dirprm/global_error_handling.?OGG_TYPE.inc
-- Global DDLOptions
INCLUDE ./dirprm/global_ddloptions.?OGG_TYPE.inc
-- Marco Include file (for replicat)
INCLUDE ./dirprm/macros/macros_?OGG_TYPE.inc
-- Include Map/Target statements
INCLUDE ./dirprm/objects_?OGG_DB.?OGG_GROUP.?OGG_NUM.inc
----- End of replicat PRM --------
Replicat #3 Parameter file:
----- Start of replicat PRM --------
REPLICAT rep3
SETENV (OGG_GROUP = "rep3")
SETENV (OGG_DB = "DBM")
SETENV (OGG_NUM = "3")
SETENV (OGG_TYPE = "replicat")
-- -------- No need to edit below this line -----------
-- All parameters/Include files below are based on variables set above
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_?OGG_DB.inc
-- Global Parameters (for all processes)
INCLUDE ./dirprm/global_parms.inc
INCLUDE ./dirprm/global_error_handling.?OGG_TYPE.inc
-- Global DDLOptions
INCLUDE ./dirprm/global_ddloptions.?OGG_TYPE.inc
-- Marco Include file (for replicat)
INCLUDE ./dirprm/macros/macros_?OGG_TYPE.inc
-- Include Map/Target statements
INCLUDE ./dirprm/objects_?OGG_DB.?OGG_GROUP.?OGG_NUM.inc
----- End of replicat PRM --------
Replicat #4 Parameter file:
----- Start of replicat PRM --------
REPLICAT rep4
SETENV (OGG_GROUP = "rep4")
SETENV (OGG_DB = "DBM")
SETENV (OGG_NUM = "4")
SETENV (OGG_TYPE = "replicat")
-- -------- No need to edit below this line -----------
-- All parameters/Include files below are based on variables set above
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_?OGG_DB.inc
-- Global Parameters (for all processes)
INCLUDE ./dirprm/global_parms.inc
INCLUDE ./dirprm/global_error_handling.?OGG_TYPE.inc
-- Global DDLOptions
INCLUDE ./dirprm/global_ddloptions.?OGG_TYPE.inc
-- Marco Include file (for replicat)
INCLUDE ./dirprm/macros/macros_?OGG_TYPE.inc
-- Include Map/Target statements
INCLUDE ./dirprm/objects_?OGG_DB.?OGG_GROUP.?OGG_NUM.inc
----- End of replicat PRM --------
Macros
Now macros are where the real fun (and efficient) begins. The macro capabilities in Oracle GoldenGate are very very basic; all you get is basic string replacement. The feature has no looping or control/evaluation capabilities (which we hope they add to it to help generate configurations automatically). Although the macro feature is very basic, we can still use to it to help streamline complex mappings (making them reusable) and/or pre-configure mappings and switch the mappings easily without two days of typing new mappings (and possibly hundreds of mappings) in 20 different parameter files.We develop macro libraries for customers that are suited and customized to their specific implementation and then teach them how to easily make global changes to all processes using the macros. Our macro libraries do all the TABLE and MAP/TARGET mappings. In many cases, we will have a macro call a macro call a macro to ensure we are not duplicating a bunch of mappings. How
far with go with nested macro calls depends on the customer's comfort level with complexity versus making a bunch of manual typing changes.
Below is a few macros written (a macro library for replicats) to use in mappings and macros that do the final mappings for any replicat process. We also do these macros for extracts and pumps, but the real work (bulk of complex mappings are in the replicat).
------------ start of Replicat Macro Library ----------------
-- Enable Macro expansion tracing to Report file
CMDTRACE DETAIL
-- ****************************************************
-- Common macros (called internally in this library
-- ****************************************************
MACRO #global_exception_table
BEGIN
TARGET GGADMIN.EXCEPTIONS
END;
MACRO #global_exception_proc
BEGIN
TARGET GGADMIN.LOGEXCEPTION
END;
MACRO #exceptioncols
BEGIN
excp_date = @DATENOW(), &
rep_name = @GETENV ('GGENVIRONMENT', 'GROUPNAME'), &
table_name = @GETENV ('GGHEADER', 'TABLENAME'), &
errno = @GETENV ('LASTERR', 'DBERRNUM'), &
errtype = @GETENV ('LASTERR', 'ERRTYPE'), &
optype = @GETENV ('LASTERR', 'OPTYPE'), &
transind = @GETENV ('GGHEADER', 'TRANSACTIONINDICATOR'), &
transimgind = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'), &
committimestamp = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'), &
reccsn = @GETENV ('TRANSACTION', 'CSN'), &
recseqno = @GETENV ('RECORD', 'FILESEQNO'), &
recrba = @GETENV ('RECORD', 'FILERBA'), &
rectranspos = @GETENV ('RECORD', 'RSN'), &
reclength = @GETENV ('GGHEADAER', 'RECORDLENGTH'), &
logrba = @GETENV ('GGHEADER', 'LOGRBA'), &
logposition = @GETENV ('GGHEADER', 'LOGPOSITION'), &
grouptype = @GETENV ('GGENVIRONMENT', 'GROUPTYPE'), &
filename = @GETENV ('GGFILEHEADER', 'FILENAME'), &
fileno = @GETENV ('GGFILEHEADER', 'FILESEQNO'), &
srcrowid = @GETENV ('TRANSACTION', 'CSN'), &
srcdbcharset = @GETENV ('GGFILEHEADER', 'DBCHARSET'), &
replag = @GETENV ('LAG', 'SEC'), &
cnt_cdr_conflicts = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_CONFLICTS'), &
cnt_cdr_resolutions = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_SUCCEEDED'), &
cnt_cdr_failed = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_FAILED'))
END;
MACRO #logexceptioncolssqlexec
BEGIN
SQLEXEC (SPNAME #global_exception_proc (), ID #idname (), PARAMS ( &
rep_name = @GETENV ('GGENVIRONMENT', 'GROUPNAME'), &
table_name = @GETENV ('GGHEADER', 'TABLENAME'), &
errno = @GETENV ('LASTERR', 'DBERRNUM'), &
errtype = @GETENV ('LASTERR', 'ERRTYPE'), &
optype = @GETENV ('LASTERR', 'OPTYPE'), &
transind = @GETENV ('GGHEADER', 'TRANSACTIONINDICATOR'), &
transimgind = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'), &
committimestamp = @STREXT(@GETENV ('GGHEADER', 'COMMITTIMESTAMP'), 1, 22), &
reccsn = @GETENV ('TRANSACTION', 'CSN'), &
recseqno = @GETENV ('RECORD', 'FILESEQNO'), &
recrba = @GETENV ('RECORD', 'FILERBA'), &
rectranspos = @GETENV ('RECORD', 'RSN'), &
reclength = @GETENV ('GGHEADAER', 'RECORDLENGTH'), &
logrba = @GETENV ('GGHEADER', 'LOGRBA'), &
logposition = @GETENV ('GGHEADER', 'LOGPOSITION'), &
grouptype = @GETENV ('GGENVIRONMENT', 'GROUPTYPE'), &
filename = @GETENV ('GGFILEHEADER', 'FILENAME'), &
fileno = @GETENV ('GGFILEHEADER', 'FILESEQNO'), &
srcrowid = @GETENV ('TRANSACTION', 'CSN'), &
SRCDBCHARSET = @GETENV ('GGFILEHEADER', 'DBCHARSET'), &
replag = @GETENV ('LAG', 'SEC'), &
cnt_cdr_conflicts = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_CONFLICTS'), &
cnt_cdr_resolutions = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_SUCCEEDED'), &
cnt_cdr_failed = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_FAILED')), &
ALLPARAMS OPTIONAL, ERROR REPORT, DBOP, TRACE ALL)
END;
MACRO #replicatetableCDR_KeyOnly
PARAMS (#source)
BEGIN
MAP #source, TARGET #source, &
COMPARECOLS(ON UPDATE KEY, ON DELETE KEY), &
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (UPDATEROWMISSING,(DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (DELETEROWMISSING,(DEFAULT, DISCARD));
MAP #source, #global_exception_table (), &
EXCEPTIONSONLY, INSERTALLRECORDS, COLMAP ( &
#exceptioncols () );
END;
MACRO #replicatetableCDR_KeyandDateUseMin_Threaded_ExTables
PARAMS (#source, #thread)
BEGIN
MAP #source, TARGET #source, THREAD (#thread), &
COMPARECOLS(ON UPDATE KEYINCLUDING (OGG_TS), ON DELETE KEYINCLUDING (OGG_TS)), &
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMIN(OGG_TS))), &
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMIN(OGG_TS))), &
RESOLVECONFLICT (UPDATEROWMISSING,(DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (DELETEROWMISSING,(DEFAULT, DISCARD));
MAP #source, TARGET #source*_EX, &
#logexceptioncolssqlexec (#thread), &
EXCEPTIONSONLY, INSERTALLRECORDS, COLMAP ( &
#exceptioncols () );
END;
MACRO #replicateschemaCDR_KeyOnly_Threaded_ExTables
PARAMS (#source, #target, #thread)
BEGIN
MAP #source.*, TARGET #target.*, THREAD (#thread), &
COMPARECOLS(ON UPDATE KEY, ON DELETE KEY), &
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (UPDATEROWMISSING,(DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (DELETEROWMISSING,(DEFAULT, DISCARD)),
MAPEXCEPTION (TARGET #target.*_EX, &
#logexceptioncolssqlexec (#thread), &
EXCEPTIONSONLY, INSERTALLRECORDS, COLMAP ( &
#exceptioncols () ));
END;
------------ start of Replicat Macro Library ----------------
Now, once the macro library written and has been included in the PRM file (as an INCLUDE), we can call the macro with parameters to do all the mappings. Below is an example of not using the macro call (typing in all the mappings). AFter that, there is an example of the macro call to do the mappings. This particular example is complex (a real one), this handles the mappings for like-to-like replication, but also logs all exceptions to a generic global exception and logs all the data from the exceptions into per base table exception tables (a copy of the base table to hold the data from the transaction as well as some additional columns to help identify the OGG information related to the transaction). This is a complex example, but shows the real power in using macros to do all the work for you.
Example, no macros used, all mappings are typed by hand (for each schema or table):
MAP GGTEST.*, TARGET GGTEST.*, &
MAPEXCEPTION (TARGET GGTEST.*_EX, &
SQLEXEC (SPNAME GGADMIN.LOGEXCEPTION, ID #idname, PARAMS ( &
rep_name = @GETENV ("GGENVIRONMENT", "GROUPNAME"), &
table_name = @GETENV ("GGHEADER", "TABLENAME"), &
errno = @GETENV ("LASTERR", "DBERRNUM"), &
errtype = @GETENV ("LASTERR", "ERRTYPE"), &
optype = @GETENV ("LASTERR", "OPTYPE"),
transind = @GETENV ("GGHEADER", "TRANSACTIONINDICATOR"), &
committimestamp = @STREXT(@GETENV ("GGHEADER", "COMMITTIMESTAMP"), 1, 19), &
reccsn = @GETENV ("TRANSACTION", "CSN"),
recseqno = @GETENV ("RECORD", "FILESEQNO"), &
recrba = @GETENV ("RECORD", "FILERBA"),
rectranspos = @GETENV ("RECORD", "RSN"), &
logrba = @GETENV ("GGHEADER", "LOGRBA"),
logposition = @GETENV ("GGHEADER", "LOGPOSITION"), &
grouptype = @GETENV ("GGENVIRONMENT", "GROUPTYPE"),
filename = @GETENV ("GGFILEHEADER", "FILENAME"), &
fileno = @GETENV ("GGFILEHEADER", "FILESEQNO"), &
srcdbcharset = @GETENV ("GGFILEHEADER", "DBCHARSET")), ALLPARAMS OPTIONAL, ERROR REPORT, DBOP, TRACE ALL), &
EXCEPTIONSONLY, INSERTALLRECORDS, COLMAP ( USEDEFAULTS, &
excp_date = @DATENOW(), &
rep_name = @GETENV ("GGENVIRONMENT", "GROUPNAME"), &
table_name = @GETENV ("GGHEADER", "TABLENAME"), &
errno = @GETENV ("LASTERR", "DBERRNUM"), &
errtype = @GETENV ("LASTERR", "ERRTYPE"), &
optype = @GETENV ("LASTERR", "OPTYPE"), &
transind = @GETENV ("GGHEADER", "TRANSACTIONINDICATOR"), &
committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), &
reccsn = @GETENV ("TRANSACTION", "CSN"), &
recseqno = @GETENV ("RECORD", "FILESEQNO"), &
recrba = @GETENV ("RECORD", "FILERBA"), &
rectranspos = @GETENV ("RECORD", "RSN"), &
logrba = @GETENV ("GGHEADER", "LOGRBA"), &
logposition = @GETENV ("GGHEADER", "LOGPOSITION"), &
grouptype = @GETENV ("GGENVIRONMENT", "GROUPTYPE"), &
filename = @GETENV ("GGFILEHEADER", "FILENAME"), &
fileno = @GETENV ("GGFILEHEADER", "FILESEQNO"), &
srcdbcharset = @GETENV ("GGFILEHEADER", "DBCHARSET")));
The next example are the same mappings (above) implemented by using a macro call with parameters. The macro uses the parameters to create the mappings with the correct schema names and thread number:
#replicateschemaCDR_KeyOnly_Threaded_ExTables (GGTEST, GGTEST, 2);
To continue with our replicats above and those configuration, each would look like the following for replicat #1 (with all the mappings) and without all the mappings by using macros instead:
Replicat #1:
----- Start of replicat PRM --------
REPLICAT rep1
SETENV (OGG_GROUP = "rep1")
SETENV (OGG_DB = "DBM")
SETENV (OGG_NUM = "1")
SETENV (OGG_TYPE = "replicat")
-- All parameter below are based on variables
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_?OGG_DB.inc
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_?OGG_DB.inc
-- Include Global DBOptions
INCLUDE ./dirprm/global_parms.?OGG_TYPE.inc
-- Global REPERROR Error handling
INCLUDE ./dirprm/global_error_handling.?OGG_TYPE.inc
-- Global DDLOptions
INCLUDE ./dirprm/global_ddloptions.?OGG_TYPE.inc
-- Marco Include file (for replicat)
INCLUDE ./dirprm/macros/macros_?OGG_TYPE.inc
-- Include Map/Target statements
-- INCLUDE ./dirprm/objects_?OGG_DB.?OGG_GROUP.?OGG_NUM.inc
-- (mappings are included below instead of the INCLUDE file)
MAP GGTEST.*, TARGET GGTEST.*, &
COMPARECOLS(ON UPDATE KEY, ON DELETE KEY), &
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (UPDATEROWMISSING,(DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (DELETEROWMISSING,(DEFAULT, DISCARD)),
MAPEXCEPTION (TARGET GGTEST.*_EX, &
SQLEXEC (SPNAME GGADMIN.LOGEXCEPTION, ID #idname, PARAMS ( &
rep_name = @GETENV ("GGENVIRONMENT", "GROUPNAME"), &
table_name = @GETENV ("GGHEADER", "TABLENAME"), &
errno = @GETENV ("LASTERR", "DBERRNUM"), &
errtype = @GETENV ("LASTERR", "ERRTYPE"), &
optype = @GETENV ("LASTERR", "OPTYPE"),
transind = @GETENV ("GGHEADER", "TRANSACTIONINDICATOR"), &
committimestamp = @STREXT(@GETENV ("GGHEADER", "COMMITTIMESTAMP"), 1, 19), &
reccsn = @GETENV ("TRANSACTION", "CSN"),
recseqno = @GETENV ("RECORD", "FILESEQNO"), &
recrba = @GETENV ("RECORD", "FILERBA"),
rectranspos = @GETENV ("RECORD", "RSN"), &
logrba = @GETENV ("GGHEADER", "LOGRBA"),
logposition = @GETENV ("GGHEADER", "LOGPOSITION"), &
grouptype = @GETENV ("GGENVIRONMENT", "GROUPTYPE"),
filename = @GETENV ("GGFILEHEADER", "FILENAME"), &
fileno = @GETENV ("GGFILEHEADER", "FILESEQNO"), &
srcdbcharset = @GETENV ("GGFILEHEADER", "DBCHARSET")), ALLPARAMS OPTIONAL, ERROR REPORT, DBOP, TRACE ALL), &
EXCEPTIONSONLY, INSERTALLRECORDS, COLMAP ( USEDEFAULTS, &
excp_date = @DATENOW(),
rep_name = @GETENV ("GGENVIRONMENT", "GROUPNAME"),
table_name = @GETENV ("GGHEADER", "TABLENAME"),
errno = @GETENV ("LASTERR", "DBERRNUM"),
errtype = @GETENV ("LASTERR", "ERRTYPE"),
optype = @GETENV ("LASTERR", "OPTYPE"),
transind = @GETENV ("GGHEADER", "TRANSACTIONINDICATOR"),
committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
reccsn = @GETENV ("TRANSACTION", "CSN"),
recseqno = @GETENV ("RECORD", "FILESEQNO"),
recrba = @GETENV ("RECORD", "FILERBA"),
rectranspos = @GETENV ("RECORD", "RSN"),
logrba = @GETENV ("GGHEADER", "LOGRBA"),
logposition = @GETENV ("GGHEADER", "LOGPOSITION"),
grouptype = @GETENV ("GGENVIRONMENT", "GROUPTYPE"),
filename = @GETENV ("GGFILEHEADER", "FILENAME"),
fileno = @GETENV ("GGFILEHEADER", "FILESEQNO"),
srcdbcharset = @GETENV ("GGFILEHEADER", "DBCHARSET")
));
MAP APP_S.*, TARGET APP_S.*, &
COMPARECOLS(ON UPDATE KEY, ON DELETE KEY), &
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (UPDATEROWMISSING,(DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (DELETEROWMISSING,(DEFAULT, DISCARD)),
MAPEXCEPTION (TARGET GGTEST.*_EX, &
SQLEXEC (SPNAME GGADMIN.LOGEXCEPTION, ID #idname, PARAMS ( &
rep_name = @GETENV ("GGENVIRONMENT", "GROUPNAME"), &
table_name = @GETENV ("GGHEADER", "TABLENAME"), &
errno = @GETENV ("LASTERR", "DBERRNUM"), &
errtype = @GETENV ("LASTERR", "ERRTYPE"), &
optype = @GETENV ("LASTERR", "OPTYPE"),
transind = @GETENV ("GGHEADER", "TRANSACTIONINDICATOR"), &
committimestamp = @STREXT(@GETENV ("GGHEADER", "COMMITTIMESTAMP"), 1, 19), &
reccsn = @GETENV ("TRANSACTION", "CSN"),
recseqno = @GETENV ("RECORD", "FILESEQNO"), &
recrba = @GETENV ("RECORD", "FILERBA"),
rectranspos = @GETENV ("RECORD", "RSN"), &
logrba = @GETENV ("GGHEADER", "LOGRBA"),
logposition = @GETENV ("GGHEADER", "LOGPOSITION"), &
grouptype = @GETENV ("GGENVIRONMENT", "GROUPTYPE"),
filename = @GETENV ("GGFILEHEADER", "FILENAME"), &
fileno = @GETENV ("GGFILEHEADER", "FILESEQNO"), &
srcdbcharset = @GETENV ("GGFILEHEADER", "DBCHARSET")), ALLPARAMS OPTIONAL, ERROR REPORT, DBOP, TRACE ALL), &
EXCEPTIONSONLY, INSERTALLRECORDS, COLMAP ( USEDEFAULTS, &
excp_date = @DATENOW(),
rep_name = @GETENV ("GGENVIRONMENT", "GROUPNAME"),
table_name = @GETENV ("GGHEADER", "TABLENAME"),
errno = @GETENV ("LASTERR", "DBERRNUM"),
errtype = @GETENV ("LASTERR", "ERRTYPE"),
optype = @GETENV ("LASTERR", "OPTYPE"),
transind = @GETENV ("GGHEADER", "TRANSACTIONINDICATOR"),
committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
reccsn = @GETENV ("TRANSACTION", "CSN"),
recseqno = @GETENV ("RECORD", "FILESEQNO"),
recrba = @GETENV ("RECORD", "FILERBA"),
rectranspos = @GETENV ("RECORD", "RSN"),
logrba = @GETENV ("GGHEADER", "LOGRBA"),
logposition = @GETENV ("GGHEADER", "LOGPOSITION"),
grouptype = @GETENV ("GGENVIRONMENT", "GROUPTYPE"),
filename = @GETENV ("GGFILEHEADER", "FILENAME"),
fileno = @GETENV ("GGFILEHEADER", "FILESEQNO"),
srcdbcharset = @GETENV ("GGFILEHEADER", "DBCHARSET")));
----- End of replicat PRM --------
Now the same configuration as above, except we use a macro call (or multiple macro calls) to do all the mappings and call the macro per schema/table to be replicated.
See how pretty and clean the PRM file is with these features being used (below). Compare this to the same configuration as above.
Replicat #1:
----- Start of replicat PRM --------
REPLICAT rep1
SETENV (OGG_GROUP = "rep1")
SETENV (OGG_DB = "DBM")
SETENV (OGG_NUM = "1")
SETENV (OGG_TYPE = "replicat")
-- All parameter below are based on variables
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_?OGG_DB.inc
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_?OGG_DB.inc
-- Include Global DBOptions
INCLUDE ./dirprm/global_parms.?OGG_TYPE.inc
-- Global REPERROR Error handling
INCLUDE ./dirprm/global_error_handling.?OGG_TYPE.inc
-- Global DDLOptions
INCLUDE ./dirprm/global_ddloptions.?OGG_TYPE.inc
-- Marco Include file (for replicat)
INCLUDE ./dirprm/macros/macros_?OGG_TYPE.inc
-- Include Map/Target statements
-- INCLUDE ./dirprm/objects_?OGG_DB.?OGG_GROUP.?OGG_NUM.inc
-- (mappings are included here instead of the INCLUDE file)
#replicateschemaCDR_KeyOnly_ExTables (GGTEST, GGTEST, 1);
#replicateschemaCDR_KeyOnly_ExTables (APP_S, APP_S, 2);
----- End of replicat PRM --------
Closing
We hope this gave you a quick but real insight into INCLUDE files, variables and macros. These are truly under utilized features of Oracle GoldenGate.
Certainly, the majority of benefit in using them is from the start, but even if a configuration is volatile, these can help streamline those changes (and reduce errors).
We also hope that Oracle will continue expand the capabilities of macros to include looping, evaluations (if-then-else or case) and variables inside macros.
Overall, Oracle GoldenGate is great product, and with a little practicing on some of its less known features, you can make configurations, especially for large implementations a cake walk to create and maintain thereafter. Good luck and start using INCLUDE files, variables and macros.
Next, we'll talk about the Event Marker System and how this can be used in situations where transactional volumes may be pushing the limits of the product. Here we are talking about
Updates and Deletes in the billions that need to be replicated along with other change data.
Good examples, Thank you for your time taken to post this valuable info.
ReplyDeleteCongratulations very good examples.
ReplyDeleteTkx for Share
Congratulations very good examples.
ReplyDeleteTkx for Share