Appendix: Administering PeopleSoft Databases on Oracle

This appendix discusses how to:

• Work with Oracle connectivity.

• Monitor PeopleSoft client database connections.

• Set the number of temporary tables.

• Use locally managed tablespaces.

• Work with Oracle Consumer Groups.

• Implement Transparent Database Encryption.

• Protect and Manage PeopleSoft applications with Database Vault.

• Work With Oracle 11g security features.

• Work with Transparent Application Failover.

• Implement Oracle Active Data Guard.

Working With Oracle Connectivity

This section discusses:

• NET/10g/11g.

• PeopleSoft servers and the Oracle connection string.

• Open cursors.

NET10g/11g

NET10g/11g offers peer-to-peer connectivity and a multi-protocol interchange (MPIC). The product is installed as multiple elements including:

• Transparent Network Substrate (TNS).

• Oracle Protocol Adapter.

• Multi-protocol interchange (MPIC).

NET10g/11g uses the configuration files SQLNET.ORA and TNSNAMES.ORA, which can be created using a system editor, or with the NET10g/11g Assistant.

PeopleSoft Servers and the Oracle Connection String

The format of the Oracle connect string used to connect to the database is userid/password@service_name for all PeopleSoft processes, including online, batch, and application server processes.

This makes setup and configuration easy for platform configurations that can support PeopleSoft batch server processes or application server processes. However, performance for the batch processes and application server processes on a server that also functions as the database server is slightly degraded, due to the overhead involved in routing through SQL*NET.

PeopleSoft provides a configuration parameter, UseLocalOracleDB, for you to indicate which connect string to use. You set the parameter while configuring the application server or the Process Scheduler in the Database Options section.

Database Options

When configuring an application server or the Process Scheduler, you can modify the parameters in the Database Options section if desired.

Values for config section - Database Options
UseLocalOracleDB=0
;ORACLE_SID=
EnableDBMonitoring=0

Do you want to change any values (y/n)? [n]:

Following are descriptions of the Database Options parameters:

The following tables describe the relationship between the UseLocalOracleDB parameter and the ORACLE_SID environment variable.

See Also

Meta-SQL Reference

Open Cursors

The minimum number of OPEN_CURSORS required for PeopleSoft applications on an Oracle database is 1000.

Monitoring PeopleSoft Database Connections

This section provides an overview of PeopleSoft database connections and discusses how to:

• Enable database connection monitoring.

• Track PeopleSoft database connections by PeopleSoft User ID.

• Monitor MODULE and ACTION.

Understanding PeopleSoft Database Connections

PeopleTools provides the ability to monitor connections to the database server from Windows workstations (two-tier and three-tier connections) and browser connections. Some possible uses of monitoring database connections include system-wide troubleshooting, performance monitoring, "chargeback" accounting, and security audits for your system.

Administrators can obtain specific information regarding the user and the associated transaction when a user is connected to the database. A PeopleSoft system has many clients and user sessions connecting to one application server (directly, or indirectly through the web server), with only the application server maintaining connections to the database server.

Suppose one of your users has executed an extremely inefficient query that severely impacts the rest of the system. A Database Administrator would want to identify that user and take appropriate action. However, without an ability to monitor users you would probably have to terminate the physical connection, which would mean dropping the connection between the application server and the database server, which could potentially affect hundreds of users.

However, while only the application server maintains the actual database connection, PeopleTools records various information associated with each user connection so that client information can be monitored. Monitoring client information enables an administrator to collect information from two-tier connections, three-tier connections, and browser connections alike.

Associated with each connection and transaction is the following set of user information:

• PeopleSoft user ID

• OSUserName

• MachineName

• AppServerDomainName

• ProgramExecutable

This information allows the system to associate activity on the database server with a particular workstation and user. This information is stored in the CLIENT_INFO column of the V$SESSION dynamic view.

Administrators are also often interested in compiling performance metrics based on the system usage per application. For this type of monitoring the PeopleSoft system populates the MODULE and ACTION fields of the V$SESSION dynamic view.

Oracle products, such as Oracle Enterprise Manager and Oracle Audit Vault use information stored in the CLIENT_IDENTIFIER column of V$SESSION.

Enabling Database Connection Monitoring

Database monitoring is always enabled for:

• COBOL programs.

• SQR programs.

• Processes run through Process Scheduler.

• Two-tier Windows workstation connections.

For connections handled by the application server (browser and three-tier Windows connections) the PeopleSoft systems administrator has the option to enable this feature by setting the EnableDBMonitoring parameter to '1' in PSADMIN or the application server configuration file (PSAPPSRV.CFG).

Tracking PeopleSoft Database Connections by PeopleSoft User ID

This section provides an overview of tracking database connections by user ID, a legend for interpreting illustrations, and discusses the following:

• Oracle process connections.

• Two-tier Windows client connections.

• Application server process connections.

• Three-tier Windows client connections.

• Browser (PIA) connections.

• Process Scheduler connections.

• SQR connections.

• COBOL connections

• Windows and browser connections multithreaded through the application server.

Understanding Tracking PeopleSoft Database Connections by PeopleSoft User ID

To view the information associated with client connections, sign on to SQLPlus for the appropriate SID and execute the following SQL Query:

Note. This is a sample query that ties the OS PID and PeopleSoft CLIENT_INFO to the process connected to the Oracle database.

set linesize 200
select p.spid,
   substr(s.osuser,1,10) osuser,
   substr(s.username,1,8) username,
   substr(s.program,1,24) program,
   substr(s.client_info,1,60) ClientInfo
from v$session s, v$process p
where s.paddr=p.addr
and s.osuser is not null
order by s.osuser
/

The result of this query will differ somewhat per connection type. The following sections describe the information returned for various scenarios.

Legend

Oracle Process Connections

Execution of the sample query noted above shows the Oracle Processes for the SID in which PeopleSoft database PT844P01 resides and this SQL*Plus session used to monitor the client info. There is no client info because no PeopleSoft client connections currently exist.

Oracle Processes and this SQLPLUS session are used to monitor the client info for network user JZARATE.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO        
           
-------- ------- -------- ----------------------------- ------------------
15276    JZARATE PT844P01 SQLPLUSW.EXE                                     
           
8364     oracle           oracle@st-sun01 (PMON)                          
           
8366     oracle           oracle@st-sun01 (DBW0)                          
           
8368     oracle           oracle@st-sun01 (LGWR)                          
           
8370     oracle           oracle@st-sun01 (CKPT)                          
           
8372     oracle           oracle@st-sun01 (SMON)                          
           
8374     oracle           oracle@st-sun01 (RECO)                          
           

7 rows selected.

Two-Tier Client Connections

For the two-tier connection, you can expect to monitor the following client information:

"%oprid%,%osusername%,%machinename%,,%executable%,"

Adding to what was previously displayed, this is a two-tier client connection from workstation TMJONES110299, Peoplesoft OPRID PS, executing PSIDE.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
           
-------- ------- -------- ----------------------------- -----------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,
pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
           
8364     oracle           oracle@st-sun01 (PMON)                                  
           
8366     oracle           oracle@st-sun01 (DBW0)                                  
           
8368     oracle           oracle@st-sun01 (LGWR)                                  
           
8370     oracle           oracle@st-sun01 (CKPT)                                  
           
8372     oracle           oracle@st-sun01 (SMON)                                  
           
8374     oracle           oracle@st-sun01 (RECO)                                  
           

8 rows selected.

Application Server Process Connections

For the application server connection, you can retrieve the following information from the database:

"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,"

Adding to what was previously displayed, this shows the application server process connections for Domain PT81, from server st-sun01, using UNIX login ID certora, with the application server processes connecting to the database as user ID PTDMO.

Keep in mind that each application server process maintains an individual connection to the database. If your application server is up and running, you should see the following information after executing the session query:

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
           
-------- ------- -------- ----------------------------- ---------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,
pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
           
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-
sun01,PT81,PSAPPSRV,
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-
sun01,PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-
sun01,PT81,PSAPPSRV,
8364     oracle           oracle@st-sun01 (PMON)                                  
           
8366     oracle           oracle@st-sun01 (DBW0)                                  
           
8368     oracle           oracle@st-sun01 (LGWR)                                  
           
8370     oracle           oracle@st-sun01 (CKPT)                                  
           
8372     oracle           oracle@st-sun01 (SMON)                                  
           
8374     oracle           oracle@st-sun01 (RECO)                                  
           

11 rows selected.

Three-Tier Connections – Windows Workstations

For the three-tier connections, you can retrieve the following client information:

"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""

When the three-tier workstation is connected, then you should see the application server process that is executing the transaction for the client. For example, the PSAPPSRV server process handles the majority of the requests. Let’s assume for this example that the PSAPPSRV is processing the current client request.

Adding to what was previously displayed, this is a three-tier workstation JRSMITH031198, signing on as PSOFT with a user ID of VP1, to Domain PT81 and utilizing two application server processes (PSAPPSRV).

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
           
-------- ------- -------- ----------------------------- ---------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,
pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
           
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,
PSAPPSRV,    
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-⇒
sun01,PT81,
PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,
PSAPPSRV,    
8364     oracle           oracle@st-sun01 (PMON)                                  
           
8366     oracle           oracle@st-sun01 (DBW0)                                  
           
