GoldenGate – NOOPUPDATE

A no-op operation is one where there is no effect on the target table. If you have a simple configuration with one source and no filters, then you would think this doesn’t affect you, right? Well – wrong unfortunately.

From the Oracle GoldenGate documentation, a no-op operation can be caused by the following scenario –

An update is made that sets a column to the same value as the current one. The database does not log the new value, because it did not really change. However, Oracle GoldenGate extracts the operation as a change record because the primary key was logged — but there is no column value for the SET clause in the Replicat SQL statement.

The effect this has on the replicat process is quite disruptive –

GGSCI (gg2) 1> info all

Program     Status      Group        Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     RPTEST01     00:00:00      00:00:09

Within the report file, you can see the following error –

ERROR   OGG-01168  Encountered an update for target table TEST_SCHEMA.TEST_TABLE, which has no unique key defined.  KEYCOLS can be used to define a key.  Use ALLOWNOOPUPDATES to process the update without applying it to the target database.  Use APPLYNOOPUPDATES to force the update to be applied using all columns in both the SET and WHERE clause.

The test scenario I used to trigger this error was as follows –

SQL> desc test_table;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               VARCHAR2(50)

SQL> select * from test_table;

no rows selected

SQL> insert into test_table values (1,'Hello world');

1 row created.

SQL> select * from test_table;

      COL1 COL2
---------- --------------------------------------------------
         1 Hello world

SQL> update test_table set col2 = 'Hello world' where col1 = 1;

1 row updated.

This is enough to cause the replicat to abend. The source system reports that one row was updated, however no actual data changes were made because the column was updated to the same value that it was already set to.

The reason that replicat abends is more apparent when you take a look at the contents of the problematic trail record within the trail file. We can use logdump for this.

First find the RBA of the problem record within the report file –

Last log location read:
     FILE:      ./dirdat/ta000004
     SEQNO:     4
     RBA:       3004
     TIMESTAMP: 2012-04-20 13:27:58.687316
     EOF:       NO
     READERR:   0

Now view this record within the trail file –

[oracle@gg2 dirdat]$ ../logdump open ta000004

Oracle GoldenGate Log File Dump Utility
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.



Current LogTrail is /u01/app/oracle/gg1111/dirdat/ta000004
Logdump 87 >ghdr on
Logdump 88 >detail data
Logdump 89 >usertoken on
Logdump 90 >pos 3004
Reading forward from RBA 3004
Logdump 91 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    28  (x001c)   IO Time    : 2012/04/20 13:27:58.687.316
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        116       AuditPos   : 984592
Continued  :     N  (x00)     RecCount   :     1  (x01)

2012/04/20 13:27:58.687.316 FieldComp            Len    28 RBA 3004
Name: TEST_SCHEMA.TEST_TABLE
After  Image:                                             Partition 4   G  s
 0000 0005 0000 0001 3100 0100 0f00 0000 0b48 656c | ........1........Hel
 6c6f 2077 6f72 6c64                               | lo world
Column     0 (x0000), Len     5 (x0005)
 0000 0001 31                                      | ....1
Column     1 (x0001), Len    15 (x000f)
 0000 000b 4865 6c6c 6f20 776f 726c 64             | ....Hello world

Logdump 92 >

From the above, you can see the problem. Replicat is trying to update a value but the before image isn’t available. A working update will have both before and after values.

If we use the ALLOWNOOPUPDATES replicat parameter, the update will be counted in the process statistics but no actual update operation will run on the database. The replicat process will no longer abend.

If you have any concerns that using this parameter could allow problematic differences between source and target data to go undetected, take a look at this test case –

SQL> select * from test_table;

      COL1 COL2
---------- --------------------------------------------------
         1 Hello world

SQL> select * from test_table@target;

      COL1 COL2
---------- --------------------------------------------------
         1 Hello world

SQL> delete from test_table@target;

1 row deleted.

SQL> update test_table set col2 = 'Hello world, updated' where col1 = 1;

1 row updated.

As you can see from the above, I deliberately put the target and source data out of sync, and then tried to do an update on a row which was no longer present in the target data set. I’m pleased to say that this does indeed cause replicat to fail. A look into the discard file shows the following –

Problem replicating TEST_SCHEMA.TEST_TABLE to TEST_SCHEMA.TEST_TABLE
Record not found
Mapping problem with compressed key update record (target format)...
*
COL1 = 1
COL2 = Hello world
COL1 = 1
COL2 = Hello world, updated
*

So enabling this parameter won’t allow problematic differences between data on source and target to go unnoticed.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Comments

  • gjilevski  On October 12, 2012 at 6:39 PM

    Hi,

    That is interesting. I would like to clarify some points.

    1. Did you define PK/UK on source and target
    2. If not did you add trandata cols( all cols)

    I think you should do either one of the above as if you have had any before images you would not have faced the problem.

    OGG uses keys (PK, UK, KEYCOLS or all columns in this order) in conjunction with add trandata to log a key before image used for OGG to locate a row on the target in order to apply the change from the source ( for delete or update).

    Add trandata logs the key if any or you should use add trandata cols(col1,col2) to log all columns in order to have a before image to use to locate a row on the target.

    The compressed updates databases like Oracle log only the changes if you do not specify add trandata, but they do not log the key or keys(no matter they are PK, UK or KEYCOLS or all rows).

    With a ‘add trandata’ you will ask Oracle to log both keys and changes.

    If you have a key, in case of Oracle performing a compressed updates where only changes is logged in the tranlog, and use add trandata both the key and a change are written to the tranlog, extracted and written to the trail by default and replicated, assuming that the same key exists on the target.

    If you do not have a key all rows are used as an identifier and you should use add tandata cols(..)

    Seems to me that this looks like an expected behavior if there is no keys or add trandata cols(all..). Could you please clarify the keys or add trandata details?

    Regards,

    • matthewdba  On October 16, 2012 at 11:30 AM

      Just to follow this up, I’ve performed a little more testing.

      I created a new table and used GGSCI to “add trandata”. This created a supplemental log group with all columns being logged (no primary key at this stage). I then disabled the ALLOWNOOPUPDATE parameter in the target replicat.

      I inserted a test row and confirmed it had replicated. I then updated the value to be the same string, ie –

      INSERT INTO TEST_TABLE VALUES (1,1,’Hello’,’World’);
      UPDATE TEST_TABLE SET TEXTCOL = ‘Hello’ where ID = 1;

      This causes replicat to abend on the target (as demonstrated in my original post).

      *However*

      If you create a PK on the source and target, replicat won’t abend on the target regardless of whether it is a no-op update. In either case, the trail record is a compressed update. If the table is missing a PK, the compressed update record contains all columns. In both cases, it ONLY contains an after image.

      Interestingly, if you perform a no-op update on the PK column (updating a PK column is usually seen as bad practice, but I do see it happening on occasion) then replicat will still abend.

      So in summary, and in response to your question, with a PK in place the chance of replicat abending due to a no-op update is reduced but not eliminated.

  • matthewdba  On October 12, 2012 at 7:58 PM

    Hi,

    Thanks for the comments. This scenario is actually a demonstration of behavior discussed in MOS article 1144303.1, specifically the second example they give on how the no-op operation can occur. However, I couldn’t say for certainty that your suggestions wouldn’t change the outcome of the test.

    I’ll run through the variations in your post and let you know the outcome!

Trackbacks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: