Step By Step Install: Creating the Database

This post is a continuation of my installation of PeopleSoft on a Windows 8 Beta installation.  Previously, I installed HCM91 application CD.  This post is about building the database on the already installed Oracle Database.

So, here’s how I generated the database for my new PeopleSoft instance.

Creating the Scripts

The PeopleTools home has all of the SQL scripts that you need to create your database.  They are all located in the <PS Home>\scripts\nt, or for Unix/Linux, they are in <PS Home>/scripts/unix.  I started by editing the scripts to match my installation details.  Here are the changes that I made:

createdb10.sql

• renamed to 01-createdb10.sql
• Removed the “REMARK” on the startup nomount … line
• Replaced all instances of <SID> with HCM91
• Replaced all instances of <drive>:\oradata with c:\oracle\data

utlspace.sql

• renamed to 02-utlspace.sql
• Replaced all instance of <drive>:\oradata\<SID> with c:\oracle\data

dbowner.sql

• renamed to 03-dbowner.sql
• no editing

hcddl.sql

• renamed to 04-hcddl.sql
• Replaced all instance of <drive>:\oradata\\<SID> with c:\oracle\data
• removed all of the REMARKS at the beginning of the line for the alter database … autoextend

psroles.sql

• renamed to 05-psroles.sql
• no editing

psadmin.sql

• renamed to 06-psadmin.sql
• no editing (unless you want to change the &ADMIN and &PASSWORD and &PROMPT)

connect.sql

• renamed to 07-connect.sql
• no editing

If you’ll notice, I like renaming the version of the scripts that I edited to show the order that they will run.  I also like to create a single master script that will call each of them in order.  So, I created 00-all.sql:

@01-createdb10.sql
@02-utlspace.sql
@03-dbowner.sql
@04-hcddl.sql
@05-psroles.sql
@06-psadmin.sql
@07-connect.sql

Update: You can avoid the prompts like this:

@01-createdb10.sql
@02-utlspace.sql
@03-dbowner.sql
@04-hcddl.sql
@05-psroles.sql
@06-psadmin.sql SYSADM SYSADM PSDEFAULT
@07-connect.sql

Preparing for the Database

tnsnames.ora:  In <Oracle Home>\network\admin\tnsnames.ora, I placed the following:

HCM91 =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP) (HOST = localhost)(Port = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = HCM91)
 )
 )

init.ora:  Next, you need the settings for the database.  I created the initHCM91.ORA file in the <Oracle Home>\database\ folder.  This is what I put in it:

DB_NAME = HCM91

DB_FILES = 1021

OPEN_CURSORS = 1000

DB_BLOCK_SIZE = 8192

control_files = (c:\oracle\data\HCM91_control1, c:\oracle\data\HCM91_control2)

listener.ora: I also want to include setting up the TNS Listener at this point just because it belongs here.  I actually did it after running the scripts and before running Data Mover, but this is a better place for it.

I put this in <Oracle Home>\network\admin\listener.ora:

LISTENER =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS=(PROTOCOL=tcp) (HOST=PT852) (PORT=1521))
 (ADDRESS=(PROTOCOL=ipc) (KEY=extproc))))
SID_LIST_LISTENER=
 (SID_LIST=
 (SID_DESC=
 (GLOBAL_DBNAME=HCM91)
 (ORACLE_HOME=c:\oracle\dbhome_1)
 (SID_NAME=HCM91))
 (SID_DESC=
 (SID_NAME=plsextproc)
 (ORACLE_HOME=c:\oracle\dbhome_1)
 (PROGRAM=extproc)))

Running the Scripts

Open a command prompt in the same directory as the scripts.  In Windows 8, you can use the “Open Command Prompt” option in the Windows Explorer window open to the “NT” directory.

First, we need to set the SID environment variable with this command:

set ORACLE_SID=HCM91

Then, I ran this to start the listener:

lsnrctl start

Then, we need to create the password file:

orapwd file=c:\oracle\dbhome_1\database\pwdHCM91.ora password=manager entries=5

Next, we need to create the Windows service.

oradim -new -sid HCM91 -intpwd manager -startmode auto -pfile c:\oracle\dbhome_1\database\initHCM91.ora

Finally, launch sqlplus like this:

sqlplus / as sysdba

And, you can run all of the scripts using my “all” script like this:

@00-all.sql

At some point, it prompts for the PeopleSoft Owner ID.  I chose SYSADM for the ower id, and I chose PSDEFAULT for the default tablespace.

Loading the Database

Data mover is the tool that will create all of the tables and load any necessary data into the tables.  At the login screen, I used my SYSADM user that I entered into my script.  Logging in with a database user id is called bootstrap mode and is necessary at this point because we don’t have a PSOPRDEFN table with any PeopleSoft users.Then, I opened up Data Mover, and connected with the SYSADM user.