8368     oracle           oracle@st-sun01 (LGWR)                                  
           
8370     oracle           oracle@st-sun01 (CKPT)                                  
           
8372     oracle           oracle@st-sun01 (SMON)                                  
           
8374     oracle           oracle@st-sun01 (RECO)                                  
           

11 rows selected.

Browser Connections – (PIA)

For browser connections (PIA connections), you can retrieve the following client information:

"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""

When the user is connected, you should see the application server process that is executing the transaction for the browser. For example, the PSAPPSRV handles the large queries executed by user connections. Let's assume for this example that the PSAPPSRV is processing the current client request.

Adding to what was previously displayed, this is a PIA client, PREILLY060499 (connecting through a web browser), signing on as PSOFT/PTDMO, to Domain PT81 and utilizing two application server processes (PSAPPSRV).

From a monitoring perspective, there is no difference between a three-tier windows connection and a PIA browser connection.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
           
-------- ------- -------- ----------------------------- ---------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,
pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
           
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,
PSAPPSRV,  
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,
PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,
PSAPPSRV,  
8364     oracle           oracle@st-sun01 (PMON)                                  
           
8366     oracle           oracle@st-sun01 (DBW0)                                  
           
8368     oracle           oracle@st-sun01 (LGWR)                                  
           
8370     oracle           oracle@st-sun01 (CKPT)                                  
           
8372     oracle           oracle@st-sun01 (SMON)                                  
           
8374     oracle           oracle@st-sun01 (RECO)                                  
           

11 rows selected.

Process Scheduler Connections

For the Process Scheduler connection, you can expect to see the following information:

"%oprid%,%osusername%,%machinename%,,%executable%,"

Adding to what was previously displayed, this is the Process Scheduler running, started by OSUSER certora, from server st-sun01, logged in as PSOFT with a user ID of PTDMO.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
-------- ------- -------- ----------------------------- ---------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,
pside.exe,
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,
psprcsrv,
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,
PSAPSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,
PSAPPSRV,
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,
PT81,PSSAMSRV,
8364     oracle           oracle@st-sun01 (PMON)                                  
           
8366     oracle           oracle@st-sun01 (DBW0)                                  
           
8368     oracle           oracle@st-sun01 (LGWR)                                  
           
8370     oracle           oracle@st-sun01 (CKPT)                                  
           
8372     oracle           oracle@st-sun01 (SMON)                                  
           
8374     oracle           oracle@st-sun01 (RECO)                                  
           

12 rows selected.

SQR Connections

For the SQR program connections, you can expect to see the following information:

"%oprid%,%spid%"

Adding to what was previously displayed, this is an SQR report run from the workstation JZARATE123199, submitted from the user ID PS, and having a PID of 15449.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
-------- ------- -------- ----------------------------- --------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,
pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
15449    JZARATE PT844P01 sqrw.exe                      PS,15449                  
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,
psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,
PSAPPSRV,  
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,
PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,
PT81,PSAPPSRV,  
8364     oracle           oracle@st-sun01 (PMON)                                  
           
8366     oracle           oracle@st-sun01 (DBW0)                                  
           
8368     oracle           oracle@st-sun01 (LGWR)                                  
           
8370     oracle           oracle@st-sun01 (CKPT)                                  
           
8372     oracle           oracle@st-sun01 (SMON)                                  
           
8374     oracle           oracle@st-sun01 (RECO)                                  
           

13 rows selected.

COBOL Connections

For the COBOL program connections, you can expect to see the following information:

"%oprid%,%osusername%,%machinename%,,%executable%,"

Adding to what was previously displayed, this a COBOL program PTPTEDIT, run from the workstation JZARATE123199, submitted from PeopleSoft user ID PS.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
-------- ------- -------- ----------------------------- --------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,
pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
           
15449    JZARATE PT844P01 sqrw.exe                      PS,329                    
           
15451    JZARATE PT844P01 PTPTEDIT.exe                  PS,JZARATE,JZARATE123199,,
PTPTEDIT,
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,
psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,
PSAPPSRV,  
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-⇒
sun01,PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,
PSAPPSRV,  
8364     oracle           oracle@st-sun01 (PMON)                                  
           
8366     oracle           oracle@st-sun01 (DBW0)                                  
           
8368     oracle           oracle@st-sun01 (LGWR)                                  
           
8370     oracle           oracle@st-sun01 (CKPT)                                  
           
8372     oracle           oracle@st-sun01 (SMON)                                  
           
8374     oracle           oracle@st-sun01 (RECO)                                  
           

14 rows selected.

Windows Workstation and Browser Connections Multithreading Through the Application Server

For multithreaded connections, you can retrieve the following client information:

"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""

The application server multithreads incoming three-tier Windows or PIA browser connections through the application server processes already connected to the database. The next several examples illustrate a continual changing of the monitoring information displayed through the application server "thread" based on user activity and incoming requests.

Adding to what was previously displayed, accessing the database again from the three-tier Windows workstation JRSMITH031198 reflects a change in the user ID VP1 and client machine name for the both application server processes.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
-------- ------- -------- ----------------------------- --------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,
pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
           
15449    JZARATE PT844P01 sqrw.exe                      PS,329                    
           
15451    JZARATE PT844P01 PTPTEDIT.exe                  PS,JZARATE,JZARATE123199,,
PTPTEDIT,
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,
psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,
PSAPPSRV,    
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,
PSAPPSRV,    
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-⇒
sun01,PT81,
PSSAMSRV,
8364     oracle           oracle@st-sun01 (PMON)                                  
           
8366     oracle           oracle@st-sun01 (DBW0)                                  
           
8368     oracle           oracle@st-sun01 (LGWR)                                  
           
8370     oracle           oracle@st-sun01 (CKPT)                                  
           
8372     oracle           oracle@st-sun01 (SMON)                                  
           
8374     oracle           oracle@st-sun01 (RECO)                                  
           

14 rows selected.

Adding to what was previously displayed, accessing the database from the browser on machine PREILLY060499 illustrates a change in the user ID PTDMO and client machine name for one of the application server processes.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
-------- ------- -------- ----------------------------- --------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,
pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
           
15449    JZARATE PT844P01 sqrw.exe                      PS,329                    
           
15451    JZARATE PT844P01 PTPTEDIT.exe                  PS,JZARATE,JZARATE123199,,
PTPTEDIT,
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,
psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,
PSAPPSRV,  
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,
PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,
PSAPPSRV,    
8364     oracle           oracle@st-sun01 (PMON)                                 
           
8366     oracle           oracle@st-sun01 (DBW0)                                 
           
8368     oracle           oracle@st-sun01 (LGWR)                                 
           
8370     oracle           oracle@st-sun01 (CKPT)                                 
           
8372     oracle           oracle@st-sun01 (SMON)                                 
           
8374     oracle           oracle@st-sun01 (RECO)                                 
           

14 rows selected.

Adding to what was previously displayed, accessing the database from the three-tier Windows workstation JRSMITH031198 reflects a change in the user ID VP1 and client machine name for one of the application server processes.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
-------- ------- -------- ----------------------------- --------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,
pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
           
15449    JZARATE PT844P01 sqrw.exe                      PS,329                    
           
15451    JZARATE PT844P01 PTPTEDIT.exe                  PS,JZARATE,JZARATE123199,,
PTPTEDIT,
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,
psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,
PSAPPSRV,    
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,
PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,
PT81,PSAPPSRV,  
8364     oracle           oracle@st-sun01 (PMON)                                  
           
8366     oracle           oracle@st-sun01 (DBW0)                                  
           
8368     oracle           oracle@st-sun01 (LGWR)                                  
           
8370     oracle           oracle@st-sun01 (CKPT)                                  
           
8372     oracle           oracle@st-sun01 (SMON)                                  
           
8374     oracle           oracle@st-sun01 (RECO)                                  
           

14 rows selected.

Adding to what was previously displayed, the following illustrates accessing the database from the three-tier Windows workstation JRSMITH031198 executing a functional process that requires use of all of the application server processes. This is reflected in the change in the user ID VP1, and client machine name for all of the application server processes.

Note. Because the SQR program has completed running, there is no SQR information available.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
-------- ------- -------- ----------------------------- --------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,
pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            ⇒
           
15451    JZARATE PT844P01 PTPTEDIT.exe                  PS,JZARATE,JZARATE123199,,
PTPTEDIT,
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,
psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,
PSAPPSRV,    
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,
PSSAMSRV,    
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,
PSAPPSRV,    
8364     oracle           oracle@st-sun01 (PMON)                                  
           
8366     oracle           oracle@st-sun01 (DBW0)                                  
           
8368     oracle           oracle@st-sun01 (LGWR)                                  
           
8370     oracle           oracle@st-sun01 (CKPT)                                  
           
8372     oracle           oracle@st-sun01 (SMON)                                  
           
8374     oracle           oracle@st-sun01 (RECO)                                  
           

13 rows selected.

Adding to what was previously displayed, the application server has been shut down and the COBOL process PTPTEDIT has completed. All clients have logged off. The Process Scheduler is still active.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                
-------- ------- -------- ----------------------------- ------------------------
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,
psprcsrv,    
8364     oracle           oracle@st-sun01 (PMON)                                  
           
8366     oracle           oracle@st-sun01 (DBW0)                                  
           
8368     oracle           oracle@st-sun01 (LGWR)                                  
           
8370     oracle           oracle@st-sun01 (CKPT)                                  
           
8372     oracle           oracle@st-sun01 (SMON)                                  
           
8374     oracle           oracle@st-sun01 (RECO)                                  
           

8 rows selected.

Monitoring PeopleSoft MODULE and ACTION Information

In addition to the CLIENT_INFO field, PeopleTools also populates the MODULE and ACTION fields of the V$SESSION and V$SQL dynamic views. This provides increased monitoring capabilities if you use Oracle performance monitoring utilities, including:

• Oracle Enterprise Manager

• Oracle Database Resource Manager

• Oracle Automatic Workload Repository

By monitoring MODULE and ACTION values you can:

• Provide more specific PeopleSoft information for several Oracle performance monitoring tools.

• View and analyze performance and system resource usage for selected PeopleSoft application modules.

• Write custom SQL to aggregate PeopleSoft performance and system usage information based on the MODULE, ACTION, and CLIENT_INFO values.

Depending on the type of connection, or the PeopleTools feature being used, the system populates the MODULE and ACTION fields with the information described in the following table.

Each SQL statement in V$SQL has a MODULE field populated based on the MODULE field of the session that first submitted the SQL. You can write additional SQL to obtain valuable performance information aggregated based on the values of these fields.

Keeping in mind that the usage of the MODULE and ACTION values is intended mainly to be used within the context of Oracle performance monitoring utilities, to become familiar with the type of information provided you can issue SQL queries, such as the following samples:

select module, action, client_info from v$session;

or

set linesize 200
select p.spid,
   substr(s.osuser,1,10) osuser,
   substr(s.username,1,8) username,
   substr(s.program,1,24) program,
   substr(s.client_info,1,60) ClientInfo,
   substr(s.module,1,48) module,
   substr(s.action,1,32) action
from v$session s, v$process p
where s.paddr=p.addr
and s.osuser is not null
order by s.osuser;

Exposing PeopleSoft User Information Through the CLIENT_IDENTIFIER Column

Additional monitoring information was included enhancing the availability of PeopleSoft user information in Oracle products like Oracle Audit Vault and Oracle Enterprise Manager. PeopleSoft user ID information is also stored in the CLIENT_IDENTIFIER column of the V$SESSION table.

The CLIENT_IDENTIFIER column contains only the user ID, whereas the CLIENT_INFO column also contains the user ID value, but it is typically accompanied by other user information, like machine name for example. In some cases, a monitoring application may only need the user ID information. To get this information from the CLIENT_INFO column would require programmatic transformation and parsing of the CLIENT_INFO string. Displaying only the user ID in the CLIENT_IDENTIFIER column, simplifies the retrieval of the user ID by products like Oracle Audit Vault and Oracle Enterprise Manager. No further transformation or parsing of the string is required.

Database administrators can also retrieve the information directly from the database with queries similar to the following:

SQL> select module, client_identifier, client_info from v$session where module =
 'pside.exe';

MODULE					CLIENT_IDENTIFIER		       CLIENT_INFO
---------------	-------------------	------------------------------
pside.exe				QEDMO							QEDMO,bng2,BENG-PC,,pside.exe,	

The example above displays the User ID information in both the CLIENT_INFO and the CLIENT_IDENTIFIER columns. The latter can be used by Oracle Audit Vault. The user ID information can be retrieved from the following connection types:

• two-tier connections.

• three-tier connections.

• programs run through Process Scheduler.

The following sections provide sample queries and results.

Example: Working with CLIENT_IDENTIFIER Information and Three-Tier Connections

The following query displays the user ID information associated with a three-tier connections:

SQL> select module, client_identifier, client_info from v$session where client_i
dentifier like 'QEDMO%';

MODULE
-----------------------------------------------
PSAPPSRV@sp-lnx07.peoplesoft.com (TNS V1-V3)	

CLIENT_IDENTIFIER
-------------------
QEDMO

CLIENT_INFO
-----------------------------------------
QEDMO,,10.138.230.162,FS850U02,PSAPPSRV,

In this example the user ID information is available under CLIENT_INTO and CLIENT_IDENTIFIEr, however CLIENT_IDENTIFIER only stores the user ID information while CLIENT_INFO stores other information, like the client connection details.

Example: Working with CLIENT_IDENTIFIER Information and Process Scheduler

It is also possible to monitor the user ID for programs running through Process Scheduler. In the following example an Application Engine program ran through Process Scheduler using the user ID QESS. By running the following query, it is possible to display the user ID information.

SELECT module, client_identifier, client_info 
FROM v$session 
WHERE client_identifier like 'QESS%';

MODULE					CLIENT_IDENTIFIER							CLIENT_INFO
--------------	------------------------------	-------------------------------------
PSAE						QESS													QESS,,sp-lnx07.peoplesoft.com,,PSAESRV,

Setting the Number of Temporary Tables

Normally you will leave the number of temporary tables set to the default of three. You may need to change this setting for optimal performance, depending on various aspects of your implementation, including account transaction volumes, benchmark numbers for the current hardware and database platform, as well as your service-level requirements. Use the following procedure if you need to adjust the number of temporary tables to improve performance in your implementation.

To set the number of temporary tables:

1. Select PeopleTools, Utilities, Administration, PeopleTools Options.

2. Set the Temp Table Instances (Total) and Temp Table Instances (Online) fields to the desired settings.

   Note. Temp Table Instances (Total) should always be set to the same values as Temp Table Instances (Online), unless you have been instructed otherwise in the application documentation.

3. Save your changes.

   Note. The total number of instances generated consists of the allocations specified on the PeopleTools Options page plus the allocations specified for each individual Application Engine program.

See Also

PeopleTools 8.51 PeopleBook: PeopleSoft Application Engine

PeopleSoft Red Paper “PeopleSoft Batch Performance Tuning on Oracle”

Using Locally Managed Tablespaces

PeopleSoft supports the latest Oracle locally managed tablespace (LMT) syntax to control segment space allocation. A Locally Managed Tablespace (LMT) is a tablespace that manages its own extents maintaining a bitmap in each data file to keep track of the free or used status of blocks in that data file. Each bit in the bitmap corresponds to a block or a group of blocks. When the extents are allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (except for tablespace quota information), unlike the default method of Dictionary - Managed Tablespaces.

Benefits of using LMTs include:

• Locally managed tablespaces do not record free space in the data dictionary, it reduces contention on these tables.

• Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.

• Avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.

• Sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.

• Changes to the extent bitmaps do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

• Reduced fragmentation No coalescing required.

Specifically, the following scripts have been modified to use this syntax: UTLSPACE.SQL, PTUPGDDL.SQL, and xxDDL.SQL. (Where 'xx' is the product code).

For example:

CREATE TABLESPACE PSINDEX DATAFILE '/u04/oradata/<SID>/psindex.dbf' SIZE 64M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
;

The following guidelines intend to help you determine which tables to migrate to the appropriate ‘LARGE’ tablespaces based on table size during the move to production. If you change tablespace assignments, you first need to run SETASPACE.SQR to synchronize the PeopleSoft metadata with the changes made to the Oracle catalog with respect to any new table space assignments. Also, we recommend that you use LMTs with 1M extent size and ASSM for large objects (> 10000 blocks) and 128K extent size for smaller objects (<10000 blocks). To determine the size of any object (table or index) in blocks > 10000, execute the following SQL statement:

select segment_name, tablespace_name, blocks 
from user_extents
where blocks > 10000
GROUP BY segment_name
/

The following is an example of a large tablespace:

CREATE TABLESPACE PSLARGE DATAFILE '/u04/oradata/<SID>/pslarge.dbf' SIZE 64M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

The following is an example of a non-large tablespace:

CREATE TABLESPACE PSSMALL DATAFILE '/u04/oradata/<SID>/pssmall.dbf' SIZE 64M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO;

Working With Oracle Consumer Groups

PeopleTools enables you to map predefined PeopleSoft resource groups to Oracle resource consumer groups that you create, specifically for use with PeopleSoft. Using Oracle Database Resource Manager features, database administrators can monitor and manage the database resource consumption of PeopleTools executables and optimize system performance.

For example, assume that occasionally long running queries run through PeopleSoft Query degrade the performance of the entire system by consuming large amounts of the available database resources. PSQRYSRV.EXE, the application server process dedicated to handling processing requests submitted by PeopleSoft Query, is mapped to the PeopleSoft Resource Group, QUERY SERVER, by default. By creating a ‘PsQuery’ consumer group in your Oracle system, you can limit the percentage of CPU processing available to PSQRYSRV.EXE. If you limit the CPU usage for PSQRYSRV.EXE to 10 percent, for example, other high-priority processing on the system will continue to have system resources available, while PSQRYSRV.EXE is limited only to its allotted 10 percent of CPU processing.

To take advantage of mapping PeopleSoft resource groups to Oracle resource groups, you need to:

• Review the delivered, predefined PeopleSoft resource groups.

• Determine areas of your PeopleSoft system where you’d like to implement control over CPU usage.

• Create the appropriate resource plan and consumer groups in your Oracle database.

