Peoplesoft passwords

As we've seen in previous posts reagarding the Peoplesoft connectivity (here, here and here), there are different type of Peoplesoft users : the applications' users and the database user (so called bootstrap mode connection or database owner).

Both of them are stored in Peoplesoft tables. But they are managed separatly, stored in different tables and more over, their password are managed in different way.

The application user and password is stored in PSOPRDEFN :

SQL> select operpswd,encrypted from psoprdefn where oprid='PS';

OPERPSWD                          ENCRYPTED
-------------------------------- ----------
5iCGeTd2aRl/N+E3E8ZUz72qEe4=              1
SQL> desc psoprdefn
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OPRID                                     NOT NULL VARCHAR2(30 CHAR)
VERSION                                   NOT NULL NUMBER(38)
OPRDEFNDESC                               NOT NULL VARCHAR2(30 CHAR)
EMPLID                                    NOT NULL VARCHAR2(11 CHAR)
EMAILID                                   NOT NULL VARCHAR2(70 CHAR)
OPRCLASS                                  NOT NULL VARCHAR2(30 CHAR)
ROWSECCLASS                               NOT NULL VARCHAR2(30 CHAR)
OPERPSWD                                  NOT NULL VARCHAR2(32 CHAR)   <-- 
ENCRYPTED                                 NOT NULL NUMBER(38) 
SYMBOLICID                                NOT NULL VARCHAR2(8 CHAR)
...

Whereas the database user and password is stored in PSACCESSPRFL table :

SQL> select * from psaccessprfl;

SYMBOLIC    VERSION ACCESSID         ACCESSPSWD        ENCRYPTED
-------- ---------- ---------------- ---------------- ----------
SYSADM1           1 sBzLcYlPrag=     sBzLcYlPrag=              1
SQL> desc psaccessprfl;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SYMBOLICID                                NOT NULL VARCHAR2(8 CHAR)
VERSION                                   NOT NULL NUMBER(38)
ACCESSID                                  NOT NULL VARCHAR2(16 CHAR)
ACCESSPSWD                                NOT NULL VARCHAR2(16 CHAR)   <--  ENCRYPTED                                 NOT NULL NUMBER(38)

Two remarks from these output :
=> The userid is not encrypted whereas accessid is encrypted
=> the length of password is not the same

Note, in theory, you could have more than one row in PSACCESSPRFL table (it would mean more than one Peoplesoft applications in one database), in reallity, this option is never used.
Whether we give an application user to the application (DMS, AppDesigner, AppServer...), it is trying to connect as the accessid taken from the following query, assuming we want to connect as PS.

SQL> select a.*
2  from   psaccessprfl a, psoprdefn b
3  where  b.oprid='PS'
4  and    a.symbolicid=b.symbolicid;

SYMBOLIC    VERSION ACCESSID         ACCESSPSWD        ENCRYPTED
-------- ---------- ---------------- ---------------- ----------
SYSADM1           7 sBzLcYlPrag=     sBzLcYlPrag=              1

1 row selected.

Started from there, ACCESSID (the Peoplesoft owner) is encrypted. The password is encrypted as well. Since nobody give the database user/password connection string on application connection, Peoplesoft will de-encrypt (decypher) the password to be able to (re)connect to the database. Do not forget the connectid user (people) which is configured (for instance in Configuration Manager) and which is used to retreive all these info from the database.

Let's try to see what Peoplesoft is doing and manages the different passwords.

For the need of the current test, the PS and SYSADM password will be changed to be the same (PASSWORD).

1. The application password
First of all, let's change the PS password in clear within the back end :

Then encrypt the PS's password with DataMover (in bootstrap mode) :

The password has been updated :
The trace file is like the following :

COM Stmt=UPDATE PSOPRDEFN SET VERSION = :2, OPERPSWD = :3, ENCRYPTED = :4, LASTPSWDCHANGE =TO_DATE(:5,'YYYY-MM-DD') WHERE OPRID = :1
Bind-1 type=2 length=2 value=PS
Bind-2 type=8 length=4 value=2
Bind-3 type=2 length=28 value=qxbYphfzHOpVJtrs6e1vd2RZXYI=
Bind-4 type=6 length=4 value=1
Bind-5 type=26 length=10 value=2010-04-04

