Troubleshooting Oracle GoldenGate Macro Error OGG-10143 in Extract Process

Oracle GoldenGate (OGG) macros are used to simplify repetitive tasks in parameter files. However, misconfigurations can lead to errors such as OGG-10143, which indicates an unrecognized parameter. This blog post covers the encountered error, its root cause, and the steps to resolve it.

Error Details

Extract Process Parameter File Snippet

LOGALLSUPCOLS
DDL INCLUDE MAPPED
#extracttableWithFiltering(ESTPDB.DRPEUWEB.ADDRESS_ESCORES_TBL,22,5,'ADDRESS_ID','NULL',ADDRESS_ID,'NULL')

Log File Output

Source Context :
  SourceModule            : [ggparam.util]
  SourceID                : [ggparam/paramutil.cpp]
  SourceMethod            : [generate_msg]
  SourceLine              : [942]
  ThreadBacktrace         : [14] elements
                          : [/u01/app/oracle/product/19c/ogg/libgglog.so(CMessageContext::AddThreadContext())]
                          : [/u01/app/oracle/product/19c/ogg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))]
                          : [/u01/app/oracle/product/19c/ogg/libgglog.so(_MSG_String_UInt32_String(CSourceContext*, int, char const*, unsigned int, char const*, CMessageFactory::MessageDisposition))]
                          : [/u01/app/oracle/product/19c/ogg/libggparam.so(ggs::ggparam::error::generate_msg(unsigned long))]
                          : [/u01/app/oracle/product/19c/ogg/libggparam.so()]
                          : [/u01/app/oracle/product/19c/ogg/libggparam.so()]
                          : [/u01/app/oracle/product/19c/ogg/extract(get_infile_params(ggs::gglib::ggapp::ReplicationContextParams&, ggs::gglib::ggdatasource::DataSourceParams&, ggs::gglib::ggdatatarget::DataTargetParams&, ggs::gglib::ggmetadata::MetadataContext&))]
                          : [/u01/app/oracle/product/19c/ogg/extract()]
                          : [/u01/app/oracle/product/19c/ogg/extract(ggs::gglib::MultiThreading::MainThread::ExecMain())]
                          : [/u01/app/oracle/product/19c/ogg/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))]
                          : [/u01/app/oracle/product/19c/ogg/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
                          : [/u01/app/oracle/product/19c/ogg/extract(main)]
                          : [/lib64/libc.so.6(__libc_start_main)]
                          : [/u01/app/oracle/product/19c/ogg/extract()]

2024-07-24 22:59:49  ERROR   OGG-10143  (drrte.prm) line 13: Parameter [#extracttableWithFiltering] is unrecognized. No parameter definition with that name could be found.

2024-07-24 22:59:49  ERROR   OGG-01668  PROCESS ABENDING.

Root Cause Analysis

The error OGG-10143 indicates that the macro #extracttableWithFiltering is not recognized. This can happen due to:

1. Macro Definition Not Included: The parameter file does not contain the Macro definition before calling it.
2. Syntax Issues in the Macro Definition: If the macro has incorrect syntax, it might not be parsed correctly.
3. Invalid Parameter Values: Passing incorrect values to the macro may lead to undefined behavior.
4. In our caes the Filtering was not enabled

Macro Definition

MACRO #extracttableWithFiltering
PARAMS (#source, #brand, #region, #pkcol1name, #pkcol2name, #pkcol1val, #pkcol2val)
BEGIN
TABLE #source, TOKENS(BRAND_ID = #brand, REGION_ID = #region, PKCOL1_NAME = #pkcol1name, PKCOL2_NAME = #pkcol2name, PKCOL1_VAL = #pkcol1val, PKCOL2_VAL = #pkcol2val)
);
END;

Solution

1. Ensure Filtering is Specified: Confirm that all necessary filtering parameters are defined correctly in the macro.
2. Ensure the Macro is Defined: Verify that the MACRO block is included before calling it in the parameter file.
3. Check Syntax and Formatting: Ensure correct syntax, particularly around PARAMS and TABLE clauses.
4. Validate Parameter Values: Ensure NULL is passed correctly. Consider using ” instead of NULL if required.

GGSCI> start extract drrte
GGSCI> view report drrte

Conclusion

The OGG-10143 error occurred because filtering parameters were not mentioned in the macro definition, causing the extract process to fail. Ensuring proper macro definition, including filtering parameters, syntax, and correct values helps prevent and resolve such issues. By following the troubleshooting steps above, the extract process should execute successfully without further errors.