• Map PeopleSoft resource groups to the consumer groups you created.

See Also

Oracle Database Administrator’s Guide: “Using the Database Resource Manager”

Reviewing PeopleSoft Resource Groups

The following table describes the delivered PeopleSoft resource groups and the PeopleTools executables they contain.

Note. How the executables are grouped is not configurable. For example, you can’t further subdivide nor combine the delivered PeopleSoft resource groups.

Determining Where to Implement a Consumer Group

While PeopleTools delivers a set of predefined resource groups that you can map to Oracle consumer groups, you only need to create consumer groups for the resource groups where you need to introduce control of system resource usage.

For example, if COBOL and PeopleSoft Analytic Calculation Engine processing are the only areas of your PeopleSoft system that cause unwanted resource usage, then you only need to create Oracle consumer groups to map to ANALYTIC SERVER and COBOL PeopleSoft resource groups.

Creating an Oracle Resource Plan and Consumer Groups

This section describes the process of defining the resource plan, consumer groups, and plan directives to correspond to the PeopleSoft resource groups.

To create the plan, groups, and directives, you can use the tool of your choice, such as SQL Plus, Oracle SQL Developer, or the Resource Manager interface in Oracle Enterprise Manager.

Note. This section covers information specific to PeopleSoft. It does not cover all topics related to Oracle Database Resource Manager. This documentation assumes that you have read and understand the information contained in Oracle Database Administrator’s Guide related to Oracle Database Resource Manager.

To create a resource plan, consumer group(s), and directives:

1. Connect to the Oracle SID containing the PeopleSoft schema.

2. Create a pending area.

   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

3. Create a resource plan, with a name of your choice.

   For example:

   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'PeopleSoft_plan',
   COMMENT => 'Resource plan/method for PeopleSoft Users Sessions');
   

4. Create the desired consumer groups.

   Create the number of consumer groups required to correspond to the PeopleSoft resource groups that need to be controlled. For this example, assume that only the resource usage of executables related to the application server and PeopleSoft Query need to be controlled.

   For example:

   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
   (CONSUMER_GROUP => 'Application Server', 
   COMMENT => 'Resource consumer group/method for online users sessions');
   
   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'PSQuery', 
   COMMENT => 'Resource consumer group/method for PSQuery sessions');
   

5. Create the directives for the consumer groups you created.

   For example:

   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
   GROUP_OR_SUBPLAN => 'Application Server',
   COMMENT => 'Applications Server sessions at level 1', CPU_P1 => 50, 
   CPU_P2=> 0, PARALLEL_DEGREE_LIMIT_P1 => 8);
   
   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
   GROUP_OR_SUBPLAN => 'PSQuery',
   COMMENT => 'PSQuery sessions at level 1', CPU_P1 => 10, CPU_P2 => 0, 
   PARALLEL_DEGREE_LIMIT_P1 => 2);
   

6. Validate the resource plan.

   EXECUTE DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

7. Submit the plan.

   EXECUTE DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

8. Grant the PeopleSoft schema user (PeopleSoft Access ID) these additional Oracle privileges necessary to administer resource plans:

   GRANT_SYSTEM_PRIVILEGE ADMINSTER_RESOURCE MANAGER

   EXECUTE
   DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE('<ACCESS_ID>',
   'ADMINISTER_RESOURCE_MANAGER',TRUE);
   

   GRANT_SWITCH_CONSUMER_GROUP

   EXECUTE 
   DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('<ACCESS_ID>', 
   '<CONSUMER_GROUP_NAME>', FALSE);

9. Enable the Database Resource Manager in your PeopleSoft SID.

   For completing this, you have two options:

   • Set RESOURCE_MANAGER_PLAN='PeopleSoft_plan’ in the init.ora file for PeopleSoft SID.

   • Issue the following ALTER statement: ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='PeopleSoft_plan’;

Example: Creating PeopleSoft Resource Plan and Consumer Groups SQL Script

You can issue each SQL statement separately, or you may elect to create a single script to create the required consumer groups. The following is a sample SQL script for creating a resource plan with consumer groups for your PeopleSoft system.

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();


DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'PeopleSoft_plan',
COMMENT => 'Resource plan/method for PeopleSoft Users Sessions');


DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Application Server',
COMMENT => 'Resource consumer group/method for online users sessions');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'PSQuery',
COMMENT => 'Resource consumer group/method for PSQuery sessions');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'PubSub',
COMMENT => 'Resource consumer group/method for PUBSUB sessions');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Application Engine',
COMMENT => 'Resource consumer group/method for Application Engine');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Cobol',
COMMENT => 'Resource consumer group/method for Cobol');


DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
GROUP_OR_SUBPLAN => 'Application Server',
COMMENT => 'Applications Server sessions at level 1', CPU_P1 => 50, CPU_P2=> 0,
 PARALLEL_DEGREE_LIMIT_P1 => 8);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
GROUP_OR_SUBPLAN => 'PSQuery',
COMMENT => 'PSQuery sessions at level 1', CPU_P1 => 10, CPU_P2 => 0, 
PARALLEL_DEGREE_LIMIT_P1 => 2);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
GROUP_OR_SUBPLAN => 'PubSub',
COMMENT => 'PubSub sessions at level 1', CPU_P1 => 10, CPU_P2 => 0, 
PARALLEL_DEGREE_LIMIT_P1 => 3);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
GROUP_OR_SUBPLAN => 'Application Engine',
COMMENT => 'Application Engine sessions at level 1', CPU_P1 => 10, CPU_P2 => 50,
 CPU_P3 => 50);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
GROUP_OR_SUBPLAN => 'Cobol',
COMMENT => 'Cobol sessions at level 1', CPU_P1 => 10, CPU_P2 => 50, CPU_P3 => 50);


DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA()

Mapping PeopleSoft Resource Groups to Oracle Consumer Groups

The Pt Ora Resource page enables you to map PeopleSoft resource groups with the Oracle consumer groups you have defined.

Select PeopleTools, Utilities, Administration, Oracle Resource Management.

PeopleSoft Resource Name

PeopleTools provides predefined PeopleSoft resource groups containing one or more PeopleTools executables. The entire set of delivered PeopleSoft resource groups appears in this list.

Oracle Consumer Group

After you have created the appropriate Oracle resource plan and consumer groups to correspond to the PeopleSoft resource groups, enter the name of the appropriate Oracle resource group in the edit box. Enter the consumer group name exactly as it appears in the SQL you submitted to create it. For example, if the SQL you used to create the consumer group appeared as: CONSUMER_GROUP => 'PSQuery' Then, in the Oracle Consumer Group edit box, enter PSQuery.

Implementing Oracle Transparent Data Encryption

This section contains an overview and discusses how to:

• Determine fields to encrypt.

• Set up the Oracle Wallet.

• Set the encryption algorithm.

• Encrypt fields.

• Manage fields encrypted for TDE.

See Also

For more information on Oracle's Transparent Data Encryption feature refer toOracle® Database Advanced Security Administrator's Guide

Understanding Transparent Data Encryption

PeopleTools enables you to implement Oracle’s Transparent data encryption (TDE) feature to encrypt the columns you select, enhancing the security of your PeopleSoft applications.

Transparent data encryption (TDE) enables encryption of sensitive data in database columns as it is stored in the operating system files. It provides for secure storage and management of encryption keys in a security module located outside database, separating ordinary program functions from those that pertain to security, such as encryption.

This separation enables you to divide administration duties between DBAs and security administrators, which is a strategy that enhances security because no administrator is granted comprehensive access to data. For example, one administrator manages only the keys, while another manages only the database.

TDE is a key-based access control system enforcing authorization using these keys:

TDE is transparent to the application, and no views or additional tables are required. The application logic associated with SQL and table access will continue to work without modification.

To implement this feature within your PeopleSoft application, you need to:

• Determine the fields that are candidates for TDE.

• Set up the Oracle wallet.

• Set the encryption algorithm.

• Encrypt fields.

Note. This feature is available for Oracle databases running 10g R2 and later. Oracle did not provide this feature on any earlier version.

Determining Fields to Encrypt

PeopleSoft applications will provide lists of candidate fields for TDE encryption, such as those that contain Personally Identifying Information (PII), in the respective applications. Review these lists and determine which fields you want to encrypt using TDE. If you have custom fields in your application, make sure to consider that set of fields as well.

Examples of information that are candidates for TDE include:

• Names.

• Contact information (address, telephone number, email address, and so on).

• Credit card number.

• Passport number.

• Driver’s license number.

• Age.

• Salary.

• Academic grades, scores, marks, and so on.

Note. Depending on the type of business and country in which you are running your PeopleSoft applications, there may be specific types of information, PII, that needs to be encrypted to comply with regulatory standards.

See Also

Your PeopleSoft application documentation

Managing the Oracle Wallet

With TDE, each individual table has its own table key, which is used to encrypt the selected columns in that table. Each table key is, in turn, encrypted using the TDE master key. The TDE master key is stored and protected outside the database in an Oracle Wallet, which is a container that stores authentication and signing credentials, including:

• TDE master key.

• PKI private keys.

• Certificates.

• Trusted certificates for SSL.

