SHOWSYNTAX : Oracle GoldenGate Tool To Debug A Replicat

SHOWSYNTAX

Oracle GoldenGate has provided an excellent parameter to debug the Replicat process called SHOWSYNTAX. Obviously this should be used in the Target side (Replicat). This parameter is used to start an interactive session. That is, whenever an INSERT is replicated from Source to Target, before it is getting applied to the Target, the respective SQL statement will be displayed on screen. Upon using this parameter, you can view each and every Replicat SQL statement before it is applied.

This parameter is mainly used to debug the Replicat by stepping through each and every statement that are processed. So you can see every SQL statement which leads to diagnose the failure of replication in ease manner.

 

The Syntax is as below.,

SHOWSYNTAX [ APPLY | NOAPPLY ] [ INCLUDELOB [ max bytes | ALL ] ]

 

When a data type is applied with the Dynamic SQL statement, the Replicat only shows some or all the data in a form of a String, Hexadecimal form or as a data identifier. But by default, the Replicat will not show the LOB data types. The LOB data type in a SQL statement will be displayed as “<LOB data type>”.

 

Using this parameter displays the complete statement clearly. Even the Statements with LOB data types are displayed. This is achieved by adding a keyword to the parameter as below.,

 

SHOWSYNTAX INCLUDELOB 2M

 

It is always recommended to get the guidance from Oracle support before utilizing this parameter in your environment. Since this is a debugging parameter and needs complete manual intervention, using this parameter leads to Latency and Lag in the Replicat. But you can create a Replicat group in a test database similar to production and test it over there to avoid risk taking in production environment.

 

SHOWSYNTAX parameter is not supported by the Coordinated Replicat. It is supported by the Integrated Replicat. When this parameter is used in Integrated Replicat group, the sqltrace is enabled for the APPLY process which is associated with the respective Replicat group.

 

When SHOWSYNTAX parameter is used in the Replicat, the Replicat process cannot be started in the GGSCI prompt. It should be started in the command shell of Operating System.

replicat paramfile /ogg/dirprm/rep1.prm

Upon starting the Replicat process from GGSCI prompt you will get the below error.,

2015-05-02 11:19:37 ERROR OGG-01991 Start Replicat from the command shell of the operating system when using the SHOWSYNTAX parameter.

 

The below workouts gives an in-depth picture about this parameter.

 

Already Oracle Goldengate is configured. The Target / Replicat is configured with parameter SHOWSYNTAX in it.

Source – OGG1

Target – OGG2

The parameters are below.,

–EXTRACT GROUPNAME
EXTRACT EXT1
–SOURCE DB GG USERNAME AND PASSWORD
USERID ggadmin, PASSWORD oracle
–SOURCE TRAIL FILE PATH AND PREFIX
TRANLOGOPTIONS EXCLUDEUSER GGADMIN
DDL INCLUDE MAPPED
EXTTRAIL /vol3/ogg/dirdat/et
–SOURCE TABLE NAMES THAT ARE TO BE REPLICATED
TABLE scott.*;
–DATAPUMP PROCESS NAME
EXTRACT DMP1
–PROCESSING OPTION (PASSTHRU OR NOPASSTHRU)
PASSTHRU
–TARGET HOSTNAME,MANAGER PORT NUMBER
RMTHOST OGG2, MGRPORT 7809
–TARGET TRAIL FILE PATH AND PREFIX
RMTTRAIL /vol3/ogg/dirdat/et
–SOURCE TABLE NAME
TABLE scott.*;
–REPLICAT GROPUP NAME
REPLICAT REP1
–TARGET DB GG USERNAME AND PASSWORD
USERID ggadmin, PASSWORD oracle
–STRUCTURES OF PRIMARY AND SECONDARY ARE SAME
ASSUMETARGETDEFS
SHOWSYNTAX

–MAPPING SOURCE AND TARGET TABLES
MAP scott.*, TARGET scott.*;

 

Extract and Pump processes in Source are already started.

 

Capture1

 

As I mentioned earlier, start the Replicat process from OS command shell.,

