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

 

clip_image004

Leave the settings as shown below and Click ‘Next’

 

clip_image006

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.

 

clip_image008

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.

 

clip_image010

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’

 

clip_image012

Click ‘Next’ on next screen

clip_image014

Click ‘Next’ to choose default settings

 

clip_image016

 

clip_image018

 

clip_image020

clip_image022

 

clip_image024

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

 

clip_image026

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

 

clip_image028

If shown, ignore the below warning.

 

 

clip_image030

 

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

 

 

clip_image036

 

Once database creation is complete

 

clip_image040

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)

clip_image042

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’

clip_image044

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

clip_image046

clip_image048

 

Edit and Run psdbowner.sql

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

clip_image050

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

ALTER USER SYSTEM IDENTIFIED BY <NEW _PASSWORD>;

clip_image052

clip_image054

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’

clip_image060

clip_image062

clip_image064

Edit and Run psroles.sql

Update the log path and run the psroles.sql

clip_image066

clip_image068

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.

clip_image070

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?

clip_image072

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.

image

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 2nd 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?

image

Go to File > Database Setup.

Choose Non-Unicode or Unicode based on your requirement.

clip_image074

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

clip_image076

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.

clip_image078

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.

clip_image080

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

clip_image082

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

clip_image084

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

clip_image086

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

clip_image088

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

clip_image090

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

clip_image092

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

clip_image094

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

clip_image096

clip_image098

Run alter Alter Audit

Open Application Designer and Select ‘Insert Into Project’

clip_image100

Choose the settings in Alter tab as shown below.

clip_image102

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

clip_image104

clip_image106

Prashant
 

Click Here to Leave a Comment Below 17 comments