Encrypted table keys are placed in the data dictionary. When a user enters data into the column defined as encrypted, the Oracle database retrieves the master key from the wallet, decrypts the encryption key for that table from the data dictionary, uses that encryption key on the input value, and stores the encrypted data in the database.

Setting up the Oracle Wallet

Before implementing TDE, creating an Oracle Wallet is required.

Warning! After implementing TDE, the Oracle Wallet must be opened each time a database instance starts (or has been restarted) or else TDE will not work. If the wallet is not open, users will see error messages if they attempt to access any data encrypted using TDE.

To set up an Oracle Wallet for TDE:

1. Specify the wallet location.

   By default, the wallet is created in the directory $ORACLE_BASE/admin/$ORACLE_SID/wallet.

   So, if $ORACLE_BASE is /ds1/product/oracle and $ORACLE_SID is HRDMO, then the wallet will be stored in the directory /ds1/product/oracle/admin/HRDMO/wallet.

   You can set a different directory by specifying it in the sqlnet.ora file located in $ORACLE_HOME/network/admin. For instance, if you want the wallet to be in /orawall directory, place the following lines in the sqlnet.ora file:

   ENCRYPTION_WALLET_LOCATION =
    (SOURCE=
      (METHOD=file)
        (METHOD_DATA=
          (DIRECTORY=/orawall)))
   

   Note. Oracle recommends adding this location to regular backup utility.

2. Create the wallet.

   Issue the following SQL as a user with the ALTER SYSTEM privilege, such as SYSTEM, SYS, or SYSDBA. In this example, HRMSTKEY is the password.

   alter system set encryption key 
   authenticated by "HRMSTKEY";
   

   The preceding command creates the wallet in the specified location, sets the password of the wallet as HRMSTKEY, and opens the wallet for TDE to store and retrieve the master key.

   Note. The password is case-sensitive and must be enclosed in double quotes. The password doesn't show up in clear text in any dynamic performance views or logs.

Opening and Closing the Wallet

After you create the wallet and set the password, every time you start the database, you'll have to open the wallet explicitly, using SYS, SYSTEM, or SYSDBA accounts.

For example,

alter system set encryption wallet open authenticated by "HRMSTKEY";

To close the wallet:

alter system set encryption wallet close;

Setting the Encryption Algorithm

You set the desired encryption algorithm used by TDE on the PeopleTools Options page in the Database Encryption Algorithm edit box.

Access the PeopleTools Options page (PeopleTools, Utilities, Administration, PeopleTools Options).

The algorithms you can enter are:

• Advanced Encryption Standard algorithm with a 128-bit, 192-bit, or 256-bit key.

• Triple DES algorithm with a 168-bit key.

Specify the desired algorithm by entering one of the following values into the Database Encryption Algorithm edit box exactly as it appears below:

• AES128

• AES192

• AES256

• 3DES168

Note. You must specify an encryption algorithm to enable the Encrypt option for a field definition in Application Designer.

Encrypting Fields

You encrypt fields in Application Designer by selecting the Encrypt check box on a field definition, and then creating a table or altering an existing table.

Note. The Encrypt check box is enabled only on Oracle databases running version 10g R2 or later that also have an encryption algorithm specified in the Database Encryption Algorithm edit box on the PeopleTools Options page.

These PeopleSoft field types can be encrypted:

• Character

• Long Character (see note below)

• Number

• Signed number

• Date

• DateTime

• Time

Note. Long Character field types may only take advantage of TDE when the following conditions are true: the field length is greater than 0 and less than 1334 and the Raw Binary field attribute is not set.

These PeopleSoft field types can not be encrypted:

• Image

• Image reference

• Attachment

After you define the field to be encrypted, and either create a table or alter an existing table containing that field definition, the Build feature generates DDL SQL containing the ENCRYPT clause in the following syntax:

ENCRYPT using ‘ALGORITHM’

For example,

ALTER TABLE  PS_AM_BI_HDR 
MODIFY (CR_CARD_NBR ENCRYPT using ‘AES256’ NO SALT);

Note. If you are using Oracle Database version 10.2.0.4 or higher, the syntax includes the NOMAC parameter. For example, ALTER TABLE PS_AM_BI_HDR MODIFY (CR_CARD_NBR ENCRYPT using 'AES192' 'NOMAC' NO SALT);

The NOMAC parameter reduces the storage requirements and provides improved performance.

See your Oracle database documentation for more information on NOMAC.

When DDL SQL containing the ENCRYPT clause is run against the database, Oracle:

• creates a cryptographically secure encryption key for the table containing the column.

• encrypts the clear text data in the column, using the specified encryption algorithm.

Managing Fields Encrypted for TDE

This section covers these topics related to the ongoing maintenance of encrypted fields:

• Decrypting fields.

• Regenerating an encryption key.

• Upgrading TDE encrypted fields.

Decrypting Fields

If you decide that you no longer want a field encrypted for TDE, you can issue a SQL ALTER operation using the DECRYPT clause. For example, assume you wanted to decrypt the SSN field on the ACCOUNT table.

ALTER TABLE ACCOUNT MODIFY (SSN DECRYPT);

Regenerating An Encryption Key

Situations where you might consider regenerating a table encryption key include:

• You suspect a table key has been compromised.

• You want to take advantage of a different encryption algorithm.

You regenerate a table encryption key by issuing a SQL ALTER operation using the REKEY clause. For example, assume you wanted to rekey the PS_AM_BI_HDR table to take advantage of AES256.

ALTER TABLE PS_AM_BI_HDR REKEY using 'AES256';

This creates a new table key and recreates the encrypted column values using the new table key.

Upgrading TDE Encrypted Fields

All metadata field definitions are delivered with no-encryption attributes enabled. PeopleSoft applications will not deliver any metadata indicating encryption enabled for any field for an initial installation database file, project, or a PeopleTools or PeopleSoft application patch.

If you customize the field by adding TDE encryption, you need to keep track of the fields and associated record definitions and ensure that you maintain the desired encryption status through any upgrades that you perform.

See Your PeopleSoft upgrade documentation

Altering Tables With TDE Encrypted Fields

When altering tables with TDE encrypted fields using the Alter in Place option, Application Designer automatically switches the Index Creation Options selection to Recreate index only if modified even if you specifically select Recreate index if it already exists in the Build Settings dialog box.

Protecting and Managing PeopleSoft Applications with Database Vault

This section provides an overview and discusses:

• Restricting access for the access ID.

• Restricting access for PSFTDBA ID.

• Using multiple, alternate, access IDs.

Understanding Oracle Database Vault

Oracle Database Vault provides an extra layer of security that protects a database against insider security threats. One of Database Vault’s key features is that it protects PeopleSoft application data from being accessed by super-privileged users, such as DBA or system administrators, but it still allows them to maintain the Oracle database.

A super-privileged user, such as a DBA, should not have access to PeopleSoft application data. Application data can include salary, identification numbers, credit card numbers, and other personal information. On the other hand, the DBA must still be able to perform database maintenance, such as back up and recovery. Database Vault allows DBAs to do their jobs, but does not allow the DBA to have access to application data.

PeopleTools has validated the use of Oracle Database Value with PeopleSoft applications. From that validation effort we’ve provided sample PeopleSoft DB Vault security policies. The sample policies are available on Oracle Technology Network (OTN).

See http://www.oracle.com/technology/software/products/database_vault/index.html

These sample policies lock the database to allow all PeopleSoft application processes to access the database, while restricting any super user, like a DBA, from viewing the data using any Oracle delivered query tool. These policies illustrate a minimal usage of Database Vault functionality and may be modified or enhanced based on your specific level of required database security. The following table illustrates how the implementation of the example Database Vault policies affects the PeopleSoft Access ID and end-users, such as VP1 or PS.

Restricting Access For the Access ID

In the PeopleSoft system, the access ID is the Oracle owner of all schema objects in a PeopleSoft database. With Database Vault you can restrict Oracle users other than the access ID from having 'SELECT' privilege on any access ID objects.

This restrictive usage is supported by using the sample PeopleSoft Database Vault security policies. When the sample PeopleSoft Database Vault security policies are implemented and Database Vault is enabled on a PeopleSoft database running on Oracle, the policies allow the access ID to do everything it currently needs to do on behalf of PeopleSoft components.

By design, all DML including SELECT DML is allowed by the access ID if the DML is issued through a "known" PeopleTools component, as defined in the sample PeopleSoft Database Vault security policies.

SELECT DML access is restricted for the access ID if not executed through a defined PeopleTools component.

SQLPlus and other ad hoc query tools are not explicitly defined in the sample policies and therefore cannot be used to issue SELECT DML against the database.

Restricting Access For PSFTDBA ID

The sample policies and scripts provide for non-access ID access to the database through the Oracle user, PSFTDBA. This user is intended to be used when you need SQLPLUS access to the system.

In order for DBAs to perform system maintenance, upgrade tasks, and so on, the sample policy scripts create the PSFTDBA account. With this account the following actions are allowed on database tables:

• INSERT

• UPDATE

• DELETE

The sample PeopleSoft Database Vault security policies restrict the PSFTDBA ID from performing a SELECT against the access ID's objects. If you use the PSFTDBA account to run a SELECT statement, an error message similar to the following appears:

