11 Replies Latest reply: Jan 31, 2014 1:55 AM by onkar.nath RSS

    Goldengate replicat abbends on update

    jjk

      Hi everyone,

       

      GoldenGate Version 12.1.2.0.0

       

      Source:

      Extract Parameters:
      GGSCI (oravm-IN) 10> view params xorat1
      extract xorat1
      -- add exttrail ./dirdat/u1, extract xorat1, megabytes 10
      setenv (oracle_home=D:\app\oravm\product\11.2.0\dbhome_1)
      setenv (oracle_sid=oratest)
      userid gg01, password gg01
      exttrail ./dirdat/u1
      nocompressupdates
      ddl include mapped all
      table us01.*;
      

       

      Pump parameters

      GGSCI (oravm-IN) 11> view params porat1
      EXTRACT PORAT1
      USERID GG01, PASSWORD gg01
      PASSTHRU
      RMTHOST oravm-IN, MGRPORT 7809
      RMTTRAIL D:\app\oravm\product\11.2.0\gghome112_2\dirdat\u1
      TABLE US01.*;
      

       

      Replicat:

      GGSCI (oravm-IN) 6> view params rorat1
      -- add replicat rorat1, exttrail D:\app\oravm\product\11.2.0\gghome112_2\dirdat\u1, checkpointtable gg01.new_us01_chkpt_tbl
      REPLICAT rorat1
      USERID gg01, PASSWORD gg01
      ASSUMETARGETDEFS
      ddlerror default discard
      HANDLECOLLISIONS
      discardfile ./rep_discards.dsc
      MAP us01.*, TARGET us01.*;
      

       

       

       

      Problem:

       

      I created two tables:

       

      us01.table1

      create table us01.table1 (numcol number primary key, varcol varchar2(30));

       

      The table is getting created on target and I'm able to:

      - insert

      - delete

      - drop

       

       

      But problem occurs when I start updating, using below command:

      update us01.table1 set varcol='Three' where numcol=3

       

      2014-01-28 13:30:09  WARNING OGG-01154  SQL error 1403 mapping US01.TABLE1 to US01.TABLE1 OCI Error ORA-01403: no data found, SQL <UPDATE "US01"."TABLE1" x SET x."VARCOL" = :a1 WHERE x."NUMCOL" = :b0>.
      
      
      2014-01-28 13:30:09  WARNING OGG-01003  Repositioning to rba 3275 in seqno 0.
      
      
      Source Context :
        SourceModule            : [er.errors]
        SourceID                : [er/errors.cpp]
        SourceFunction          : [take_rep_err_action]
        SourceLine              : [682]
        ThreadBacktrace         : [12] elements
                                : [D:\app\oravm\product\11.2.0\gghome112_2\gglog.dll(?CreateMessage@CMessageFactory@@QEAAPEAVCMessage@@PEAVCSourceContext@@IZZ+0x886) [0x000007FEE5D90246]]
                                : [D:\app\oravm\product\11.2.0\gghome112_2\gglog.dll(?_MSG_ERR_MAP_TO_TANDEM_FAILED@@YAPEAVCMessage@@PEAVCSourceContext@@AEBV?$CQualDBObjName@$00@ggapp@gglib@ggs@@1W4MessageDisposition@CMessageFactory@@@Z+0x81) [0x000007FEE5D53661]]
                                : [D:\app\oravm\product\11.2.0\gghome112_2\replicat.exe(ERCALLBACK+0x7a77) [0x000000013F379B67]]
                                : [D:\app\oravm\product\11.2.0\gghome112_2\replicat.exe(ERCALLBACK+0x32de1) [0x000000013F3A4ED1]]
                                : [D:\app\oravm\product\11.2.0\gghome112_2\replicat.exe(ERCALLBACK+0x6eb9e) [0x000000013F3E0C8E]]
                                : [D:\app\oravm\product\11.2.0\gghome112_2\replicat.exe(_ggTryDebugHook+0xe8a3) [0x000000013F4A1A23]]
                                : [D:\app\oravm\product\11.2.0\gghome112_2\replicat.exe(_ggTryDebugHook+0xde80) [0x000000013F4A1000]]
                                : [D:\app\oravm\product\11.2.0\gghome112_2\replicat.exe(_ggTryDebugHook+0xe74d) [0x000000013F4A18CD]]
                                : [D:\app\oravm\product\11.2.0\gghome112_2\replicat.exe(ERCALLBACK+0x6ecc9) [0x000000013F3E0DB9]]
                                : [D:\app\oravm\product\11.2.0\gghome112_2\replicat.exe(CommonLexerNewSSD+0xbcca) [0x000000013F571E9A]]
                                : [C:\windows\system32\kernel32.dll(BaseThreadInitThunk+0xd) [0x00000000775C652D]]
                                : [C:\windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x21) [0x00000000777FC541]]
      
      
      2014-01-28 13:30:09  ERROR   OGG-01296  Error mapping from US01.TABLE1 to US01.TABLE1.
      

       

      In the discard file on replicat, I see that the value for numcol isn't captured:


      Oracle GoldenGate Delivery for Oracle process started, group RORAT1 discard file opened: 2014-01-28 11:11:49.310000
      Current time: 2014-01-28 13:30:09
      Discarded record from action ABEND on error 1403
      
      
      OCI Error ORA-01403: no data found, SQL <UPDATE "US01"."TABLE1" x SET x."VARCOL" = :a1 WHERE x."NUMCOL" = :b0>
      Aborting transaction on D:\app\oravm\product\11.2.0\gghome112_2\dirdat\p1 beginning at seqno 0 rba 3275
                               error at seqno 0 rba 3275
      Problem replicating US01.TABLE1 to US01.TABLE1
      Record not found
      Mapping problem with compressed update record (target format)...
      *
      NUMCOL =
      
      
      VARCOL = Three
      000000: 54 68 72 65 65                                  |Three           |
      
      
      *
      


      The same thing is visible in logdump also. No value for "NUMCOL" while update, but there is one while inserting and delete.

       

      Anyone faced this before ? Any help please ?

        • 1. Re: Goldengate replicat abbends on update
          onkar.nath

          JJK,

           

          the error is "ORA-01403: no data found" which simply means that in target database, there is no record for NUMCOL=3. Make sure that you have the committed record on target side and then try to perform the same update.


          -Onkar

          • 2. Re: Goldengate replicat abbends on update
            jjk

            Sorry, it's my fault for not providing the complete scenario.

             

            Source:

            SQL> select * from us01.table1;
            
                NUMCOL VARCOL
            ---------- ----------------------
                     1 one
                     2 two
                     3 Three
            SQL> desc us01.table1
            Name                                      Null?    Type
            ----------------------------------------- -------- ------------
            NUMCOL                                    NOT NULL NUMBER
            VARCOL                                             VARCHAR2(30)
            

             

            Target:

             

            SQL> list
              1* select * from us01.table1
            SQL> /
            
                NUMCOL VARCOL
            ---------- ------------------------------
                     1 one
                     2 two
                     3 three
            
            SQL> desc us01.table1
            Name                                      Null?    Type
            ----------------------------------------- -------- ------------
            NUMCOL                                    NOT NULL NUMBER
            VARCOL                                             VARCHAR2(30)
            

             

            Querying target:

             

            SQL> select * from us01.table1 where numcol=3;
            
                NUMCOL VARCOL
            ---------- ------------------------------
                     3 three
            

             

            After I gave statement "update us01.table1 set varcol='Three' where numcol=3;" the replicat abended on target with above error

             

            (Wish) it was as simple as you replied, then wouldn't have posted it at first place

            • 3. Re: Goldengate replicat abbends on update
              Baris Yildirim

              the first character on source is upper

               

              Soruce

                  Three

               

              Target

                   three

               

              you update

               

              update us01.table1 set varcol='Three'

               

              no data found error is normal

               

              Regards

              • 4. Re: Goldengate replicat abbends on update
                jjk

                I said that after I hit below command at source, the replicat abends on the target which will obviously cause the data in source and target to mismatch. That exactly is the problem

                After I gave statement "update us01.table1 set varcol='Three' where numcol=3;" the replicat abended on target with above error

                 

                And as shown above, when "select * from us01.table1 where numcol=3" is fired on target, it does return a row.

                • 5. Re: Goldengate replicat abbends on update
                  Baris Yildirim

                  Hi,

                  sorry why don't you  update manually on target to match data on source and target?

                   

                  Regards

                  • 6. Re: Goldengate replicat abbends on update
                    onkar.nath

                    JJK,

                     

                    Can you please check :

                     

                    1. if supplemental logging is enabled

                    2. check if you have ALLOWNOOPUPDATES in your prm file and if not then add it and restart the process and check

                     

                    Check this one:http://minersoracleblog.wordpress.com/2013/12/20/mapping-problem-with-compressed-update-record-key-column-missing-from-update/

                     

                    Replicat Abending With Mapping Error and discard file shows Missing Key Columns (Doc ID 1276538.1)

                     

                    Hope this helps.

                    • 7. Re: Goldengate replicat abbends on update
                      jjk

                      1. The supplemental logging is enabled.

                      2. I was not sure if ALLOWNOOPUPDATES will help, since that parameter is used when we get updates on target which doesn't affect it.

                       

                      anyways, I did put allownoopupdates and it didn't help

                       

                      The issue is for update, the discard file show below record:

                       

                      Oracle GoldenGate Delivery for Oracle process started, group RORAT1 discard file opened: 2014-01-28 11:11:49.310000

                      Current time: 2014-01-28 13:30:09

                      Discarded record from action ABEND on error 1403

                       

                       

                      OCI Error ORA-01403: no data found, SQL <UPDATE "US01"."TABLE1" x SET x."VARCOL" = :a1 WHERE x."NUMCOL" = :b0>

                      Aborting transaction on D:\app\jkeshwan\product\11.2.0\gghome112_2\dirdat\p1 beginning at seqno 0 rba 3275

                                               error at seqno 0 rba 3275

                      Problem replicating US01.TABLE1 to US01.TABLE1

                      Record not found

                      Mapping problem with compressed update record (target format)...

                      *

                      NUMCOL =                                                    >>>>> Problem, no value !!!??

                      VARCOL = Three

                      000000: 54 68 72 65 65                                  |Three           |

                       

                       

                       

                      ...

                      • 8. Re: Goldengate replicat abbends on update
                        Baris Yildirim

                        Hi,

                        if you have an unique key column on the table, Goldengate uses it to find the record which will update.

                        if you haven't, you should determine which columns accomplish  unique, you should use KEYCOL for this. if you don't, goldengate uses all columns on the table to find the record so you see that goldengate can't find your record in this case. Becuase three and Three.

                         

                        Regards

                        • 9. Re: Goldengate replicat abbends on update
                          jjk

                          NUMCOL is the primary key in both source as well as target ...

                          • 10. Re: Goldengate replicat abbends on update
                            onkar.nath

                            JJK,

                             

                            Can you please run below command and post the output (from both source and target):

                             

                            1. select dbms_metadata.get_ddl('TABLE','TABLE1','US01') from dual; -- I am sure you know this command to generate DDL.

                            2. Did you refer the Document I mentioned above.

                            3. Check if the PK is defined on both source and target (you have already mentioned that it but just cross check) and enabled and validated.

                             

                            can you please try this:

                             

                            MAP us01.tabel1, target us01.tabel1, keycols (numcol);


                            -Onkar

                            • 11. Re: Goldengate replicat abbends on update
                              Partha Sarathy S

                              Ok. Why dont you skip the particular transaction which is causing the error and do the update manually at the target, (if it is not bidirectional). But how you got a data mismatch between these two tables when they are in sync?