Using ShowSyntax in Oracle GoldenGate 12.2

In order to understand what Oracle GoldenGate is doing under the covers you can use the parameter SHOWSYNTAX.  The SHOWSYNTAX parameter allows you to see the actual SQL statement is that the replicat process is about to apply.  SHOWSYNTAX in Oracle GoldenGate 12.2 is used in interactive mode and does not work with coordinated replicat.

I used this recently in order to do some testing with non-primary key tables in GoldenGate in order to see what is actually happening.  Let’s look at an example where this can be very useful.

As a GoldenGate architect and user I wish to understand how GoldenGate works.  Specifically, how does GoldenGate use primary keys and how does it handle tables with no primary key.  So, let’s try a few experiments.

I have created two tables on a PDB in my test environment.  The table test1 has no primary key and table test2 has a primary key.

SQL> create table test1 (col1 int, col2 int, col3 int, col4 int, col5 int);

SQL> create table test2 (col1 int, col2 int, col3 int, col4 int, col5 int, primary key (col1));

I have inserted into test 1 and test 2 a row of all 1’s.  Now as an experiment I want to modify both rows to change col 5 to a 2 where col 2 is equal to 1.

SQL> update test1 set col5=2 where col2=1;

SQL> update test1 set col5=2 where col2=1;

Now, what is GoldenGate going to do with this.  I’d really like to know.

Here’s how I’m going to find out.  In the parameter file for the replicat that handles this table I will add the parameter SHOWSYNTAX.  Then I will run the replicat from the command line as follows:

$ replicat paramfile dirprm/rtest1.prm

Depending on the type of replicat that you are running you will see a different result.  If you are using a classic replicat you will be placed into interactive mode where you will be prompted to view and proceed to the next record as shown here:

UPDATE “TEST”.”TEST1″ x SET x.”COL1″ = ‘1’,x.”COL2″ = ‘1’,x.”COL3″ = ‘1’,x.”COL4″ = ‘1’,x.”COL5″ = ‘2’ WHERE x.”COL1″=’1′ AND x.”COL2″=’1′ AND x.”COL3″=’1′ AND x.”COL4″=’1′ AND x.”COL5″=’1′ AND ROWNUM = 1

Statement length: 204

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

UPDATE “TEST”.”TEST2″ x SET x.”COL2″ = ‘1’,x.”COL3″ = ‘1’,x.”COL4″ = ‘1’,x.”COL5″ = ‘2’ WHERE x.”COL1″=’1′

Statement length: 106

So, I see that for the table with the primary key value on COL1 (test2), that column is the only one used in the WHERE clause, as expected.  The table with no primary key is also as expected, all of the columns in the table are found in the WHERE clause as well as all of the columns are set in the UPDATE statement.

If you are using Integrated Replicat the results are a little different.  You must still start up from the command line as shown above, however you will not be able to run in interactive mode.  You will see this message:

2016-09-05 12:02:18  INFO    OGG-02534  SHOWSYNTAX is not interactive in integrated mode.  Refer to the Oracle RDBMS trace files for the SQL trace output.

When using integrated replicat you will not see the transactions interactively.  You will need to go to the trace directory in the Automatic Diagnostic Repository (ADR) on the database system and grep for UPDATE in order to find the updates.  Since the Oracle Server Process is not the same as the replicat process you will not be able to directly correlate the Process ID of the replicat with the process ID of the Oracle process.

In order to find the process ID look in v$session for Apply Server.

SQL> select action, process from v$session where action like ‘%Apply Server%’;

 

ACTION                                                           PROCESS

—————————————————————- ————————

OGG$RTEST2 – Apply Server                                        5522

OGG$RTEST3 – Apply Server                                        5537

OGG$RTEST1 – Apply Server                                        6139

So, knowing that I was using the Replicat RTEST1 I will need to look for the trace file with 6139 in it.

When I edit gg21bdb_as02_6139.trc and search for UPDATE.  In doing so I found the following entries.  This is not all of the data, but just a snippet that shows the UPDATE statements.

PARSING IN CURSOR #139702572772960 len=222 dep=1 uid=106 oct=6 lid=106 tim=4510399038 hv=530092277 ad=’677939e8′ sqlid=’92wazw0gtj47p’

UPDATE /*+ restrict_all_ref_cons */ “TEST”.”TEST1″ SET “COL1″=:1   ,”COL2″=:2   ,”COL3″=:3   ,”COL4″=:4   ,”COL5″=:5    WHERE “COL1″=:6    AND “COL2″=:7    AND “COL3″=:8    AND “COL4″=:9    AND “COL5″=:10   and rownum < 2

END OF STMT

 

PARSING IN CURSOR #139702572882808 len=126 dep=1 uid=106 oct=6 lid=106 tim=4510401728 hv=4056931630 ad=’6778ee98′ sqlid=’3283kw7swzr9f’

UPDATE /*+ restrict_all_ref_cons */ “TEST”.”TEST2″ SET “COL2″=:1   ,”COL3″=:2   ,”COL4″=:3   ,”COL5″=:4    WHERE “COL1″=:5

END OF STMT

So, in this blog I have showed both the value of SHOWSYNTAX and how to view SHOWSYNTAX output in both integrated and non-integrated modes.

I hope that you find some value in this blog.