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.
