PeopleSoft Application Server – Won’t Start

It really amazes me how many times I find at client sites Application Servers that randomly won’t start. There are a combination of issues that can cause the problem, but typically I find the problem to be related to this group of tables.

PSSTATUS – this table has a field called OWNERID, which needs to make the schema owner in Oracle, and should match the ACCESSID in SQL Server.

PSACCESSPRFL – this table has the fields SYMBOLICID, VERSION, ACCESSID, ACCESSPSWD, ENCRYPTED. The accessid should match the OWNERID from PSSTATUS table, and the password would be the password at the database level for the schema/accessid user. If you change this to an unencrypted value you will want to change the accesspswd to an unencrypted value and set the encrypted field to 0, and then run datamover and do the encrypt_password *; statement to reencrypt the passwords. You will also notice the SYMBOLICID field, this is associated to the SYMBOLICID field on the PSOPRDEFN table. VERSION can be reset using the VERSION Application Engine. I recommend that there only be one entry in this table, sometimes having multiple entries can cause problems if the accessid/password are invalid for the environment, because PeopleSoft has a tendency to expect only one entry in this table.

PSOPRDEFN – this is your primary parent information table for security. Make sure that the password is set correctly, if you reset it to a clear text entry, you can reencrypt it using the encrypt_password *; command in datamover. Make sure you set the encrypted field to 0 (zero) before running datamover. You will also want to make sure that the SYMBOLICID matches that of the PSACCESSPRFL table. Lastly, make sure that the ACCTLOCK is set to 0 for the user trying to start the Application Server. You will also want to make sure it has a Permission List that allows it to start the application server.

PS.PSDBOWNER – In Oracle there is another table in the PS schema that contains the Database Name and Owner, make sure these are set correctly.

Make sure that the connect id user often known as “people” is setup in your database and it has been granted select access to the tables: PSACCESSPRFL, PSSTATUS, PSOPRDEFN.

Lastly, make sure that the USERID matches your user that you want to start the Application Server from PSOPRDEFN, and that the password is set correctly, and make sure that the CONNECT ID user / password is correct in the configuration files of the application server.

The typical 2-tier connect takes the userid and authenticates it against the PSOPRDEFN table using the connect id user, which in turn allows a connection to be made to the database using the symbolicid associated with the PSOPRDEFN table, which is tied to the accessid on the PSACCESSPRFL table. So once authenticated that system is actually running the session as “accessid” but controlled by the PeopleSoft security assigned to the user.

Crystal 2008 – Error Status

The other day I ran into a situation where all Crystal’s were going to error, after working fine. Doing a little research on My Oracle Support I found that this error:

Warning: ORB::BOA_init: hostname lookup returned `localhost’ (127.0.0.1/::1)
Use the -OAhost option to select some other hostname

is actually related to the IPv6 being disabled. On the servers were this occurred the odd thing appears to be that IPv6 was disabled prior to the error occurring. So it is possible that some patch or fix or another change the administration team did started this error to occur. After re-enabling the IPv6 the error went away. This appears to occur with PeopleTools 8.5x with Crystal 2008 running in the Windows 2008 environment.

Windows 2008 non-R2:
Control Panel > Network > Manage Network Connections > highlight Local Area Connection > Advanced > Advanced Settings > Bindings for Local Area Connection.
1) If IPv6 is not enabled, enable it, then retest.
2) If enabled, try switching the order, then retest.

Windows 2008 R2:
Control Panel > Network > double-click on Local Area Connection > Properties >
1) If IPv6 is not enabled, enable it, then retest.
2) If enabled, try switching the order, then retest.

Runaway Queries

When PeopleSoft runs sql queries and it runs longer then the service timeout for the service operation, the service will terminate its connection to that query, however, it may stay running on the database. This can result in long running queries on the database draining valuable CPU/memory from the database server. Oracle has the ability to check for “dead” connections and terminate them.

Adding the parameter sqlnet.expire_timeout to the sqlnet.ora file that is housed on your database server ($ORACLE_HOME/network/admin directory), will send a probe to verify that connections are active versus dead due to some client termination. If a connection is determined to be terminated or no longer in use, an error is returned which will cause the server process to exit. The parameter is specified in a time interval of minutes, and PeopleSoft recommends a 10 minute interval.

sqlnet.expire_timeout=10

Some considerations to this are you must be using TPC/IPC connections for this to work, so if you are using the parameter UseLocalOracleDB=1 in your configurations you are not using TPC/IPC. Most client use TPC/IPC so this should not be an issue. This will also put some additional overhead on the server to execute the probes, however, if you have large/long running sql queries running against the database, the performance hit should be offset by the gains in killing the unnecessary (dead) connection.

Unix SCP – Remote Copy

The SCP command in unix is extremely useful for move files/directories from one server to another. If you log into the machine you want to transfer the files to and navigate to the directory where you want the file or directory, you can issue the following command:

To move a file: scp username@remoteserver:/home/username/example .

To move a directory: scp -r username@remoteserver:/home/username/exampledirectory .

The -r flag is to move all files/directories within the example directory. username is the remote servers user name, this can be eliminated if you want to login with the same user as you are currently logged in to the local machine with. remoteserver is the server that houses the file(s)/directory(s) you want to copy, and the “.” signifies to copy to the current location.

PSVersion

As part of a system administrators play book, you should be familiar with the version application engine. This AE can fix all sorts of odd issues, however, it should not be necessary to run it often. There are many locations within PeopleSoft that have a version control, you may have noticed it on PSOPRDEFN, PSPNLDEFN, PSCLASSDEFN, etc… this version is compared against the version stored for the object type on the PSVERSION table, and this determines if the object needs to be used from cache or if it needs to be recached.

Often times, when objects are migrated from one environment to another, patches/fixes/bundles/maintenance packs are applied, upgrades are done, you will find these version numbers will get out of sync. In order to get them in sync, you should run the AE from a command line:

psae -CT -CD -CO -CP -R -AI VERSION

= ORACLE / MICROSFT — is your database — is a user in your PeopleSoft Security with administrative priviledges — PSUser’s password — is a run control id anything will do.

You will want to clear all cache and bounce the application, process and web services, and you will want to make sure that all developers are out of the system (they can even clear their cache on their local machines).