On The Peoplesoft Road : Peoplesoft Database creation - Part I

Please, before to run the installation ensure you understood the concepts explain previously here.

This first part of the Peoplesoft Database Creation concern the Oracle database creation itself and the Application (including PeopleTools) data.
Of course, you need to have install the Database server previously, see here.

Peoplesoft provide scripts to create the database, you can find all the required scripts under $PS_HOME/scripts/unix. Because we'll need to modify some of these files, it's better to copy all the files under a working directory, or like me, under $ORACLE_BASE/admin/$ORACLE_SID/create folder.

So, I'll use that ones.
Step 1 to 14 have to be run on the database server.

1. Modify the script createdb10.sql (for the 10g customer), character set and strings :
[ora102@orion:/oradata/DMOHRMS9/create]$ vi createdb10.sql
set termout on
set echo on
spool createdb.log

startup nomount pfile=$ORACLE_HOME/dbs/initDMOHRMS9.ora
CREATE DATABASE "DMOHRMS9"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/oradata/DMOHRMS9/datafiles/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oradata/DMOHRMS9/datafiles/sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/DMOHRMS9/datafiles/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "PSUNDOTS" DATAFILE '/oradata/DMOHRMS9/datafiles/psundots01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET UTF8
LOGFILE GROUP 1 ('/oradata/DMOHRMS9/redologs/redo01.log') SIZE 51200K,
GROUP 2 ('/oradata/DMOHRMS9/redologs/redo02.log') SIZE 51200K,
GROUP 3 ('/oradata/DMOHRMS9/redologs/redo03.log') SIZE 51200K;
spool off

2. Create the directories to receive your new database, and your init.ora file. Please refer to the Oracle Database Documentation http://docs.oracle.com

3. Ensure your NLS_LANG env variable is set correctly.

4. Create the password file

5. Add the tns entry for the new database in tnsnames.ora (and listener.ora for fixed addressing)

6. Set the ORACLE_SID, and run the creation database script :
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 6 14:50:12 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> @createdb10.sql
SQL>
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initDMOHRMS9.ora

ORACLE instance started.

Total System Global Area 767557632 bytes
Fixed Size 1264136 bytes
Variable Size 205522424 bytes
Database Buffers 557842432 bytes
Redo Buffers 2928640 bytes
SQL> CREATE DATABASE "DMOHRMS9"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 100
7 DATAFILE '/oradata/DMOHRMS9/datafiles/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
8 EXTENT MANAGEMENT LOCAL
9 SYSAUX DATAFILE '/oradata/DMOHRMS9/datafiles/sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
10 SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/DMOHRMS9/datafiles/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
11 SMALLFILE UNDO TABLESPACE "PSUNDOTS" DATAFILE '/oradata/DMOHRMS9/datafiles/psundots01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
12 CHARACTER SET AL32UTF8
13 NATIONAL CHARACTER SET UTF8
14 LOGFILE GROUP 1 ('/oradata/DMOHRMS9/redologs/redo01.log') SIZE 51200K,
15 GROUP 2 ('/oradata/DMOHRMS9/redologs/redo02.log') SIZE 51200K,
16 GROUP 3 ('/oradata/DMOHRMS9/redologs/redo03.log') SIZE 51200K;

Database created.

7. Modify and run the utlspace.sql script (dictionaries views)
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ vi utlspace.sql
-- ******************************************************************

REM * Set terminal output and command echoing on; log output of this script.
REM *
set termout on

REM * The database should already be started up at this point from createdb.sql

set echo off
spool utlspace.log

REM * Creates data dictionary views. Must be run when connected AS SYSDBA
@$ORACLE_HOME/rdbms/admin/catalog.sql;

REM * Creates views of oracle locks
@$ORACLE_HOME/rdbms/admin/catblock.sql;

REM * Scripts for procedural option. Must be run when connected AS SYSDBA
@$ORACLE_HOME/rdbms/admin/catproc.sql;

set echo on

REM * Create a temporary tablespace for database users.
REM *
CREATE TEMPORARY TABLESPACE PSTEMP
TEMPFILE '/oradata/DMOHRMS9/datafiles/pstemp01.dbf' SIZE 300M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
;

REM * Create a tablespace for database users default tablespace.
REM *
CREATE TABLESPACE PSDEFAULT
DATAFILE '/oradata/DMOHRMS9/datafiles/psdefault.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
;

spool off

