Working With Oracle Connectivity
Oracle Net Services (Oracle Database 11g or higher) 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).
Oracle Net Services uses the configuration files SQLNET.ORA and TNSNAMES.ORA, which can be created using a system editor, or with the Oracle Net Configuration Assistant.
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:
|
Parameter |
Description |
|---|---|
|
UseLocalOracleDB |
Indicates if the PeopleSoft database that you are connecting to is in a Local Oracle SID. The default is 0, meaning that the database you are connecting to is remote. The resulting connect string is in the following format: userid/password@service_name. If you set this to 1, then the system used the following connect string when attempting to connect to the target database: userid/password. This implies a local connection. If you decide to use UseLocalOracleDB, then you must add the BEQUEATH_DETACH=YES parameter to the SQLNET.ORA file of the machine running the application server or Process Scheduler servers. This enables Oracle to clean up any orphaned database processes spawned on behalf of PeopleSoft transactions left over from aborted transactions. Note: A LOCAL connection (or BEQ) is not possible with Oracle 12c or higher if a multi-tenant DB configuration is used. If a customer is still using a Single instance DB (non-multi-tenant) then a local connection is still supported, by enabling this and the following parameter Oracle_SID. |
|
Oracle_SID |
Indicates for a Local Oracle connection only, the name of the Local ORACLE_SID to which you want the PeopleSoft processes to connect. Many sites set up more than one ORACLE_SID on their servers. This parameter gives you the ability to choose which ORACLE_SID you wish to connect to when connecting in Local mode. |
|
EnableDBMonitoring |
This parameter enables or disables DB monitoring of three-tier connections. This feature is covered later in this documentation. |
The following tables describe the relationship between the UseLocalOracleDB parameter and the ORACLE_SID environment variable.
|
UseLocalOracleDB Flag |
The target database is local |
The target database is remote |
|---|---|---|
|
0 is the default setting Internally the system will generate the following connect string when attaching to the target database: UID/PW@TNS_ALIAS |
Access will be made via TNSNAMES |
Access will be made via TNSNAMES |
|
1 is the setting you use if you intend to use a Local Oracle DB. Internally the system will generate the following connect string when attaching to the target database: UID/PW (Note the omission of the TNS_ALIAS.) |
Access will default to the Local DB as designated by the ORACLE_SID environment variable If the ORACLE environment variable TWO_TASK is set to a valid TNS_ALIAS, then this would also work. The existence of the TWO_TASK environment variable is in effect overriding the generated connect string. |
To choose this option does not make sense if it is your intention to use a Local Oracle DB. This combination will work if the ORACLE environment variable TWO_TASK is set to a valid TNS_ALIAS. You are in effect overriding the generated connect string. |
|
ORACLE_SID Parameter |
UseLocalOracleDB Flag |
UseLocalOracleDB Flag |
|---|---|---|
|
This parameter is delivered in the application server and Process Scheduler configuration file commented out. This indicates that the default setting is however the current ORACLE_SID environment variable is set. |
0 The target database is remote |
1 The target database is local The ORACLE_SID parameter is not enabled (commented out) therefore ORACLE_SID for this process will default to the current ORACLE_SID environment variable. |
|
ORACLE_SID=xxxxxxx where xxxxxxxx equals a valid ORACLE_SID for the server that this process is running on. |
If UseLocalOracleDB Flag is set to zero, then enabling ORACLE_SID is invalid since you are indicating a remote connection, the value associated with the ORACLE_SID parameter will be ignored. |
If UseLocalOracleDB Flag is set to one, and ORACLE_SID is enabled, the value associated with the ORACLE_SID parameter will be exported as an operating system environment variable thus overriding the current ORACLE_SID environment variable. |
The minimum number of OPEN_CURSORS required for PeopleSoft applications on an Oracle database is 1000.