sp-hp15:$ sqlplus PSFTDBA/PSFTDBA@Q8501123

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Apr 9 10:45:36 2008

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Oracle Label Security, Oracle Database Vault and 
Real Application Testing options

SQL> select * from Q8501123.PSSTATUS;
select * from Q8501123.PSSTATUS
                       *
ERROR at line 1:
ORA-01031: insufficient privileges

The PSFTDBA ID is designed so that your DBA’s use it rather than the access ID to increase security when performing database maintenance. When performing some tasks, keep in mind that PSFTDBA does not have sufficient access to the database to perform all PeopleSoft maintenance tasks, such as all upgrade tasks.

For example, when running SQRs from the workstation, the PSFTDBA user ID cannot run SELECTs on the database to generate reports. This is a defined PeopleSoft Database Vault policy restriction. SQR’s should be run as scheduled Process Scheduler jobs on the server. Also, when applying PeopleSoft upgrades involves some steps that require access to the database using the access ID. For example, in some cases you need to run Data Mover in bootstrap mode using the access ID/password. Data Mover scripts cannot be run as PSFTDBA. In these cases, the key limitation to keep in mind is that the PSFTDBA ID cannot run a select against any access ID owned tables, which includes tables required for Data Mover to log in to the system.

In cases, where you need SELECT access for certain features (SQR, Data Mover, and so on) you can configure a set of specific, alternative ID’s to be used for PeopleSoft upgrade tasks while still remaining in compliance with the Database Vault policies.

Using Multiple Alternate Access IDs

The sample PeopleSoft Database Vault security policies provide protection of highly sensitive information in the PeopleSoft tables from database "super users." In some cases, you may need a more tailored access, such as in the cases of upgrades, patching, auditing, and the separation of duties for the PeopleSoft Access ID.

You can leverage Database Vault so that PeopleSoft tables, procedures and triggers could be protected can still be protected while allowing special access to complete upgrade and maintenance tasks. The privileges in the Database Vault PeopleSoft template can be given to the multiple, alternate, access IDs. By using multiple, alternate, access IDs to perform PeopleSoft maintenance, you can mitigate the issues involved with distributing the password of the base access ID to multiple users.

The multiple, alternate, access IDs (PSFTDBAnn) technique has been tested with Database Vault in the field on PeopleSoft installations and offers a solution where unique, identifiable accounts can be used to perform PeopleSoft patching and upgrades. These accounts can be limited to the modules and machine names from which the PSFTDBAnn ID can run. These accounts also can be heavily audited, to make sure that they do not introduce malicious code, which removes the need to implement heavy auditing on the base access ID account.

With multiple, alternate, access IDs you can:

• Use multiple, alternate, access IDs that can be used just for PeopleSoft upgrade/maintenance.

• Create PSFTDBAnn accounts that have many auditing options enabled, not affecting the use of the production access ID (SYSADM).

• Use Oracle’s Audit Vault to enhance the separation-of-duties when it comes to centrally managing audit information.

• Configure Database Vault so that the PSFTDBAnn account can be restricted to particular machines and times, and so on.

• Tale advantage of Database Vault's strictly DBA account(s) (PSFTDBA) that can modify the database and system, but not issue selects on tables in the PeopleSoft Realm. The PSFTDBA account can do many DBA activities such as alter tablespaces, examine performance, start and stop the system, but not see sensitive information. The PSFTDBA account can apply Oracle Database Patches, but not apply PeopleSoft type of patches.

• Restrict knowledge of the access ID password, as it is no longer required for PeopleSoft maintenance.

• Address many more of the concerns third party auditors are identifying on systems that contain highly sensitive information in PeopleSoft applications.

In the following examples, the unofficial account "PSFTDBAn" represents multiple access IDs, although it can be almost any name. The PSFTDBAn accounts need to retain the ability to do ‘SELECTS’ on PeopleSoft objects. This technique leverages a protected Login Trigger that alters the CURRENT_SCHEMA, so that the PSFTDBAn accounts can act as the access ID (SYSADM) account, but preserve the user's identity (PSFTDBA1) when running any commands.

To configure multiple, alternate, access IDs:

1. Create one to 'n' multiple, alternate, access IDs (authorized Oracle USERS):

   create user psftdba1 identified by oracle_1;
   create user psftdba2 identified by oracle_1;
   create user psftdba3 identified by oracle_1;
   

2. Grant minimal privilidges to these alternate authorized USERS:

   grant connect,resource to psftdba1;
   grant connect,resource to psftdba2;
   grant connect,resource to psftdba3;

3. CREATE an Oracle instance level logon trigger to issue an ALTER SESSION SET CURRENT_SCHEMA whenever an alternative authorized user logs into the instance.

   drop trigger psft_login_trg;
   create or replace trigger psft_login_trg
   after logon on database
   
   begin
   -- * use dvf if in a database vault environemnt.
   -- * database vault would also help protect the peoplesoft realm, and 
   logon trigger, and so on
   -- if dvf.f$session_user in ('PSFTDBA1' , 'PSFTDBA2', 'PSFTDBA3') then
   if sys_context('userenv','session_user') in in ('PSFTDBA1' , 'PSFTDBA2',
    'PSFTDBA3') then
   execute immediate 'alter session set current_schema=’SYSADM';
   end if;
   end;
   /
   
   

Every time one of the alternative authorized USERs logs into the instance, an ALTER SESSION SET CURRENT_SCHEMA=ACCESSID is issued. From here on in any operation performed that is unqualified would be done in the ACCESSID schema.

For example, if the 'PSFTDBA1' were logged into the database directly using SQLPLUS or indirectly using Data Mover, then any 'VALID' operation performed that is unqualified would be done in the ACCESSID schema. All of ‘PSFTDBA1's actions on the database could be audited if the Oracle Auditing facility (Audit Vault) were used. If you need to verify you have database connectivity, you can use the PSFTDBAn account for your test. Data Mover and SQR testing from the workstation will be able to use the PSFTDBAn account.

Working With Oracle 11g Security Features

Oracle 11g introduces security features, which from a database security perspective, increase restrictions for database access. These changes are part of the "Secure By Default" configuration of 11g. These changes include setting a defined limit for the PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME associated with the default profile. This section discusses how PeopleSoft systems are affected and what your options are.

Understanding Default Profiles

All Oracle users created in an instance are assigned a default profile, such as the default profile delivered with 11g. There are differences between the default profiles for 10g and 11g.

For pre-11g Oracle releases, the default profile did not specify a PASSWORD_LIFE_TIME limit. As such, by default, the password for a given Oracle user never expired. PASSWORD_LOCK_TIME and PASSWORD_GRACE_TIME were also unlimited. For 11g, the default profile has a PASSWORD_LIFE_TIME of 180 days. PASSWORD_LOCK_TIME and PASSWORD_GRACE_TIME also have limits.

For a PeopleSoft installation on the Oracle platform, several Oracle user IDs are created during the installation. Those Oracle users are:

• ACCESSID (default is SYSADM)

• CONNECT ID (default is people)

• PS (owns the PSDBOWNER table)

The ACCESSID is the schema owner for all database objects related to a specific PeopleSoft application installation. The ACCESSID and ACCESSID password are stored and encrypted in the PeopleSoft security table PSACCESSPRFL.

SQL> descr SYSADM.PSACCESSPRFL
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------

 SYMBOLICID                                NOT NULL VARCHAR2(8 CHAR)
 VERSION                                   NOT NULL NUMBER(38)
 ACCESSID                                  NOT NULL VARCHAR2(16 CHAR)
 ACCESSPSWD                                NOT NULL VARCHAR2(16 CHAR)
 ENCRYPTED                                 NOT NULL NUMBER(38)

SQL> SELECT  * from SYSADM.PSACCESSPRFL;;

SYMBOLIC    	VERSION 	ACCESSID        	ACCESSPSWD        	ENCRYPTED
-------- 		---------- 		---------------- 	---------------- 		----------
SYSADM1           7		 sBzLcYlPrag=     sBzLcYlPrag=              	1

The connect ID is a pseudo logon which allows PeopleSoft to associate multiple PeopleSoft user IDs to the same connect ID. The connect ID has the minimum privileges required to connect to the database (only SELECT privileges on specific PeopleTools tables). After a connection has been established using the connect ID, PeopleSoft security uses the PeopleSoft user ID to control access to objects in the database. The PeopleSoft signon process validates the connect ID on the server, rather than the user ID. The connect ID simplifies database security maintenance, as you don't need to maintain access for all PeopleSoft users, just for the connect ID.

The PS ID is used once, during PeopleSoft database creation, to create the PSDBOWNER table. Once this table has been created, read access and write privileges are made public to everyone, then the PS user ID privileges are revoked.

Encountering Issues Related to Oracle 11g Security

When the PASSWORD_LIFE_TIME has been reached, the PeopleSoft Oracle users (in this case the PeopleSoft ACCESSID and CONNECT ID) will be locked out of the database. This means that any PeopleSoft process cannot access the database, such as application server, Process Scheduler, COBOL, Data Mover, and so on.

If this occurs you will see any of the following Oracle database error messages:

ORA-28000: the account is locked
Cause: The user has entered wrong password consequently for maximum number of⇒
 times specified by the user's 