[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ sqlplus / as sysdba
SQL> @utlspace.sql

8. Modify (don't change the PS user name) and run the dbowner.sql script
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ vi dbowner.sql
-- ******************************************************************

set echo on
spool dbowner.log

GRANT CONNECT, RESOURCE, DBA TO PS IDENTIFIED BY PS;
CONNECT PS/PS;
CREATE TABLE PSDBOWNER (DBNAME VARCHAR2(8) NOT NULL, OWNERID VARCHAR2(8) NOT NULL ) TABLESPACE PSDEFAULT;
CREATE UNIQUE INDEX PS_PSDBOWNER ON PSDBOWNER (DBNAME) TABLESPACE PSDEFAULT;
CREATE PUBLIC SYNONYM PSDBOWNER FOR PSDBOWNER;
GRANT SELECT ON PSDBOWNER TO PUBLIC;
CONNECT system/manager;
REVOKE CONNECT, RESOURCE, DBA FROM PS;

spool off
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ sqlplus / as sysdba
SQL> @dbowner.sql

9. . To create the specific tablespaces (for HRMS hcddl.sql), modify and run hcddl.sql :
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ vi hcddl.sql
-- **************************************************************
REMARK * This script is used to create tablespaces for the PeopleSoft 9 HRMS Products
REMARK * on Oracle UNIX.
REMARK * Date last modified: 01/30/2007

REMARK * NOTE: The sizing of the tablespaces here are only sample sizes that will
REMARK * hold the demo database tables. This is not sized correctly for a
REMARK * production database.

REMARK * Users can choose to use the autoextending tablespace feature.
REMARK * The syntax is at the end of this script.

set echo on
spool hcddl.log

CREATE TABLESPACE AAAPP DATAFILE '/oradata/DMOHRMS9/datafiles/aaapp.dbf' SIZE 21M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
/
[...]
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ sqlplus / as sysdba
SQL> @hcddl.sql
[...]
You may want to set AUTOEXTEND to ON to avoid space extension problem against the tablespace during the application load.

10. Check and run the database ROLES creation : [ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ more psroles.sql
-- ******************************************************************

REMARK -- These are the minimum privileges required to run PeopleSoft
REMARK -- applications. If you plan to run SQL<>Secure, you will need to
REMARK -- grant "execute any procedure" to PSUSER and PSADMIN.

set echo on
spool psroles.log

DROP ROLE PSUSER;
DROP ROLE PSADMIN;

CREATE ROLE PSUSER;
GRANT CREATE SESSION TO PSUSER;

CREATE ROLE PSADMIN;
GRANT
ANALYZE ANY,
ALTER SESSION, ALTER TABLESPACE, ALTER ROLLBACK SEGMENT,
CREATE CLUSTER, CREATE DATABASE LINK, CREATE PUBLIC DATABASE LINK,
CREATE PUBLIC SYNONYM, CREATE SEQUENCE, CREATE SNAPSHOT,
CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW,
CREATE PROCEDURE, CREATE TRIGGER, CREATE TABLESPACE, CREATE USER,
CREATE ROLLBACK SEGMENT,
DROP PUBLIC DATABASE LINK, DROP PUBLIC SYNONYM, DROP ROLLBACK SEGMENT,
DROP TABLESPACE, DROP USER, MANAGE TABLESPACE, RESOURCE,
EXP_FULL_DATABASE, IMP_FULL_DATABASE,
GRANT ANY ROLE, ALTER USER, BECOME USER
TO PSADMIN WITH ADMIN OPTION;

spool off
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ sqlplus / as sysdba
SQL> @psroles.sql

11. Check and modify if necessary the ceation of the owner of the Peoplesoft objects (psadmin.sql script). Commonly, The admin user is named SYSADM, the default tablespace is PSDEFAULT :
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ more psadmin.sql
-- ******************************************************************

REMARK -- This script sets up the PeopleSoft Owner ID. An Oracle DBA is
REMARK -- required to run this script.

set echo on
spool psadmin.log

ACCEPT ADMIN CHAR PROMPT 'Enter name of PeopleSoft Owner ID: '
ACCEPT PASSWORD CHAR PROMPT 'Enter PeopleSoft Owner ID password:'
PROMPT
PROMPT Enter a desired default tablespace for this user.
PROMPT
PROMPT Please Note: The tablespace must already exist
PROMPT If you are unsure, enter PSDEFAULT or SYSTEM
PROMPT
ACCEPT TSPACE CHAR PROMPT 'Enter desired default tablespace:'


REMARK -- Create the PeopleSoft Administrator schema.

create user &ADMIN identified by &PASSWORD default tablespace &TSPACE
temporary tablespace pstemp;
grant PSADMIN TO &ADMIN;

REMARK -- PeopleSoft Administrator needs unlimited tablespace in order to
REMARK -- create the PeopleSoft application tablespaces and tables in Data
REMARK -- Mover. This system privilege can only be granted to schemas, not
REMARK -- Oracle roles.

grant unlimited tablespace to &ADMIN;

REMARK -- Run the commands below to create database synonyms.
REMARK -- Modify the connect string appropriately for your organization.

connect system/manager

set echo off

@$ORACLE_HOME/rdbms/admin/catdbsyn
@$ORACLE_HOME/sqlplus/admin/pupbld

spool off
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ sqlplus / as sysdba
SQL> @psadmin.sql

12. Check and modify if necessary the creation script of the security check user people - connector user -, take care the default password is peop1e (1, one, instead of the letter l) :
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ more connect.sql
-- ******************************************************************

REMARK -- This script sets up the PeopleSoft Connect ID.
REMARK -- An Oracle DBA is required to run this script prior
REMARK -- to loading a PSOFT database.
REMARK --
REMARK -- If you wish to use the default CONNECTID and PASSWORD,
REMARK -- then run this script as is.
REMARK -- If you wish to change the default CONNECTID and PASSWORD,
REMARK -- DELETE the default CREATE and GRANT statements below and
REMARK -- uncomment the template version modifying the following
REMARK -- parameters , ,
REMARK --

REMARK -- Create the PeopleSoft Administrator schema.

set echo on
spool connect.log

REMARK -- drop user people cascade;

create user people identified by peop1e default tablespace PSDEFAULT
temporary tablespace PSTEMP;

GRANT CREATE SESSION to people;

REMARK -- drop user cascade;

REMARK -- create user identified by default tablespace
REMARK -- temporary tablespace ;

REMARK -- GRANT CREATE SESSION to ;

spool off
[ora102@orion:/oradata/DMOHRMS9/create]DMOHRMS9$ sqlplus / as sysdba
SQL> @connect.sql

13. For Unicode database, if you don't want to raise the following error on the application datamover load : ''Character Length Semantics (CLS) is not enabled'' , you may want to add the following line in your parameters file : NLS_LENGTH_SEMANTICS=CHAR, or modify the spfile :
SQL> alter system set NLS_LENGTH_SEMANTICS=CHAR scope=both;

System altered.

14. Lastly, create spfile and bounce your database :

SQL> create spfile from pfile='/apps/oracle/admin/DMOHRMS9/pfile/initDMOHRMS9.ora';

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
show parameter ORACLE instance started.

Total System Global Area 767557632 bytes
Fixed Size 1264136 bytes
Variable Size 205522424 bytes
Database Buffers 557842432 bytes
Redo Buffers 2928640 bytes
Database mounted.
Database opened.

From step 15, you have to be on your client machine.

15. Set up your environement on your client machine, started by the connector password (people user), run the Configuration of your client environment Peopletools :
Set the connect Id and connect password as defined at the step 12.
16. Start the DataMover tool from of your client environment Peopletools :
Give the Database Name (you need previously to modify your tnsnames.ora of your Oracle client install), the admin user name and password create at the step 11 (commonly SYSADM) :
17. Run the the Database Setup within the DataMover.
18. Choose the type of Database, Unicode or non-Unicode and the chareacter set.
19. Choose the type of database you want (DEMO, SYSTEM) or if you want to add product or language to an existing database :
20. Add the languages you want to see in your application :21. Give the database name (will be included in the PSDBOWNER table), symbolic id shouldn't be change, leave SYSADM1, Access ID is the admin user created at the step 11 (commonly SYSADM), Connect ID is PEOPLE (better to not change it). Well, on this screen just change the access password if you changed it at the step 11.22. The base language should be English, unless for some specific needs.23. At the end, a script has been created, checkit and run it (take care of the time to apply, since it require Mb od dataload in your dataase through the network, it can take hours on slow network) :
It's finish for the basics tasks.
If you're sure your application database version (the Peopletools version which your application has been built on) is same as your current database, go directly at the Peoplesoft Database creation part III, else go to the Peoplesoft Database creation part II.