Then, I started the database setup wizard.  It is located at File > Database setup.

The first question is the character set.  I chose non-unicode because I only plan to use English.

Next, I chose the application to install.  The PeopleSoft HR Database was only available here because I installed the HR application CD prior to this step.

Now, I had to provide all of the user information for the database.

After this, the script should be ready to go.  I just clicked the green light button to run the script.

After quite a while, the script finished saying it completed successfully.

Just for reference, I went ahead and placed the full script that I ran for my install:

REM - hcm91ora.dms
REM - Created by Data Mover 8.52.09 Sun Aug 05 00:02:58 2012
REM -
REM - Database Platform: Oracle
REM - Non-Unicode Database
REM - Selected Character Set: WE8ISO8859P1 - Western European ISO 8859-1
REM - Generate Latin-1 Code
REM -
/
REM - PeopleSoft HR Database - US English
/
SET LOG C:\pshome\log\hcengs.log;
SET INPUT C:\pshome\data\hcengs.db;
SET COMMIT 30000;
SET NO VIEW;
SET NO SPACE;
SET NO TRACE;
SET UNICODE OFF;
SET IGNORE_DUPS;
SET ENABLED_DATATYPE 9.1;
IMPORT *;

REM - PeopleSoft HR Database - US English
/
SET LOG C:\pshome\log\hcengl.log;
SET INPUT C:\pshome\data\hcengl.db;
SET COMMIT 30000;
SET NO VIEW;
SET NO SPACE;
SET NO TRACE;
SET UNICODE OFF;
SET IGNORE_DUPS;
SET ENABLED_DATATYPE 9.1;
IMPORT *;

REM - Final Database cleanup
REM -
REM - Based on your inputs to Database Setup, you will be using
REM - ConnectID's to connect to your PeopleSoft Application
REM -
/
INSERT INTO PS.PSDBOWNER VALUES('HCM91', 'SYSADM');
UPDATE PSSTATUS SET OWNERID = 'SYSADM';
UPDATE PSOPRDEFN SET SYMBOLICID = 'SYSADM1', OPERPSWD = OPRID, ENCRYPTED = 0;
UPDATE PSACCESSPRFL SET ACCESSID = 'SYSADM', SYMBOLICID = 'SYSADM1', ACCESSPSWD = 'SYSADM', VERSION = 0, ENCRYPTED = 0;
UPDATE PSOPTIONS SET LICENSE_CODE = '117fefffff8fffffebfb197c32sm64u', LICENSE_GROUP = '1';
UPDATE PS_INSTALLATION SET
HGA='Y', BENEFIT_ADMINISTRN='Y', PSERECRUIT='Y', CCU='Y', DI='Y', INSTALLED_GP_ARG='Y',
INSTALLED_GP_AUS='Y', INSTALLED_GP_BRA='Y', INSTALLED_GP_CHN='Y', INSTALLED_PAY_GBL='Y',
INSTALLED_GP_FRA='Y', INSTALLED_GP_DEU='Y', INSTALLED_GP_HKG='Y', INSTALLED_GP_IND='Y',
INSTALLED_GP_ITA='Y', INSTALLED_GP_JPN='Y', INSTALLED_GP_MYS='Y', INSTALLED_GP_MEX='Y',
INSTALLED_GP_NLD='Y', INSTALLED_GP_NZL='Y', INSTALLED_GP_SGP='Y', INSTALLED_GP_ESP='Y',
INSTALLED_GP_CHE='Y', INSTALLED_GP_THA='Y', INSTALLED_GP_UK='Y', INSTALLED_GP_USA='Y',
EHC='Y', HR='Y', FO_PBM='Y', INSTALLED_PAY_INT='Y', ADP='Y', INSTALLED_PAY_NA='Y',
PA='Y', RSRW='Y', ST='Y', SPN='Y', PSERECRUIT_MGR='Y', TL='Y', PSEBENEFITS='Y', PSECOMP='Y',
PSECOMP_MGR='Y', PSEA='Y', PSEPAY='Y', PSEPERF_MANAGEMENT='Y', PSEPROFILE='Y', PSEPROFILE_MGR='Y';
GRANT SELECT ON PSSTATUS TO PEOPLE;
GRANT SELECT ON PSOPRDEFN TO PEOPLE;
GRANT SELECT ON PSACCESSPRFL TO PEOPLE;

REM - ENCRYPT PASSWORD
/
SET LOG C:\pshome\log\encrypt.log;
ENCRYPT_PASSWORD *;

REM - CREATE TRIGGERS
/
SET LOG C:\pshome\log\triggers.log;
CREATE_TRIGGER *;

REM - CREATE VIEWS
/
SET LOG C:\pshome\log\views.log;
REPLACE_VIEW *;

REM - CREATE TEMP TABLES
/
SET LOG C:\pshome\log\temptbls.log;
CREATE_TEMP_TABLE *;