Peoplesoft OVM PORTAL9.1

Few weeks ago a new Peoplesoft OVM template has been released, Portal Solution 9.1, announced here.
After HCM9.1, this is the second one, time to test it as well.

As usual, download from http://edelivery.oracle.com/linux (check the template 64bit : PeopleSoft VM Templates for PS 9.1 Release Media Pack for x86_64 (64 bit)).

Only two templates for this : database and App/Batch/PIA.

I’ll skip all the steps regarding Oracle VM Manager, Oracle VM Server configuration, uncompress and import of the template since this is the same exercise I did last year for HCM9.1, please if you want to know more, have a look to the tutorial on the top-right of the main blog page. I’ll start the description below from the starting of the new VM.

1. The database server
Select the VM and click on start
 
Then on console (be fast otherwise it will be configured as DHCP you certainly don’t want). 
 
Enter the password defined earlier when you created the template :

Give all what it needs, IP address and so on :

Here below, we observe the same CSS error as before for the HCM9.1 template, that fails, but it is not an issue for the next steps :


It continues, give the database name

Few notes :
1.1. Well, so far so good despite I choose automatic startup of the database (see screenshot above), the database does not start on the server boot (same as HCM9.1).
1.2. Note, the service is your_db_name.us.oracle.com :
[oracle@psovmpsdb ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 03-AUG-2010 13:04:20

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=psovmpsdb)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date                03-AUG-2010 12:28:15
Uptime                    0 days 0 hr. 36 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/psovmpsdb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=psovmpsdb)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "P91TMPLT.us.oracle.com" has 1 instance(s).
  Instance "P91TMPLT", status READY, has 1 handler(s) for this service...
Service "P91TMPLT_XPT.us.oracle.com" has 1 instance(s).
  Instance "P91TMPLT", status READY, has 1 handler(s) for this service...
Service "XDB.us.oracle.com" has 1 instance(s).
  Instance "P91TMPLT", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@psovmpsdb ~]$
1.3. SYSADM’s password is in upper case :
C:\>sqlplus sysadm/sysadm@p91tmplt

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 3 22:04:49 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
C:\>sqlplus sysadm/SYSADM@p91tmplt

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 3 22:04:56 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.
1.4. Language installed is only English :
SQL> select language_cd from pslanguages where installed=1;

LAN
---
ENG
1.5. The default profile of SYSADM and PEOPLE users expires password the 23-AUG-2010 :
SQL> select username,account_status,expiry_date,profile
  2  from   dba_users
  3  where username in ('PEOPLE','SYSADM');

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ---------
PEOPLE                         OPEN                             23-AUG-10 DEFAULT
SYSADM                         OPEN                             23-AUG-10 DEFAULT

SQL> select *
  2  from dba_profiles
  3  where profile='DEFAULT'
  4  and limit is not null
  5  and limit ! ='UNLIMITED';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1

You should change it to avoid future issues :
SQL> alter profile default limit
  2  failed_login_attempts unlimited
  3  password_life_time unlimited
  4  password_lock_time unlimited
  5  password_grace_time unlimited;

Profile altered.

SQL> select *
  2  from dba_profiles
  3  where profile='DEFAULT'
  4  and limit is not null
  5  and limit ! ='UNLIMITED';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- -----
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

2. The App/Batch/PIA server
Select the VM and click on start
 
Then on console (be fast otherwise it will be configured as DHCP you certainly don’t want). 
 
Enter the password defined earlier when you created the template :


 

Give all what it needs, IP address and so on :

   
Here it will continue if you want :

Well done, AppServer AND process scheduler start on the first shot :

It will continue by the Webserver if you want :



Done, we get the login page as expected :
 

Much appreciated, and really, except posting report, all works on the first shot ! Really well done !

The few errors revealed within HCM9.1 template has been solved (db connection, kernel settings…).
Now to be able to post report, you should do small effort on your own, create a report node repository and attached to the process server defined :

