oraesb database schema corrupted

Problem description
oc4j_soa instance consuming 100% cpu.
ESB message processing running in an infinite loop where JMS adapter message consumers throws exception. When an MDB responds to a message by throwing an exception, the message is not considered delivered and it may be
redelivered. If the MDB might always respond to a given message by throwing an exception, and MaxDeliveryCnt is set to 0 to prevent the message from ever being discarded, the result may be an MDB stuck in an “infinite loop” – failing to process the same message over and over again.) For an MDB this is fine. For a JMS adapter this is more troublesome.
The problem can be seen in the application log $ORACLE_HOME/j2ee/oc4j_soa/oc4j_soa_default_group_1/oc4j/log.xml as continous flow of “Global transaction get” “global transaction begin”, infinite transaction loop, even if no load on system.
So the oraesb schema is basically corrupted.

Error message of type can be seen in container log:
Designtime cache has not been initialized Please look in logs for following signs of failure.
Fix them and restart. (a) Database access errors (b) ESB Bootstrap errors
(c) OC4J class load errors (d) Product installation errors
(e) Export ESB params and verify if host and port parameters are correct.
Please contact Oracle Support if unable to fix the issue.

reset oraesb schema

Of course, Oracle Support was contacted, but they tend to take some time responding, so meanwhile a qucik check on some of the trusted oracle bloggers around gave result:
http://www.thisisahmed.com/tia/soa/esbdesigntimecacheerror.html

So now the reset of oraesb was performed, but still there is an issue with the instances view:

ESB instances view:
“Could not retrieve the messages due to the error : ORA-00904: “SEQ”: invalid identifier ”

“Could not retrieve the messages due to the error : ORA-00904: “SEQ”: invalid identifier ”

This was caused by the table ORAESB.ESB_ACTIVITY did not contain the columns SEQ and SUB_FLOW_SEQ. Happened on reset of ORAESB.
The correct DDL was executed, and voila, suddenly the ESB instances view was behaving as expected.

The ESB_ACTIVITY DDL

drop table oraesb.esb_activity;
CREATE TABLE "ORAESB"."ESB_ACTIVITY"
( "ID" NUMBER NOT NULL ENABLE,
"FLOW_ID" VARCHAR2(256 BYTE) NOT NULL ENABLE,
"SUB_FLOW_ID" VARCHAR2(48 BYTE),
"SEQ" NUMBER,
"SUB_FLOW_SEQ" NUMBER(3,0),
"BATCH_ID" VARCHAR2(48 BYTE),
"SOURCE" VARCHAR2(48 BYTE),
"OPERATION_GUID" VARCHAR2(48 BYTE),
"TIMESTAMP" NUMBER NOT NULL ENABLE,
"TYPE" NUMBER(2,0) NOT NULL ENABLE,
"RR_OUTPUT_STATUS" NUMBER(2,0),
"ADDI_INFO" VARCHAR2(500 BYTE),
"IS_STALE" VARCHAR2(1 BYTE)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
commit;

Comment are closed.