ProTrace Application Error NSP-0404.NSP_QUERY_EXEC_FAILED and Oracle Error ORA-00942: table or view does not exist

Asset ID:	1-72-2208662.1
Update Date:	2016-12-01
Keywords:

Solution Type Problem Resolution Sure

Solution 2208662.1 : ProTrace Application Error NSP-0404.NSP_QUERY_EXEC_FAILED and Oracle Error ORA-00942: table or view does not exist

Applies to:

Oracle Communications Performance Intelligence Center (PIC) Software - Version 4.1 and later
Information in this document applies to any platform.

Symptoms

Users are getting the following error when running ProTrace queries on specific sessions.

Message pattern is missing for given exception code in com.tekelec.cssg.nsp.protrace.common.ServerMessages. ExceptionCode: NSP-0404.NSP_QUERY_EXEC_FAILED.

Changes

Cause

This issue happens only if one of the DWS servers is in "Query Only" mode when running the ProTrace query on the impacted session.

Basically, the impacted session has been created when the same DWS server was in "Query Only" mode. Consequently, the corresponding table was not created on the "Query Only" DWS database. Due to a known old bug, ProTrace will query all available DWS databases including the one in "Query Only" mode. And since the needed table doesn't exist on that DWS, the query will fail and the above error will fetch up.

In the application.log files of the NSP we distinguish the following entries:

2016-11-24 23:31:57,551 - ERROR - NSP - Trouble- shooting - - SYSTEM - ORA-00942: table or view does not exist
- java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

  at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
  at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
  at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
  at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
  at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
  at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1178)
  at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
  at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
  at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
  at com.steleus.nsp.business.queryexec.NSPAbstractOracleQueryExecuter.executeQuery(NSPAbstractOracleQueryExecuter.java:405)
  at com.steleus.nsp.business.queryexec.NSPAbstractOracleQueryExecuter.executeQuery(NSPAbstractOracleQueryExecuter.java:296)
  at com.steleus.nsp.business.queryexec.ThreadedQueryExecution.run(ThreadedQueryExecution.java:41)

2016-11-24 23:31:57,551 - ERROR - NSP - SYSTEM - - SYSTEM - NSP-0415.NSP_QUERY_SQL_ERROR - com.steleus.nsp.business.queryexec.NSPQueryExecException: Unable to execute query
  at com.steleus.nsp.business.queryexec.NSPAbstractOracleQueryExecuter.executeQuery(NSPAbstractOracleQueryExecuter.java:306)
  at com.steleus.nsp.business.queryexec.ThreadedQueryExecution.run(ThreadedQueryExecution.java:41)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

  at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
  at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
  at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
  at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
  at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
  at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1178)
  at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
  at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
  at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
  at com.steleus.nsp.business.queryexec.NSPAbstractOracleQueryExecuter.executeQuery(NSPAbstractOracleQueryExecuter.java:405)
  at com.steleus.nsp.business.queryexec.NSPAbstractOracleQueryExecuter.executeQuery(NSPAbstractOracleQueryExecuter.java:296)
  ... 1 more

2016-11-24 23:31:57,566 - ERROR - NSP - Trouble- shooting - - BUSINESS - NSP-0404.NSP_QUERY_EXEC_FAILED - com.steleus.nsp.business.queryexec.NSPQueryExecException: System busy or request too big, decrease the request or retry later

Verify that the corresponding table doesn't exist on the "Query Only" DWS:

Connect to the "Query Only" DWS server as "oracle" user.
Connect to the DWS database as "ixp" user:
sqlplus ixp/<password
Check if the table related to the impacted session exists:
desc session_name

Solution

To resolve the issue, put back the "Query Only" DWS in ACTIVE mode to let the store processes create the needed table on the database. If needed, switch back the concerned DWS to "Query Only" mode once the table is created (after few minutes).

Go "Mediation -> IXP -> Storage" menu.
Move the concerned DWS to ACTIVE mode.
Apply Changes.
After few minutes and if needed put back the DWS to "Query Only" mode.

Attachments

This solution has no attachment