12 Replies Latest reply on Dec 21, 2015 10:48 AM by 3132468

    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