Which means the password is sent to the database already encrypted, the DataMover tool is encrypting password and update the table as well.

2. The Peoplesoft owner password
It can be changed in different way, the first one is to use the DataMover command change_access_password, linked to the SYMBOLICID the accessid is working with. Note, by this way, the database user password is automatically changed.
The password is changed into the table :
Read carefully the encrypted value password here and compare with the PS encrypted password showed earlier => whether both passwords are same in clear (= PASSWORD) they are not same once encrypted.

An other way to change the ACCESSID's password is to update the back end table, then run encrypt_password * DataMover command :
Note, by this way, you should also change the database user password manually (alter user sysadm identified by PASSWORD).
In both cases, the back end PSACCESSPRFL table is changed.

I used the word "changed" and not "updated". Because it is not an update statement in database sense.
The trace file generated by the command above to change the accessid's password is the following :

COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSSTATUS
COM Stmt=UPDATE PSSTATUS SET DATABASE_OPTIONS = DATABASE_OPTIONS + :1 WHERE MOD(FLOOR(DATABASE_OPTIONS/:2),2) = 0
Bind-1 type=6 length=4 value=32
Bind-2 type=6 length=4 value=32
Commit
COM Stmt=SELECT VERSION,ACCESSID,ACCESSPSWD,ENCRYPTED FROM PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Fetch
Commit
Commit
Commit
COM Stmt=SELECT VERSION, OBJECTTYPENAME FROM PSLOCK WHERE OBJECTTYPENAME IN ('CLM') FOR UPDATE OF VERSION
Fetch
Fetch
COM Stmt=UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME IN ('CLM')
COM Stmt=SELECT VERSION FROM PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Fetch
Connect=Primary/H91TMPLT/sysadm/
COM Stmt=DELETE FROM PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Disconnect
Connect=Primary/H91TMPLT/sysadm/
Disconnect
Commit
COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSSTATUS
COM Stmt=UPDATE PSSTATUS SET DATABASE_OPTIONS = DATABASE_OPTIONS + :1 WHERE MOD(FLOOR(DATABASE_OPTIONS/:2),2) = 0
Bind-1 type=6 length=4 value=32
Bind-2 type=6 length=4 value=32
Commit

A DELETE is fired onto that PSACCESSPRFL table. But this table is not empty after changing the password, somehow, the DataMover trace file does not show everything. Let's see a little bit more with Oracle Fine Grained Auditing against that particular table.

begin
  dbms_fga.add_policy ( object_schema   => 'SYSADM',
                        object_name     => 'PSACCESSPRFL',
                        policy_name     => 'PWD',
                        statement_types => 'DELETE,UPDATE,INSERT' );
end;
/

Then, re-run the password change in DataMover, and finally check the audited statements :

SQL> select * from dba_fga_audit_trail;

   6380034 04-APR-10 SYSADM
Administrator
WORKGROUP\ANTLIA
sysadm
SYSADM
PSACCESSPRFL
PWD                               5370605
DELETE FROM PSACCESSPRFL WHERE SYMBOLICID = :1
 #1(7):SYSADM1
DELETE
04-APR-10 11.48.22.627791 AM +02:00
                                                              0
9549             08001F0030160000         108          1

   6380034 04-APR-10 SYSADM
Administrator
WORKGROUP\ANTLIA
sysadm
SYSADM
PSACCESSPRFL
PWD                               5370607
INSERT INTO PSACCESSPRFL (SYMBOLICID, VERSION, ACCESSID, ACCESSPSWD, ENCRYPTED)
VALUES (:1, :2, :3, :4, :5)
 #1(7):SYSADM1 #2(1):7 #3(12):sBzLcYlPrag= #4(12):sBzLcYlPrag= #5(1):1
INSERT
04-APR-10 11.48.22.636170 AM +02:00
                                                               0

9549             08001F0030160000         109          2

SQL>

