3 Replies Latest reply: Jun 21, 2013 3:18 PM by soa999 RSS

    Inbound 820 Error after migrating 11.1.1.6 to 11.1.1.7

    soa999

      Hi All,

       

      I am getting an error HIPAA in Inbound 820, which used to work fine with 11.1.1.6. We recently upgraded to 11.1.1.7 for TA1 acknowledgement and seeing the following errors in the Logs. Outbound direction is working fine, but not able to see messages in Business Message, Wire message tab but was able to see messages only on Application Message Tab

       

      Error Code: 904

      Call: INSERT INTO B2B_WIRE_MESSAGE (ID, JOB_ID, LABEL, ATTRIBUTE1, MESSAGE_DIGEST, ATTRIBUTE3, MESSAGE_ID, ATTRIBUTE5, MODIFIED, CREATED, MSG_SIZE, LAST_RESUBMITTED_USER_NAME, LAST_RESUBMITTED_TIME_STAMP, MESSAGE_STATUS, LAST_VIEWED_USER_NAME, CHANNEL_NAME, PASSWORD, DIGEST_ALGORITHM, PORT, ENCRYPTION_ALGORITHM, PROTOCOL_COLLABORATION_ID, ENCRYPTION_PROTOCOL, PROTOCOL_MESSAGE_ID, ERROR_DESCRIPTION, PROTOCOL_TRANSPORT_BINDING, ERROR_TEXT, REFER_TO_PROTOCOL_MESSAGE_ID, EXCHANGE_ATTEMPT_COUNT, RESUBMIT_COUNT, EXCHANGE_RETRY_INTERVAL, SIGNING_ALGORITHM, HOST_NAME, SIGNING_CERTIFICATE, ACKNOWLEDGEMENT_MODE, SIGNING_PROTOCOL, ATTRIBUTE4, STATE, LAST_VIEWED_TIME_STAMP, TO_ADDRESS, DIRECTION, TRANSPORT_CERTIFICATES, ERROR_CODE, TRANSPORT_CERTIFICATES_CLOB, ERROR_TEXT_CLOB, TRANSPORT_CORRELATION_ID, FROM_ADDRESS, TRANSPORT_HEADERS, ATTRIBUTE2, TRANSPORT_PROTOCOL, DELIVERED_ENDPOINT, TRANSPORT_PROTOCOL_VERSION, ERROR_DESCRIPTION_CLOB, TRANS_SECURITY_PROTOCOL, TRANS_SECURITY_PROT_VERSION, ENCRYPTION_CERTIFICATE, URL, CPST_INST_CREATED_TIME, USER_NAME, EXCHANGE_REMAINING_RETRY, VERSION, PACKED_MESSAGE, PAYLOAD_STORAGE, RESUBMIT_REF_TO) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

          bind => [0AE9028813F3A14AB880000045F7D236, null, null, null, null, null, 0AE9028813F3A14AB880000045F7D236, null, 2013-06-12 13:30:00.583, 2013-06-12 13:30:00.583, 745, null, null, null, null, null, null, null, null, null, null, null, null, null, filename=Plan_820_5010.dat

      filesize=745

      ChannelName=PlanFinder_ListeningChannel

      file_ext=dat

      filename_format=%FROM_PARTY%_%TO_PARTY%_%DOCTYPE_NAME%_%DOCTYPE_REVISION%.dat

      timestamp=2013-06-12T10:46:30.000-08:00

      MSG_RECEIVED_TIME=Wed Jun 12 13:30:00 PDT 2013

      , null, null, null, null, null, null, null, null, ASYNC, null, null, PROCESSING, null, null, INBOUND, null, null, null, null, null, null, MSG_RECEIVED_TIME=Wed Jun 12 13:30:00 PDT 2013

      , null, File, null, 1.0, null, null, null, null, file://localhost//u01/app/soa/Error, 2013-06-12 13:30:00.59, null, null, null, 0AE9028813F3A14AB880000045F7D237, null, null]

      Query: InsertObjectQuery(oracle.tip.b2b.model.instance.WireMessage@240430d5)

          at oracle.toplink.exceptions.DatabaseException.sqlException(DatabaseException.java:296)

          at oracle.toplink.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:725)

          at oracle.toplink.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:791)

          at oracle.toplink.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:525)

          at oracle.toplink.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:468)

          at oracle.toplink.internal.sessions.AbstractSession.executeCall(AbstractSession.java:800)

          at oracle.toplink.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)

          at oracle.toplink.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:179)

          at oracle.toplink.internal.queryframework.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:314)

          at oracle.toplink.internal.queryframework.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:153)

          at oracle.toplink.internal.queryframework.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:168)

          at oracle.toplink.internal.queryframework.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:454)

          at oracle.toplink.queryframework.InsertObjectQuery.executeCommit(InsertObjectQuery.java:67)

          at oracle.toplink.queryframework.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:77)

          at oracle.toplink.internal.queryframework.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:279)

          at oracle.toplink.queryframework.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:47)

          at oracle.toplink.queryframework.DatabaseQuery.execute(DatabaseQuery.java:679)

          at oracle.toplink.queryframework.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:602)

          at oracle.toplink.queryframework.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:103)

          at oracle.toplink.queryframework.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:75)

          at oracle.toplink.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2807)

          at oracle.toplink.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1079)

          at oracle.toplink.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1063)

          at oracle.toplink.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1022)

          at oracle.toplink.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:244)

          at oracle.toplink.internal.sessions.CommitManager.commitAllObjectsForClassWithChangeSet(CommitManager.java:219)

          at oracle.toplink.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:175)

          at oracle.toplink.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:3459)

          at oracle.toplink.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1331)

          at oracle.toplink.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1427)

          at oracle.toplink.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(UnitOfWorkImpl.java:3071)

          at oracle.toplink.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(UnitOfWorkImpl.java:3051)

          at oracle.toplink.transaction.AbstractSynchronizationListener.beforeCompletion(AbstractSynchronizationListener.java:146)

          at oracle.toplink.transaction.JTASynchronizationListener.beforeCompletion(JTASynchronizationListener.java:57)

          at weblogic.transaction.internal.ServerSCInfo.doBeforeCompletion(ServerSCInfo.java:1244)

          at weblogic.transaction.internal.ServerSCInfo.callBeforeCompletions(ServerSCInfo.java:1219)

          at weblogic.transaction.internal.ServerSCInfo.startPrePrepareAndChain(ServerSCInfo.java:121)

          at weblogic.transaction.internal.ServerTransactionImpl.localPrePrepareAndChain(ServerTransactionImpl.java:1355)

          at weblogic.transaction.internal.ServerTransactionImpl.globalPrePrepare(ServerTransactionImpl.java:2172)

          at weblogic.transaction.internal.ServerTransactionImpl.internalCommit(ServerTransactionImpl.java:300)

          ... 14 more

      Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "TRANSPORT_CERTIFICATES_CLOB": invalid identifier

       

          at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)

          at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

          at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)

          at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)

          at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)

          at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)

          at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)

          at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)

          at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)

          at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)

          at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3904)

          at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1512)

          at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStatement.java:172)

          at oracle.toplink.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:716)

          ... 52 more

       

      ]]

      [2013-06-12T13:30:00.604-07:00] [soa_server1] [ERROR] [] [oracle.soa.b2b.engine] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId: <anonymous>] [ecid: a0701707ce1f4efb:3fe5a774:13f350db6dc:-8000-00000000000203e3,0] [APP: soa-infra] InterfaceListener:onMessageLocal There was an exception in commit or rollback of the  current transaction. Returning transport error to the transport layer

      [2013-06-12T13:31:16.785-07:00] [soa_server1] [ERROR] [] [oracle.soa.b2b.engine] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId: <anonymous>] [ecid: 0000Jwp3S1TDScX5Hvh8if1Hht3I000001,0] [APP: soa-infra] Error -:  B2B-50025:  Repository error[[

          at oracle.tip.b2b.msgproc.ToplinkHelper.getAcquiredAndLockedSequenceManagers(ToplinkHelper.java:3945)

          at oracle.tip.b2b.utility.SequenceManagerUtility.manageSequenceManager(SequenceManagerUtility.java:673)

          at oracle.tip.b2b.transport.SequenceManagerWorker.processSequenceManagerAcquireEvent(SequenceManagerWorker.java:53)

          at oracle.tip.b2b.transport.SequenceManagerWorker.run(SequenceManagerWorker.java:35)

          at oracle.integration.platform.blocks.executor.WorkManagerExecutor$1.run(WorkManagerExecutor.java:120)

          at weblogic.work.j2ee.J2EEWorkManager$WorkWithListener.run(J2EEWorkManager.java:184)

          at weblogic.work.DaemonWorkThread.run(DaemonWorkThread.java:30)

      Error -:  B2B-50025:  Repository error

          at oracle.tip.b2b.msgproc.ToplinkHelper.getAcquiredAndLockedSequenceManagers(ToplinkHelper.java:3945)

          at oracle.tip.b2b.utility.SequenceManagerUtility.manageSequenceManager(SequenceManagerUtility.java:673)

          at oracle.tip.b2b.transport.SequenceManagerWorker.processSequenceManagerAcquireEvent(SequenceManagerWorker.java:53)

          at oracle.tip.b2b.transport.SequenceManagerWorker.run(SequenceManagerWorker.java:35)

          at oracle.integration.platform.blocks.executor.WorkManagerExecutor$1.run(WorkManagerExecutor.java:120)

          at weblogic.work.j2ee.J2EEWorkManager$WorkWithListener.run(J2EEWorkManager.java:184)

          at weblogic.work.DaemonWorkThread.run(DaemonWorkThread.java:30)

       

      Please share your thoughts.

       

      Thanks In Advance!

        • 1. Re: Inbound 820 Error after migrating 11.1.1.6 to 11.1.1.7
          Ismail-m

          Can you run the below query in soa_infra database.

           

          select TRANSPORT_CERTIFICATES_CLOB from B2B_WIRE_MESSAGE;

           

          If you dont see this column in the table, that means in 11.1.1.7, Oracle has added this new column TRANSPORT_CERTIFICATES_CLOB in the INSERT statement but forgot to add this column to B2B_WIRE_MESSAGE table.

          You might have to log a Service request with Oracle support.

           

          For a quick and officially not recommended testing you can manually add this column to the B2B_WIRE_MESSAGE table and see if you still get this error.

          The column must definitely be of type CLOB.

           

          ~Ismail.

          • 2. Re: Inbound 820 Error after migrating 11.1.1.6 to 11.1.1.7
            Anuj Dwivedi--Oracle

            Either you not ran the patch set assistant to upgrade your schemas or it failed because of some issue. Please follow below link and get your schemas upgraded -

             

            section "3.7 Updating Your Schemas with Patch Set Assistant" at - http://docs.oracle.com/cd/E28280_01/doc.1111/e16793/patch_set_installer.htm#BABHJBFG

             

            This will resolve your issue.

             

            Regards,
            Anuj

            • 3. Re: Inbound 820 Error after migrating 11.1.1.6 to 11.1.1.7
              soa999

              Thanks for your Reply Anuj...After updating the sachems B2B transactions where working fine, but I see lot MDS and a particular table related  Error's regularly. Please find the diagnostic log messages below.

               

              MDS Error LOG:

               

              Message    Error -:  B2B-50025:  Repository error

              Supplemental Detail    at oracle.tip.b2b.system.TransactionManager.end(TransactionManager.java:245)

              at oracle.tip.b2b.engine.ThreadWorkExecutor.processEvent(ThreadWorkExecutor.java:642)

              at oracle.tip.b2b.engine.ThreadWorkExecutor.run(ThreadWorkExecutor.java:214)

              at oracle.integration.platform.blocks.executor.WorkManagerExecutor$1.run(WorkManagerExecutor.java:120)

              at weblogic.work.j2ee.J2EEWorkManager$WorkWithListener.run(J2EEWorkManager.java:184)

              at weblogic.work.DaemonWorkThread.run(DaemonWorkThread.java:30)

              Error -:  B2B-50025:  Repository error

              at oracle.tip.b2b.system.TransactionManager.end(TransactionManager.java:245)

              at oracle.tip.b2b.engine.ThreadWorkExecutor.processEvent(ThreadWorkExecutor.java:642)

              at oracle.tip.b2b.engine.ThreadWorkExecutor.run(ThreadWorkExecutor.java:214)

              at oracle.integration.platform.blocks.executor.WorkManagerExecutor$1.run(WorkManagerExecutor.java:120)

              at weblogic.work.j2ee.J2EEWorkManager$WorkWithListener.run(J2EEWorkManager.java:184)

              at weblogic.work.DaemonWorkThread.run(DaemonWorkThread.java:30)

               

               

              B2B Batch Table Insert Error:

               

              Message    weblogic.transaction.RollbackException: Unexpected exception in beforeCompletion: sync=oracle.toplink.transaction.JTASynchronizationListener@3ccfbc5

              Supplemental Detail    Internal Exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SOAINFRA.B2B_BE_CON1) violated

               

              Error Code: 1

              Call: INSERT INTO B2B_BATCH_EVENT (ID, DOC_TYPE, SCHEDULE_TYPE, DOC_TYPE_REV, ATTRIBUTE1, ENABLE, ATTRIBUTE3, END_DATE, ATTRIBUTE5, EXPIRE_TIME, BATCH_TIME_STAMP, CRITERIA2, INTERVAL, CRITERIA4, MSG_COUNT, CHANNEL_NAME, REPEAT_EVERY, ATTRIBUTE4, REPEAT_ON_DAY, CRITERIA1, REPEAT_TYPE, CRITERIA5, SCHEDULE, BATCH_NAME, START_DATE, ATTRIBUTE2, STATE, CRITERIA3, TO_TP_NAME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

              bind => [1020::834:5010, 834, null, 5010, null, 1, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, 23 39 12 21 6 ? 2013 , null, null, 1020::834:5010, null, null, 1, null, ]

              Query: InsertObjectQuery(oracle.tip.b2b.model.instance.BatchEvent@6c994f70)

              at weblogic.transaction.internal.TransactionImpl.throwRollbackException(TransactionImpl.java:1884)

              at weblogic.transaction.internal.ServerTransactionImpl.internalCommit(ServerTransactionImpl.java:376)

              at weblogic.transaction.internal.ServerTransactionImpl.commit(ServerTransactionImpl.java:267)

              at weblogic.transaction.internal.TransactionManagerImpl.commit(TransactionManagerImpl.java:307)

              at weblogic.transaction.internal.TransactionManagerImpl.commit(TransactionManagerImpl.java:301)

              at oracle.tip.b2b.system.TransactionManager.end(TransactionManager.java:214)

              at oracle.tip.b2b.engine.ThreadWorkExecutor.processEvent(ThreadWorkExecutor.java:642)

              at oracle.tip.b2b.engine.ThreadWorkExecutor.run(ThreadWorkExecutor.java:214)

              at oracle.integration.platform.blocks.executor.WorkManagerExecutor$1.run(WorkManagerExecutor.java:120)

              at weblogic.work.j2ee.J2EEWorkManager$WorkWithListener.run(J2EEWorkManager.java:184)

              at weblogic.work.DaemonWorkThread.run(DaemonWorkThread.java:30)

              Caused by: Exception [TOPLINK-4002] (Oracle TopLink - 11g Release 1 (11.1.1.6.0) (Build 111018)): oracle.toplink.exceptions.DatabaseException

              Internal Exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SOAINFRA.B2B_BE_CON1) violated

               

               

              Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SOAINFRA.B2B_BE_CON1) violated

               

              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)

              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

              at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)

              at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)

              at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)

              at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)

              at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)

              at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)

              at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)

              at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)

              at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3904)

              at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1512)

              at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStatement.java:172)

              at oracle.toplink.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:716)

              ... 47 more

              weblogic.transaction.RollbackException: Unexpected exception in beforeCompletion: sync=oracle.toplink.transaction.JTASynchronizationListener@3ccfbc5

               

              Internal Exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SOAINFRA.B2B_BE_CON1) violated

               

               

              Thanks!!!