PeopleSoft Installation Made Extremely Easy: Part 2

Creating and Configuring the Database

In this part we will be creating Database for our PeopleSoft application and before we begin going so we must have the Oracle Database Server, PeopleTools 8.52 and PeopleSoft HCM 9.1 Installed in our system as mentioned in the part 1 -

There are two types of databases we create in PeopleSoft:

System: The System database has no company specific data and can be used to load your data and
begin development of your production database. This is the database which is chosen when PeopleSoft where company intend to load the live production data and perform the developments.

Demo: The Demo database contains data for a sample company, and can be used immediately for
demonstration. This one will be most suitable for us because with the sample data for a company already available we as an individual can use it for our PeopleSoft training.

So Let's get down to the business and create the database for our PeopleSoft installation.

Step 1: Creating Windows Service for the Database

Since we are going to use the term SID everywhere in this article so let me explain you first that an SID is unique System Identifier for a particular database. Since, in our case we are going to create a demo database so we will create an SID for it with the name HRDEMO. It's not mandatory to have this name, you can choose your own name but the point is, SID should be meaningful so that it reflect the purpose of database for which it's being created.

Once we have decided the SID for our demo database, we need to create a window service for it. so follow below steps:

1. open the command prompt
2. run the following command -   oradim -NEW -SID HRDEMO
3. Check if the service OracleServiceHRDEMO has been created in windows service manager (Start > Run > Services.msc)
4. Right click on the service OracleServiceHRDEMO, open the properties and change the Startup Type to Automatic and then start the service.

Step 2: Creating oracle initialization  parameter file (initHRDEMO.ora) 

When the Oracle database is started, one of the first things it needs to do is read the database initialization parameter file initHRDEMO.ora. The parameter file defines the overall instance configuration, such as how much memory should be allocated to the instance, the file locations, and internal optimization parameters.

Do following steps to create initHRDEMO.ora file

1. Go to  ORACLE_HOME\dbs and open the existing file init.ora and create a copy of it with the name initHRDEMO.ora by Save As option.
2. Replace all the occurrences of with your oracle base directory i.e - C:\app\Administrator 
3. Replace the existing SID- 'ORCL' on the db_name with your new SID- 'HRDEMO'
4. Replace the 'UNDOTBS1' on the undo_tablespace with the Table space present in createdb10.sql at C:\PS_HOME\scripts\nt
5. Once the above changes are done in the file initHRDEMO, save it and move it from the current directory \dbs to the directory \database

Now, create a folder HRDEMO under the directory \admin and then create three sub folders inside HRDEMO named - adump, dpdump, pfile.
Also, create a folder HRDEMO under the directory \oradata.

Step 3: Configure and Execute the delivered SQL Scripts

We need to modify and then execute several delivered SQL scripts located in \scripts\nt

createdb10.sql

1. replace with HRDEMO
2. replace with C:\app\Administrator i.e
   
3. replace %ORACLE_HOME% with C:\app\Administrator\product\11.2.0\dbhome_1

utlspace.sql

1. Add follwing command in the beginning:  set ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1
2. replace with HRDEMO
3. replace with C:\app\Administrator

dbowner.sql
we don't have to make any changes in this script

hcddl.sql

1. replace with HRDEMO
2. replace with C:\app\Administrator

psroles.sql
we don't have to make any changes in this script

psadmin.sql
Add following command in the beginning:  set ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1

connect.sql
we don't have to make any changes in this script

Once the scripts have been modified, its time to execute them. Follow below steps to do the same:

1. Open the dos command prompt and set the ORACLE_SID be executing below command. Make sure  that there is no space around '='
       set ORACLE_SID=HRDEMO

2. Now we need to connect to our dummy database by executing below command:
       sqlplus / as sysdba

3. Once connected, run the blow command to startup the dummy instance:
       SQLgt; startup

4. Once the dummy instance has started, execute createdb10.sql as shown below:

       SQL>@C:\PS_HOME\scripts\nt\createdb10.sql

Execute all the other scripts in the same fashion. Note that the scripts should be executed in following order:

Createdb10.sql  >> utlspace.sql >> dbowner.sql >> hcddl.sql >> psroles >> psadmin.sql >> connect.sql

While executing the script psadmin.sql you will be prompted for an PeopleSoft Owner ID and Password and Default Tablespace. Please provide following for the same:

• PeopleSoft Owner ID - SYSADM
• Password - SYSADM
• Default Tablespace - PSDEFAULT

Step 4: Update the Connection Information for Database

Now that we have created the database and created the necessary tablespaces and tables with appropriate roles to be able to login via data mover. But before we could login into this newly created database we need to define connection information in tnsnames.ora and listener.ora files.

Check out the Step 2 Updating tnsnames.ora and lister.ora File  in the article How to Connect to a Database through SQL Developer in MS Window to know more about how to update tnsnames.ora and listener.ora files.

Now, since we have made changes in these two files we have to restart the TNS Listener service to get the changes in the effect and to do that:

Open the DOS command prompt and execute below:

1. Execute the command-  lsnrctl stop
2. Once the TNS Listner has stopped, restart it back again by executing the command-  lsnrctl start

Step 5: Verify the Database Connection

Now that we have updated the connection information, its time to establish the connection via sqlplus command line. We are doing so to ensure that Access ID (SYSADM) and Connect ID (people) are able to connect to the database so that while loging into database through Data Mover we don't face any issue.

1. Start the Database HRDEMO as shown in the the article Connecting and Starting the Database through sqlplus Command
2. Connect to the database HRDEMO through Access ID (SYSADM) by executing-  connect SYSADM/SYSADM
3. Disconnect from HRDEMO by executing-  disconnect
4. Now connect to the database HRDEMO through Connect ID (people) by executing-  connect people/peop1e.  Note that in the Connect ID password there is a number 1 and not the letter l.

Step 6: Running Data Mover Import Scripts

In this step we will be running the Data Mover import scripts to complete the database setup for our PeopleSoft installation.

First configure the connection information in PeoleSoft Configuration Manager through the following steps:

1. Create a desktop shortcut of the PS Configuration Manager executable pscfg which is located at \bin\client\winx86
2. Open the configuration manager by running pscfg
3. Select Oracle as the Database Type
4. Enter HRDEMO as Database Name
5. Enter people as Connect ID
6. Enter peop1e as Connect Password, confirm the connect password.

Running Data Mover Import Script for our Demo Database:

1. Launch the Data Mover (Start>Data Mover or \bin\client\winx86\psdmt)
2. Login with user SYSADM and the password also SYSADM
3. Go to File > Database Setup
4. Select the Target Database as Oracle and Database Type Non-Unicode and click Next
5. First select the Database Type as Demo and then click on Add button to select the database for which the Data Mover script is to be created.  Click on Next                                                                 
6. In the next window keep the selection but make sure that the Access Password is SYSTEM. Click on finish to create the Data Mover script.
7. Once the script is created, click on Run to run the script.             
   
8. The script will take several hours (5-6 hours or may be  more) to complete so keep patience and do not close Data Mover until the script completes. If Data Mover appears not responding or kind of stuck then don't worry, the script is running. You can see the log files located under C:\PS_HOME\log to track the progress on the script execution.
9. There is on more script grant.sql located under C:\PS_HOME\scripts that we need to run in Data Mover but before doing so we need to modify it. Just open this script and replace the with people then run the script.

With this we have successfully run the Data Mover import scripts to load the database HRDEMO with necessary data.

Visit the next part - PeopleSoft Installation made Extremely Easy: Part 3