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:
- 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
- renamed to 02-utlspace.sql
- Replaced all instance of <drive>:\oradata\<SID> with c:\oracle\data
- renamed to 03-dbowner.sql
- no editing
- 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
- renamed to 05-psroles.sql
- no editing
- renamed to 06-psadmin.sql
- no editing (unless you want to change the &ADMIN and &PASSWORD and &PROMPT)
- 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:
Then, I ran this to start the listener:
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:
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 *;