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

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

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.

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

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 and Pump processes in Source are already started.

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

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

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

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

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

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.

There are two options displayed here.,

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

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

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

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.