INCLUDE files, variables and macro features of GoldenGate

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.

Good use case examples of the efficiency of Include files are:

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)

DISCARDFILE ./dirrpt/?OGG_GROUP.dsc, APPEND, MEGABYTES 10
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 #3 Parameter file:

REPLICAT rep3
.....
-- Database Parameters Include file
INCLUDE ./dirprm/db_target_DBM.inc

-- Global Parameters (for all processes)
INCLUDE ./dirprm/global_parms.inc

.....

Replicat #4 Parameter file:

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

-- 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

----- 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

-- Database Parameters Include file
INCLUDE ./dirprm/db_target_?OGG_DB.inc

-- Global Parameters (for all processes)
INCLUDE ./dirprm/global_parms.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

----- 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

-- 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

----- 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

-- 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

----- 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.

How do we monitor Oracle GoldenGate?

Update Aug-16:

The Perl-based custom monitoring script i has been a hit and therefore some small bugs/enhancements need to be addressed. Check this blog from time to time as I will start to update the gg_monitor.pl and the corresponding documentation to go with it as several enhancements have been made as a result of customer requests.


Original Blog Content:

This is a question that comes up repeatedly, and by that statement, I mean all the time!

I hope this blog will help highlight the options available from Oracle, provide enough information for you to plan a solution (ie, pick an approach or a product or two from the “pack”, test and validate the solution), and offer a couple of free methods that can help monitor Oracle GoldenGate by themselves or in conjunction with any of the Oracle products that will be highlighted.

Oracle GoldenGate is “file-system” centric. This statement does not imply that it does file-system replication (as it does not); it simply means that installation and setup/confguration, management and maintenance, files created and/or used within its processing, and monitoring of status/lag is generally tracked by files in the file system and accomplished via command-line utilities through an operating system shell.

The main tool of course is the “ggsci” utility; this is Oracle GoldenGate’s key command line interface utility used for administration/configuration and status/monitoring of Oracle GoldenGate processes.  Other tools may be involved in monitoring or troubleshooting, such as operating system utilities: ps, top, lsof, strace/prace, ls, tcpdump, etc to look at processes, files or process existence/activity on the system.  The OS tools of course depend on the platforms involved and the utilities available on the platform. 

Oracle GoldenGate has a number of other command line utilities (one being “logdump”), but these are not generally used in monitoring or inquiring about status information, they are used more so for troubleshooting or capacity planning.  We will not cover those tools here as they need their own space.

However, all the tools mentioned provide information on Oracle GoldenGate processes and files outside of a database. This is what we mean by “file-system centric”.  Even though Oracle GoldenGate may be replicating data from one database to another database, there is very little information that can be attained about its status (for classic processes) from inside the databases themselves (of course you have the checkpoint table in the target, if configured for that), we are simply making a generalization that monitoring Oracle GoldenGate is done outside the databases involved.

The statements above are true regardless of whether you are replicating Oracle-to-Oracle, something-to-Oracle, or Non-Oracle-to-Non-Oracle.  And, whether you are using a classic type Oracle GoldenGate process or integrated processes in Oracle databases (we will not go into detail about those here). This is changing with the new integrated processes (integrated capture/integrated apply).
Integrated processes do maintain views/tables in the database in which status information and run time information can be obtained. We will cover them in a different post for just integrated process types.

The point, most monitoring of Oracle GoldenGate is done outside the databases involved when using classic processes.

I highlight the monitoring solutions available and will also cover how to get a database-level view of process status, lag information and/or transaction statistics from inside the database.

The following products are available from Oracle for monitoring Oracle GoldenGate instances;  all of the following are part of the Oracle GoldenGate Management Pack. The Management Pack is highly recommended and being enhanced with every release by Oracle. It is a very feature rich monitoring tool (plug-in for OEM).

•        Oracle Enterprise Manager (OEM) Plug-in
•   called the OGG Plug-in from the Management Pack for Oracle GoldenGate
•        Oracle GoldenGate Monitor
• Referred to as the “standalone monitor”
• This tool can be used if not using or have OEM in the environment
  such as non-Oracle replication environments *Sybase, SQLServer, DB2
• OEM and the OEM Management pack is highly recommended
•        Oracle GoldenGate Director
•   only product that allows remote administration of Oracle GoldenGate instances
•    the new OEM plugin version is suppose to include this functionality

Other “free” methods of monitoring Oracle GoldenGate instance include the following:

•       Oracle GoldenGate Heartbeat
• Free, set of database objects and mappings to provide a heartbeat transaction
• Can be customized to make a solution that meaningful for you
• See the following MOS Notes
• Using A Heartbeat Table To Monitor LAG Between Source And Target 
• Doc ID 968710.1
• Oracle GoldenGate Best Practices: Heartbeat Table for Monitoring 
• Doc ID: 1299679.1
• Heartbeat will be embedded in future Oracle releases
• Oracle continues to enhance and add to the product
•       Custom Script(s)
•          Look for the free Perl script (gg_monitor.pl) you can use to monitor Oracle GoldenGate
•          Any script (written in Perl, shell such as Bourne/Korn/C, Java or a Windows scripting or any other language available on the platform) that you write to perform the “ggsci” commands and parse the resulting output according to your logic for determining status/performance
•        Scripts may also check on processes, files or system utilization at the operating system or file system level

Another valuable product that is not used for primarily for monitoring Oracle GoldenGate processes directly, but nonetheless is important tool for ensuring data synchronization (monitoring for logical corruption as a result of replication) and continued synchronization of data between source and target is:

•            Veridata

Veridata (another great product) is not licensed as part of the Management Pack for Oracle GoldenGate. It is a separate product and is therefore licensed separately from the Management Pack for Oracle GoldenGate (please be aware of this, don't let this stop you from using it though).

Each product/approach has its own page used to cover each solution in more detail and highlight the solution requirements, components and configuration involved and also highlight the benefits and drawbacks.  We will not cover the installation or usage details of each product here, as there is plenty of material available for that already.

Overall, thorough monitoring Oracle GoldenGate instances generally involves a hybrid approach, meaning, uses a combination of the products/approaches.

Most often, the heartbeat and a custom script is always put into the implementation (as a minimum in all uni or bi-directional implementations).  This keeps replication hot (live) and gives a database view of replication status from the target side (checkpoint table can also be used, but be cautious and never alter the checkpoint table structure or records), and offers some configurable thresholds for monitoring the processes and their status/lag/checkpoint delays. The heartbeat itself is not very useful for diagnosing or troubleshooting process failures, it is merely a quick status check which is an indication of delays or process failure. You can use it on a regular interval and be alerted when the interval updates have not been meet, and someone needs to dive in to investigate.  As customers grow their GoldenGate implementation base (and we hope you do) and in more complex implementations or in environments with many implementations, we tend to roll out one or two of the other products from Oracle mentioned above. These products offer a great visual view of the GoldenGate instances, their components/topology and how they integrate together (dependencies).