[oracle@OGG2 ogg]$ ./replicat paramfile /vol3/ogg/dirprm/rep1.prm
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:50:41
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
Starting at 2015-05-02 15:24:59
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Wed Feb 2 18:40:23 EST 2011, Release 2.6.32-100.28.5.el6.x86_64
Node: OGG2.localdomain
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 12209
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2015-05-02 15:24:59 INFO OGG-03059 Operating system character set identified as UTF-8.
2015-05-02 15:24:59 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
REPLICAT REP1
USERID ggadmin, PASSWORD ******
SHOWSYNTAX
ASSUMETARGETDEFS
MAP scott.*, TARGET scott.*;
2015-05-02 15:24:59 INFO OGG-06451 Triggers will be suppressed by default.
2015-05-02 15:24:59 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/vol3/ogg/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE: 2G
CACHEPAGEOUTSIZE (default): 8M
PROCESS VM AVAIL FROM OS (min): 4G
CACHESIZEMAX (strict force to disk): 3.41G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
PL/SQL Release 11.2.0.4.0 – Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 – Production
NLSRTL Version 11.2.0.4.0 – Production
Database Language and Character Set:
NLS_LANGUAGE = “AMERICAN”
NLS_TERRITORY = “AMERICA”
NLS_CHARACTERSET = “WE8MSWIN1252”
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file /vol3/ogg/dirdat/et000000 at 2015-05-02 15:24:59
2015-05-02 15:24:59 INFO OGG-03522 Setting session time zone to source database time zone ‘GMT’.

You can clearly see the Run Time Messages in the above screen. The Replicat process also started.

Capture2

 

Note: scott.emp is a new table created and hence no records are there in it.

 

On the source side check the table scott.emp.,

Capture3

 

Now let us do some INSERT operations in the table scott.emp and check how it gets replicated to the Target side.,

Capture4

 

As soon as I insert a record in to the table emp, in the Replicat side, we will get a run time message. That is we can see the statement executed in the source side has been replicated to the Target and before applying to the target, it will be displayed as below.,

The record to be applied is displayed and it is waiting for the input. Once I give the input, the record will be applied to the target.,

Capture5

 

Now we can check on the Target side. The Replicat process is running but the record is not applied. As said earlier, it is waiting for our intervention or confirmation.

Capture6

 

There are two options displayed here.,

(S)top display, (K)eep displaying (default):

(S)top – Stops displaying the SQL statements on the screen.

(K)eep – Keeps displaying the SQL statements on the screen.

The default is (K)eep.

 

Now I had given the input as K and the SQL statement got applied to the Target emp table.

Capture7

 

 

Capture8

 

Now let us take a look about the Parameter SHOWSYNTAX keywords.,

SHOWSYNTAX [ APPLY | NOAPPLY ] [ INCLUDELOB [ max bytes | ALL ] ]

APPLY | NOAPPLY

This controls the Replication. Means, it controls whether or not the Replicat process applies the data which are displayed in the Run Time Message screen with SHOWSYNTAX to the Target table. If you use APPLY keyword, then the data is applied to the respective table. This is the default one. If NOAPPLY is specified, then the statements displayed in the screen are not applied to the Target table.

 

INCLUDELOB | ALL

When this keyword is used with the parameter, the LOB data types are also displayed in the SHOWSYNTAX screen. If this parameter is not used then only the data identifier will be displayed “<LOB data type>” as said earlier.

If ALL keyword is used, then it displays the entire data types with LOB.

 

maxbytes

This keyword can be mentioned either in K, M or G. It specifies the maximum length of the data that are displayed in the run time screen.

 

Few examples are below.,

 

SHOWSYNTAX INLCUDELOB 5M
 SHOWSYNTAX APPLY
SHOWSYNTAX NOAPPLY
SHOWSYNTAX INLCUDELOB 1G

Once after completing the debugging, we can stop the Replicat process by issuing the usual command STOP REPLICAT <REP_NAME> and start it again from the GGSCI prompt.

 

You may also like...

2 Responses

  1. Himachalapathy says:

    Excellent document on Showsyntax.

Leave a Reply

Your email address will not be published. Required fields are marked *