7 Replies Latest reply: Feb 9, 2012 1:00 PM by 855380 RSS

    Oracle Streams - Error ORA-26786,ORA-26787

    855380
      Good day to all,

      I wish someone can help me in Oracle Streams, scoring to identify the following error in the Target with dba_apply_error:

      ORA-26786: A row with key ("RECKEY") = (1) exists But you have conflicting column (s) "ULT_NRO" in table PROD2.NUM_TRANSACCIONES
      ORA-01403: no data found

      This makes many of my tables in the destination are desynchronized. Moreover also I have cases where I get the ORA-26787 which has the following definition:

      ORA-26787: "The row with key% s does not exist in table% s"

      The BD I have in the Source is a 11gR2 EE and target is 11g R2 Edition One is only pasive and the source database is active.

      Hopefully I can help me,
      I hope your comments.

      Thank you.
        • 1. Re: Oracle Streams - Error ORA-26786,ORA-26787
          Andy Klock
          Have you reviewed the troubleshooting apply section of the Streams Concepts guide? You errors are in there with advice on how to handle them.

          http://docs.oracle.com/cd/E11882_01/server.112/e17069/strms_trapply.htm#CEGICDJB

          Welcome to the forums!
          • 2. Re: Oracle Streams - Error ORA-26786,ORA-26787
            855380
            thanks for you response,
            In reality my question go of the why ocurre this error.. a bad configuration is posible?

            I also did it to implement handlers for PLSQL but increasingly growing problem to other tables, that is why I wonder if the problem can be eliminated in the initial configurations and how.

            Regards.

            Oscar.
            • 3. Re: Oracle Streams - Error ORA-26786,ORA-26787
              734707
              Sound like it is an update and local value before update is different of LCR value (array of old values) so there is no match possible.
              For an update to take place, Streams checks that all values match before the update is performed.
              you are failing this check between the array of old values (in LCR) and the value of columns of the row in the table to updates: this is your desync.



              If you don't mind the old values and want the new value to overwrite, you can use the function DBMS_APPLY_ADM.COMPARE_OLD_VALUES
              setting the column "compare => FALSE"

              http://docs.oracle.com/cd/E11882_01/server.112/e10705/conflict.htm#i1018934

              Remember that streams do not use ROWID , so it identifies the row to update on target by comparing the values before update. The LCR has 2 arrays of column values: the before update and after update of source table are part of the LCR.

              For insert LCR the array of old value in the LCR is empty.
              For Delete the array of new values in LCR is empty.
              For update both arrays have values.
              • 4. Re: Oracle Streams - Error ORA-26786,ORA-26787
                Boochi
                See the metalink note below.

                Master Note for Troubleshooting Streams Apply Errors ORA-1403, ORA-26787 or ORA-26786,Conflict Resolution [ID 265201.1]

                This error is basically occur when the table has no primary key or when the apply parallelism is set for >1. In 11gR2, the default apply parallelism is set to 4. If the replication table / schema is small, then you can set the apply parallelism to 1.

                Most of these errors are reexecutable successfully when you reexecute the error transactions.
                • 5. Re: Oracle Streams - Error ORA-26786,ORA-26787
                  855380
                  Hi,
                  Well, I have to tell you I tried implmentar the Streams with parallelism 1, however I keep getting the same error.

                  ORA-26787: The row with key ("ANO", "CAPITAL_AMORTIZADO", "CAPITAL_APROBADO", "CAPITAL_DESEMBOLSADO", "CAPITAL_VENCIDO", "CREDITO", "CUOTAS_ATRAZADAS", "DIAS_MOROSIDAD_HOY", "DIAS_MOROSIDAD_PROY", "FECHA_ULTIMA_CAP", "FECHA_ULT_MOV", "FECHA_VENCIMIENTO", "FLAG_ESTADO_ANT", "FLAG_ESTADO_CRED", "GARANTIA_CANT", "GASTO_COBRANZA", "ID_AGENCIA", "INTERES_ACUM", "INTERES_COMP_PROY", "INTERES_MOR_PROY", "INTERES_PROY", "MES", "NRO_PROCESO", "OTRO_CARGO", "PAGO_INTERES", "PRODUCTO", "SALDO_GARANTIA", "SECTORISTA", "TASA") = (2012, 857508.04, 45000, 858078.39, 570.35, 2002000321, 1, 2858, 2880, 2004-04-13:00:00:00, 2004-04-13:00:00:00, 2004-01-14:00:00:00, 1, 7, , 230, 0002, 0, 4481.53, 651.6, 0, 2, 1, 0, 13144.13, 268 , 0, dfigue, 31.37) does not exist in table PROD2.CREDITO_SALDO_PRYCTDO
                  ORA-01403: no data found


                  ORA-26786: A row with key ("ITEM") = (2) exists but has conflicting column(s) "FCHA_ULT_PROC", "FLAG_PRCSO", "HORA_FIN", "HORA_INI" in table PROD2.CIE_PROYCCION_PRVSION
                  ORA-01403: no data found



                  It's good to tell you also that once the error occurs, I proceed to stop the apply, then to erase the mistakes and finally start the application in the destination, and thus gradually disappear errors and re-based sincornizar destination data, however this is constant.
                  Is due to some configuration I'm missing?
                  Thanks for your opinions.

                  Regards.
                  • 6. Re: Oracle Streams - Error ORA-26786,ORA-26787
                    Boochi
                    Is primary key set on this table?. You propably may need a conflict handler.

                    See this link.

                    http://docs.oracle.com/cd/B28359_01/server.111/b28322/troub_rep.htm#CEGICDJB
                    • 7. Re: Oracle Streams - Error ORA-26786,ORA-26787
                      855380
                      hello Boochi ,
                      the table has a primary key .. Now, to solve my problem, You coments the use DML Handlers,
                      The question is: Would it be necessary to create the DML Handler for all my tables? because every time that I start the apply of Streams..the console showing error in other tables.
                      or would be no way to solve this another way?