Monthly Archive: September 2016

CHECKPRM – Oracle GoldenGate 12.2 New Feature

CHECKPRM – An utility to check and validate the parameter files

Prior to Oracle GoldenGate 12.2, there is no command or utility to check if the parameters which we had specified for the Extract (Primary and Secondary), Replicat and Manager processes are correct or not. We would only come to know once after we start the process. If there was some mistake in the parameter file as expected the process will abend and we will be finding the error in the report file of the abended process.

This was little difficult or uncomfortable or time extending process where we will be literally wasting time on checking the parameters manually.

From Oracle GoldenGate 12.2, a new utility is introduced called “CHECKPRM”. We can run this utility to check and validate our parameter files of the processes before the deployment avoiding errors like “Invalid Parameter” or Syntax Errors.

The syntax with complete options is below,

checkprm
[None]
[-v]
[? | help]
[parameter_file]
[-COMPONENT | -C) component_name]
[-MODE | -M) mode_name]
[-PLATFORM | -P) platform_name]
[-DATABASE | -D) database _ame]
[-VERBOSE | -V)]

To know more about the descriptions of the above options, you can refer the “Reference for Oracle GoldenGate for Windows and UNIX
12c (12.2.0.1)”. The link is below,

https://docs.oracle.com/goldengate/c1221/gg-winux/GWURF/GWURF.pdf

A simple syntax which is pretty more enough to check and validate the parameter files is below,

./checkprm ./dirprm/parameter_file_name

The checkprm resides under the Oracle GoldenGate Home directory (The directory where the OGG binaries are installed).

Below is the parameter file of my Integrated Extract Process.

GGSCI (OGGR2-1.localdomain) 8> view params INEXT

EXTRACT inext
SETENV ("ORACLE_SID=GGDB1")
SETENV ("ORACLE_HOME=/oracle/product/11.2.0.4/db_1")
USERID ggadmin, PASSWORD oracle
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100, PARALLELISM 2)
EXTTRAIL /ogg/dirdat/lt
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE veera.*;

To check if the above parameters of the INEXT process is valid, we can use the below checkprm utility as below,

1. Navigate to OGG_HOME directory. Here in my case the OGG_HOME directory is /ogg.

[oracle@OGGR2-1 ~]$ cd /ogg

2. Execute the below command and you can see the result,

[oracle@OGGR2-1 ogg]$ ./checkprm ./dirprm/inext.prm

2016-09-23 11:13:27  INFO    OGG-02095  Successfully set environment variable "ORACLE_SID=GGDB1".

2016-09-23 11:13:27  INFO    OGG-02095  Successfully set environment variable "ORACLE_HOME=/oracle/product/11.2.0.4/db_1".

2016-09-23 11:13:27  INFO    OGG-10139  Parameter file ./dirprm/inext.prm:  Validity check: PASS.

Runtime parameter validation is not reflected in the above check.

So in the above result, we can see the “Validity check: PASS”. So here the parameters which we had used have no issues and you safely go ahead and start the process.

Now, I am going to insert a invalid parameter to the parameter file of the INEXT process and check how the checkprm utility results. I have added the parameter ASSUMETARGETDEFS to the Extract Process INEXT. But this parameter is valid only for the REPLICAT process.

GGSCI (OGGR2-1.localdomain) 12> view params inext

EXTRACT inext
SETENV ("ORACLE_SID=GGDB1")
SETENV ("ORACLE_HOME=/oracle/product/11.2.0.4/db_1")
USERID ggadmin, PASSWORD oracle
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100, PARALLELISM 2)
EXTTRAIL /ogg/dirdat/lt
ASSUMETARGETDEFS
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE veera.*;

Now execute the checkprm utility to check and validate the parameter file as below,

[oracle@OGGR2-1 ogg]$ ./checkprm ./dirprm/inext.prm

2016-09-23 11:30:27  INFO    OGG-02095  Successfully set environment variable "ORACLE_SID=GGDB1".

2016-09-23 11:30:27  INFO    OGG-02095  Successfully set environment variable "ORACLE_HOME=/oracle/product/11.2.0.4/db_1".

(inext.prm) line 7: Parameter ['ASSUMETARGETDEFS'] is not valid for this configuration.

2016-09-23 11:30:27  INFO    OGG-10139  Parameter file ./dirprm/inext.prm:  Validity check: FAIL.

The results clearly shows that the “Validity check:FAIL”.

Let us check how it results if there is any spelling mistakes in the parameter file.,

GGSCI (OGGR2-1.localdomain) 16> view params inext

EXTRACT inext
SETENV ("ORACLE_SID=GGDB1")
SETENV ("ORACLE_HOME=/oracle/product/11.2.0.4/db_1")
USERID ggadmin, PASSWORD oracle
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100, PARALLELISM 2)
EXTRAIL /ogg/dirdat/lt
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE veera.*;

