Monday, December 28, 2009

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>
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.

4 comments:

Anonymous said...

Nicolas, wow! That was exhausting just reading it! I remember having such difficulty with the DB wizard what I used it at my organization. I swore I'd do the manual DB creation in the future and this blog post confirms not much has changed since PT8.45 DB wizard and now. Great post, hope the guys at Oracle see it and look to improve the wizard. -Mike

Nicolas Gasparotto said...

Exhausting. You're absolutely right. This is the best word I can think about to describe how to get the DBWizard working.
Anyway, that was a one shot exercise, and I don't think I'll try it again (at least for Oracle DB) till the next release. I'll leave to other people the pleasure to test it on Windows, someone ?

Nicolas.

PS: very funny, the word verification to post this comment is "gamer", an other nice word regarding people trying to play around DBWizard, isn't it ?

Anonymous said...

The tool is a solution looking for a problem. If a person needs a wizard to create a database on SQL Server maybe they should reconsider if they have the skills to install PSFT at all. Another benefit of doing it manually is you don't have to install PeopleTools on your DB server. Thanks for all your informative posts.

Vinicius Papa said...

you are the best dude !!

Thanks a lot,