Peoplesoft Database creation - Part I

Before going through all the steps of a Peoplesoft database creation, you should have some concepts in mind, including difference between Peopletools and application, what are the PSconnectid and accessid users, I advise you to read this article I wrote two years ago, but still very valid (nothing changed on that side within Peopletools 8.50).

You could decide to go for a Database Wizard to avoid all the manual steps, on one side, you won't bother which scripts have to be run in which order, on an other side there are some restrictions I don't like (dbname in upper, only one mount point for the tablespaces)... Please find out more my test about Database Wizard in PT8.50 (if you go for DB Wizard skip this article, you've done).

This first part of the Peoplesoft Database Creation concern the Oracle database creation itself.
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.

1. Connect as oracle user and create the required folders

[oracle@orion3:/apps/oracle/admin]$ mkdir -p hc91dmo/pfile
[oracle@orion3:/apps/oracle/admin]$ mkdir -p /oradata/hc91dmo/datafiles
[oracle@orion3:/apps/oracle/admin]$ mkdir -p /oradata/hc91dmo/ctlfiles
[oracle@orion3:/apps/oracle/admin]$ mkdir -p /oradata/hc91dmo/redologs
[oracle@orion3:/apps/oracle/admin]$ mkdir -p hc91dmo/create
[oracle@orion3:/apps/oracle/admin]$ cd  hc91dmo/create

2. Copy the standard Peoplesoft script into your own folder

[oracle@orion3:/apps/oracle/admin/hc91dmo/create]$ cp /apps/psoft/hc91/scripts/unix/* .

3. Create a "minimum" required init.ora file

[oracle@orion3:/apps/oracle/admin/hc91dmo/pfile]$ more inithc91dmo.ora
DB_NAME = hc91dmo
DB_FILES = 1021
CONTROL_FILES = /oradata/hc91dmo/ctlfiles/ctlfile01.ctl,/oradata/hc91dmo/ctlfiles/ctlfile02.ctl
OPEN_CURSORS = 1000
db_block_size = 8192
NLS_LENGTH_SEMANTICS=CHAR
remote_login_passwordfile=EXCLUSIVE
[oracle@orion3:/apps/oracle/admin/hc91dmo/pfile]$ 

4. Modify the database creation script createdb10.sql and run it

[oracle@orion3:/apps/oracle/admin/hc91dmo/create]$ more createdb10.sql
set termout on
set echo on
spool createdb.log

startup nomount pfile=/apps/oracle/admin/hc91dmo/pfile/inithc91dmo.ora

create database   hc91dmo
    maxdatafiles  1021
    maxinstances  1
    maxlogfiles   8
    maxlogmembers 4
    CHARACTER SET AL32UTF8
    NATIONAL CHARACTER SET UTF8
DATAFILE '/oradata/hc91dmo/datafiles/system01.dbf' SIZE 2000M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oradata/hc91dmo/datafiles/sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/hc91dmo/datafiles/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
UNDO TABLESPACE "PSUNDOTS" DATAFILE '/oradata/hc91dmo/datafiles/psundots01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
LOGFILE GROUP 1 ('/oradata/hc91dmo/redologs/redo01.log') SIZE 100M,
        GROUP 2 ('/oradata/hc91dmo/redologs/redo02.log') SIZE 100M,
        GROUP 3 ('/oradata/hc91dmo/redologs/redo03.log') SIZE 100M;
spool off

[oracle@orion3:/apps/oracle/admin/hc91dmo/create]$ export ORACLE_SID=hc91dmo
[oracle@orion3:/apps/oracle/admin/hc91dmo/create]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Feb 14 14:55:04 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> @createdb10.sql
Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
SQL>

5. Create a spfile, and bounce the database

SQL> create spfile='/apps/oracle/admin/hc91dmo/pfile/spfilehc91dmo.ora' from pfile='/apps/oracle/admin/hc91dmo/pfile/inithc91dmo.ora';

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@orion3:/apps/oracle/admin/hc91dmo/create]$ cd $ORACLE_HOME/dbs
[oracle@orion3:/apps/oracle/product/11.2.0/dbs]$ ln -s /apps/oracle/admin/hc91dmo/pfile/spfilehc91dmo.ora .
[oracle@orion3:/apps/oracle/product/11.2.0/dbs]$ cd -
/apps/oracle/admin/hc91dmo/create
[oracle@orion3:/apps/oracle/admin/hc91dmo/create]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Feb 14 15:10:32 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
Database mounted.
Database opened.

6. Run the utlspace.sql script (CATALOG and CATPROC, + TEMP and PSDEFAULT tablespaces)

SQL> @utlspace.sql
...
SQL> SELECT dbms_registry_sys.time_stamp('CATPROC') AS timestamp FROM DUAL;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATPROC    2010-02-14 15:20:10

1 row selected.

SQL>
SQL> SET SERVEROUTPUT OFF
SQL>
SQL>
SQL>
SQL> set echo on
SQL> spool utlspace.log
SQL>
SQL> REM * Create a temporary tablespace for database users.
SQL> REM *
SQL> CREATE TEMPORARY TABLESPACE PSTEMP
  2  TEMPFILE              '/oradata/hc91dmo/datafiles/pstemp01.dbf'           SIZE 300M
  3  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
  4  ;

Tablespace created.

SQL>
SQL> REM * Create a tablespace for database users default tablespace.
SQL> REM *
SQL> CREATE TABLESPACE       PSDEFAULT
  2  DATAFILE              '/oradata/hc91dmo/datafiles/psdefault.dbf'          SIZE 100M
  3  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  4  SEGMENT SPACE MANAGEMENT AUTO
  5  ;

Tablespace created.

SQL>

7. psdbowner.sl script (PS database user - note, this user should not be able to connect onto the db afterward, see the revoke commands)

SQL> @dbowner.sql
SQL> set echo on
SQL> spool dbowner.log
SQL>
SQL> GRANT CONNECT, RESOURCE, DBA TO PS IDENTIFIED BY PS;

Grant succeeded.

SQL> CONNECT PS/PS;
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> CREATE TABLE PSDBOWNER (DBNAME VARCHAR2(8) NOT NULL, OWNERID VARCHAR2(8) NOT NULL ) TABLESPACE PSDEFAULT;

Table created.

SQL> CREATE UNIQUE INDEX PS_PSDBOWNER ON PSDBOWNER (DBNAME) TABLESPACE PSDEFAULT;

Index created.

SQL> CREATE PUBLIC SYNONYM PSDBOWNER FOR PSDBOWNER;

Synonym created.

SQL> GRANT SELECT ON PSDBOWNER TO PUBLIC;

Grant succeeded.

SQL> CONNECT system/manager;
Connected.
SQL> REVOKE CONNECT, RESOURCE, DBA FROM PS;

Revoke succeeded.

SQL> ALTER USER PS QUOTA UNLIMITED ON PSDEFAULT;

User altered.

SQL>

8. hcddl.sql, create all the application tablespaces (hc means HCM, cr for CRM...). Here we can get advantage of manual database creation, modify and specify the mount point you want to create the tablespaces to.

SQL> @hcddl.sql
SQL> CREATE TABLESPACE AAAPP DATAFILE '/oradata/hc91dmo/datafiles/aaapp.dbf' SIZE 22M
  2  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  3  SEGMENT SPACE MANAGEMENT AUTO
  4  /

Tablespace created.

...
SQL>
SQL> ALTER DATABASE DATAFILE '/oradata/hc91dmo/datafiles/waapp.dbf' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

Database altered.

SQL>

9. psroles.sql - create the roles required by Peoplesoft

SQL> @psroles.sql
SQL> set echo on
SQL> spool psroles.log
SQL>
SQL> DROP ROLE PSUSER;
DROP ROLE PSUSER
          *
ERROR at line 1:
ORA-01919: role 'PSUSER' does not exist


SQL> DROP ROLE PSADMIN;
DROP ROLE PSADMIN
          *
ERROR at line 1:
ORA-01919: role 'PSADMIN' does not exist


SQL>
SQL> CREATE ROLE PSUSER;

Role created.

SQL> GRANT CREATE SESSION TO PSUSER;

Grant succeeded.

SQL>
SQL> CREATE ROLE PSADMIN;

Role created.

SQL> GRANT
  2  ANALYZE ANY,
  3  ALTER SESSION, ALTER TABLESPACE, ALTER ROLLBACK SEGMENT,
  4  CREATE CLUSTER, CREATE DATABASE LINK, CREATE PUBLIC DATABASE LINK,
  5  CREATE PUBLIC SYNONYM, CREATE SEQUENCE, CREATE SNAPSHOT,
  6  CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW,
  7  CREATE PROCEDURE, CREATE TRIGGER, CREATE TABLESPACE, CREATE USER,
  8  CREATE ROLLBACK SEGMENT,
  9  DROP PUBLIC DATABASE LINK, DROP PUBLIC SYNONYM, DROP ROLLBACK SEGMENT,
 10  DROP TABLESPACE, DROP USER, MANAGE TABLESPACE, RESOURCE,
 11  EXP_FULL_DATABASE, IMP_FULL_DATABASE,
 12  GRANT ANY ROLE, ALTER USER, BECOME USER
 13  TO PSADMIN WITH ADMIN OPTION;

Grant succeeded.

SQL>

10. psadmin.sql script - Peoplesoft object owner creation (generally SYSADM)
Note : on Oracle 11gRx, if you activated the password case sensitivity, it's better to specify the SYSADM's password in upper case, otherwise you couldn't be able to connect within the AppDesigner.

SQL> @psadmin.sql
SQL> set echo on
SQL> spool psadmin.log
SQL>
SQL> ACCEPT ADMIN CHAR FORMAT 'A8' -
> PROMPT 'Enter name of PeopleSoft Owner ID(max. 8 characters): '
Enter name of PeopleSoft Owner ID(max. 8 characters): sysadm
SQL> ACCEPT PASSWORD CHAR FORMAT 'A8' -
> PROMPT 'Enter PeopleSoft Owner ID password(max. 8 characters): '
Enter PeopleSoft Owner ID password(max. 8 characters): sysadm
SQL> PROMPT

SQL> PROMPT Enter a desired default tablespace for this user.
Enter a desired default tablespace for this user.
SQL> PROMPT

SQL> PROMPT Please Note:  The tablespace must already exist
Please Note:  The tablespace must already exist
SQL> PROMPT               If you are unsure, enter PSDEFAULT or SYSTEM
If you are unsure, enter PSDEFAULT or SYSTEM
SQL> PROMPT

SQL> ACCEPT TSPACE CHAR PROMPT 'Enter desired default tablespace:'
Enter desired default tablespace:psdefault
SQL>
SQL>
SQL> REMARK -- Create the PeopleSoft Administrator schema.
SQL>
SQL> create user &ADMIN identified by &PASSWORD default tablespace &TSPACE
  2  temporary tablespace pstemp;
old   1: create user &ADMIN identified by &PASSWORD default tablespace &TSPACE
new   1: create user sysadm identified by sysadm default tablespace psdefault

User created.

SQL> grant PSADMIN TO &ADMIN;
old   1: grant PSADMIN TO &ADMIN
new   1: grant PSADMIN TO sysadm

Grant succeeded.

SQL>
SQL> REMARK -- PeopleSoft Administrator needs unlimited tablespace in order to
SQL> REMARK -- create the PeopleSoft application tablespaces and tables in Data
SQL> REMARK -- Mover.  This system privilege can only be granted to schemas, not
SQL> REMARK -- Oracle roles.
SQL>
SQL> grant unlimited tablespace to &ADMIN;
old   1: grant unlimited tablespace to &ADMIN
new   1: grant unlimited tablespace to sysadm

Grant succeeded.

SQL>
SQL> REMARK -- Run the commands below to create database synonyms.
SQL> REMARK -- Modify the connect string appropriately for your organization.
SQL>
SQL> connect system/manager
Connected.
SQL>
SQL> set echo off
...

11. Creation of the connectid (people user) - it is often better to keep the default password (peop1e) to avoid mistake and misconfiguration on client side within Configuration Manager

SQL> @connect.sql
SQL> spool connect.log
SQL>
SQL> REMARK -- drop user people cascade;
SQL>
SQL> create user people identified by peop1e default tablespace PSDEFAULT
2  temporary tablespace PSTEMP;

User created.

SQL>
SQL> GRANT CREATE SESSION to people;

Grant succeeded.

SQL>

12. Finally, start the listener, register the database and modify the tnsnames.ora file

[oracle@orion3:/apps/oracle/admin/hc91dmo/create]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 14-FEB-2010 15:53:44

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /apps/oracle/product/11.2.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Log messages written to /apps/oracle/diag/tnslsnr/orion3/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orion3.phoenix-nga)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                14-FEB-2010 15:53:45
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /apps/oracle/diag/tnslsnr/orion3/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orion3.phoenix-nga)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@orion3:/apps/oracle/admin/hc91dmo/create]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Feb 14 15:53:51 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system register;

System altered.

SQL>
$ more $ORACLE_HOME/network/admin/tnsnames.ora
HC91DMO =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.141)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = HC91DMO)
  )
)

Next step, the database creation part II, will load the data into the database.