In the above parameter file of the process INEXT, there is a spell mistake for the parameter EXTTRAIL. Instead I had put it as EXTRAIL. Let us check how it results.,

[oracle@OGGR2-1 ogg]$ ./checkprm ./dirprm/inext.prm

2016-09-23 11:33:52  INFO    OGG-02095  Successfully set environment variable "ORACLE_SID=GGDB1".

2016-09-23 11:33:52  INFO    OGG-02095  Successfully set environment variable "ORACLE_HOME=/oracle/product/11.2.0.4/db_1".

(inext.prm) line 6: Parameter [EXTRAIL] is unrecognized and will be ignored.  No parameter definition with that name could be found.

2016-09-23 11:33:52  INFO    OGG-10139  Parameter file ./dirprm/inext.prm:  Validity check: FAIL.

ERROR OGG-02912 Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later.

ERROR OGG-02912  Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later.

There are some recommended patches which are needed to be applied to the database to use the Integrated Capture / Extract in Oracle GoldenGate. Even though after applying the recommended patch to the database, when starting the Integrated Extract process, the process abended with the below error,

2016-09-20 11:58:36 ERROR OGG-02912 Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later.
2016-09-20 11:58:36 ERROR OGG-01668 PROCESS ABENDING.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 2> info all

Program Status  Group  Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED INEXT  00:00:00      00:13:08

There is a work around available to overcome this issue. A script included known “prvtlmpg.plb” and this script will be under the Oracle GoldenGate installation directory.
Check tbe below, my OGG_HOME is /ogg,

[oracle@OGGR2-1 ogg]$ ls -lrt prvtlmpg.plb
-rw-r----- 1 oracle oinstall 9487 May 27 2015 prvtlmpg.plb
[oracle@OGGR2-1 ogg]$ pwd
/ogg
[oracle@OGGR2-1 ogg]$

Login to the database and just run this script,