Note : the local node is already password configured (again one more improvement compared to the HCM9.1).
Everything should post correctly now :

There is one problem I encountered, I don’t know if this is Peoletools related (here it is 8.50.09) but it is rather annoying, and since I don’t have any other other env. in the same Peopletools release, I cannot reproduce somewhere else.
When I enter a text in any lookup field, I receive a Internet Explorer error :

Same issue in the role page of a user :

I’m sure this is not coming from template, but still….
Addendum : it seems to be an error introduced within Peopletools 8.50.07/08, here on OTN forum, a workaround to fix that issue (with TYPEAHD) given by Greg Kelly from Peopletools team. You could also have a look in MOS note #1147744.1 which describe the way to deactivate it.

Nicolas.

Oracle patchset 10.2.0.5 recut (Linux 32bit)

Important note for those who work on Linux 32bit, the patchset 10.2.0.5 has been recut and few fixes has been added to the original package dated from 30-APR-2010.
Do not forget to download it again to be sure to work on the latest version.
Read the note 1156958.1 for more information, this is only for 32bit version of Linux.

Nicolas.

Oracle Database Vault and Peoplesoft

If you want to secure and close down your database to all the non-authorized people, especially not seeing the data inside the database, what’s better than Database Vault ? Nothing.
And this is more and more right, especially on financial database or even on HR system hosting the salary and a lot of confidential information about the employees of the company. So, what’s refrain to use Database Vault ? Nothing. Almost nothing. Till 10gR2, Database Vault was an additional option to download and install onto the Oracle home directory, from 11g, it is included in the standard package, no need anymore additional software install.
For Peoplesoft however, you’ll need an additional component dedicated to Peoplesoft to create all the required policies.

Installation
I’m not going to describe all the installation steps, but there are several constraints when installing Database Vault, have a dedicated Oracle home, have EM Console, have one database (declared in /etc/oratab) to name few (note, at least in 10gR2 ASM is not supported by DBVault), but you can find everything in the well documented link.

Implementation
Once the Database Vault has been installed, you’ll need to apply it onto the database, following the installation through the dvca (Database Vault Creation Assistant).
Some new users are created to manage the database vault policies, owner and manager (you can decide to have only the owner). Only them can allow other users to do something on the database.

Peoplesoft package
Once your database has been “vaulted”, you have to install the dedicated Peoplesoft packages pointed above in the download page.

Maintenance
Whether the installation process might fail for several reasons, and it is not always easy to figure out why (the messages are not really clear), as long as you read very carefully the document, this is rather simple. As soon has the database is vaulted and Peoplesoft packages installed, no one else than SYSADM (the Peoplesoft objects’ owner) can see the data (even SYS/SYSTEM are “blind”). And more than that, SYSADM cannot connect through SQL*Plus (or any other non-Peoplesoft tools) to see data. Happy ? Well, yes and no.
In the documentation coming within the Peoplesoft package, it is clearly explained that a new user – PSFTDBA – has been created and authorized to do all the maintenance tasks in place of SYSADM through SQL*Plus for instance.
PSFTDBA can create/drop/alter objects in SYSADM’s with the ALTER SESSION SET SCHEMA=SYSADM. But again to be respectful to the vault policies, PSFTDBA is not able to select data from SYSADM. Very easy.

But for who know the Peoplesoft project build, that is not so simple. Most of the time, the “project build” generate script within CTAS (Create Table As Select) which obviously cannot work in that database vault context. So what ?
The only one known workaround is to ask to the database vault owner (or manager) to deactivate the PSFTDBA restriction, he can then select data from SYSADM, run the script and reactivate the restriction… It is rather against the rules, isn’t it ? If we have to disable the vault to apply project, nothing refrain to see data anymore.

I’d be curious if someone has already implemented Database Vault on a Peoplesoft database and how they are managing it. Feel free to comment out.

Enjoy it,

Nicolas.