DELETE + INSERT is the way how Peoplesoft is changing the ACCESSID password. And again, the DataMover is directly cypher and update the underlying table.

DB Wizard 8.50 for Oracle on Linux

I should start this article by saying DB Wizard is not usually my cup of tea, especially for Oracle database.
I already used it for DB2 database on Peopletools 8.48 and 8.49, and it worked smoothly, eventhough I'm not a DB2 guy ! But DB Wizard for Oracle had always screwed me up, just by seeing the number of manual modifications before to be able to run the Wizard. Honestly, I've never had the patience to go through the DB Wizard till the end of the process, in comparison it was so easy (and most probably faster) for me to create the database manually.
But thing can changed, and why not check this within the latest Peopletools version.

Here we go.

Note, I'm working on OEL5.3 64-bit and Oracle 11.2.0.1.

1. The user to be used :
Since we create an Oracle database, manage starting it up, use Oracle user (owner of Oracle software) is more than recommanded.

2. The init.ora and password files :
First, we need to have a valid init.ora file, let's follow the doc word by word on this, and create the file under $ORACLE_HOME/dbs with the minimum required :

[oracle@orion2:/apps/oracle/product/11.2.0/dbs]$ more initO2H91WZD.ora
DB_NAME = O2H91WZD
DB_FILES = 1021
CONTROL_FILES = /oradata/O2H91WZD/O2H91WZD_ctl1.ora,/oradata/O2H91WZD/O2H91WZD_ctl2.ora
OPEN_CURSORS = 1000
db_block_size = 8192
NLS_LENGTH_SEMANTICS=CHAR
[oracle@orion2:/apps/oracle/product/11.2.0/dbs]$

Then create the password file as well :

[oracle@orion2:/apps/oracle/admin/o2h91wzd/pfile]$ cd $ORACLE_HOME/dbs
[oracle@orion2:/apps/oracle/product/11.2.0/dbs]$ orapwd file=pwdo2h91wzd.ora entries=5 password=oracle

3. The directories to be created :
Under $ORACLE_BASE :

[oracle@orion2:/apps/oracle/admin]$ mkdir -p o2h91wzd/bdump
[oracle@orion2:/apps/oracle/admin]$ mkdir -p o2h91wzd/adump
[oracle@orion2:/apps/oracle/admin]$ mkdir -p o2h91wzd/udump
[oracle@orion2:/apps/oracle/admin]$ mkdir -p o2h91wzd/cdump
[oracle@orion2:/apps/oracle/admin]$ mkdir -p o2h91wzd/pfile

And for the datafiles :

[oracle@orion2:/apps/oracle/admin/o2h91wzd/pfile]$ mkdir /oradata/o2h91wzd

4. tnsnames.ora modification :
Add the tns entry for the new database :

more $ORACLE_HOME/network/admin/tnsnames.ora
[...]

o2h91wzd =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orion2.phoenix-nga)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = o2h91wzd)
    )
  )

5. start the listener :

lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-DEC-2009 16:45:05

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

Starting /apps/oracle/product/11.2.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Log messages written to /apps/oracle/diag/tnslsnr/orion2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orion2.phoenix-nga)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                27-DEC-2009 16:45:06
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /apps/oracle/diag/tnslsnr/orion2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orion2.phoenix-nga)(PORT=1521)))
The listener supports no services
The command completed successfully

6. Run the psconfig.sh :
Be sure the Oracle user has enough rights against the PS_HOME you are running the Wizard from (especially $PS_HOME/log, $PS_HOME/modifiedscript and $PS_HOME/scripts).
And invoke the psconfig.sh shell script :

[oracle@orion2:/apps/psoft/ptools850]$ . ./psconfig.sh

7. Scripts modifications :Before running the DB Wizard, we need to be sure the mount points will be the right ones. Three scripts have to be modify according to your configuration. By default, the datafiles of the database are going to /mount_point_chosed_in_Wizard/oradata/SID.
The simplest way is to remove the "/oradata" from the files createdb10.sql, hcddl.sql and utlspace.sql under $PS_HOME/scripts.

8. Run the Wizard :