[oracle@OGGR2-1 ogg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 20 12:00:42 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @prvtlmpg.plb

Oracle GoldenGate Workaround prvtlmpg

This script provides a temporary workaround for bug 17030189.
It is strongly recommended that you apply the official Oracle
Patch for bug 17030189 from My Oracle Support instead of using
this workaround.

This script must be executed in the mining database of Integrated
Capture. You will be prompted for the username of the mining user.
Use a double quoted identifier if the username is case sensitive
or contains special characters. In a CDB environment, this script
must be executed from the CDB$ROOT container and the mining user
must be a common user.

=========================== WARNING ==========================
You MUST stop all Integrated Captures that belong to this mining
user before proceeding!
================================================================

Enter Integrated Capture mining user: ggadmin

Installing workaround...
No errors.
No errors.
No errors.
Installation completed.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

You can see the message above that the “Installing workaround”. Once this is completed, you can start the Integated Extract process,

GGSCI (OGGR2-1.localdomain) 1> dblogin userid ggadmin, password oracle
Successfully logged into database.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 5> info all

Program Status  Group  Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING INEXT  00:14:06     00:00:05

The Integrated Extract process INEXT has started.

Report File of the INEXT
2016-09-20 12:10:24 INFO OGG-02089 Source redo compatibility version is: 11.2.0.4.0.

2016-09-20 12:10:24 INFO OGG-02086 Integrated Dictionary will be used.

2016-09-20 12:10:24 WARNING OGG-02901 Replication of UDT and ANYDATA from redo logs is not supported with the Oracle compatible parameter setting. Using fetch instead.

2016-09-20 12:10:25 INFO OGG-01052 No recovery is required for target file /vol3/ogg/dirdat/lt000000000, at RBA 0 (file not opened).

2016-09-20 12:10:25 INFO OGG-01478 Output file /vol3/ogg/dirdat/lt is using format RELEASE 12.2.

Oracle GoldenGate 12.2 New Feature – GETPARAMINFO

GETPARAMINFO
New parameter introduced from Oracle GoldenGate 12.2

This parameter will provide you the information of the parameters which are associated with the active running processes like Manager, Extract and Replicat. 
You can get the information of one single parameter of the running process or information of all the parameters.

SYNTAX

             SEND MGR | group GETPARAMINFO [parameter_name] [FILE output_file]

MGR - Manager process Name

group - Extract or Replicat Group name.

parameter_name - Name of the parameter of which the information is to be displayed.

FILE output_file - The output information can be either displayed in the screen or it can written to a file. To write or redirect the output information to a file use this option.

By default it will display the information of all the parameters used in the Extract, Replicat or Manager processes with respect to the request. Let us see by the examples below,

GGSCI (OGGR2-1.localdomain) 1> SEND EXTRACT INEXT GETPARAMINFO

Sending GETPARAMINFO request to EXTRACT INEXT ...

GLOBALS

checkpointtable : ggadmin.ggschkpt

/ogg/dirprm/inext.prm

extract : inext
userid : ggadmin
password : *******
tranlogoptions : <enabled>
integratedparams : (MAX_SGA_SIZE 100, PARALLELISM 2)
exttrail : /ogg/dirdat/lt
logallsupcols : <enabled>
updaterecordformat : COMPACT
table : veera.*

Default Values

deletelogrecs : <enabled>
fetchoptions :
userowid : <enabled>
usekey : <enabled>
missingrow : ALLOW
usesnapshot : <enabled>
uselatestversion : <enabled>
maxfetchstatements : 100
usediagnostics : <disabled>
detaileddiagnostics : <disabled>
diagnosticsonall : <disabled>
nosuppressduplicates : <enabled>
flushsecs : 1
passthrumessages : <enabled>
ptkcapturecachemgr : <enabled>
ptkcaptureift : <enabled>
ptkcapturenetwork : <enabled>
ptkcapturequeuestats : <enabled>
ptkspstats : <enabled>
tcpsourcetimer : <enabled>
tranlogoptions :
bufsize : 1024000
asynctransprocessing : 300
checkpointretentiontime : 7.000000
failovertargetdestid : 0
getctasdml : <disabled>
minefromsnapshotstby : <disabled>
usenativeobjsupport : <enabled>
warnlongtrans : 3600 second(s)
retrydelay : 60
allocfiles : 500
allowduptargetmap : <disabled>
binarychars : <enabled>
checkpointsecs : 10 second(s)
cmdtrace : OFF
dynamicresolution : <enabled>
eofdelay : 1
eofdelaycsecs : 100
functionstacksize : 200
numfiles : 1000
ptkcapturetablestats : <enabled>
ptkmaxtables : 100
ptktablepollfrequency : 1
varwidthnchar : <disabled>
enableheartbeat : <enabled>
ptkcaptureprocstats : <enabled>
ptkmonitorfrequency : 1
use_traildefs : <enabled>
.

To get the information of a particular parameter of an Active running Extract process INEXT,

GGSCI (OGGR2-1.localdomain) 2> SEND EXTRACT INEXT GETPARAMINFO TRANLOGOPTIONS

Sending GETPARAMINFO request to EXTRACT INEXT ...

/ogg/dirprm/inext.prm

tranlogoptions : <enabled>
integratedparams : (MAX_SGA_SIZE 100, PARALLELISM 2)

Default Values

tranlogoptions :
bufsize : 1024000
asynctransprocessing : 300
checkpointretentiontime : 7.000000
failovertargetdestid : 0
getctasdml : <disabled>
minefromsnapshotstby : <disabled>
usenativeobjsupport : <enabled>
.

Oracle GoldenGate 12.2 New Feature – INFO PARAM

INFO PARAM
New command introduced in Oracle GoldenGate 12.2

Displays the parameter definition. It is similar to the "man" command in the Linux OS. As the man command in Linux displays the Description, options etc, the INFO PARAM command also displays the full information of the parameter which is specified. The child options which are available for this command is listed in the "OPTIONS" tab.

The below are the list of information displayed for the specified parameter in the command,

param name 
description
argument 
default 
options 
component(s) 
mode(s) 
platform(s)
versions 
database(s)
status 
mandatory 
dynamic 
relations

SYNTAX

           INFO PARAM <parameter_name>

If you need the information of the particular option of the parameter, then the command would be as below,

           INFO PARAM <parameter_name>.<option_nanme>

Check the below example,

GGSCI (OGGR2-1.localdomain) 1> INFO PARAM RMTHOST

param name : rmthost
description : Name or IP address of the remote host.
argument : string
default : no default
options : compress, compressthreshold, cpu, encrypt, hometerm, keyname, mgrport
 nostreaming, params, password, port, pri, processname, socksproxy
 streaming, tcpbufsize, tcpflushbytes, timeout, user
component(s): EXTRACT
mode(s) : all Extract modes
platform(s) : all platforms
versions :
 min ver : 10.1.0.1
database(s) : all supported databases (on the supported platforms).
status : current
mandatory : false
dynamic : false
relations : none

To get the information of the particular option available for this parameter RMTHOST, use the below command.,

For example, option - COMPRESS

GGSCI (OGGR2-1.localdomain) 2> INFO PARAM RMTHOST.COMPRESS

param name : rmthost.compress
description : This option is valid for online or batch Extract processes and any Oracle GoldenGate initial-load method that uses trails. Compresses outgoing blocks of records to reduce bandwidth requirements. 
Oracle GoldenGate decompresses the data before writing it to the trail. COMPRESS typically results in compression ratios of at least 4:1 and sometimes better. However, compressing data can consume CPU resources.
argument : no argument
options :
component(s): EXTRACT
mode(s) : all Extract modes
platform(s) : all platforms
versions :
 min ver : 10.1.0.1
database(s) : all supported databases (on the supported platforms).
status : current
mandatory : false
dynamic : false
relations : none