7 Replies Latest reply on Jun 13, 2012 4:07 PM by 942279

    Mapping problem with compressed key update record

    942279
      Hi, could you please advise?

      I'm getting the following problem:

      About a week ago replicat abened with "Error in mapping" error. I found in discard file some record looking like:
      filed1 = NULL
      field2 =
      field3 =
      field4 =
      field5 =
      datefield = -04-09 00:00:00
      field6 =
      field8 =
      field9 = NULL
      field10 =

      Where filed9 = @GETENV("GGHEADER", "COMMITTIMESTAM"), field10 = = @GETENV("GGHEADER", "COMMITTIMESTAM"), others are table fields mapped by USEDEFAULTS
      So I got Mapping problem with compressed key update record at 2012-06-01 15:44
      I guess I need to mention that extract failed in 5 minuts before it with: VAM function VAMRead returned unexpected result: error 600 - VAM Client Report <[CFileInfo::Read] Timeout expired after 10 retries with 1000 ms delay, waiting to read transaction log or backup files. To increase the number of retries, use SETENV (GGS_CacheRetryCount = n) in Extract parameter file. To control retry delay time, use SETENV (GGS_CacheRetryDelay = n). handle: 0000000000000398 ReadFile GetLastError:997 Wait GetLastError:997>.

      I don't know if it has ther same source as data corruption, could you tell me if it is?

      Well, I created new extract, starting 2012-06-01 15:30 to check if there was something with extract at the time, but got the same error.
      If I run extract beging at 15:52 it starts and works.

      But well, I got another one today. Data didn't look that bad, but yet one column came with null value:( And I'm using it as a key column, so I got Mapping problem with compressed key update record again:(
      I'm replicating from SQL Server 2008 to Oracle 11g.
      I'm actually using NOCOMPRESSUPDATES in Extract.
      CDC is enabled for all tables replicated. The only thing is that it is enabled not by ADD TRANDATA command, but by SQL Server sys.sp_cdc_enable_table, does it matter?

      Could you please advise why does it happen?
        • 1. Re: Mapping problem with compressed key update record
          Paddu kandimalla
          Please use ADD trandata for sql server also.
          Seems it is not having any primary key or unique. Goldengate is unable to handle the update statement where most of the columns are NULL.
          try to use keycols for the same table.
          • 2. Re: Mapping problem with compressed key update record
            942279
            Hi, Paddu, thanks for the answer!
            I have CDC up for SQL Server and all replicated tables, what do you mean?.. Doesn't CDC do the same as add trandata?
            Yeah, there is no primary key on the table, but I do have KEYCOLS for this table in Replicat parameter file.
            And I don't think that "most of the columns are NULL" is how the first case I got can be described:) I think, data looks more like corrupted >___< The only column with value is date column, but it is still wrong - year is lost.
            If I try using "INFO TRANDATA" for the table I get "Logging of supplemental log data is enabled for table ...", how do you think, is it something wrong with it?..
            • 3. Re: Mapping problem with compressed key update record
              amardeep.sidhu
              It is always recommended to enable trandata using ADD TRANDATA command. But in your case INFO TRANDATA shows logging as enabled so i guess there shouldn't be a problem there. Can you rephrase the problem you are hitting ? Is the Replicat Abended now ?

              If YES then post the contents of parameter file, report file and discard file.
              • 4. Re: Mapping problem with compressed key update record
                942279
                Well, the problem begins somewhere in extract or before extract, may be in transaction log, I don't know:(

                Here are extract parameters:
                EXTRACT ETCHECK
                TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
                SOURCEDB TEST, USERID **, PASSWORD *****
                exttrail ./dirdat/ec

                NOCOMPRESSUPDATES

                NOCOMPRESSDELETES

                TABLE tst.table1, COLS (field1, field2, field3, field4, field5, field6, field7, field8 );
                TABLE tst.table2, COLS (field1, field2, field3, field4 );

                Data pump:

                EXTRACT DTCHECK

                SOURCEDB TEST, USERID **, PASSWORD *****

                RMTHOST ***, MGRPORT 7809

                RMTTRAIL ./dirdat/dc

                TABLE tst.table1;
                TABLE tst.table2;

                Replicat:

                REPLICAT rtcheck
                USERID tst, PASSWORD ***
                DISCARDFILE ./dirrpt/rtcheck.txt, PURGE
                SOURCEDEFS ./dirdef/sourcei.def
                HANDLECOLLISIONS

                UPDATEDELETES

                MAP tst.table1, t.table1, COLMAP (USEDEFAULTS , filed9 = @GETENV("GGHEADER", "COMMITTIMESTAMP"), filed10= @CASE(@GETENV("GGHEADER", "OPTYPE"), "SQL COMPUPDATE", "U", "PK UPDATE", "U",@GETENV("GGHEADER", "OPTYPE")) ), KEYCOLS (field3);
                MAP dbo.TPROCPERIODCONFIRMSTAV, TARGET R_019_000001.TPROCPERIODCONFIRMSTAV, COLMAP (USEDEFAULTS , field5 = @GETENV("GGHEADER", "COMMITTIMESTAMP"), filed6= @CASE(@GETENV("GGHEADER", "OPTYPE"), "SQL COMPUPDATE", "U", "PK UPDATE", "U",@GETENV("GGHEADER", "OPTYPE")) ), KEYCOLS (filed1, field2, field3);

                Rpt file for replicat:

                ***********************************************************************
                Oracle GoldenGate Delivery for Oracle
                Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
                Windows x64 (optimized), Oracle 11g on Apr 22 2011 00:34:07

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


                Starting at 2012-06-05 12:49:38
                ***********************************************************************

                Operating System Version:
                Microsoft Windows Server 2008 R2 , on x64
                Version 6.1 (Build 7601: Service Pack 1)

                Process id: 2264

                Description:

                ***********************************************************************
                ** Running with the following parameters **
                ***********************************************************************
                REPLICAT rtcheck

                USERID tst, PASSWORD ***

                DISCARDFILE ./dirrpt/rtcheck.txt, PURGE

                SOURCEDEFS ./dirdef/sourcei.def

                HANDLECOLLISIONS



                UPDATEDELETES



                MAP tst.table1, t.table1, COLMAP (USEDEFAULTS , filed9 = @GETENV("GGHEADER", "COMMITTIMESTAMP"), filed10= @CASE(@GETENV("GGHEADER", "OPTYPE"), "SQL COMPUPDATE", "U", "PK UPDATE", "U",@GETENV("GGHEADER", "OPTYPE")) ), KEYCOLS (field3);
                MAP dbo.TPROCPERIODCONFIRMSTAV, TARGET R_019_000001.TPROCPERIODCONFIRMSTAV, COLMAP (USEDEFAULTS , field5 = @GETENV("GGHEADER", "COMMITTIMESTAMP"), filed6= @CASE(@GETENV("GGHEADER", "OPTYPE"), "SQL COMPUPDATE", "U", "PK UPDATE", "U",@GETENV("GGHEADER", "OPTYPE")) ), KEYCOLS (filed1, field2, field3);


                CACHEMGR virtual memory values (may have been adjusted)
                CACHEBUFFERSIZE: 64K
                CACHESIZE: 512M
                CACHEBUFFERSIZE (soft max): 4M
                CACHEPAGEOUTSIZE (normal): 4M
                PROCESS VM AVAIL FROM OS (min): 1G
                CACHESIZEMAX (strict force to disk): 881M

                Database Version:
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                PL/SQL Release 11.2.0.1.0 - Production
                CORE     11.2.0.1.0     Production
                TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
                NLSRTL Version 11.2.0.1.0 - Production

                Database Language and Character Set:
                NLS_LANG = "AMERICAN_AMERICA.CL8MSWIN1251"
                NLS_LANGUAGE = "AMERICAN"
                NLS_TERRITORY = "AMERICA"
                NLS_CHARACTERSET = "CL8MSWIN1251"

                For further information on character set settings, please refer to user manual.

                ***********************************************************************
                ** Run Time Messages **
                ***********************************************************************

                Opened trail file ./dirdat/dc000000 at 2012-06-05 12:49:39

                2012-06-05 12:58:14 INFO OGG-01020 Processed extract process RESTART_ABEND record at seq 0, rba 925 (aborted 0 records).

                MAP resolved (entry tst.table1):
                MAP tst.table1, t.table1, COLMAP (USEDEFAULTS , filed9 = @GETENV("GGHEADER", "COMMITTIMESTAMP"), filed10= @CASE(@GETENV("GGHEADER", "OPTYPE"), "SQL COMPUPDATE", "U", "PK UPDATE", "U",@GETENV("GGHEADER", "OPTYPE")) ), KEYCOLS (field3);

                2012-06-05 12:58:14 WARNING OGG-00869 No unique key is defined for table table1. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
                Using the following default columns with matching names:
                field1=field1, field2=field2, field3=field3, field4=field4, field5=field5, field6=field6, field7=field7, field8=field8

                Using the following key columns for target table R_019_000001.TCALCULATE: field3.


                2012-06-05 12:58:14 WARNING OGG-01431 Aborted grouped transaction on 'tst.table1', Mapping error.

                2012-06-05 12:58:14 WARNING OGG-01003 Repositioning to rba 987 in seqno 0.

                2012-06-05 12:58:14 WARNING OGG-01151 Error mapping from tst.table1 to tst.table1.

                2012-06-05 12:58:14 WARNING OGG-01003 Repositioning to rba 987 in seqno 0.

                Source Context :
                SourceModule : [er.main]
                SourceID : [er/rep.c]
                SourceFunction : [take_rep_err_action]
                SourceLine : [16064]
                ThreadBacktrace : [8] elements
                : [C:\App\OGG\replicat.exe(ERCALLBACK+0x143034) [0x00000001402192B4]]
                : [C:\App\OGG\replicat.exe(ERCALLBACK+0x11dd44) [0x00000001401F3FC4]]
                : [C:\App\OGG\replicat.exe(<RCALLBACK+0x11dd44) [0x000000014009F102]]
                : [C:\App\OGG\replicat.exe(<RCALLBACK+0x11dd44) [0x00000001400B29CC]]
                : [C:\App\OGG\replicat.exe(<RCALLBACK+0x11dd44) [0x00000001400B8887]]
                : [C:\App\OGG\replicat.exe(releaseCProcessManagerInstance+0x25250) [0x000000014028F200]]
                : [C:\Windows\system32\kernel32.dll(BaseThreadInitThunk+0xd) [0x000000007720652D]]
                : [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x21) [0x000000007733C521]]

                2012-06-05 12:58:14 ERROR OGG-01296 Error mapping from tst.table1 to tst.table1.

                ***********************************************************************
                * ** Run Time Statistics ** *
                ***********************************************************************

                Last record for the last committed transaction is the following:
                ___________________________________________________________________
                Trail name : ./dirdat/dc000000
                Hdr-Ind : E (x45) Partition : . (x04)
                UndoFlag : . (x00) BeforeAfter: A (x41)
                RecLength : 249 (x00f9) IO Time : 2012-06-01 15:48:56.285333
                IOType : 115 (x73) OrigNode : 255 (xff)
                TransInd : . (x03) FormatType : R (x52)
                SyskeyLen : 0 (x00) Incomplete : . (x00)
                AuditRBA : 44 AuditPos : 71176199289771
                Continued : N (x00) RecCount : 1 (x01)

                2012-06-01 15:48:56.285333 GGSKeyFieldComp Len 249 RBA 987
                Name: DBO.TCALCULATE
                ___________________________________________________________________

                Reading ./dirdat/dc000000, current RBA 987, 0 records

                Report at 2012-06-05 12:58:14 (activity since 2012-06-05 12:58:14)

                From Table tst.table1 to tst.table1:
                # inserts: 0
                # updates: 0
                # deletes: 0
                # discards: 1


                Last log location read:
                FILE: ./dirdat/dc000000
                SEQNO: 0
                RBA: 987
                TIMESTAMP: 2012-06-01 15:48:56.285333
                EOF: NO
                READERR: 0


                2012-06-05 12:58:14 ERROR OGG-01668 PROCESS ABENDING.

                Discard file:

                Oracle GoldenGate Delivery for Oracle process started, group RTCHECK discard file opened: 2012-06-05 12:49:39

                Key column filed3 (0) is missing from update on table tst.table1
                Missing 1 key columns in update for table tst.table1.
                Current time: 2012-06-05 12:58:14
                Discarded record from action ABEND on error 0

                Aborting transaction on ./dirdat/dc beginning at seqno 0 rba 987
                error at seqno 0 rba 987
                Problem replicating tst.table1 to tst.table1
                Mapping problem with compressed key update record (target format)...
                *
                filed1 = NULL
                field2 =
                field3 =
                field4 =
                field5 =
                datefield = -04-09 00:00:00
                field6 =
                field8 =
                field9 = NULL
                field10 =
                *

                Process Abending : 2012-06-05 12:58:14
                • 5. Re: Mapping problem with compressed key update record
                  942279
                  Do anybody have any ideas?(
                  • 6. Re: Mapping problem with compressed key update record
                    EmaxG
                    have you tried adding a supplemental log group that captures all the columns in the table?
                    • 7. Re: Mapping problem with compressed key update record
                      942279
                      Could you please explain what do you mean? CDC is up for this table and it worked ok for all others.