8.1 Wrong rights :

[oracle@orion2:/apps/psoft/ptools850]$ cd $PS_HOME/setup/PsMpDbInstall
[oracle@orion2:/apps/psoft/ptools850/setup/PsMpDbInstall]$ ./setup.sh
Setting temporary directory /tmp/IA.4774
Executing setup.linux   -DPS_UMASK=0022
Preparing to install...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...

Launching installer...

Preparing CONSOLE Mode Installation...

===============================================================================
Peoplesoft Database Configuration (created with InstallAnywhere by Macrovision)
-------------------------------------------------------------------------------



===============================================================================
Welcome
-------

Welcome to the PeopleSoft Database Configuration Wizard 8.50

This Wizard will assist you in configuring and loading a PeopleSoft database.



PRESS  TO CONTINUE:
===============================================================================


Please enter an installation location or press  to accept the default
   (DEFAULT: /apps/psoft/ptools850):
You do not have write permissions to the chosen installation destination.
Please choose a different location for installation

=> Note, nowhere in the doc it is saying that, but if you got this error, be sure you have write right against the folder containing the PS_HOME, in my case, I changed the rights on /apps/psoft regarding the Oracle user.
Once it is changed, go back to the Wizard, and press enter to continue :

8.2 DBName in upper case

Please enter an installation location or press  to accept the default
   (DEFAULT: /apps/psoft/ptools850):

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] :



===============================================================================




For a database platform of 'Oracle', are you installing a:


    1- Non-Unicode Database
  ->2- Unicode Database

To select an item enter its number, or 0 when you are finished [0] :

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] :


Oracle - UNICODE


===============================================================================




Select Character Set:


  ->1- AL32UTF8
    2- UTF8




To select an item enter its number, or 0 when you are finished [0] :


Database Create Type:


  ->1- Demo
    2- System
    3- PeopleTools System

To select an item enter its number, or 0 when you are finished [0] :

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] :

===============================================================================




Select PeopleSoft Application:


  ->1- PeopleSoft HRCS Database - US English

To select an item enter its number, or 0 when you are finished [0] :

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] :



===============================================================================



   Specify the directory path for 'sqlplus' [/apps/oracle/product/11.2.0/bin]
   :

   Specify the location for ORACLE_HOME [/apps/oracle/product/11.2.0]
   :

Location of modified scripts: /apps/psoft/ptools850/modifiedscripts

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] :



===============================================================================




Do you want to create a new SID or use existing one?


  ->1- Create new SID
    2- Use existing SID

To select an item enter its number, or 0 when you are finished [0] :

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] :



===============================================================================



   Oracle SID [HC]: o2h91wzd

   DatabaseName [HC]: o2h91wzd

   Mount Point 1( for SYSTEM and REDO LOGS file in createdb.sql, ex: NT 'C'  ,
   UNIX 'u01') [c]: oradata

   Mount Point 2 ( for PSTEMP and PSDEFAULT file in utlspace.sql, ex: NT 'C'  ,
   UNIX 'u01') [c]: oradata

   Mount Point 3 ( for all files in xxddl.sql, ex: NT 'C'  , UNIX 'u01') [c]
   : oradata

Here be careful, be sure about the mount points, no extra slashes are needed.

Enable AutoExtend for Peoplesoft Tablespaces?


  ->1- Yes
    2- No

To select an item enter its number, or 0 when you are finished [0] :

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] :



===============================================================================
Error
-----

Invalid entry: Database Name has to be within 8 characters and all upper cases

PRESS  TO ACCEPT THE FOLLOWING (OK):

=> well, as mentionned in the document, the database name must ne in upper case, it is dispointing me, most of customers I work for refuse to use dbname in upper case in Linux/Unix systems.
So, right now, we have to change everything in upper case :

