PeopleSoft 8.53: Creating PeopleSoft HCM9.2 Database

by Prashant on April 22, 2013

in PeopleSoft Administration, PeopleSoft Basics

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 .

Also Read:  (SQR 3725) Bad return fetching row from database.

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.

Also Read:  Common Database Errors

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.

Also Read:  PeopleSoft Performance Tuning Using OEM

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

{ 17 comments… read them below or add one }

Rafa May 12, 2014 at 3:25 pm

Hi Prashant,
Congrats for all your very useful tutorials.
I was trying this one
http://peoplesofttutorial.com/peoplesoft-8-53-creating-peoplesoft-hcm9-2-database/
My problem comes when trying to do the database setup because I don’t see any demo database to add, just system one.
Any idea of what I could check?
best regards,
Rafa

Reply

Prashant May 12, 2014 at 10:19 pm

Hi Rafa
Happy to hear that you’re finding the information helpful. 🙂
While doing the database setup in datamover, please make sure that option for database type is selected as ‘Demo’.

– Prashant

Reply

Rafa May 13, 2014 at 3:14 am

Yes, it is selected as Demo but nothing appears. However If I select system it appears the ‘Peopletools system database’.
-rafa

Reply

Rafa May 13, 2014 at 3:48 am

I found the issue. I am using the new file structure where Ptools and Application are in different homes. Configuration Manager should point correctly to those PS_HOME and PS_APP_HOME paths.
Another question: why is important to change the path on DBBIN as you said in the post?
kr

Reply

adnan Rana May 23, 2014 at 11:59 am

Dear prashant Hi,
can you plz provide complete installation file in pdf or word for peoplesoft.

Reply

Prashant June 12, 2014 at 3:24 pm

Hi Adnan
Our blog articles can be downloaded in PDF format by clicking the PDF button at the bottom of each article. This will allow you to save the article in a easy to read format.

Reply

Michele October 7, 2014 at 3:00 am

Dear Prashant,
I have got some issue with the SetUp of Data base but I don’t see any demo database to add, just system one. I read the info of Rena about the file structure but i don’t work yet .

thx in advance.
Best regards
Michele

Reply

Prashant October 8, 2014 at 10:02 pm

Hi Michele

Please open the configuration manager and on profile tab, go to process scheduler tab . Please check PS_APP_HOME is pointing to your PeopleSoft Application Home. Invoke data mover again and you should see demo database option now.
Hope it helps.

Reply

Michele October 6, 2014 at 6:09 pm

Sorry Rafa,

I don’u understand very well the step where you check the PS_HOME and PS_APP_HOME .
I’ve PS8 in the path : c:\pt8.54\appserv
\bin
\ccr
…..
So, when I open the configuration Manager –> Profile –> edit –> Process Scheduler , what i ‘ll put in box General –> PeopleSoft Home DIrectory ( PS_HOME ) and People Soft App Home DIrectory (PS_APP_HOME?)?

I tried with C:\PT8.54 in (PS_HOME) and C:\PT8.54 in (PS_APP_HOME) bat I nothing appears . ( Demo is select ).

Please help me.
bye

Reply

Prashant October 8, 2014 at 10:03 pm

Hi Michele

Have you installed PeopleSoft Application home yet. If not, please use application setup files to install PeopleSoft application (HRMS/FSCM etc) and then enter that path in PS_APP_HOME.

Reply

Francis Xavier December 22, 2014 at 12:56 am

Hi Prashant –

First of all thanks for this nice blog. I am stuck with booting datamover in bootstrap mode.
Getting the error “File: SQL Access ManagerSQL error. Stmt #: 2 Error Position: 0 Return: 12154 – ORA-12154: TNS:could not resolve the connect identifier specified ” .

I have followed the steps provided by you.

1. Installed Oracle 11g 64 bit.
2 .Installed Oracle 11g 32 bit client to a different folder.
3. Ran all the scripts in the order provided.

I am able to connect to database from sqlplus.
“Select name from v$database” also gives my database name correctly.
Where am I going wrong ? I have done installation with SQL server several times and never faced any Issue.

I am installing all this in my personal PC.

Reply

Nicolas January 3, 2015 at 11:55 am

Hi Prashant,

I’ve followed your instructions thoroughly.
However when I go into the step: Update PeopleSoft PeopleTools messages,
Open Application Designer using user id PS and then go to Tools > Copy Project > From File.

I get:
“File: SQL Access ManagerSQL error. Stmt #: 2 Error Position: 0
Return: 1045 – ORA-01045: user PS lacks CREATE SESSION privilege; logon denied ”

Followed by:
“Invalid User ID and password for signon.”

1.- For the first notification:

What I’ve observed: User PS does not have CREATE SESSION
And as I’ve read through various sources: PS -> should not You will be asked to provide details about few ids.

.. for the Access Password for SYSADM and PEOPLE should they be on either lower case or upper case?
(Same thing with PTWEBSERVER)

Please help as I’ve been for weeks trying to get this tool setup.

Reply

Nicolas January 3, 2015 at 11:58 am

please disregard the first comment.

Hi Prashant,

I’ve followed your instructions thoroughly.
However when I go into the step: Update PeopleSoft PeopleTools messages,
Open Application Designer using user id PS and then go to Tools > Copy Project > From File.

I get:
“File: SQL Access ManagerSQL error. Stmt #: 2 Error Position: 0
Return: 1045 – ORA-01045: user PS lacks CREATE SESSION privilege; logon denied ”

Followed by:
“Invalid User ID and password for signon.”

1.- For the first notification:

What I’ve observed: User PS does not have CREATE SESSION
And as I’ve read through various sources: PS – should not – have Create Session.

So why do I have a misleading message saying user PS lacks create session?

2.- For the second notification. How do I fix the invalid used id and password signon?

————————————————-
As for that, I have a couple of questions.

1.- I installed Oracle Client 32-bit as mentioned on step: Running Database Creation Wizard
“Choose Process Scheduler tab and change the Oracle Home path in DDBIN field as shown below.”

On the screenshot it shows the following directory:
\client2\product\11.2.0\client_1\network\admin

but on the internet they mention it should be as followed:
\client2\product\11.2.0\client_1\BIN

What is the correct path?

2.- On the step: Log into Data mover and run database setup wizard.
– You will be asked to provide details about few ids.

.. for the Access Password for SYSADM and PEOPLE should they be on either lower case or upper case?
(Same thing with PTWEBSERVER)

Please help as I’ve been for weeks trying to get this tool setup.

Reply

Raju December 29, 2016 at 9:12 am

Hi,

You mentioned to change the log path for running grant.sql
Please, provide more information about that step.

Reply

Raju December 29, 2016 at 9:44 am

Hi,
Please Provide more information about log path for running grant.sql file

Reply

Prashant December 29, 2016 at 12:04 pm

It’s not mandatory but you can edit grant.sql to update the path where log files are stored. e.g you can add a statement to log output as shown below
SPOOL C:\LOGS\grant.log;

Reply

Mathivanan May 7, 2017 at 10:34 am

While executing psroles.sql scripts am getting the below error. Can you please help on this.

ORA-01917: user or role ‘PSADMIN’ does not exist
ORA-29393: user PSADMIN does not exist or is not logged on
ORA-06512: at “SYS.DBMS_RMIN”, line 33
ORA-06512: at “SYS.DBMS_RESOURCE_MANAGER_PRIVS”, line 110
ORA-06512: at line 1

Thanks in advance.

Reply

Cancel reply

Leave a Comment

Previous post:

Next post: