Applying latest Oracle Patch 22502456 Database Patch Set Update : 11.2.0.4.160419 for Oracle Run Time Client errors out

When trying to apply patch 22502456 to Oracle Runtime client using latest OPatch version 11.2.0.3.12
it fails with the following error. This patch is also included in combo patch : 22738777 - COMBO OF OJVM COMPONENT 11.2.0.4.160419 DB PSU + DB PSU 11.2.0.4.1604 (APR2016)


Verifying environment and performing prerequisite checks...
Skip patch 20760982 from list of patches to apply: This patch is not needed.
UtilSession failed: null
Log file location:
OPatch failed with error code 73
Log file error shows this.
[May 9, 2016 3:46:33 PM]     OUI-67073:UtilSession failed: null
[May 9, 2016 3:46:33 PM]     Finishing UtilSession at Mon May 09 15:46:33 EDT 2016
[May 9, 2016 3:46:33 PM]     Log file location: /opt/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2016-05-09_15-46-25PM_1.log
[May 9, 2016 3:46:33 PM]     Stack Description: java.lang.NullPointerException
[May 9, 2016 3:46:33 PM]     StackTrace: java.lang.StringBuffer.<init>(StringBuffer.java:104)
[May 9, 2016 3:46:33 PM]     StackTrace: oracle.opatch.UtilSession.process(UtilSession.java:337)
[May 9, 2016 3:46:33 PM]     StackTrace: oracle.opatch.OPatchSession.main(OPatchSession.java:2580)
[May 9, 2016 3:46:33 PM]     StackTrace: oracle.opatch.OPatch.main(OPatch.java:634)


Oracle support is able to reproduce this issue however currently no workaround is provided.
They have internally 2 bugs opened to address this issue.


Bug 22932939 -- OPATCH FAILS TO PATCH RUNTIME CLIENT ENVIRONMENTS
Bug 22740194 - INSTALLATION OF 11.2.0.4.7 AND LATER PSU FAILS IN RUNTIME CLIENT ENVIRONMENT

Global Database Link feature in Oracle 11G can result in Peoplesoft Signon Failure

If you use Oracle Internet Directory (OID) to resolve database names, then you have this feature enabled by default. With Global database link feature, users can access tables/view in other database using @dbname syntax even when there is no explicit database link is created. For e.g User A logged in to Database A using his personal id : XXXX can run the below query to access table in database b. Only thing that is needed to make this work is that password for the user A must be same in both database A and B.

select * from ps_job@databaseb

This may create a potential locking situation,if password in database are not synced up and there is an account lock policy implemented to lock account after x invalid attempts. As soon as user runs more than x sql statements using global database link feature, it will lock the account in database b. When you look at dba audit trail, you will see the connection attempt was made by oracle user and machine name will be of database server machine name. This makes it confusing to determine how this account got locked up.

This is even more dangerous, if some one runs a SQR or Application engine program using peoplesoft process scheduler with a remotedbname sysntax for sql for e.g. ps_job@databaseb

This is because, PeopleSoft process scheduler runs the sql using database access id in database A, which inurn access the database b using the access id password in Database A. Most likely you would keep the database access id password (default : SYSADM) different for different databases and this can potentially lock the sysadm password in database B due to account lock out policy. If this happens, users will see when trying to access the application

“Bea.jolt.serviceexception: TPENOENT – No entry”

You can find more information on this at My Oracle Support

What are Global Database Link and How do you Disable them? (Doc ID 1632329.1)

http://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_concepts.htm#ADMIN02801

If this happens in your environment, Please check if database access id is locked out. If yes, unlock the database access id and restart all your app and batch servers.

Retrieve milliseconds from Oracle database and display it on a page.

You can use the following SQL to get milliseconds from Oracle 9i and higher and assign it to a Field of Type Time in PeopleSoft page. May Place this code in FieldDefault event.

SQLExec("select TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF') from dual", XX_TEST_DERIVED.XX_TIME);

Set the Time Formatting to HH:MI:SS:999999 in Field properties.

For Date Time Field, use the following SQL.

SQLExec("select TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD-HH24:MI:SS.FF') from dual", XX_TEST_DERIVED.XX_DATETIME);

Set the Time Formatting to HH:MI:SS:999999 in Field properties. Select Display Century and Display Time Zone in Page Field Properties to display complete date and time.

You will see this on page as follows:`

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.