[oracle@orion2:/home/oracle]$ cd $ORACLE_BASE/admin
[oracle@orion2:/apps/oracle/admin]$ ls
DMOCRM9  DMOFSCM9  DMOHRMS9  o2h91wzd
[oracle@orion2:/apps/oracle/admin]$ mv o2h91wzd O2H91WZD
[oracle@orion2:/apps/oracle/admin]$ cd O2H91WZD/pfile
[oracle@orion2:/apps/oracle/admin/O2H91WZD/pfile]$ ls
inito2h91wzd.ora
[oracle@orion2:/apps/oracle/admin/O2H91WZD/pfile]$ mv inito2h91wzd.ora initO2H91WZD.ora
[oracle@orion2:/apps/oracle/admin/O2H91WZD/pfile]$ vi initO2H91WZD.ora
[oracle@orion2:/apps/oracle/admin/O2H91WZD/pfile]$ more initO2H91WZD.ora
DB_NAME = O2H91WZD
DB_FILES = 1021
CONTROL_FILES = /oradata/O2H91WZD/O2H91WZD_ctl1.ora,/oradata/O2H91WZD/O2H91WZD_ctl2.ora
OPEN_CURSORS = 1000
db_block_size = 8192
NLS_LENGTH_SEMANTICS=CHAR
[oracle@orion2:/apps/oracle/admin/O2H91WZD/pfile]$ cd $ORACLE_HOME/dbs
[oracle@orion2:/apps/oracle/product/11.2.0/dbs]$ ls
hc_DMOCRM9.dat   hc_DMOHRMS9.dat   init.ora   lkDMOFSCM9  pwdDMOCRM9.ora   pwdDMOHRMS9.ora  spfileDMOCRM9.ora   spfileDMOHRMS9.ora
hc_DMOFSCM9.dat  inito2h91wzd.ora  lkDMOCRM9  lkDMOHRMS9  pwdDMOFSCM9.ora  pwdo2h91wzd.ora  spfileDMOFSCM9.ora
[oracle@orion2:/apps/oracle/product/11.2.0/dbs]$ rm inito2h91wzd.ora
[oracle@orion2:/apps/oracle/product/11.2.0/dbs]$ rm pwdo2h91wzd.ora
[oracle@orion2:/apps/oracle/product/11.2.0/dbs]$ ln -s /apps/oracle/admin/O2H91WZD/pfile/initO2H91WZD.ora .
[oracle@orion2:/apps/oracle/product/11.2.0/dbs]$ orapwd file=pwdo2h91wzd.ora password=oracle entries=5
[oracle@orion2:/apps/oracle/product/11.2.0/dbs]$
[oracle@orion2:/apps/oracle/product/11.2.0/dbs]$ more $ORACLE_HOME/network/admin/tnsnames.ora
[...]
O2H91WZD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orion2.phoenix-nga)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = O2H91WZD)
    )
  )

[oracle@orion2:/oradata]$ ls
DMOCRM9  DMOFSCM9  DMOHRMS9  lost+found  o2h91wzd
[oracle@orion2:/oradata]$ mv o2h91wzd O2H91WZD
[oracle@orion2:/oradata]$

Here, you need to restart the Wizard from scratch.

8.3 ORACLE_SID need to be set :
Restart from the dbname :

...
   Oracle SID [o2h91wzd]: O2H91WZD

   DatabaseName [o2h91wzd]: O2H91WZD

   Mount Point 1( for SYSTEM and REDO LOGS file in createdb.sql, ex: NT 'C'  ,
   UNIX 'u01') [oradata]:

   Mount Point 2 ( for PSTEMP and PSDEFAULT file in utlspace.sql, ex: NT 'C'  ,
   UNIX 'u01') [oradata]:

   Mount Point 3 ( for all files in xxddl.sql, ex: NT 'C'  , UNIX 'u01')
   [oradata]:


Enable AutoExtend for Peoplesoft Tablespaces?


  ->1- Yes
    2- No

To select an item enter its number, or 0 when you are finished [0] :

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] :



===============================================================================



   Peoplesoft owner ID (ex: 'SYSADM') [SYSADM]:

   Peoplesoft owner password (ex: 'SYSADM') [SYSADM]:

   Peoplesoft connect ID [people]:

   Peoplesoft connect password [peop1e]:

   Peoplesoft default tablespace  (ex: 'PSDEFAULT') [PSDEFAULT]
   :

   Location of init.ora file complete path
   [/apps/oracle/product/11.2.0/dbs/initO2H91WZD.ora]:

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] :
===============================================================================




