Goldengate ALLOWNOOPUPDATES error - Why it happens ?

Before you read this article , you might want to read my other 3 blogs -
Goldengate supplemental logging , keycols, logallsupcols testcase 1/2/3 

As I have explained , an update on the source can either trigger a GGSPKUpdate or a FieldComp record on the trail file.

Now ALLOWNOOPUPDATES is specific to FieldComp update.

A usual update statement consists of

update <table name> set <set clause> where <where clause> ;

The error happens when a replicat tries to construct the  'set clause' from a trail record after it has successfully constructed the 'where clause' .

Again as explained in the earlier blog, if the trail file does not have the column information which replicat needs (based on PK/UK on the target table or KEYCOLS in replicat parameter table) then the "where clause" construction part of the update will itself fail. All those failures are documented in the other blogs mentioned above.

Considering that it has gone past that part successfully, it has to construct the "set clause" of the update statement.

Usually if a particular column is picked by where clause that column should be skipped from set clause.

When replicat sees that all the columns in the trail file are used by the where clause then it reaches a condition that there are no more columns left to construct the "set clause". This is exactly the reason why we encounter a   ALLOWNOOPUPDATES.


Let me start with a simple example with logdump views to explain more clearly.

1.Create a table in Source and make sure a PK is built. A PK is needed to make sure any update goes as FieldComp.



SQL> create table junk(col1 number ,col2 number,col3 number,col4 number,col5 num
ber,col6 number)
  2  /

Table created.

SQL> alter table junk add constraint pk_junk primary key(col1)
  2  /

Table altered.

2.Make sure you add supplemental logging for ALL Columns -


GGSCI (titan.gautham.com) 239> add trandata scott.junk, allcols

Logging of supplemental redo data enabled for table SCOTT.JUNK.
TRANDATA for scheduling columns has been added on table 'SCOTT.JUNK'.TRANDATA for all columns has been added on table 'SCOTT.JUNK'.
GGSCI (titan.gautham.com) 240> 

GGSCI (titan.gautham.com) 240> info trandata scott.junk

Logging of supplemental redo log data is enabled for table SCOTT.JUNK.

Columns supplementally logged for table SCOTT.JUNK: ALL.

3.Ensure the table has arrived on target and drop the PK on the target .

The reason why we drop the PK on target is, that when replicat tries to construct the 'where clause' of the update, we want all the columns in the trail file to be used so that nothing is left for the 'set clause' 


SQL> desc junk
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 COL1                                      NOT NULL NUMBER
 COL2                                               NUMBER
 COL3                                               NUMBER
 COL4                                               NUMBER
 COL5                                               NUMBER
 COL6                                               NUMBER

SQL> alter table junk drop constraint pk_junk
  2  /

Table altered.

SQL> desc junk
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 COL1                                               NUMBER
 COL2                                               NUMBER
 COL3                                               NUMBER
 COL4                                               NUMBER
 COL5                                               NUMBER
 COL6                                               NUMBER

SQL>


4.Insert a record in the source and ensure record arrives in target


--source
SQL> insert into junk values(100,100,100,100,100,100);

1 row created.

SQL> commit;

Commit complete.

--target
SQL> select * from junk;

      COL1       COL2       COL3       COL4       COL5       COL6
---------- ---------- ---------- ---------- ---------- ----------
       100        100        100        100        100        100

We are not interested in seeing logdump for above insert as it does not serve us any purpose

5.Update the source -

SQL> update junk set col5=500 where col1=100;

1 row updated.

SQL> commit;

Commit complete.

Check the replicat -


GGSCI (titan.gautham.com) 242> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
JAGENT      STOPPED                                           
EXTRACT     RUNNING     xxxxx     00:00:09      00:00:01    
REPLICAT    ABENDED     yyyy     00:00:00      00:00:25    

Check the error from ggserr.log

2014-08-09 21:28:51  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.
2014-08-09 21:29:08  ERROR   OGG-01168  Oracle GoldenGate Delivery for Oracle, rphvn1d.prm:  Encountered an update for target table SCOTT1.JUNK, 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.
2014-08-09 21:29:17  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rphvn1d.prm:  PROCESS ABENDING.

I hope the above explanation is clear.

Obviously if we create a PK or UK on the target or use KEYCOLS in replicat , we should be able to start the replicat with no issues.