PeopleSoft on Oracle FAQ

Oracle on PeopleSoft FAQ:

What is a PeopleSoft database?

A PeopleSoft database comprises of Three components, the PeopleSoft system catalog (or PeopleTools) , the application data and Meta data. The PeopleTools, similar in purpose to the Oracle system catalog, is a repository for the application (record, fields, panels, PeopleCode, etc.).

The following figure illustrates how PeopleSoft is organized within the Oracle RDBMS:

	Usage	Examples
Application Data	The Application Data layer houses the actual user-entered data in the database	PS_PERSONAL_DATA PS_EMPLOYMENT PS_JOB
PeopleTools	The PeopleTools layer houses the tools used for development of the application	RECORDS PANELS PEOPLECODE
RDBMS Catalog	The RDBMS Catalog houses the core structures that make up the database	DBA_USERS (oracle) SYSUSERS (Sybase/ SQL-Server) SYSUSERAUTH (DB2)

NOTE: Although it is technically incorrect to refer to it as a PeopleSoft database, the general user community refers to it as such.

What is PeopleSoft?

PeopleSoft is a software Enterprise Resource Planning (ERP) vendor. They currently produce software for the Human Resources Management System (HRMS, Financials, Material Management, Higher Education (Student Administration), Customer Relationship Management (CRM), Project Management, and Supply Chain Management, to name a few major products. For more information, visit the PeopleSoft site at www.peoplesoft.com.

How does the PeopleSoft database interact with the Oracle database?

The PeopleSoft database exists within an Oracle user schema. The schema, SYSADM, contains all the objects that make up the PeopleSoft application.

How many PeopleSoft databases do I need?

The following environments are needed to support a PeopleSoft database:

• PSAU - PeopleSoft application update database;
• Development - Development database;
• Testing - Regression and Integration testing database;
• User acceptance - User testing and acceptance environment.
• Production - Live production database.
• (Optional) Vanilla - uncustomized PeopleSoft delivered database.

What is the order of installing PeopleSoft on Oracle?

The following table matrix summarizes the order in which the PeopleSoft CDs are to be used for the installation.

Installation	HRMS	FDM	Student Admin	EPM
PeopleTools CD	1	1	1	1
PeopleTools Language Pack	2*	2*	2*	2*
Application CD	3	3	3	3
Database CD	n/a	4	4	n/a
Global Multilingual CD	4*	5*	5*	4*
Folio and PeopleBooks CD	5	6	6	5
Application Client CD	n/a	7	n/a	n/a

• denotes if applicable.

When installing PeopleSoft on the file server, what are the recommended directory structures?

When installing a single or multiple PeopleSoft products, the recommended directory structures are as follows:

Installing a single PeopleSoft product:

PSFT                           - PS_HOME
        HR750                  - HRMS
                SQRBINW        - Scribe SQR EXEs
                CRW            - Seagate Crystal Reports EXEs
                FOLIO          - Folio and PeopleBooks
                ...

Installing multiple PeopleSoft products:

PSFT                   - PS_HOME
        HR750          - HRMS
        EP750          - Enterprise Performance
        HE750          - Student Admin
        CRW            - Seagate Crystal Reports EXEs
        SQRBINW        - Scribe SQR EXEs
        FOLIO          - Folio and PeopleBooks

How can the client workstation connect to a PeopleSoft database?

There are three ways a client workstation can connect to a PeopleSoft database. First using the two-tier direct connection with the Oracle RDBMS using SQL*NET/Net8; second using the three-tier Tuxedo application server; and third using the three-tier Jolt/Tuxedo combination to connect to a PeopleSoft web-enabled page.

What is needed for the clients workstation to connect to a PeopleSoft database?

To connect using the two-tier approach, the correct version of SQL*NET/Net8 would need to be installed. The TNSNAMES.ORA file on the client would also need to be amended.

To connect using the three-tier approach, the PeopleSoft Configuration Manager would be needed to configure to connect to the application server.

To connect using the three-tier web approach, the user would need a web browser and a Universal Resource Locator (URL) address.

When I installed Oracle, I used the US7ASCII character set. Is that good enough?

US7ASCII supports 7 bit characters. For US English users in the United States, this would suffice. However if your organization also deploys the same application abroad or in the case of Canada where the database must support both English and French, then you must use a different character set.

Typically, the WE8ISO8859P1 character set is used to support most Europeen languages. This is the Oracle default character set. This character set can support 8 bit characters.

To support asian languages, the respective character sets would need to be used at database creation.

How many databases should be in one Oracle instance?

PeopleSoft recommends that you install no more than one database per instance.

What version of Oracle supports which version of PeopleSoft?

The most up-to-date information on the version of Oracle certified by PeopleSoft would be in their customer connection site. As of 01-Apr-2001, the following versions are supported:

	PeopleSoft 7.0x	PeopleSoft 7.5x	PeopleSoft 8.0	PeopleSoft 8.1x
Oracle 7.3.4.x	Y	Y	N	N
Oracle 8.0.x	Y	Y	Y	Y
Oracle 8.1.x	Y	Y	Y	Y
Oracle 9.1.x	n/a	n/a	n/a	n/a

Cannot login - splash dialog box disappears and nothing happens

When your log in to PeopleSoft, and you type in your OPRID and PASSWORD and the PeopleSoft splash dialog box disappears, but nothing happens, do the following:

PeopleSoft has a table defined under the PS user schema. The table is PS.PSDBOWNER. The columns for this table is DB_NAME and OWNER. The DB_NAME must be the same as your SID for your instance. The OWNER should be SYSADM.

Use the query to check the table:

SELECT * FROM PS.PSDBOWNER;

To perform any updates, use this SQL:

UPDATE PS.PSDBOWNER SET DB_NAME='<your_sid>' and OWNER='SYSADM';

What is the relationship between PeopleTools tables and the system catalog in Oracle?

Table Description	Oracle	PeopleSoft
Data Definition
1 row per table/view in database	DBA_TABLES	PSRECDEFN
1 row per column in table/view	DBA_TAB_COLUMNS	PSRECFIELD
1 row per column in database		PSDBFIELD
1 row per view	DBA_VIEWS	PSVIEWTEXT
1 row per synonym	DBA_SYNONYMS	n/a
1 row per index	DBA_INDEXES	PSINDEXDEFN
1 row per column in index	DBA_IND_COLUMNS	PSKEYDEFN
Security
1 row per valid user	DBA_USERS	PSOPRDEFN
GRANTs for table and column access	DBA_TAB_PRIVS	n/a

What is the sizing of a generic PeopleSoft HRMS database?

Tablespace Name	Default Size (M)	Use
SYSTEM	75	Oracle data dictionary
PSRBS	75	Rollback segment tablespace
PSTEMP	60	Temporary segment tablespace
BNAPP	9	188 tables
BNLARGE	7	6 tables
FSAPP	5	2 tables
GIAPP	3	13 tables
GPAPP	8	457 tables
HRAPP	22	917 tables
HRLARGE	5	20 tables
HTAPP	7	207 tables
PAAPP	8	194 tables
PALARGE	3	26 tables
PIAPP	3	34 tables
PILARGE	3	10 tables
PSIMAGE	65	18 tables
PSINDEX	300	4645 indexes
PTAPP	3	11 tables
PTPRC	3	6 tables
PTTBL	80	449 tables
PYAPP	15	284 tables
PYLARGE	37	73 tables
STAPP	3	67 tables
STLARGE	3	42 tables
TLAPP	4	88 tables
TLLARGE	3	4 tables
SUM	809

How does PeopleSoft knows which object is the most current?

PeopleSoft assigns a version number for each object. A master table, PSLOCK, stores the maximum version number for each object type.

How do I synchronize all the version numbers in PeopleSoft?

PeopleSoft provides the VERSION.SQR that will update PSLOCK so that each object type contains the maximum version number.

How does the version number change for an object type?

There are three ways in which the version number can change.

• Developer makes change to existing object or creates a new object;
• An upgrade is performed, moving an object(s) from one database instance to another;
• Using Data Mover to move or change an object.

How many rollback segments are needed?

For an online PeopleSoft database, 10 - 20 small rollback segments sized between 5MB - 10MB would be sufficient. For a decision support system (DSS), fewer large rollback segments would be recommended.

What optimizer mode should I use for my database?

Up until PeopleTools 6.x, PeopleSoft recommended the use of the RULE base optimizer. With PeopleTools 7.x, PeopleSoft is slowly making the transition from RULE base to COST base. The difference? COST base optimizer (CBO) uses table statistics to determine the best method to retreive the data from a given query. Table statistics would need to be updated periodically for the queries to perform optimally. RULE base optimzer (RBO) will always follow a set of rules to determine the best method to retrieve the data.

Oracle Corporation has indicated that with newer versions of its Oracle RDBMS, it will eventually discontinue support for RBO. Starting PeopleTools 7.5+, PeopleSoft is now recommending users to use CBO.

How do I know which optimizer my database is set to?

select upper(name) Name, upper(value) Value
  from v$parameter
 where name = 'optimizer_mode'
 order by name
/

How to fix the toolbar below the menu when it disappears?

There currently exists a PeopleTools problem that can cause the Toolbar to disappear. This problem is known to PeopleSoft as I have found 4 Case Resolution ID's referring to this problem on Customer Connection. It appears to be an ongoing problem and can occur regardless of the version of PeopleTools you are currently running. The Case ID's I found start at PeopleTools 6.10 and range up to and including PeopleTools 7.59 although the problem is not limited to these versions; the Cases reported just happened to be reported by customers running on those versions.

PeopleSoft does provide a solution to the problem although the details as to the cause of the problem are not known. In one of the Case resolutions this is noted and PeopleSoft states that under certain circumstances, the problem can crop up although what those circumstances are is apparently not known.

The problem is caused by a missing entry in the PeopleTools table PSTOOLBARDEFN. In order for the Toolbar to be displayed, a row must exist in this table with a blank OPRID. If this row is missing, then the Toolbar will disappear once the user's cache is refreshed. In addition, you will not be able to restore the Toolbar under Edit, Preferences. This problem can occur for one user at first but it can eventually spread nationally if it is not noticed early on.

The solution provided by PeopleSoft is to recreate the row with the blank OPRID in PSTOOLBARDEFN. Their suggestion is to check the row in your Demo Database and recreate it your Production environment (or whichever environment where the Toolbar has disappeared).

In addition to PSTOOLBARDEFN, the following related tables should also be checked to make sure that data rows exist with a blank OPRID:

• PSTOOLBARITEM
• PSTBARITEMLANG
• PSTOOLBARDEL

If the rows with a blank OPRID do not exist, then these rows should also be recreated according to how they are defined in the Demo Database for the specific table(s) affected.

The four Case Resolution ID's are listed below as well as links to the Case Resolutions on Customer Connection if you are interested in obtaining more details.

• Case ID 39384 - reported in PeopleTools 6.10 Resolution:Dragged toolbar off of panel and closed. toolbar won't return.
• Case ID 278396 - reported in PeopleTools 7.53 Resolution:PeopleSoft tool bar is missing suddenly!
• Case ID 20838 - reported in PeopleTools 7.55 Resolution:Missing toolbar - operator deleted the custom toolbar from PSFT
• Case ID 695603 - reported in PeopleTools 7.59 Resolution:PeopleSoft panels : toolbar icons disappeared from all panels

What has happened to my toolbar? I am the only user that has lost it!

There are two reasons why only your toolbar has disappeared; (1) You've disabled the display of your toolbar; (2) You've dragged the toolbar out of the current PeopleSoft window and that window is no longer the active window. In the first case, simply select VIEW | TOOLBAR from the online application (note: if you are within Application Designer, the TOOLBAR option is not available). In the second case, make sure that there is a checkmark beside the TOOLBAR option. If the toolbar is not located under the menu bar, then drag the toolbar box and drop it directly under the menu bar. Your toolbar should be restored.

What is the PSAU database used for?

The PeopleSoft Application Upgrade (PSAU) database is used as a staging area for application fixes released by the Global Suppose Centre (GSC). The fixes released by the GSC are delivered in the form of a Data Mover data (DAT) file and the corresponding Data Mover Script (DMS) to load it into the PSAU database. This will create a project in the PSAU database. The project can be migrated from the PSAU database to the target database using the regular migration process.

How do I apply a PeopleSoft application fix?

Note: This solution assumes that the PSAU database was created beforehand.

Download the appropriate EXE from the PeopleSoft Customer Connection web site. The EXE file is a sel-extracting file containing a data move script (DMS) and a data file (DAT). After extracting the the DMS and DAT file follow the steps:

• Connect to the PSAU database using Data Mover.
• Load AUREFRESH.DMS.
• Execute the script. This step clears the PSAU database.
• Load the DMS script into Data Mover.
• Execute the script. This script will create a project in the PSAU database.
• Logout of PSAU.
• Log in to the Application Designer Tools in the PSAU database.
• Use FILE | OPEN |PROJECT. There should be one project available; representing the application data fix case ID.
• Perform a project compare report against the target database. This step is to ensure that the fix does not overwrite any of your customization.
• If there are no changes to the project, perform a migration of the project to your target database.
• After confirming that the migration was successful, run AUREFRESH.DMS against the PSAU database.

Should I be refreshing the PSAU database after applying each fix?

PeopleSoft recommends that the AUREFRESH.DMS script be run after the fix has been migrated to the target database.