Replicating data can be a time consuming process to setup. Fortunately, Oracle GoldenGate provide a few tools to help ease the complexity of setup. One of these tools is the “macro”. Macros are used to simplify and automate the work associated with setting up and replicating data. So what exactly is a macro? Oracle defines a macro as:
A macro is a built-in automation tool that enables you to call a stored set of processing steps from within the Oracle GoldenGate parameter file.
In a nutshell, a macro is a stored set of commands that are used on a frequent basis; consisting of parameters for simple to complex series of substitutions, calculations or conversions. Macros may be written inline in the parameter file or stored in a macro library.
What this post will show you is how to add a macro to a library, accept parameters and then pass the parameter to within the parameter file during replication.
First thing that needs to be done is setup a standard directory to contain all the macro files. A directory called “dirmac” needs to be created in the OGG_HOME.
Note: The “dirmac” directory is something that you will need to create with the following command “mkdir -p $OGG_HOME/dirmac”. A macro directory can be created anywhere you like, I personally try to keep all OGG items together.
In the macro directory, create a file to use as the macro library. Ideally, you should have a file for each type of process running in that $OGG_HOME. Once the file is created, then edit the library file and add the macros desired.
> mkdir -p $OGG_HOME/dirmac > cd $OGG_HOME/dirmac > touch <library_name>.mac > vi <library_name>.mac
When the macro library file is open for editing add macro that is desired. Remember, a macro library can house more than one macro. In the example below, you will see two examples. The first example is setting a tokens that can be called. The second example is setting a macro to map tokens and header information to a specific table.
—Example 1— BEGIN SRC_CSN_TS = @GETENV(‘GGHEADER’,’COMMITTIMESTAMP’) END; MACRO #src_icnt BEGIN ICNT = @GETENV('STATS', 'INSERT') END; MACRO #src_ucnt BEGIN UCNT = @GETENV('STATS', 'UPDATE') END; MACRO #src_dcnt BEGIN DCNT = @GETENV('STATS', 'DELETE') END; MACRO #src_dmlcnt BEGIN DMLCNT = @GETENV('STATS', 'DML') END; —Example 2— MACRO #hb_mappings PARAMS (#src_schema) BEGIN MAP #src_schema.RANDOM_VALUES, target SCOTT.GG_REP_OP_STATUS, INSERTMISSINGUPDATES COLMAP ( SRC_DB_SCHEMA=@token('SRC_SCHEMA'), GG_REPLICAT_NAME=@GETENV('GGENVIRONMENT','GROUPNAME'), TGT_LAST_UPDATE_DT=@DATE('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV('JULIANTIMESTAMP')), SRC_CSN_TS=@token('SRC_CSN_TS'), ICNT=@token('ICNT'), UCNT=@token('UCNT'), DCNT=@token('DCNT'), DMLCNT=@token('DMLCNT') ); END;
Notice in example 2 the PARAMS statement. When using macros this can get a bit confusing since defining a macro uses the hash mark (#) and parameters in the PARAMS statement use the hash mark (#) as well. Also notice that the parameter #src_schema is used in the MAP statement in the macro. This is how the value for #src_schema is passed into the macro from the parameter files.
Now, lets take a look at a parameter file.
In my test environment, I have the following processes running:
GGSCI (oel.acme.com) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING JAGENT STOPPED EXTRACT RUNNING EXT 00:00:09 00:00:09 EXTRACT RUNNING PMP 00:00:00 00:00:07 REPLICAT RUNNING REP 00:00:00 00:00:04
Taking a look at the extract parameter file (apply side); in order to use the macro in example 1, add an INCLUDE statement which references macro library to the parameter file. Then in the TABLE statement, using the TOKEN string, the macro for the token can be referenced.
-- Verifies parameter file syntax. COMMENT OUT AFTER TESTING. --CHECKPARAMS --Specifies the name of the extract process EXTRACT EXT --Set Oracle Environment Variables SETENV (ORACLE_HOME="/oracle/app/product/22.214.171.124/dbhome_1") SETENV (ORACLE_SID="bc11g") --Oracle Login USERID ggate, PASSWORD ggate --Warns for a long running transaction WARNLONGTRANS 1h, CHECKINTERVAL 30m --Trace process info --TRACE ./dirrpt/trace_ext.trc --Specifies the location of the remote trail file on target machine EXTTRAIL ./dirdat/lt --Ignore transactions for golden gate user TRANLOGOPTIONS EXCLUDEUSER GGATE --Resolves the TABLES to be replicated ON START-UP WILDCARDRESOLVE IMMEDIATE <strong>INCLUDE ./dirmac/ops_info.mac</strong> --Table Mappings TABLE SCOTT.RANDOM_VALUES, TOKENS(<strong>#src_csn_ts(), #src_icnt(),#src_ucnt(),#src_dcnt(),#src_dmlcnt()</strong>);
Once the extract parameter file is updated, then the extract needs to be restarted. Upon restart of the extract, keep an eye out and make sure the process doesn’t abend. The VIEW REPORT command can be used to check the report file during startup. Once the extract starts, you can see how the macro is used and expanded in the parameter file. From the extract side, the needed info will be captured and placed in the associated trail files.
In the middle, the pump really doesn’t need any changes or restarting.
Now on the replicat (capture) side, in order to use the macro defined in example 2 (above); the replicat parameter file needs to be edited to include the macro library and statements to call the macros. The next code block shows the contents of my replicat parameter file.
--Specifies the name of the replicat load process. REPLICAT REP -- Verifies parameter file syntax. COMMENT OUT AFTER TESTING. --CHECKPARAMS SETENV (ORACLE_HOME="/oracle/app/product/126.96.36.199/dbhome_1") SETENV (ORACLE_SID="bc11g") --Oracle login. USERID ggate, PASSWORD ggate --surpress triggers - enable for 188.8.131.52 or later --DBOPTIONS SUPPRESSTRIGGERS ALLOWDUPTARGETMAP --The source ddl and target ddl are identical ASSUMETARGETDEFS --Tracing info --TRACE ./dirrpt/trace_rep.trc --Specifies name and location of a discard file. DISCARDFILE ./dirrpt/REP.dsc, append, megabytes 200 --Resolves the TARGETs to be replicated during process start-up --WILDCARDRESOLVE IMMEDIATE --Specify error handling rules: REPERROR(default, discard) REPERROR(default2, discard) --Table Mappings INCLUDE ./dirmac/ops_info.mac map SCOTT.RANDOM_VALUES, target SCOTT.RANDOM_VALUES_HIST; #hb_mappings(SCOTT);
You will notice that I have included the INCLUDE statement to call the macro library. Then the macro (example 2) that does the table mapping for the desired information can be accessed using #hb_mappings() (last line of parameter file example). Passing the schema name is simple by placing it in between the parenthesis. As the example above shows, I’m passing SCOTT as the schema I want to use.
Upon restart of the replicat, by looking at the report (VIEW REPORT), I can see where the macro library is read and how the macro is translated into a map statement for the replicat to use.
— Report Output (summerized)— map SCOTT.RANDOM_VALUES, target SCOTT.RANDOM_VALUES_HIST; #hb_mappings(SCOTT); MAP SCOTT.RANDOM_VALUES, target SCOTT.GG_REP_OP_STATUS, INSERTMISSINGUPDATES COLMAP ( SRC_DB_SCHEMA=@token('SRC_SCHEMA'), GG_REPLICAT_NAME=@GETENV('GGENVIRONMENT','GROUPNAME'), TGT_LAST_UPDATE_DT=@DATE('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV('JULIANTIMESTAMP')), SRC_CSN_TS=@token('SRC_CSN_TS'), ICNT=@token('ICNT'), UCNT=@token('UCNT'), DCNT=@token('DCNT'), DMLCNT=@token('DMLCNT') );
After the replicat has restarted. Then the table in the mapping statement can be checked to see if any data was inserted (SCOTT.GG_REP_OP_STATUS). Image 1 below shows the output of the data I requested to be replicated using the macro.
This should have shown you a way to use macros within your replication environment.
Current Oracle Certs
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.