17

PeopleSoft 8.53: Creating PeopleSoft HCM9.2 Database

This post is in series of post for Installation of HCM9.2 on Windows 7. It’s one of the most important task in PeopleSoft Installation and require that you finish all the previous steps. PeopleSoft database creation is a long process and to make it easy to follow, below post has been divided into sections.

Creation Oracle Database using DBCA

To start with PeopleSoft database creation, we are going to need an Oracle Database first. So using Oracle DBCA (Database Configuration Assistant), we will create a new database.

Go to Start > Oracle –Oradb11g_dbhome1 > Configuration and Migration Tool > Database Configuration Assistant.

Choose ‘Create a Database’ and Click ‘Next’

 

Leave the settings as shown below and Click ‘Next’

 

Next step, requires you to provide a unique name for your database, e.g. in our case I’ve kept the db name as PSHRDMO representing PeopleSoft HRMS database for demo. You can choose any name upto 8 characters.

 

You would surely want to configure Enterprise Manager. For any alert notifications or backup settings, choose whichever you want. I haven’t chosen those settings to keep the installation simple.

 

This is important step and here you need to set password for your database user ids like SYS, SYSTEM etc. You can either give separate password for each account (recommended) or simply select on password for all account.

After configuring the password, click ‘Next’

 

Click ‘Next’ on next screen

Click ‘Next’ to choose default settings

 

 

 

 

Finally you will be asked to review the settings, before the database creation begins.  Click ‘OK’ to start the database creation.

 

Progress of database creation progress will be shown in the progress bar.

 

If shown, ignore the below warning.

 

 

 

You can also see the steps database creation is currently performing.

 

 

 

Once database creation is complete

 

Oracle Database creation is finished now and you can access the database using oracle user ids/password.

 

 

Creating PeopleSoft Database

 

Now we need to create our PeopleSoft Database by following the steps one after the other. If you get any errors, make sure you resolve those before moving ahead with installation .

Start Sqlplus.exe and run the following commands

SET ORACLE_SID=PSHRDMO
SQLPLUS / AS SYSDBA

In SQL Prompt, run the following command to check the database name

SELECT NAME FROM V$DATABASE;

(show the name of current database)

Edit and run UTLSPACE.SQL

(C:\PT8.53\scripts\nt\PSHRDMO\utlspace.sql)

In the next steps, replace the path of dbf files with the actual location of dbf files of PSHRDMO database that we created in above step.

E.g. Replace ‘<drive>:\oradata\<SID>\’ with ‘C:\app\tyagip\oradata\PSHRDMO’

Save the changes and then run the script via sqlplus

Go to Sql prompt and run following command

@C:\PT8.53\scripts\nt\PSHRDMO\utlspace.sql

 

Edit and Run psdbowner.sql

Add the log path and update the password for system user. Make sure the password is correct.

If you want to reset the password for system user run the following command in sqlplus.

ALTER USER SYSTEM IDENTIFIED BY <NEW _PASSWORD>;

Edit and Run hcddl.sql

Set log to point to C:\PT8.53\scripts\nt\PSHRDMO\logs

Replace ‘<drive>:\oradata\<SID>\’ with ‘C:\app\tyagip\oradata\PSHRDMO’

Edit and Run psroles.sql

Update the log path and run the psroles.sql

Edit and Run psadmin.sql

Connect as system user and run psadmin.sql

You will be asked to provide the access id/owner id for the database. This is the id that owns the PeopleSoft database. Usually it’s chosen as ‘sysadm’ but if you desire you can change it.

Edit and Run connect.sql

Edit the log path and run connect.sql

Below script prompts you to provide a connect id which is used for initial authentication. Usually it’s chosen as ‘people’ but can be changed.

Related Article : What is PeopleSoft Connect ID?

Running Database Creation Wizard

Before you run next step, we need to make sure that PeopleSoft Configuration Manager is pointing to Oracle 32-bit Client Home instead of default home of Oracle database.

