Understanding Oracle Database and PeopleSoft Database

Some of the terms especially when it comes to database are loosely used and may cause confusion when performing a PeopleSoft installation. This post will help you revisit and understand PeopleSoft database concepts when used with Oracle database. When you perform a PeopleSoft installation, you will create these types of PeopleSoft databases:
• System (also called SYS) databases, which contain the PeopleSoft PeopleTools and product-specific metadata required for development of a production database.
• Demo (DMO) databases, which are populated with sample data for study, demonstration, or training purposes.

It is important to note the distinction between an Oracle database and a PeopleSoft database. PeopleSoft database essentially is a set of SQL objects that reside within a single Oracle database and share the same owner ID. Oracle recommends
that you install only one PeopleSoft database per Oracle instance. If you are installing PeopleSoft software on an Oracle Real Application Cluster (RAC) database, you must use the manual procedure for database setup, and complete other configuration procedures. Before installing PeopleSoft software, you should install the Oracle RDBMS software.

Be aware that you need to install both the 64-bit server and 32-bit client Oracle RDBMS software for your PeopleSoft installation. The PeopleSoft PeopleTools database server, Application Server, and Process Scheduler require 64-bit connectivity, while the PeopleSoft client features use 32-bit.

Before you begin the installation process, you should determine how many PeopleSoft databases (System or Demo) of which type you need and how you intend to use them. You should also determine the names of the databases at this point, using database names that:
• Are limited to eight characters, all UPPERCASE.
• Capture information about the PeopleSoft product line and the type of database. For example, you may want to create two databases with the names PSHRDMO and PSHRSYS for HCM.

An Oracle database is a set of SQL objects defined by one system catalog in the SYSTEM tablespace and one SID (system identifier), using one instance of the Oracle server executables and associated files. Within Oracle shops, the Oracle database is commonly referred to as the Oracle SID.

A PeopleSoft database is a set of SQL objects defined as having the same owner ID. These tables are always contained within a single Oracle database. A PeopleSoft database includes the PeopleSoft objects and application data for one or more products in a PeopleSoft product line. This owner ID (an Oracle user ID) may also be referred to as an Oracle schema or Oracle logical database.

Each PeopleSoft database needs its own owner. We refer to this as the PeopleSoft owner ID, which is also the PeopleSoft access ID.
Note. You must limit the owner ID (access ID) and owner password (access password) to eight characters or less.

It is recommend that you install no more than one PeopleSoft database per Oracle instance. When each PeopleSoft database has its own instance, the system is more robust for the following reasons:
• Your developers can bring down their instance of the Oracle server executables, but the others will remain running.
• You have added security with one PeopleSoft database per Oracle instance.
• It is more efficient to tune each instance to the requirements of its corresponding PeopleSoft application database.

If you are unable to run extra Oracle database instances because of resource limitations, such as memory and system semaphores, you can install multiple PeopleSoft databases in the same Oracle instance. Keep the following points in mind if installing multiple PeopleSoft databases within one Oracle database instance:
• When supporting multiple databases in one Oracle instance, increase maxdatafiles from the default of 32 when you create the database. Check the limits of maxdatafiles for your operating system and increase the value accordingly.
• You need to increase the size of the tablespaces if you use the same ones for each PeopleSoft database schema. Sharing tablespaces is not recommended.
• Each PeopleSoft database should have different operator IDs (also known as user IDs) to avoid problems with passwords.
• Each PeopleSoft database needs its own PeopleSoft database name.

TSNAMES.ORA – The service name defined in TNSNAMES.ORA must be the same as that in PS.PSDBOWNER.

PS.PSDBOWNER table – A single table, PS.PSDBOWNER, is created in the Oracle SID as part of the database creation procedures. PS.PSDBOWNER is maintained and acts as directory during the sign-on process to all of the PeopleSoft databases in the Oracle database/SID. There is a single row in the PS.PSDBOWNER for each PeopleSoft DB contained in the SID.

Note that with Oracle 11gR1 the database user ID is case-sensitive. So, when running the installation, system administrators need to make sure the database user ID, such as SYSADM, with all upper-case letters, is represented consistently, wherever it needs to be supplied in the configuration tools and scripts as well as on any signon screen.