Tags: applicaiton, connect, database, drivers, frequent, friends, jdbc, logged, mysql, ora-01012, oracle, server, sql, websphere

Frequent ORA-01012: not logged on

On Database » Oracle

4,029 words with 4 Comments; publish: Wed, 13 Feb 2008 22:15:00 GMT; (25062.50, « »)

Friends,

We have a WebSphere applicaiton server which connect to Oracle 9.2.0.8 database. JDBC 10.1 drivers are used to connect Oracle.

We are experiecing abnormal Oracle session disconnects, typically when a user wants to insert some data in database (say 10k records in a table) sequentially one by one and session would break after 2k records. No reasons identified.

Application server throws an error: ORA-01012: not logged on

Any thoughts on what could be wrong.

Thanks

Vishal V.

All Comments

Leave a comment...

  • 4 Comments
    • There certainly should be another error prior to the ORA-01012.

      Look into the alert and trace files to see what additional information is available. If the same number of rows were processed using other clients like SQL*Plus, does that work correctly.

      Is this a problem after upgrade to 10.1 JDBC?

      post in more detail about how the application does 10K rows insert one by one ? where is the data for those rows coming from?

      #1; Sat, 23 Feb 2008 15:55:00 GMT
    • There are no errors in Oracle alert log file

      ORA-1012 is logged into application server log.

      SQLPLUS connection is abosultly fine and remains connected for ever with no issues in large dml or select statements.

      Application make a conneciton to DB2 and Oracle and fetches 100 records from DB2 at a time and sends them to a Oracle procedure one by one for processing. These steps are repeated until all rows from DB2 are fetched and processed in Oracle.

      Applcation log

      1/2/07 07:56:24:906 AM || 2 || Unavailable ID || UpdateCAMQuoteLineListCommand || insertCAMQuoteEntry || Failed connection rollback.ORA-01012: not logged on
      DSRA0010E: SQL State = 72000, Error Code = 1,012
      Error stacktrace is below.
      [1/16/07 13:09:32:586 PST] 66bba089 WSRdbManagedC W DSRA0180W: Exception detected during ManagedConnection.destroy(). The exception is: com.ibm.ws.exception.WsException: DSRA0080E: An exception was received by the Data Store Adapter. See original exception message: Closed Connection. with SQL State : null SQL Code : 17008
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
       at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1133)
       at oracle.jdbc.OracleConnectionWrapper.rollback(OracleConnectionWrapper.java:122)
       at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.cleanupTransactions(WSRdbManagedConnectionImpl.java:2626)
       at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.destroy(WSRdbManagedConnectionImpl.java:2068)
       at com.ibm.ejs.j2c.MCWrapper.destroy(MCWrapper.java:1380)
       at com.ibm.ejs.j2c.poolmanager.FreePool.cleanupAndDestroyMCWrapper(FreePool.java:505)
       at com.ibm.ejs.j2c.poolmanager.FreePool.returnToFreePool(FreePool.java:317)
       at com.ibm.ejs.j2c.poolmanager.PoolManager.release(PoolManager.java:1309)
      #2; Sat, 23 Feb 2008 15:56:00 GMT
    • ORA-01012 is typically returned when the client is already disconnected and it tries to issue an SQL statement without performing a connect again.
      Make sure that there is no issue in your application code that for certain error cases preforms a disconnect or closes a connection but still continues to perform SQL statements against that closed connection.
      See example below. After the client performs a disconnect (Connection.close() ), the next attempt to execute a SQL statement returns the ORA-01012.
      Oracle > disconnect
      Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
      PL/SQL Release 9.2.0.7.0 - Production
      Oracle > select * from scott.emp ;
      ORA-01012: not logged on
      Oracle >
      #3; Sat, 23 Feb 2008 15:57:00 GMT
    • Thanks Kamal
      I agree with you and I think developers must verify that connection is not closed before executing sql command on connection as a good practice (Connection.isClosed()).
      Regards,
      Vishal V.
      #4; Sat, 23 Feb 2008 15:58:00 GMT