Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication

3132468

    Hello,

     

    I am getting following issues in my GoldenGate Replication Implementation:


    Golden Gate Version ==Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

     

    Database Version: Oracle Database 10g Release 10.2.0.1.0 - 64bit Production


    Discard file Record


    Issue#1 << ORA-01403 >>

    OCI Error ORA-01403: no data found, SQL <UPDATE /*+ RESTRICT_ALL_REF_CONS */ "SEVADM"."ATMSTATUS" SET "BANK" = :a4,"VALIDATIONSTATUS" = :a5,"POSTINGSTATUS" = :a6,"GO_OFFLINE" = :a7 WHERE "BANK" = :b0 AND "VALIDATIONSTATUS" = :b1 AND "POSTINGSTATUS" = :b2 AND "GO_OFFLINE" = :b3 AND ROWNUM = 1>

    Operation failed at seqno 3241 rba 54218185

    Discarding record on action TRANSDISCARD on error 1403

    Record not found

    Aborted compressed key update from sevadm.ATMSTATUS to SEVADM.ATMSTATUS (target format)...

     

    Issue#2 <<i am getting error for Insert >>

     

    Begin TRANSDISCARD

    Operation at seqno 3241 rba 54217857

    Record not found

    Aborted insert from sevadm.ATMACQUIREHIST to SEVADM.ATMACQUIREHIST (source format)...

     

    Has anyone had same problem? Please help how to fix this problem.

     

    Thanks

      • 1. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
        Maheswar

        Hi

         

        please provide replicate parameter file.

         

        Thanks

        Mahesh

        • 2. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
          2788865

          Hi

           

          This is the Replicat param file

           

           

          replicat repcbs

          setenv (ORACLE_SID=seva)

          userid ggs_owner,password ggs_owner

          batchsql batchesperqueue 100,opsperbatch 2000

          grouptransops 2000

          dboptions deferrefconst

          allownoopupdates

          assumetargetdefs

          discardfile /home/oracle/ogg/dirdat/discard/repcbs_discard_3DEC15.dsc,append,megabytes 2000

          statoptions resetreportstats

          reportcount every 2 hours,rate

          report at 01:00

          reportrollover at 01:15

          discardrollover at 02:00 on sunday

          ddl include all

          ddlerror default ignore retryop

          ddloptions report

          REPERROR 1403 TRANSDISCARD

          reperror (-1,IGNORE)

          REPERROR(DEFAULT,EXCEPTION)

          REPERROR(DEFAULT2,DISCARD)

          DYNAMICRESOLUTION

          WILDCARDRESOLVE IMMEDIATE

          --mapexclude sevadm.emailschedule

          mapexclude sevadm.da_amounts

          mapexclude sevadm.MONTHLY_SAL_ALL_DED

          mapexclude sevadm.VARIOUSALLOWANCESWITHWAGES

          mapexclude sevadm.MONTHLY_SALARY_FILE

          mapexclude sevadm.lwpdata

          mapexclude sevadm.sumlwpdata

          mapexclude sevadm.transferdetail

          mapexclude sevadm.transferheader

          mapexclude sevadm.transactions

          mapexclude SEVADM.VIJAYAPBSINPUT

          mapexclude SEVADM.ARR_DED

          mapexclude sevadm.tableisn

          mapexclude sevadm.ecsupload

          mapexclude sevadm.ecsdatafile

          --mapexclude sevadm.vijay*

          --mapexclude sevadm.sentmessages

          mapexclude sevadm.VARIOUSDEDUCTIONSWITHWAGES

          mapexclude sevadm.PTEARNINGS

          mapexclude sevadm.VIJAYSCRUTINYUCB

          mapexclude sevadm.VIJAYCDMTOCOMFILE

          mapexclude sevadm.REGISTRATION

          mapexclude sevadm.ACDOCUMENTS

          mapexclude SEVADM.TEMPIWCLGREG

          mapexclude SEVADM.DOCSFORSCHEME

          mapexclude SEVADM.ADHARDETAILS

          --mapexclude sevadm.REPPARAMETERS

          --mapexclude sevadm.LEAVE_TRANSACTION

          --mapexclude SEVADM.LEAVE_BALANCE

          --mapexclude SEVADM.VIJAYREGISTRATION

          mapexclude sevadm.TXNHISTORY

          mapexclude sevadm.USEROPERNTRACKING

          map sevadm.LOCKTABLE,target sevadm.LOCKTABLE,keycols(BRANCH,HANDLEID,USERCODE),REPERROR(1403 TRANSDISCARD);

          map sevadm.*,target sevadm.*;

          --map sevadm.* target ggs_owner.exceptions_table

          --COLMAP ( replicat = "REPCBS"

          --, table_name = @GETENV ("GGHEADER", "TABLENAME")

          --, errno = @GETENV ("LASTERR", "DBERRNUM")

          --, db_err_msg = @GETENV ("LASTERR", "DBERRMSG")

          --, op_type = @GETENV ("LASTERR", "OPTYPE")

          --, error_type = @GETENV ("LASTERR", "ERRTYPE")

          --, logrba = @GETENV ("GGHEADER", "LOGRBA")

          --, log_posn = @GETENV ("GGHEADER", "LOGPOSITION")

          --, commit_ts = @GETENV ("GGHEADER", "COMMITTIMESTAMP"))

          --, INSERTALLRECORDS

          --, EXCEPTIONSONLY;

           

           

          Thanks

          • 3. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
            ORASCN

            Hi ,

             

            1. Whether your supplemental logging is enabled for the table?

            2. Whether the table has a primary key?

            3. Have you done your Initial Load successfully before starting the Change Data Replication?

            4. A discard file will be created during this Abend. Please check the discard file and check the before image and after image of this record.

            5.Please do not use SKIPTRANSACTIONS until it is necessary. For how many transactions will you skip the Replicat from applying it on the target. It will be a data loss for you and not a recommended one.

             

            These problems occurs only because of the absence of the Primary key, proper supplemental logging not enabled and mainly when initial load is not done. Before starting any Online Change Replication, one should sync the

            Target with the Source.

             

            Check if any discard file is generated in the Target side. If yes, check what is the record discarded and frame a select query in it. Execute it on both source and target side. Check the data over there.

             

            Regards,

            Veera

            • 4. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
              3132468

              Hi ,

               

               

              1) supplemental logging is enabled for all the table .

              2) some tables has primary key  .

               

               

              i am try to sync the source and target by using export/import .

               

               

               

               

              thanks

              • 5. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
                3132468

                Hi

                 

                 

                1) Supplemental logging is enabled for all table

                 

                 

                i have sync the source DB and target DB .

                 

                Could any one  please explain  the meaning of  warning  ?

                 

                This is the report file for replicat group

                 

                 

                2015-12-08 23:04:21  WARNING OGG-01004  Aborted grouped transaction on 'SEVADM.ATMSTATUS', Database error 1403 (OCI Error ORA-01403: no data found, SQL <UPDATE /*+ RESTRICT_ALL_REF_CONS */ "SEVADM"."ATMSTATUS" SET "BANK" = :a4,"VALIDATIONSTATUS" = :a5,"POSTINGSTATUS" = :a6,"GO_OFFLINE" = :a7 WHERE "BANK" = :b0 AND "VALIDATIONSTATUS" = :b1 AND "POSTINGSTATUS" = :b2 AND "GO_OFFLINE" = :b3 AND ROWNUM = 1>).

                 

                2015-12-08 23:04:21  WARNING OGG-01003  Repositioning to rba 34584602 in seqno 3353.

                 

                2015-12-08 23:04:21  WARNING OGG-01003  Repositioning to rba 34586084 in seqno 3353.

                 

                2015-12-08 23:04:21  INFO    OGG-01139  BATCHSQL resumed, recovered from error.

                 

                2015-12-08 23:04:22  INFO    OGG-01018  Recovered from error at rba 34621598 in seqno 3353, replicat continuing.

                 

                2015-12-08 23:04:24  WARNING OGG-00869  Aborting BATCHSQL transaction. Detected inconsistent result: executed 1 operations in batch, resulting in 0 affected rows.

                 

                2015-12-08 23:04:24  WARNING OGG-01137  BATCHSQL suspended, continuing in normal mode.

                 

                2015-12-08 23:04:24  WARNING OGG-01003  Repositioning to rba 34621598 in seqno 3353.

                 

                2015-12-08 23:04:24  WARNING OGG-01004  Aborted grouped transaction on 'SEVADM.PUSHMESSAGE', Database error 1403 (OCI Error ORA-01403: no data found, SQL <DELETE /*+ RESTRICT_ALL_REF_CONS */ FROM "SEVADM"."PUSHMESSAGE"  WHERE "MOBILENUMBER" = :b0 AND "CMD_CODE" is NULL AND "MESSEGE" = :b2 AND "MODULETYPE" = :b3 AND "SCHEMECODE" = :b4 AND "ACCOUNTNUMBER" = :b5 AND "SENT" = :b6 AND ROWNUM = 1>).

                 

                 

                ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                 

                This is Discard file for Replicat group .

                 

                 

                Issue#1 << ORA-01403 >>

                OCI Error ORA-01403: no data found, SQL <UPDATE /*+ RESTRICT_ALL_REF_CONS */ "SEVADM"."ATMSTATUS" SET "BANK" = :a4,"VALIDATIONSTATUS" = :a5,"POSTINGSTATUS" = :a6,"GO_OFFLINE" = :a7 WHERE "BANK" = :b0 AND "VALIDATIONSTATUS" = :b1 AND "POSTINGSTATUS" = :b2 AND "GO_OFFLINE" = :b3 AND ROWNUM = 1>

                Operation failed at seqno 3241 rba 54218185

                Discarding record on action TRANSDISCARD on error 1403

                Record not found

                Aborted compressed key update from sevadm.ATMSTATUS to SEVADM.ATMSTATUS (target format)...

                 

                 

                 

                 

                Thanks

                • 6. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
                  ORASCN

                  Hi ,

                   

                  Please provide the below information

                   

                  1) INFO TRANDATA SEVADM.ATMSTATUS

                   

                  2) Please send the discard file generated in the target during the Abend.

                   

                  3) Steps you followed to perform the Initial Load

                   

                  4) select * from dba_log_groups where owner='SEVADM' and table_name='ATMSTATUS';

                   

                  Regards,

                  Veera

                  • 7. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
                    3132468

                    Hi

                     

                     

                    1) INFO TRANDATA SEVADM.ATMSTATUS

                     

                    GGSCI (sevadm) 6> info trandata sevadm.atmstatus

                     

                    Logging of supplemental redo log data is enabled for table SEVADM.ATMSTATUS.

                     

                    Columns supplementally logged for table SEVADM.ATMSTATUS: ALL.

                     

                    2)Steps followed to perform the Initial Load

                     

                    1) We have taken export backup with flashback  scn  of SEVADM schema .

                    2) We import that logical backup on target database  (SEVADM schema).

                    3) then we start the replicat process afterscn which is flashback scn.   

                     

                     

                     

                    3)SQL> select * from dba_log_groups where OWNER='SEVADM' and TABLE_NAME='ATMSTATUS';

                     

                    OWNER           LOG_GROUP_ TABLE_NAME      LOG_GROUP_TYPE      ALWAYS      GENERATED

                    --------------- ---------- --------------- ------------------- ----------- --------------

                    SEVADM          GGS_185704 ATMSTATUS       USER LOG GROUP      ALWAYS      USER NAME

                    SEVADM          SYS_C00209 ATMSTATUS       ALL COLUMN LOGGING  ALWAYS      GENERATED NAME

                                    227

                    SQL>

                     

                     

                    4) Discard file

                     

                    OCI Error ORA-01403: no data found, SQL <UPDATE /*+ RESTRICT_ALL_REF_CONS */ "SEVADM"."ATMSTATUS" SET "BANK" = :a4,"VALIDATIONSTATUS" = :a5,"POSTINGSTATUS" =

                    :a6,"GO_OFFLINE" = :a7 WHERE "BANK" = :b0 AND "VALIDATIONSTATUS" = :b1 AND "POSTINGSTATUS" = :b2 AND "GO_OFFLINE" = :b3 AND ROWNUM = 1>

                    Operation failed at seqno 3353 rba 34586084

                    Discarding record on action TRANSDISCARD on error 1403

                    Record not found

                    Aborted compressed key update from sevadm.ATMSTATUS to SEVADM.ATMSTATUS (target format)...

                    *

                    BANK = 412

                    VALIDATIONSTATUS = ONLINE

                    POSTINGSTATUS = ONLINE

                    GO_OFFLINE = N

                    BANK = 412

                    VALIDATIONSTATUS = NULL

                    POSTINGSTATUS = NULL

                    GO_OFFLINE = N

                    *

                    Begin TRANSDISCARD

                    Operation at seqno 3353 rba 34586084

                    Record not found

                    Aborted compressed key update from sevadm.ATMSTATUS to SEVADM.ATMSTATUS (source format)...

                    *

                    BANK = 412

                    VALIDATIONSTATUS = ONLINE

                    POSTINGSTATUS = ONLINE

                    GO_OFFLINE = N

                    BANK = 412

                    VALIDATIONSTATUS = NULL

                    POSTINGSTATUS = NULL

                    GO_OFFLINE = N

                    *

                    Operation at seqno 3353 rba 34586295

                    Record not found

                    Aborted compressed key update from sevadm.ATMSTATUS to SEVADM.ATMSTATUS (source format)...

                    *

                    --------------------------------------------------------------------------------------------------------------

                    My other replicat Group is also ABENDED

                     

                    Report File Error

                    2015-12-10 12:49:21  ERROR   OGG-01296  Error mapping from AMLOSEVADM.ISSUE_MASTER to AMLOSEVADM.ISSUE_MASTER.

                    • 8. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
                      ORASCN

                      Hi ,

                       

                      Please execute in the source and target and share the output,

                       

                      select * from SEVADM.ATMSTATUS where BANK=412;

                       

                      Check if this record is existing in the target.

                       

                      Regards,

                      Veera

                      • 9. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
                        3132468

                        Hi ,

                         

                         

                        Source:-

                         

                        SQL> select * from SEVADM.ATMSTATUS where BANK=412 ;

                         

                        BAN VALIDAT POSTING G

                        --- ------- ------- -

                        412 ONLINE  ONLINE  N

                         

                        SQL> desc SEVADM.ATMSTATUS

                        Name                                      Null?    Type

                        ----------------------------------------- -------- ----------------------------

                        BANK                                               VARCHAR2(3)

                        VALIDATIONSTATUS                                   VARCHAR2(7)

                        POSTINGSTATUS                                      VARCHAR2(7)

                        GO_OFFLINE                                         VARCHAR2(1)

                         

                        Target:-

                         

                        SQL> select * from SEVADM.ATMSTATUS where BANK=412;

                         

                        BAN VALIDAT POSTING G

                        --- ------- ------- -

                        412 OFFLINE OFFLINE Y

                         

                        SQL> desc SEVADM.ATMSTATUS

                        Name                                      Null?    Type

                        ----------------------------------------- -------- ----------------------------

                        BANK                                               VARCHAR2(3)

                        VALIDATIONSTATUS                                   VARCHAR2(7)

                        POSTINGSTATUS                                      VARCHAR2(7)

                        GO_OFFLINE                                         VARCHAR2(1)

                         

                         

                        It start generate the Discard record from this SEVADM.ATMSTATUS every time .

                         

                        Thanks .

                        • 10. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
                          ORASCN

                          Hi,

                           

                          Definitely you will face errors like 1403, 1400 etc, because you don't have Primary Key for this table. For Oracle GoldenGate Primary Key is a mandatory one. If you don't have a primary key, you should have a Unique key or index. If even this is not there, then you need to configure KEYCOLS for that table.

                           

                          Please check the below document.,

                           

                          https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters160.htm#i1146401

                           

                          Check KEYCOLS under this.

                           

                          Regards,

                          Veera

                          • 11. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
                            3132468

                            Hi ,

                             

                            Yes

                            You are right .

                            I am getting error 1403 in report file .

                             

                             

                            1) My Extract PUMP in the Passthru mode

                                -----------------------------------

                            Do not use KEYCOLS for tables being processed in pass-through mode by a data-pump Extract group

                               

                            EXTRACT DPCBS

                            userid ggs_owner,password ggs_owner

                            discardfile /home/oracle/ogg/dirdat/discard/dpcbs_discard.dsc,append,megabytes 1

                            0

                            rmthost 10.10.250.198,mgrport 7809,tcpbufsize 100000

                            rmttrail /home/oracle/ogg/dirdat/rc

                            passthru

                            sequence sevadm.*;

                            table sevadm.*;

                             

                            2) some table has the primary key but that table are also in the discard file.

                             

                            2) When i enabled supplemental loging by using add trandata it logged all column .

                            my table has the primary key. it  should log  the Primary key only . 

                            I have drop supplemental logging database level also .

                             

                            Syntax :- Delete trandata sevadm.accountmaster.

                                

                                           Add trandata sevadm.accountmaster.

                             

                             

                            Thanks .

                            • 12. Re: Getting OCI Error ORA-01403: no data found for Oracle to Oracle GoldenGate 11g Replication
                              3132468

                              Hi

                               

                               

                              Extract PUMP in the passthru mode


                              any other option to use KEY COLS parameter .



                              Thanks

                              Mandar