profile parameter FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
Action: Wait for PASSWORD_LOCK_TIME or contact DBA

                
ORA-28001: the password has expired
Cause: The user's account has expired and the password needs to be changed
Action: change the password or contact the DBA

                
ORA-28002 the password will expire within string days
Cause: The user's account is about to about to expire and the password needs to be
 changed.
Action: Change the password or contact the database administrator.

These messages may appear in a SQL trace, an application server log, a Process Scheduler log, or in an error message in the GUI when attempting to access the database (signon to Application Designer or Data Mover). The following are some select examples of what you can expect to see in log and trace files.

The trace will show the login failing as follows:

CONNECTID.


2-4     13.06.56    1.581000 Cur#0.6060.QE849C42 RC=28001 Dur=1.581000 
Connect=Primary/QE849C42/people/
2-5     13.06.56    0.000000 Cur#0.6060.QE849C42 RC=-1 Dur=0.000000 XER 
rtncd=761802124 msg=
2-6     13.06.56    0.000000 Cur#0.6060.QE849C42 RC=0 Dur=0.000000 ERR rtncd=28001
 msg=ORA-28001: the password has expired

The following illustrates an application server or Process Scheduler boot with passwords already expired:

PeopleTools 8.xx.07 Client Trace - 2008-10-24 
  
PID-Line  Time        Elapsed  Trace Data... 
--------  --------  ---------- --------------------> 
 1-1      14.25.45             Tuxedo session opened {oprid='QEDMO', 
appname='Two Tier', addr='//TwoTier:7000', open at 01C67EC8, pid=4956}
 1-2      14.25.45    0.058000 Cur#0.4956.QE849C41 RC=0 Dur=0.003000 --- router
 PSORA load succeeded
 1-3      14.25.45    0.155000 Cur#0.4956.QE849C41 RC=0 Dur=0.155000 INI
 1-4      14.25.45    0.192000 Cur#0.4956.QE849C41 RC=28002 Dur=0.192000 
Connect=Primary/QE849C41/people/
 1-5      14.25.45    0.000000 Cur#0.4956.QE849C41 RC=-1 Dur=0.000000 XER 
rtncd=761800508 msg=
 1-6      14.25.45    0.000000 Cur#0.4956.QE849C41 RC=0 Dur=0.000000 ERR
rtncd=28002 msg=ORA-28002: 
the password will expire within 7 days 
 1-7      14.25.48    2.718000 Cur#0.4956.notSamTran RC=0 Dur=0.000000 DON
 1-8      14.25.51    2.742000 Tuxedo session opened { DisconnectAll at01C67EC8,
 pid=4956}

The following illustrates a client trace of a application server or Process Scheduler boot:

PeopleTools 8.49.07 Client Trace - 2008-10-24 
  
PID-Line  Time        Elapsed  Trace Data... 
--------  --------  ---------- --------------------> 
 1-1      14.30.38             Tuxedo session opened {oprid='QEDMO', 
appname='Two Tier', addr='//TwoTier:7000', open at 01C67EC8, pid=3328}
 1-2      14.30.38    0.056000 Cur#0.3328.QE849C41 RC=0 Dur=0.004000 --- router
 PSORA load succeeded
 1-3      14.30.38    0.238000 Cur#0.3328.QE849C41 RC=0 Dur=0.238000 INI
 1-4      14.30.38    0.529000 Cur#1.3328.QE849C41 RC=0 Dur=0.529000 
Connect=Primary/QE849C41/people/
 1-5      14.30.38    0.036000 Cur#1.3328.QE849C41 RC=0 Dur=0.000000 GET 
type=1003 dbtype=4
 1-6      14.30.38    0.000000 Cur#1.3328.QE849C41 RC=0 Dur=0.000000 GET 
type=1004 release=11
 1-7      14.30.38    0.076000 Cur#1.3328.QE849C41 RC=0 Dur=0.000000 COM 
Stmt=
SELECT OWNERID FROM PS.PSDBOWNER 
WHERE DBNAME=:1
.
1-41     14.30.40    0.200000 Cur#1.3328.QE849C41 RC=0 Dur=0.200000 Disconnect
 1-42     14.30.40    0.251000 Cur#0.3328.QE849C41 RC=28002 Dur=0.220000 
Connect=Primary/QE849C41/QE849C41/
 1-43     14.30.40    0.000000 Cur#0.3328.QE849C41 RC=-1 Dur=0.000000 XER 
rtncd=18874368 msg=
 1-44     14.30.40    0.000000 Cur#0.3328.QE849C41 RC=0 Dur=0.000000 ERR 
rtncd=28002 msg=ORA-28002: the password will expire within 7 days 
 1-45     14.30.42    2.293000 Cur#0.3328.notSamTran RC=0 Dur=0.000000 DON
 1-46     14.30.43    0.788000 Tuxedo session opened { DisconnectAll 
at01C67EC8, pid=3328}

The failure and return of the GRACE PERIOD warning message gives you time to react before the password actually expires, enabling you to be proactive and reset or change the ACCESSID and/or the CONNECT ID password(s).

Oracle 11g Security Configuration Options

This section discusses options for dealing with Oracle 11g security, including:

• Setting the PASSWORD_LIFE_TIME to unlimited.

• Creating a PeopleSoft-specific profile.

• Resetting the PeopleSoft installation user IDs.

• Changing the PeopleSoft installation user IDs.

Setting the PASSWORD_LIFE_TIME to Unlimited

You can set the PASSWORD_LIFE_TIME in the default profile to unlimited. If this is done prior to creating the PeopleSoft-specific Oracle user IDs used for the PeopleSoft database installation, then the default behavior will mimic the pre-Oracle 11g behavior.

This can be done by creating the ACCESSID and CONNECT ID using the following command:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
;

Note. While feasible, this particular solution is counter to the secure by default positioning of Oracle 11g and to regulations requiring periodic changes to important passwords.

Creating a PeopleSoft-Specific Profile

You can create a PeopleSoft-specific profile which sets the PASSWORD_LIFE_TIME to unlimited. Creating the new PeopleSoft profile should be done when you create the database rather than altering PeopleSoft users from the default profiles to the PeopleSoft-specific profiles. Switching the a PeopleSoft-specific profile after you have created the PeopleSoft-specific users expired password limits does not automatically modify the expiry_date column in USER_USERS (done when creating the users with the default profile).

Create the ACCESSID and CONNECT ID user IDs using the delivered scripts, PS_HOME/scripts/PSADMIN.SQL and PS_HOME/scripts/CONNECT.SQL. After doing so, the PeopleSoft Oracle user IDs would have the default profile assigned. Alter the ACCESSID and CONNECT ID user IDs to make use of the alternate profile rather than the default. This can be done using the following commands:

CREATE PROFILE PSPROFILE LIMIT  PASSWORD_LIFE_TIME UNLIMITED
;

This creates the PSPROFILE profile with password limits values set. All values not explicitly listed are derived from the default profile.

The following statements alter both the default ACCESSID and CONNECT ID to utilize the PSPROFILE profile with the password limit set for PASSWORD_LIFE_TIME to unlimited:

ALTER USER SYSADM PROFILE PSPROFILE
;
ALTER USER PEOPLE  PROFILE PSPROFILE
;

Note. While feasible, this solution will allow the profile expiration behavior to mimic the pre-Oracle 11g behavior, but this runs counter to the intent of regulations that require changing critical passwords on a regular basis.

Resetting the PeopleSoft Installation User IDs

You can reset the PeopleSoft installation Oracle user ID passwords (the ACCESSID and CONNECT ID) in all of the places it needs to be reset. After the passwords expire, reset them to the original value. You can reset the password using the PASSWORD command or by ALTER USER command.

Note. If using Database Vault, then only the database vault account manager can reset the account, because the access ID cannot login to SQLPLUS to change the password.

Note. While feasible, this option runs counter to the intent of regulations that require changing critical passwords on a regular basis.

Changing the PeopleSoft Installation User IDs

The recommended option is to change the PeopleSoft installation required Oracle user ID passwords (the ACCESSID and CONNECT ID) after they have expired, and reflect those changes in all required locations. This option enables you to conform to regulations that require changing critical passwords on a regular basis.

If the password expires and an Oracle user ID password is changed within the Oracle database for the ACCESSID or CONNECT ID, the PeopleSoft system will still have the old password stored in the PeopleSoft security metadata tables and configuration files. These changed passwords will have to be reflected in the PeopleSoft security metadata tables and configuration files as well as the database.

At the database level, you can use the PASSWORD and ALTER USER commands to change the ACCESS ID and CONNECT ID passwords. For example:

C:\Documents and Settings\>sqlplus people/peop1e@QE849C42

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 21 10:55:57 2008

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

ERROR:
ORA-28001: the password has expired

Changing password for people
New password: <changed to  ‘peop2e’>
Retype new password: <changed to  ‘peop2e’>
Password changed

SQL> exit

Or,

ALTER USER QE849C42 IDENTIFIED BY CHANGEPW ACCOUNT UNLOCK;

User altered.

ALTER USER people IDENTIFIED BY peop2e ACCOUNT UNLOCK;

User altered.

SQL> exit

Note. You may also have to include the UNLOCK keyword to unlock the account (if the password retry has been exceeded).

In PeopleTools, open Configuration Manager and change the Connect Password value on the Startup tab.

