Thursday, December 9, 2010

"ORA-00942: table or view does not exist" in Oracle SOA Suite 11g

Problem:
We have been receiving continuous errors of "ORA-00942: table or view does not exist" in our SOA logs.

When we start up the SOA managed server in Oracle SOA Suite 11g, we get the following error in the "soa_server1.log" log file:
####<Dec 8, 2010 4:08:02 PM EST> <Info> <EJB> <oradev> <soa_server1> <[ACTIVE] ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'> <weblogic> <> <> <1291842482959> <BEA-010227> <EJB Exception occurred during invocation from home or business: weblogic.ejb.container.internal.StatelessEJBHomeImpl@7ab12bdc threw exception: java.lang.RuntimeException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.2.v20100323-r6872): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Error Code: 942
Call: SELECT f.CIKEY, f.NODE_ID, f.SCOPE_ID, f.COUNT_ID, f.FAULT_NAME, f.FAULT_TYPE, f.POLICY_NAME, f.POLICY_VERSION, f.POLICY_CATEGORY, f.POLICY_ECID, f.CREATION_DATE, f.MODIFY_DATE, f.MESSAGE, wi.LABEL, wi.CREATOR, wi.MODIFIER, wi.STATE, ci.ECID, ci.CMPST_ID, ci.DOMAIN_NAME, ci.COMPOSITE_NAME, ci.COMPONENT_NAME, ci.COMPOSITE_REVISION, ci.COMPOSITE_LABEL FROM WI_FAULT f LEFT JOIN WORK_ITEM wi ON f.CIKEY = wi.CIKEY AND f.NODE_ID = wi.NODE_ID AND f.SCOPE_ID = wi.SCOPE_ID AND f.COUNT_ID = wi.COUNT_ID LEFT JOIN CUBE_INSTANCE ci ON f.CIKEY = ci.CIKEY WHERE ci.COMPONENTTYPE = ? AND ci.DOMAIN_NAME = ? AND ci.COMPOSITE_NAME = ? AND ci.COMPOSITE_REVISION = ? AND (f.FAULT_TYPE is null OR f.FAULT_TYPE = ?) AND ci.STATE != 9 AND (wi.STATE is null OR wi.STATE IN (9, 4, 13, 3))  ORDER BY f.CREATION_DATE DESC
        bind => [bpel, default, raiseEventEMF_BPEL, 1.0, {http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage]
Query: ReadAllQuery(referenceClass=WorkItemFault sql="SELECT f.CIKEY, f.NODE_ID, f.SCOPE_ID, f.COUNT_ID, f.FAULT_NAME, f.FAULT_TYPE, f.POLICY_NAME, f.POLICY_VERSION, f.POLICY_CATEGORY, f.POLICY_ECID, f.CREATION_DATE, f.MODIFY_DATE, f.MESSAGE, wi.LABEL, wi.CREATOR, wi.MODIFIER, wi.STATE, ci.ECID, ci.CMPST_ID, ci.DOMAIN_NAME, ci.COMPOSITE_NAME, ci.COMPONENT_NAME, ci.COMPOSITE_REVISION, ci.COMPOSITE_LABEL FROM WI_FAULT f LEFT JOIN WORK_ITEM wi ON f.CIKEY = wi.CIKEY AND f.NODE_ID = wi.NODE_ID AND f.SCOPE_ID = wi.SCOPE_ID AND f.COUNT_ID = wi.COUNT_ID LEFT JOIN CUBE_INSTANCE ci ON f.CIKEY = ci.CIKEY WHERE ci.COMPONENTTYPE = ? AND ci.DOMAIN_NAME = ? AND ci.COMPOSITE_NAME = ? AND ci.COMPOSITE_REVISION = ? AND (f.FAULT_TYPE is null OR f.FAULT_TYPE = ?) AND ci.STATE != 9 AND (wi.STATE is null OR wi.STATE IN (9, 4, 13, 3))  ORDER BY f.CREATION_DATE DESC")>
When we click on any composite in the EM Console, we receive kind of similar errors in the "soa_server1.out" log file:
[TopLink Warning]: 2010.12.09 11:24:37.981--ClientSession(959167924)--Exception [TOPLINK-4002] (Oracle TopLink - 11g Release 1 (11.1.1.3.0) (Build 100323)): oracle.toplink.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Error Code: 942
Call: SELECT CASE_ID, CONTAINER_ID, STATUS, MSG_ID, COMPONENT_DN, OPERATION, LOCK_TIME, PRIORITY, IS_EVENT, QNAME_LOCAL_PART, CREATION_DATE, QNAME_NAMESPACE, CASE_INFO, SOURCE_URI, COMPONENT_STATUS, INSTANCE_CREATED FROM MEDIATOR_DEFERRED_MESSAGE WHERE (((STATUS = ?) AND (LOCK_TIME = ?)) AND (CONTAINER_ID = ?))
        bind => [1, 2010-12-09 10:02:00.271, 4F41D83003A411E0BFA4D79E82BD21D4]
Query: ReadAllQuery(oracle.tip.mediator.common.persistence.MediatorDeferredMessage)
<Dec 9, 2010 11:24:37 AM EST> <Error> <oracle.soa.mediator.common.listener> <BEA-000000> <DBLocker caught exception while retrieving locked messages. Will retry retrieval after 2 seconds
Exception [TOPLINK-4002] (Oracle TopLink - 11g Release 1 (11.1.1.3.0) (Build 100323)): oracle.toplink.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Error Code: 942
Call: SELECT CASE_ID, CONTAINER_ID, STATUS, MSG_ID, COMPONENT_DN, OPERATION, LOCK_TIME, PRIORITY, IS_EVENT, QNAME_LOCAL_PART, CREATION_DATE, QNAME_NAMESPACE, CASE_INFO, SOURCE_URI, COMPONENT_STATUS, INSTANCE_CREATED FROM MEDIATOR_DEFERRED_MESSAGE WHERE (((STATUS = ?) AND (LOCK_TIME = ?)) AND (CONTAINER_ID = ?))
        bind => [1, 2010-12-09 10:02:00.271, 4F41D83003A411E0BFA4D79E82BD21D4]
Query: ReadAllQuery(oracle.tip.mediator.common.persistence.MediatorDeferredMessage)
        at oracle.toplink.exceptions.DatabaseException.sqlException(DatabaseException.java:305)
        at oracle.toplink.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:613
Solution:

This is directly attributed to invalid credentials of the "SOADataSource" data source.

1. Log in to the WebLogic Server Administration Console.

2. Navigate to <domain> -> Services -> JDBC -> Data Sources

3. Click on the SOADataSource data source

3. Click on Connection Pool

4. Verify the information on this page, specifically the JDBC URL and properties

In our case, someone had accidentally changed the user of the connection pool.


5. Click on Monitoring and Testing and test the data source to ensure that it's working properly

Applicable Versions:
  • Oracle SOA Suite 11g (11.1.1.x)

1 comment:

Hiren said...

I resolved mine with this.

Thanks,
HIren