Select Base Lanaguage


  ->1- ENG - US English




To select an item enter its number, or 0 when you are finished [0] :

Press 1 for Next, 2 for Previous, 3 to Cancel or 5 to Redisplay [1] :



===============================================================================
Pre-Installation Summary
------------------------

Please Review the Following Before Continuing:

Product Name:
    Peoplesoft Database Configuration

Install Folder:
    /apps/psoft/ptools850

Database Platform:
    Oracle  -  Unicode

Oracle SID:
    O2H91WZD

Application:
          PeopleSoft HRCS Database - US English


Database Name:
    O2H91WZD

Please type 'back' to go to previous panels




PRESS  TO CONTINUE:
===============================================================================
Installing...
-------------

 [==================|==================|==================|==================]
 [------------------|------------------|------------------|------------------]



===============================================================================
Please Wait...
--------------

Executing.. /apps/psoft/ptools850/modifiedscripts/runCreatedb10.sh.

=> This script should create the database, but if you forget to set the ORACLE_SID before running the DBWizard, this step will hang...

8.4 More parameters to be set :
Once the ORACLE_SID is set, of course, restart again the DB Wizard from the beginning. Everything run fine till the next failure :

Installing...
-------------

 [==================|==================|==================|==================]
 [------------------|------------------|------------------|------------------]



===============================================================================
Please Wait...
--------------

Executing.. /apps/psoft/ptools850/modifiedscripts/runCreatedb10.sh.



===============================================================================
Please Wait...
--------------

Executing.. /apps/psoft/ptools850/modifiedscripts/runUtlspace.sh.


===============================================================================
Please Wait...
--------------

Executing.. /apps/psoft/ptools850/modifiedscripts/hcddl.sql.


===============================================================================
Please Wait...
--------------

Executing.. /apps/psoft/ptools850/modifiedscripts/dbowner.sql.



===============================================================================
Please Wait...
--------------

Executing.. /apps/psoft/ptools850/modifiedscripts/psroles.sql.


===============================================================================
Please Wait...
--------------

Executing.. /apps/psoft/ptools850/modifiedscripts/psadmin.sql.




===============================================================================
Please Wait...
--------------

Executing.. /apps/psoft/ptools850/modifiedscripts/connect.sql.



===============================================================================


Executing DM: -CT ORACLE -CS orion2.phoenix-nga -CD LOCAL -CO SYSADM -CP SYSADM
-CI people -CW peop1e -FP /apps/psoft/ptools850/modifiedscripts/dbsetup.dms
Console Mode: true

Initializing Data Mover ... please wait

Console DM Mode: /apps/psoft/ptools850/bin/psdmtx
/apps/psoft/ptools850/setup/parm1
ExitCode: 0
Error: false

Initializing Data Mover ... please wait

ExitCode: 0
Error: false



===============================================================================
Error
-----

The following error occurred while running data mover .
Error: File not found: /apps/psoft/ptools850/scripts/o2h91wzdora.dms
Please select OK to exit installer.

PRESS  TO ACCEPT THE FOLLOWING (OK):

Ok, the file does not exists, eventhough Oracle user got all the rights.
After looking more, the Oracle user is not able to run the psdmtx at all (it is invoked by the DB Wizard) :

[oracle@orion2:/apps/psoft/ptools850]$ $PS_HOME/bin/psdmtx
/apps/psoft/ptools850/bin/psdmtx: error while loading shared libraries: libtmib.so: cannot open shared object file: No such file or directory

=> We need to set few more parameters before running the DB Wizard to be able to run psdmtx :

oracle@orion2:/home/oracle]$ export TUXDIR=/apps/bea/tuxedo/10gR3
[oracle@orion2:/home/oracle]$ export PS_HOME=/apps/psoft/ptools850
[oracle@orion2:/home/oracle]$ export LD_LIBRARY_PATH=$TUXDIR/lib:$LD_LIBRARY_PATH
[oracle@orion2:/home/oracle]$ . $PS_HOME/psconfig.sh
[oracle@orion2:/home/oracle]$ $PS_HOME/bin/psdmtx
PeopleTools 8.50.02 - Data Mover
Copyright (c) 2009 PeopleSoft, Inc.
All Rights Reserved

