PeopleTools and Oracle 11g installation issues

Until now I was used to installing PeopleSoft on SQLServer 2005/2008 or Oracle 9i/10g. For this particular installation, PeopleTools 8.51 on a Unicode Oracle 11g database, I ran into two issues I never ran into before. Luckily fixes were easy, but finding a solution usually takes a lot of time.

The first issue I ran into, was that Datamover crashed with the error

Character Length Semantics (CLS) is not enabled

when setting Datamover database creating wizard to database type Unicode and trying to add PeopleSoft HR Database – US English.

This was an age old issue that could be fixed by setting database initialization parameter NLS_LENGTH_SEMANTICS to CHAR. By default this parameter is set to BYTE. After having the DBA set this parameter to CHAR, I was able to successfully seed a DEMO Unicode database using Datamover.

The second issue a ran into was a bit more tricky to solve. After seeding the database with Demo data using Datamover, you have to log in to Datamover with a PeopleSoft userid and log in to Application Designer with a PeopleSoft userid to import projects. Here I ran into the following error

SQL Access ManagerSQL error. Stmt #: 2 Error Position: 0 Return: 404 - ORA-01017: invalid username/password; logon denied

I was able to log in Datamover with user sysadm and password sysadm, but I was not able to log in Datamover with user SYSADM and password SYSADM. So, there must be a case-sensitive issue.

The issue is caused by Oracle 11g initialization parameter SEC_CASE_SENSITIVE_LOGON

SEC_CASE_SENSITIVE_LOGON enables or disables password case sensitivity in the database. During the installation scripts user sysadm was created with password sysadm. When Datamover seeds the database it populated table PSACCESPRFL with user SYSADM and password SYSADM

UPDATE PSACCESSPRFL SET ACCESSID = 'SYSADM', SYMBOLICID = 'SYSADM1', ACCESSPSWD = 'SYSADM', VERSION = 0, ENCRYPTED = 0;

Whenever you try to log in to Datamover or Application Designer using a PeopleSoft userid, PeopleTools uses the ACCESSID and ACCESSPWD to connect to the database. With the parameter SEC_CASE_SENSITIVE_LOGON set to TRUE you are not able to log in, because PeopleTools is trying to log in with a password in capitals.

Changing parameter SEC_CASE_SENSITIVE_LOGON to FALSE enables you to log in Application Designer and Datamover with PeopleSoft userids.

Have your DBA change this parameter to false

alter system set sec_case_sensitive_logon=false;