Then, open Data Mover in bootstrap mode (using the new ACCESSID password) to run the necessary commands to change the ACCESSID passwords on the appropriate PeopleSoft metadata tables. For example,

SET LOG c:\temp\changeaccessidpswd.out; 
UPDATE PSSTATUS SET OWNERID = 'QE849C42'; 
UPDATE PSOPRDEFN SET OPERPSWD = OPRID, ACCTLOCK=0, ENCRYPTED = 0; 
UPDATE PSACCESSPRFL SET ACCESSID = 'QE849C42', ACCESSPSWD = 'CHANGEPW', 
VERSION = 0, ENCRYPTED = 0; 
ENCRYPT_PASSWORD *;

Note. For Oracle 11g, the password is case sensitive.

Lastly, apply the connect ID changes to the psprcs.cfg and psappsrv.cfg configurations files and rebuild the domains. For example:

[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=QEDMO
DBType=ORACLE
UserId=QEDMO
UserPswd==QEDMO
ConnectId=people
ConnectPswd=peop2e
ServerName=

Working With Oracle Transparent Application Failover

PeopeTools provides limited support for Oracle Transparent Application Failover (TAF). PeopleTools TAF support includes:

• PeopleSoft servers can be configured to transparently reconnect to a surviving RAC instance in the event of an instance failure with in a RAC cluster.

• PeopleSoft servers can be configured to transparently fail over to an Oracle Database Data Guard standby when the primary database is lost.

Note. In most cases, other than a slight pause in the operation, the failover is transparent to the application end user.

PeopleTools is designed to listen for Oracle fast application notification (FAN) events to derive the failover behavior. Upon receipt of a FAN event, PeopleSoft servers break their existing TCP connections and initiate TAF, which references the TNSNAMES.ORA connect alias address list and establishes a connection to the surviving instance.

See Your Oracle RAC and database administration guides for the details of implementing and managing Oracle RAC clusters.

End-User System Behavior With TAF Configured

The following table summarizes PeopleSoft behavior during RAC or Data Guard failover when TAF is configured.

Batch System Behavior With TAF Configured

The following table summarizes PeopleSoft batch system behavior during RAC or Data Guard failover when TAF is configured.

Implementing Oracle Active Data Guard

This section provides an overview and discusses:

• Installing and configuring Oracle Active Data Guard.

• Setting up the secondary access ID.

• Creating the domain boot user.

• Creating synonyms and database links.

• Configuring Domains.

• Configuring read-only components.

• Configuring read-only process definitions.

Understanding Active Data Guard Within PeopleSoft

Oracle Active Data Guard, with Oracle Database 11g Enterprise Edition, enables you to offload resource-intensive activities from a production database to a synchronized standby database. Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, and so on, while continuously applying changes received from the production database.

If you use Oracle Active Data Guard at your site, PeopleTools provides the infrastructure to use Active Data Guard with your PeopleSoft application databases.

The following diagram depicts how Oracle Active Data Guard enables you to incorporate the use of a standby database for you to offload designated read-only transactions, freeing up more resources to handle the read-write transactions on your primary (production) database.

Oracle Active Data Guard synchronizing the primary and standby database so read-only requests can be routed to the standby database for processing

The following table describes the elements within the diagram:

Installing and Configuring Oracle Active Data Guard

Oracle Active Data Guard needs to be licensed, installed, and enabled for your server before you can begin setting up your PeopleSoft system to take advantage of this feature. The primary and standby databases need to be set up according to the Oracle Active Data Guard documentation.

See Also

Oracle Data Guard Concepts and Administration 11g

Creating the Secondary Access ID

This section discusses how to configure the secondary access ID for use with your primary and standby databases.

To configure the secondary access ID:

1. Run the psadmin.sql script against your primary database.

   • Run the script from PS_HOME\scripts.

   • When prompted, provide the secondary access ID and password.

2. Insert a row in the PSDBOWNER table for the standby database and the secondary access ID.

   For example:

   INSERT INTO PS.PSDBOWNER VALUES('DATABASE', 'ACCESS_ID’);
   Commit;
   

3. Create an access profile to associate the secondary access ID with a symbolic ID.

   • In Application Designer, select Tools, Misc Def, Access Profile, and click New.

   • Provide the new symbolic ID, and enter the secondary access ID and password you created using psadmin.sql.

Creating the Domain Boot User

Create or clone a PeopleSoft user ID and associate it with the secondary access ID so that the user profile will be able to start an application server domain and alternate Process Scheduler server connecting to the standby database.

To create the domain boot user:

1. Select PeopleTools, Security, Copy User Profiles, and enter a new user ID and password.

2. From the Symbolic ID dropdown list on the General tab for the user ID you just created, select the symbolic ID associated with your secondary access ID.

3. Open Data Mover and encrypt the new user profile’s password by running the following:

   UPDATE  PSOPRDEFN SET operpswd=’<PASSWORD>’, encrypted=0
   Where oprid=’<USER_ID>’;
   ENCRYPT_PASSWORD *;
   

Creating Synonyms and Database Links

The mechanism used in the PeopleSoft implementation of Oracle Active Data Guard for making database objects in the standby database available relies on the existence of a secondary access ID.

The secondary access ID does not own any objects, so, sufficient access to and awareness of objects in the primary database. To associate standby database objects with the equivalents in the primary database, the following must be established:

• Local synonyms.

• Remote synonyms.

• Database links to the primary database.

Note. Remote synonyms and database links are required for the tables to which the standby database needs non-SELECT access.

To create the synonyms and database links, PeopleTools delivers these scripts in PS_HOME\scripts:

• createlocalsynonyms.sql

• createremotesynonyms.sql

• createdblinktoprimarydb.sql

Creating Local Synonyms

To create local synonyms you use the createlocalsynonyms.sql script. Run this script against the primary database while connected as the primary access ID. Before running the script, edit it to include your primary access ID in place of the <SYSADM> variable.

The createlocalsynonyms.sql generates the following scripts:

• createlocaltablesynonyms.sql

• createlocalviewsynonyms.sql

You run these scripts against the primary database connected as the secondary access ID.

Creating Remote Synonyms

Remote synonyms are required for the tables identified as requiring DML access. If you decide to set a component to "read-only" after your analysis, then you need to include the underlying tables that require DML access to this script manually.

To create remote synonyms, you run the createremotesynonyms.sql script.

You run this script against the primary database connected as the secondary access ID.

Creating Database Links to the Primary Database

To create database links, you run the createdblinktoprimarydb.sql script.

This script creates a fixed database link between the secondary access ID and the primary access ID on the primary database.

Before running this script, open it and edit it, and replace:

• <DBNAME> with the primary database TNSALIAS.

• <SYSADM> with the primary access ID.

• <PASSWORD> with the primary access ID password.

You run this script connected to the primary database with the secondary access ID.

Configuring Domains

The domains connecting to your primary database must also be configured to connect to your standby database. This is accomplished by specifying the standby connection credentials in the Startup section of the domain configuration for both the application server and Process Scheduler domains.

For example:

Values for config section - Startup
    DBName=PRIMARY
    DBType=ORACLE
    UserId=QEDMO
    UserPswd=QEDMO
    ConnectId=people
    ConnectPswd=peop1e
    ServerName=
    StandbyDBName=STANDBY
    StandbyDBType=ORACLE
    StandbyUserId=PTSTNDBY
    StandbyUserPswd=PTSTNDBY

The Startup section enables you to specify signon credentials for both the primary and standby databases. The following table shows which parameters are associated with which database.

The Standby… parameters are used to maintain the simultaneous connection to the standby database.

Note. Typically, the primary and standby database share a common connect ID.

Configuring Read-Only Components

If you have Oracle Active Data Guard configured and enabled for your PeopleSoft system, by default, the requests generated by the following components will be run against the standby database:

• PSQUERY Viewer

• Tree Viewer

• XMLP Viewer

• SES Feeds Generation

• FEEDS Reader

However, you can also set other components to run against the standby database to divert selected requests from your primary database. To set a component to run in “Read-Only” mode (run against the standby database) you need to modify the component properties.

To configure a component for read-only processing:

1. In Application Designer, open the component.

2. Select View, Definition Properties.

3. On the Component Properties dialog box, select the Use tab.

4. Select the Read Only check box.

5. Click OK.

Note. Selecting the Read Only property should only be done after detailed analysis. You need to make sure that the requests generated from the component perform operations like selecting and displaying lists, rather than inserting, updating, or deleting rows. If there are some DML operations that the component must make, the affected tables need to be identified, and a remote synonym needs to be created between the standby and primary databases.

Configuring Read-Only Processes

If you have Oracle Active Data Guard configured and enabled for your PeopleSoft system, Process Scheduler processes can be set to run against the standby database to divert selected processes from your primary database.

To configure a Process Scheduler processes for read-only processing:

1. Select PeopleTools, Process Scheduler, Processes.

2. Open the process definition..

3. On the Process Definition page, select the Read Only check box.

4. Click OK.

Note. Selecting the Read Only option should only be done after detailed analysis. You need to make sure that the processes perform operations like selecting data or generating reports, rather than inserting, updating, or deleting rows. If there are some DML operations that the process must make, the affected tables need to be identified, and a remote synonym needs to be created between the standby and primary databases.