JDev/ADF – the importance of getting PS_TXN and PS_TXN_SEQ correct

This is a revisit post about two important database objects for ADF, the database objects PS_TXN and PS_TXN_SEQ. The table and sequence are used by ADF to serialize user session state to the database. An old Oracle OTN whitepaper gives the low-down on these 2 objects.

Without these database objects your application can't scale effectively to multiple users, you'll see some bizarre and wonderful behaviour as ADF chokes on not having the ability to serialize to the database objects. However it's not an ADF problem, the manuals clearly state that you need to grant specific privileges to the database user or create the database objects beforehand.

I know now of 2 projects where serious problems occurred because the database wasn't configured correctly to accept the objects, and found another blog post detailing the same issue.

Pascal Alma's blog post can be found here.

In the 1st project, the developers overlooked giving privileges to the ADF database user schema to create the table and sequence, and didn't undertake load/stress tests to see how the application performed with more than one user. As soon as the application hit production with multiple users, ADF attempted to serialize to the database objects, and the system started hitting a huge array of issues. The worst bit was some of the errors were real red herrings making the problem hard to diagnose. However after much time with Oracle Support assistance the problem was solved. This destroyed the developers' faith in ADF and the users' faith in the developers and new ADF system.

The 2nd project is one I'm involved in currently. We were creating new database schemas for the ADF user and for some reason we didn't grant the create sequence privilege, but did grant the create table priv. Luckily I stress tested the app and found the problem fairly early on (with Steve Muench's kind help). However it did take a full 8 hours to debug, so I thought worth documenting here to help others.

What I'd thought I document is what errors you'll see in the WLS logs for JDev 11g build 5188 and JDev 11gR1 build 5407. I don't have an earlier version of JDev to test what happens but Pascal's blog post from above may assist.

Under JDev 11g build 5188 you'll see the following WLS log entries:

First it'll throw errors that it can't retrieve user session state:

SEVERE: Could not find saved view state for token -505abe38
11/08/2009 14:29:29 org.apache.myfaces.trinidadinternal.application.StateManagerImpl restoreView
SEVERE: Could not find saved view state for token -505abe38
11/08/2009 14:29:29 org.apache.myfaces.trinidadinternal.application.StateManagerImpl restoreView
SEVERE: Could not find saved view state for token -505abe38
11/08/2009 14:29:29 org.apache.myfaces.trinidadinternal.application.StateManagerImpl restoreView

At a later point in the logs you'll see exceptions thrown, but they're not very meaningful:

WARNING: ADFc: Error while opening JDBC connection.
oracle.jbo.DMLException: JBO-26061: Error while opening JDBC connection.
 at oracle.jbo.server.ConnectionPool.createConnection(ConnectionPool.java:253)
 at oracle.jbo.server.ConnectionPool.instantiateResource(ConnectionPool.java:168)
 at oracle.jbo.pool.ResourcePool.createResource(ResourcePool.java:546)
 at oracle.jbo.pool.ResourcePool.useResource(ResourcePool.java:327)

Further in the logs you may see the following TNS Listener issue (though this may just be particular to my Oracle XE setup):

Caused by: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
localhost:1521:xe
 at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
 at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:116)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:177)

And you may see NullPointerExceptions dependent on what ADF was attempting to do with the database connection at the time:

Caused by: java.lang.NullPointerException
 at oracle.adf.model.binding.DCIteratorBinding.initSourceRSI(DCIteratorBinding.java:1735)
 at oracle.adf.model.binding.DCIteratorBinding.callInitSourceRSI(DCIteratorBinding.java:1625)
 ... 75 more

Under JDev 11gR1 build 5407 gives you a more meaningful error message:

Again, first you'll see errors that it can't retrieve user session state:

SEVERE: Could not find saved view state for token -ce0efchp5
11/08/2009 2:35:31 PM org.apache.myfaces.trinidadinternal.application.StateManagerImpl restoreView
SEVERE: Could not find saved view state for token -ce0efchp7
11/08/2009 2:35:31 PM org.apache.myfaces.trinidadinternal.application.StateManagerImpl restoreView
SEVERE: Could not find saved view state for token -ce0efchp7
11/08/2009 2:35:31 PM org.apache.myfaces.trinidadinternal.application.StateManagerImpl restoreView

But then you'll see a much more meaningful message that ADF can't create the required objects:

oracle.jbo.PCollException: JBO-28006: Could not create persistence table PS_TXN_seq
 at oracle.jbo.PCollException.throwException(PCollException.java:36)
 at oracle.jbo.pcoll.OraclePersistManager.createTable(OraclePersistManager.java:908)
 at oracle.jbo.pcoll.OraclePersistManager.queryNextCollectionId(OraclePersistManager.java:1444)

And further down:

Caused by: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
 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)

Hopefully in Googling these error messages and landing on this page, you'll be able to work out how to correctly configure the database objects to avoid these issues.