PSDMTX Error: invalid command line argument list!
PSDMTX Error: open file /apps/psoft/ptools850/log/psdmtx.log
Usage:  psdmtx  [-CT DB2|DB2ODBC|DB2UNIX|INFORMIX|MICROSFT|ORACLE|SYBASE]
                [-CS server name]
                [-CD database name]
                [-CO user id]
                [-CP user pswd]
                [-CI connect id]
                [-CW connect id pswd]
                [-I  process instance]
                [-FP filename]
          or
        psdmtx  [parmfile]
[oracle@orion2:/home/oracle]$

9. End of process :
Right now, we can re-run the DB Wizard and that'll will be till the end of the process.
The DMS should process entirely, if not, then it's coming back to the standard troubleshoting database load.
At the end we got :

...
Ended: Mon Dec 28 23:48:43 2009
Successful completion
ExitCode: 0
Error: false



===============================================================================
Installation Complete
---------------------

Congratulations. Peoplesoft Database Configuration has been successfully
installed to:

   /apps/psoft/ptools850

PRESS  TO EXIT THE INSTALLER:
[oracle@orion2:/apps/psoft/ptools850/setup/PsMpDbInstall]$

Exit 0 and error : false... hmmm, could be more friendly, but it was working fine.

We can verify all the log from $PS_HOME/log, especially the last two ones :

[root@orion2:/apps/psoft/ptools850/log]# more verifyToolsVer.log verifyAppsVer.log
::::::::::::::
verifyToolsVer.log
::::::::::::::

TOOLSREL
------------------------------------------------------------
8.50

::::::::::::::
verifyAppsVer.log
::::::::::::::

RELEASELABEL
--------------------------------------------------------------------------------
Core 5.10.00.000
Core 5.11.00.000
Core 5.12.00.000
Core 6.00.00.000
Core 6.01.00.000
Core 7.00.00.000
Core 7.01.00.000
Core 7.02.00.000
U.S. Federal Govt 7.00.00.000
Core 7.50.00.000
Core 7.51.00.000

RELEASELABEL
--------------------------------------------------------------------------------
Education and Government 7.50.00.000
U.S. Federal Govt 7.50.00.000
Public Sector 7.02.00.000
Education and Government 7.51.00.000
U.S. Federal Govt 7.51.00.000
HRMS 8.00.00.000
HRMS 8.00.01.000
Learning Solutions 8.00.00.000
Learning Solutions 8.00.01.000
HRMS 8.30.00.000
HRMS 8.80.00.000

RELEASELABEL
--------------------------------------------------------------------------------
HRMS 8.30.01.000
HRMS 8.80.01.000
HRMS and Campus Solutions 8.90.00.000
HRMS and Campus Solutions 9.00.00.000
HRMS 9.10.00.000

27 rows selected.

[root@orion2:/apps/psoft/ptools850/log]#

To confirm, from a client machine, you can open AppDesigner, and verify tables against the database directly (keep in mind, 11g is password case sensitive) :

[oracle@orion2:/apps/psoft/ptools850/setup/PsMpDbInstall]$ sqlplus sysadm/sysadm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 29 00:01:07 2009

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

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


Enter user-name:
[oracle@orion2:/apps/psoft/ptools850/setup/PsMpDbInstall]$ sqlplus sysadm/SYSADM

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 29 00:01:12 2009

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from psaccessprfl;

SYMBOLICID                  VERSION
------------------------ ----------
ACCESSID
------------------------------------------------
ACCESSPSWD                                        ENCRYPTED
------------------------------------------------ ----------
O2H91WZD                          1
sBzLcYlPrag=
sBzLcYlPrag=                                              1


SQL> select * from psdbowner;

DBNAME                   OWNERID
------------------------ ------------------------
O2H91WZD                 SYSADM

SQL>