Tuesday, 11 August 2009

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.

8 comments:

Mario said...

Hi Chris

I landed on your page by Googling the errors, so that objective worked.

Firstly, your link to the "manuals" is broken. Looks like Oracle cannot find the page.

What is the solution here? Do we create the DB table and seq as Pascal Alma's link suggests or do we do the neccessary grants to the connection pool user to create any table and create any sequence?

Regards,
Mario

Chris Muir said...

Hi Mario

The link works for me, I tried 2 different browsers from 2 different sites. Maybe try accessing them again, could be an Oracle hiccup. However the manual in question is just the Fusion Middleware Fusion Developer's Guide for 11g and is available online with JDev - see section entitled "Application State Management", subsection "Controlling the Schema Where the State Management Table Resides".

Either of your solutions are suitable. For a production system I lean towards creating them manually and not granting the privileges such that the privileges can't be abused. However it's your choice.

Also have a read of Simon Haslam's paper on Practical ADF Application Deployment - http://www.veriton.co.uk/download/Practical_ADF_Application_Deployment.pdf.

Cheers,

CM.

Simon Haslam said...

A useful post Chris. There haven't been many discussions about BC internals recently so it's good of you to raise the visibility of an important topic.

I personally always create a separate schema for the BC "internal/core" tables - see section 3.1 in my paper. Whether you pre-create the BC objects or let ADF do that I've less of an opinion about - as long as the DBA team knows they're there and roughly what they're for that's fine by me.

Chris Muir said...

Very much so Simon, and you're guidelines in your best practice paper are something we've followed closely, great work ;-)

Cheers,

CM.

Matt said...

Hi Chris,

I found this post while investigating "SEVERE: Could not find saved view state for token". Our ADF application is facing this issue during load testing. I have tried creating the DB table and seq as Pascal Alma's link suggests and added the grants to the connection pool user to create any table and create any sequence and the issue still exists. Is there anything else that could cause this issue?

Regards,

Matt

Chris Muir said...

Hi Matt

Are you sure your load tests are pushing back the complete token value? This error can occur because you load test is truncating the token when sending it back to the midtier.

And unfortunately yes, there are other reasons this error can occur but you'll need to search the OTN forums. The one I've documented is but 1 reason.

CM.

Spyros Doulgeridis said...

Hi Chris,

Excellent post!

Is there a possibility to get "SEVERE: Could not find saved view state for token"
because the PS_TXN is not cleared after any new deployment? The DB user has all 3 roles.

Thanx in advance,

Spyros

Chris Muir said...

Hi Spyros

Thanks for the compliment. Unfortunately as per the last comment, there are other reasons this error can occur but you'll need to search the OTN forums. The one I've documented is but 1 reason.

CM.