ORA-00022: invalid session ID; access denied

If you are getting this error, here is a quick fix.

Set the following value in psappsrv.cfg (appserver) and psprcs.cfg (batch server) for all the configured appservers and batch servers. You may have to reconfigure the appserver and batch servers and restart them.

DbFlags=8

Default value is DbFlags=0 which means use Persistent Secondary DB Connection.

Setting DbFlags=4 is not recommended by PeopleSoft, which completely disables the secondary database connection.

Setting it to 8 disables Persistent Secondary DB Connection, but it still uses on demand Secondary DB Connection for each request. This is required for using GetNextNumberWithGapsCommit (GNNWGC) function, which is internally used by PeopleSoft for workflow transactions to generate APPR_INSTANCE

If you do not do this, you may get row inserted in PS_APPR_INST_LOG with

APPR_INSTANCE = 0

which may cause, some undesired workflow routings.

The easiest solution I have found is

delete from ps_appr_inst_log where APPR_INSTANCE = 0

Please make sure that you backup the data and test it.

Here is some more information on GetNextNumberWithGapsCommit (GNNWGC) function from peoplebooks.

Use this function instead of the GetNextNumberWithGaps function. The GetNextNumberWithGaps function is very restrictive in its usage. The GetNextNumberWithGapsCommit function can be used in any event. The sequence number (record.field ) is incremented right away and it doesn't hold any database internal row lock beyond the execution of this function.

Note. A secondary database connection is used to increment and retrieve record.field. The default behavior is to keep the secondary database connection persistent in order to improve performance for the next GetNextNumberWithGapsCommit usage. If the database administrator finds the persistent connection too high an overhead for the production environment (which should not be the case since PeopleSoft uses application server to mulitplex the database connection), the database administrator can change the default behavior to use an on-demand connection method. The persistent second connection is disabled using DbFlags bit eight in the application server and process scheduler configuration files. The second connection can be completely disabled using DbFlags bit four in the application server and process scheduler configuration files

This issue may be happening in Tools 8.45 and higher (8.46, 8.47, 8.48, 8.49 etc.), as secondary connection is introduced in 8.45.

See Peoplesoft Customer connection Resolutions for  more information.

Resolution : 201049233 - E-ORACLE:10g Master Performance Solution for Oracle 10g 

In this resolution, PeopleSoft generally recommends to Set DbFlags=8. I hope thay deliver this value by default in future peopletools releases.

Resolution : 201049902 - E-PC: Sporadic ORA-00022 Errors in AE w/ GetNextNumberWithGapsCommit PCode

Resolution : 201022463 - E-NV nVision reports that are run on Client Machine fail - Error message Invalid Cursor Number

Resolution : 201015931 - E-SEC: SQL looking for inactive roles causes slow logins for users

Resolution : 201023068 - E-WF:Deadlocks whenever a high load of transactions create worklists 

Resolution : 201024183 - E-NV Running nVision via Define Report Book gives 8055 cursor entry error

Resolution : 200987809 - E-NV: nVision reports are queued when running in Windows 3 tier mode

Note : All the discussion is surrounding oracle database only. I found that it is disabled automatically for informix. For all other databases you may need to test the functionality your self.