Step By Step Virtual PS Install: Database Creation

This is a continuation of my virtual PeopleSoft installation.  In this step, we will create the database in an already installed Oracle Database system.  So, this assumes that the Oracle Database software is already installed.  Loading the PeopleTools data will be covered in the next step.  To see the complete list of steps, click here.

I have been told that it is much better to do this the manual way.   The manual database method is covered in Appendix B in the “Enterprise PeopleTools 8.49 Installation for Oracle” and will give you a better feel for what is going on during this install.

Before beginning, we need to create the directory where the actual database files will reside.  In my case, I chose to create a directory called “db” in my home directory.  So, I ran in a terminal:

mkdir -p /home/pshr/db/oradata/HRDMO

Also, make sure that you add the Oracle bin directory and the PeopleSoft bin directory to your path.  You can view your path with:

echo $PATH

You can add it to your path with (make sure the directory is correct):

export PATH=/home/pshr/app/pshr/product/11.1.0/db_1/bin/:/home/pshr/pshome/bin:$PATH

You can verify that Linux sees sqlplus by typing “which sqlplus”.  This should give you the path of the program would run if you typed sqlplus.

You also need to set the ORACLE_HOME.  You do it the same way:

export ORACLE_HOME=/home/pshr/app/pshr/product/11.1.0/db_1

Do the same for the ORACLE_SID and the NLS_LANG variable:

export ORACLE_SID=HRDMO
export NLS_LANG=AMERICAN_AMERICA.UTF8

Now, if the database is not started, make sure that you start it with:

lsnrctl start
dbstart $ORACLE_HOME

Create Database

Next create the init.ora file.  The setup installer will ask you where you created it, but I am using /home/pshr/app/pshr/product/11.1.0/db_1/dbs/initHRDMO.ora.  You could change some of the paths, but my file looked like this:

DB_NAME = HRDMO
DB_FILES = 1021
CONTROL_FILES = /home/pshr/db/cntrlHRDMO
OPEN_CURSORS = 255
db_block_size = 2048

Note: You could create the file like this:

gedit /home/pshr/app/pshr/product/11.1.0/db_1/dbs/initHRDMO.ora

CreateDB10.SQL

Make sure the <ps home>/modifiedscripts directory exists.  Then, copy the createdb10.sql file to the modified scripts directory.

mkdir /home/pshr/pshome/modifiedscripts
cp ~/pshome/scripts/unix/createdb10.sql ~/pshome/modifiedscripts/

Now, edit the SQL file:

gedit ~/pshome/modifiedscripts/createdb10.sql

Here are the changes you should make:

• Replace <SID> with HRDMO or whatever SID you have chosen.
  • Line 30 (init<SID>)
  • Line 32 (create database <SID>)
  • The paths listed below
• Change the paths for all of the files
  • Line 39 (system01.dbf)
  • Line 41 (sysaux01.dbf)
  • Line 42 (temp01.dbf)
  • Line 43 (psundots01.dbf)
  • Line 44 (redo01.log)
  • Line 45 (redo02.log)
  • Line 46 (redo03.log)

Save your changes and run it like this:

cd ~/pshome/log
sqlplus ‘/ as sysdba’
@/home/pshr/pshome/modifiedscripts/createdb10.sql

Type exit to leave sqlplus once the script is finished running.

tnsnames.ora

You need to make sure that you have the database setup in the tnsnames.ora file:

gedit $ORACLE_HOME/network/admin/tnsnames.ora

Mine looks like this:

HRDMO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = HRDMO)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

utlspace.sql

cp ~/pshome/scripts/unix/utlspace.sql ~/pshome/modifiedscripts/

Now, edit the SQL file:

gedit ~/pshome/modifiedscripts/utlspace.sql

You will need to replace all of the occurrences of “/u03/oradata/<SID>” with your actual path.  In my case, this is “/home/pshr/db/oradata/HRDMO”.  You can do so with Search, Replace.

Then, run the script the same way:

cd ~/pshome/log
sqlplus ‘/ as sysdba’
@/home/pshr/pshome/modifiedscripts/utlspace.sql

Type exit to leave sqlplus once the script is finished running.

pupbld.sql

The first time that I ran the dbowner.sql, I got an error message about product user profile information not being loaded.  The message looked like this:

SQL> CONNECT PS/PS;
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.

So to fix the issue, I ran the pupbld.sql:

sqlplus ‘/ as sysdba’
@/home/pshr/app/pshr/product/11.1.0/db_1/sqlplus/admin/pupbld.sql

dbowner.sql

cp ~/pshome/scripts/unix/dbowner.sql ~/pshome/modifiedscripts/

You may want to review, but you probably won’t need to make any changes to the SQL script:

gedit ~/pshome/modifiedscripts/dbowner.sql

Then, run the script the same way:

cd ~/pshome/log
sqlplus ‘/ as sysdba’
@/home/pshr/pshome/modifiedscripts/dbowner.sql

Type exit to leave sqlplus once the script is finished running.

xxddl.sql

The ddl scripts depend on what application you plan to install.  For HCM, the only one to install is hcddl.sql.

cp ~/pshome/scripts/unix/hcddl.sql ~/pshome/modifiedscripts/

Now, edit the SQL file:

gedit ~/pshome/modifiedscripts/hcddl.sql

You will need to replace all of the occurrences of “/u04/oradata/<SID>” with your actual path.  In my case, this is “/home/pshr/db/oradata/HRDMO”.  You can do so with Search, Replace.

Then, run the script the same way:

cd ~/pshome/log
sqlplus ‘/ as sysdba’
@/home/pshr/pshome/modifiedscripts/hcddl.sql

Type exit to leave sqlplus once the script is finished running.

psroles.sql

cp ~/pshome/scripts/unix/psroles.sql ~/pshome/modifiedscripts/

You may want to review, but you probably won’t need to make any changes to the SQL script:

gedit ~/pshome/modifiedscripts/psroles.sql

Then, run the script the same way:

cd ~/pshome/log
sqlplus ‘/ as sysdba’
@/home/pshr/pshome/modifiedscripts/psroles.sql

Type exit to leave sqlplus once the script is finished running.

psadmin.sql

cp ~/pshome/scripts/unix/psadmin.sql ~/pshome/modifiedscripts/

You may want to review, but you probably won’t need to make any changes to the SQL script:

gedit ~/pshome/modifiedscripts/psadmin.sql

Then, run the script the same way:

cd ~/pshome/log
sqlplus ‘/ as sysdba’
@/home/pshr/pshome/modifiedscripts/psadmin.sql

The script has three prompts; here are the values I used

PeopleSoft Owner ID: SYSADM
Password: SYSADM
Default Tablespace: PSDEFAULT

Note: make sure these values are uppercase.  11g is case sensitive.

Type exit to leave sqlplus once the script is finished running.

connect.sql

cp ~/pshome/scripts/unix/connect.sql ~/pshome/modifiedscripts/

You may want to review the script.  Make sure that it is creating the correct user, password, and tablespace.  For me, it was already people, password peop1e, and tablespace PSDEFAULT.

gedit ~/pshome/modifiedscripts/connect.sql

Then, run the script the same way:

cd ~/pshome/log
sqlplus ‘/ as sysdba’
@/home/pshr/pshome/modifiedscripts/connect.sql

Type exit to leave sqlplus once the script is finished running.