Go to Start > All Programs > PeopleTools 8.5 > Configuration Manager. Select ‘Profile’ tab and click edit. Choose Process Scheduler tab and change the Oracle Home path in DDBIN field as shown below.

If you haven’t already installed the Oracle 32-bit client, you can do it now and then follow the steps below. Reason for installing Oracle 32-bit client is that PeopleTools developer tools like App Designer, data mover are still 32 bit applications, even though they are support on 64-bit operating systems.

Note: Install 32 bit oracle client for windows 7. While installing choose 2^nd option to install manage components too. Also install it in a separate directory than oracle home for oracle database.

Log into Data mover and run database setup wizard.

Now invoke the data mover utility from the PeopleTools in Start Menu and login into bootstrap mode i.e. login using access id /owner id (sysadm).

Related Article: How Does PeopleSoft Signon Works?

Go to File > Database Setup.

Choose Non-Unicode or Unicode based on your requirement.

Select the ‘Demo’ from Database Type and then click ‘Add’ button. Make sure your screen looks similar to one below.

You will be asked to provide details about few ids.

Database Name : <Name of your PeopleSoft Database> e.g. PSHRDMO
Symbolic Id : SYSADM1
Access ID: SYSADM
Access Password: <password entered to log into data mover in boot strap mode>
Connect ID: PEOPLE
Application Server ID : Id that will be used to start application server
WebServer ID: Id that will be used for WebServer. Default value is PTWEBSERVER.

After you click ‘Finish’, a data mover script (dms) will be generated.  Before running the below script, you can make sure path for the log files are updated.

It may take few hours for the above script to finish. In the most of the cases, data mover window will show NOT RESPONDING, but it will be actually doing the task. For status, you can check the log files and increase in their log size will verify the execution of the scripts. Don’t Cancel or kill the data mover window, when it shows not responding.

Connect to database as sysadm and run the following command

SELECT TOOLSREL FROM PSSTATUS;

Edit and Run Grant.sql

Grant.sql is located in PS_HOME/scripts. Update log path and  Replace <CONNECTID> with people

Connect to database as sysadm and run grant.sql

We have finished first step and major step in our PeopleSoft database creation.

Update PeopleSoft PeopleTools messages

• Edit msgtlsupg.dms to update log path and data file path.

Log into data mover as sysadm (bootstrap) and run msgtlsupg.dms

• Copying PPLTLS84CUR project from File
  Open Application Designer using user id PS and then go to Tools > Copy Project > From File. Browse PS_HOME/project and select PPLTLS84CUR project and click ‘Select’. After all the objects are selected, click Copy to start copying the project into database.
• Copying PPLTLS84CURDEL project from File
  Open Application Designer using user id PS and then go to Tools > Copy Project > From File. Browse PS_HOME/project and select PPLTLS84CURDEL project and click ‘Select’. After all the objects are selected, click Copy to start copying the project into database.
• Build records in PPLTLS84CUR project
  In Application Designer, Open PPLTLS84CUR project and build project and execute the scripts.
• Copying PT84TBLSPC project from file
• Build PT84TBLSPC project

Note: Please check the build and alter settings for the above step in Installation Guide for PeopleTools 8.53 for Oracle.

Running Createvw.dms

Running AE programs

Open command prompt and change directory to PS_HOME\bin\client\winx86 and run the below AE programs

psae -CD PSHRDMO -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPT850PTFP

psae -CD PSHRDMO -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPT852PTFP

psae -CD PSHRDMO -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPTWBPFNVP

psae -CD PSHRDMO -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPTPGFLRS

psae -CD PSHRDMO -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPTHASH

psae -CD PSHRDMO -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPTSERVOPR

Run VERSION AE program

Run DDDAUDIT.SQR
(Use similar settings as shown below for SYSAUDIT.SQR, Make sure to change the report name and log path)

Run SYSADUIT.SQR
Run SYSAUDIT.SQR as shown below

Run alter Alter Audit

Open Application Designer and Select ‘Insert Into Project’

Choose the settings in Alter tab as shown below.

Update the Scripts tab as shown below. You can select to generate separate sql files for different tasks.