Peoplesoft Database Creation :-
This
first part of the Peoplesoft Database Creation concern the Oracle database
creation itself and the Application (including PeopleTools) data.
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 :
[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.
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 :
[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)
[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
[oradata/DMOHRMS9/create]DMOHRMS9$
[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
[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
[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 :
[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/2015
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
/
[...]
[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 :
[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
[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 :
[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
[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) :
[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
[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.
