On The Peoplesoft Road: December 2009

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>

Note the SYMBOLICID is the DBNAME, usually it is SYSADM1 (when you're doing it manually).

10. Conclusion :
Well, I cannot say I'm convinced by the DBWizard, still too much restrictive, starting by the dbname in UPPER case. If someone can explain why is that mandatory, I'll be happy to know.
Second reason is when something is going wrong, like hanging or error out, then you have not many things in your hands to debug it.
Third, still too many tasks have too be done manually up front...

The only reason I can see so far to use it is... hmmm... you don't care about the order of running all the different scripts which need to be run when you're doing it manually. But they are not a lot and rightly descfribed in the doc.

Enjoy,

Nicolas.