Oracle® Database Net Services Administrator's Guide 10g Release 2 (10.2) Part Number B14212-02 |
|
|
View PDF |
This chapter describes how to configure advanced features of Oracle Net Services, including advanced connect data parameters, load balancing, failover, and connections to non-database services.
This chapter contains these topics:
This section contains the following advanced connect descriptor topics:
A database service may be accessed by more than one network route, or protocol address. In the following example, sales.us.acme.com
can connect to sales.us.acme.com
using listeners on either sales1-server
or sales2-server
.
sales.us.acme.com= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com)))
To add a network protocol address to an existing net service name or database service, use either Oracle Enterprise Manager or Oracle Net Manager.
Access the Directory Naming or Local Naming page in Oracle Enterprise Manager:
Access the Net Services Administration page in Oracle Enterprise Manager.
See Also:
"Oracle Enterprise Manager"Select Local Naming or Directory Naming from the Administer list, and then select the Oracle home for the directory server or the location of the local configuration files.
Click Go.
The Directory Naming or Local Naming pages appear.
Select the directory service or net service name.
For Directory Naming, perform a search of the net service name in the Simple Search section, select the net service or database service from the Results list, and then click Edit. For Local Naming, select a net service from the list, and then click Edit.
In the Addresses section, click Add.
The Add Address page appears.
From the Protocol list, select the protocol on which the listener is configured to listen. This protocol must also be installed on the client.
Enter the appropriate parameter information for the selected protocol in the fields provided.
See Also:
Oracle Database Net Services Reference for protocol parameter settingsOptionally, in the Advanced Parameters section, specify the I/O buffer space limit for send and receive operations of sessions in the Total Send Buffer Size and Total Receive Buffer Size fields.
See Also:
"Configuring I/O Buffer Space" for further informationClick OK.
The protocol address is added to the Addresses section.
Click OK to update the address information.
Start Oracle Net Manager.
See Also:
"Oracle Net Manager"In the navigator pane, expand Directory or Local > Service Naming.
Select either the net service name or a database service.
The right pane displays the current destination service and address list.
In the Address Configuration box, click plus (+) to add a new address.
A new Address tab appears:
Select the protocol and enter appropriate address information.
See Also:
Oracle Database Net Services Reference for details about protocol address parametersOptionally, in Address tab, click Advanced to specify the I/O buffer space limit for send and receive operations of sessions in the Total Send Buffer Size and Total Receive Buffer Size fields.
See Also:
"Configuring I/O Buffer Space" for further informationOrder the protocol addresses according to where they should be in the protocol address list with the left-arrow and right-arrow buttons. Unless multiple address options are configured, the first address in the list is contacted.
See Also:
"Configuring Address List Parameters" for address list optionsIf you are making these changes to the Local folder, then choose File > Save Network Configuration.
When a database service is accessible by multiple listener protocol addresses, specify the order in which the addresses are to be used. The addresses can be chosen randomly or tried sequentially.
When multiple protocol addresses have been configured for a net service name or database service, you can configure the parameters described in Table 13-1.
Table 13-1 Address List Parameters
Parameter | Description |
---|---|
When set to See Also: "Configuring Clients for Oracle Connection Manager" on page 13-6 |
|
At connect time, instructs Oracle Net to fail over to a different listener if the first listener fails when set to Connect-time failover is turned Important: When using a connect descriptor with a |
|
When set to Client load balancing is turned |
Note:
It is not possible to set client load balancing or connect-time failover with source routing. While connect-time failover and client load balancing select an address from a list, source routing connects to each address in the list sequentially.Source routing involves other configuration that goes beyond the scope of this section.
See Also:
"Configuring Clients for Oracle Connection Manager" for more information about configuring clients for source routingTo configure connect-time failover or client load balancing:
Perform the procedure in "Creating a List of Listener Protocol Addresses".
Use either Oracle Enterprise Manager or Oracle Net Manager to configure address list options.
For Oracle Enterprise Manager, select the appropriate option in the Connect-time Failover and Client Load Balancing section.
For Oracle Net Manager, click Advanced in the Address Configuration box. The Address List Options dialog box appears. Select the appropriate option.
Table 13-2 describes the address list options.
Table 13-2 Address List Options Dialog Box
The following example shows a tnsnames.ora
file configured for client load balancing:
sales.us.acme.com=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com)))
The following example shows a tnsnames.ora
file configured for connect-time failover:
sales.us.acme.com= (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=off) (FAILOVER=ON) (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)))
The CONNECT_DATA
section of a connect descriptor defines the destination database service. In the following example, SERVICE_NAME
defines a service called sales.us.acme.com
:
sales.us.acme.com= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com)))
Besides the service name, you can optionally configure the connect data information with the parameters described in Table 13-3.
Table 13-3 Advanced Connect Data Settings
Oracle Enterprise Manager/Oracle Net Manager Option | tnsnames.ora File Parameter | Description |
---|---|---|
Instance Name |
Used to identify the database instance to access. The instance name can be obtained from the Note: This parameter is not enabled if Use Oracle8i Release 8.0 Compatible Identification is checked. See Also: "About Connect Descriptors" |
|
Session Data Unit Size |
To optimize the transfer rate of data packets being sent across the network, you can specify the session data unit (SDU) size to change the performance characteristics having to do with the packets sent across the network. See Also: "Configuring Session Data Unit" |
|
Use for Heterogeneous Services |
If you want an Oracle database server to access a non-Oracle system through Heterogeneous Services, turn this option on. See Also: "Configuring Oracle Net Services for Oracle Heterogeneous Services" |
|
Oracle Rdb Settings |
|
|
Specify the file name of the Oracle Rdb database. See Also: "Configuring Oracle Net Services for an Oracle Rdb Database" |
||
Specify the type of service to use for the Oracle Rdb database. See Also: "Configuring Oracle Net Services for an Oracle Rdb Database" |
||
Use to identify an Oracle Rdb database. See Also: "Configuring Oracle Net Services for an Oracle Rdb Database" |
To configure advanced CONNECT_DATA
parameters for either a net service name or a database service:
To add a network protocol address to an existing net service name or database service, use either Oracle Enterprise Manager or Oracle Net Manager.
Access the Directory Naming or Local Naming page in Oracle Enterprise Manager:
Access the Net Services Administration page in Oracle Enterprise Manager.
See Also:
"Oracle Enterprise Manager"Select Local Naming or Directory Naming from the Administer list, and then select the Oracle home for the directory server or the location of the local configuration files.
Click Go.
The Directory Naming or Local Naming pages appear.
Select the directory service or net service name.
For Directory Naming, perform a search of the net service name in the Simple Search section, select the net service or database service from the Results list, and then click Edit. For Local Naming, select a net service from the list, and then click Edit.
Click the Advanced tab.
Enter fields or select options as appropriate, and then click OK.
See Also:
Table 13-3 for a description of the fields and optionsClick OK to update the connect data information.
Start Oracle Net Manager.
See Also:
"Oracle Net Manager"In the navigator pane, expand Directory or Local > Service Naming.
Select either the net service name or a database service.
The right pane displays the current destination service and address list.
In the Service Identification box, click Advanced.
The Advanced Service Options dialog box appears.
See Also:
Table 13-3 for a description of the fields and optionsEnter fields or select options as appropriate, and then click OK.
If you are making these changes to the Local folder, choose File > Save Network Configuration.
The runtime connection load balancing feature improves connection performance by balancing the number of active connections among multiple dispatchers. In an Oracle Real Application Clusters environment, connection pool load balancing also has the capability to balance the number of active connections among multiple instances.
Because the PMON process can register with remote listeners, a listener can always be aware of all instances and dispatchers, regardless of their location. Depending on the load information, a listener decides which instance and, if shared server is configured, which dispatcher to send the incoming client request to.
In a shared server configuration, a listener selects a dispatcher in the following order: 1) least loaded node, 2) least loaded instance, and 3) least loaded dispatcher for that instance. In a dedicated server configuration, a listener selects an instance in the following order: 1) least loaded node, and 2) least loaded instance.
If a database service has multiple instances on multiple nodes, the listener selects the least loaded instance on the least loaded node. If shared server is configured, then the least loaded dispatcher of the selected instance is chosen.
An Oracle9i Real Application Clusters environment requires that the dispatchers on each instance be cross registered with the other listeners on the other nodes. This is achieved by the use of the LISTENER
attribute of the DISPATCHERS
parameter.
See Also:
"Registering Information with a Remote Listener" for complete information about cross registration
Oracle Database Reference for complete information about the SERVICE_NAMES
and INSTANCE_NAME
parameters
Chapter 12, "Configuring Dispatchers" for complete information about the LISTENER
attribute
Note:
For optimum connection pool load balancing results, the instances that belong to the same database service should be on equivalent hardware and software configurations.Figure 13-1 shows an Oracle9i Real Application Clusters shared server database with two instances, sales1
and sales2
, of the same service, sales.us.acme.com
. The instances sales1
and sales2
reside on computers sales1-server
and sales2-server
, respectively. sales1
has one dispatcher and sales2
has two dispatchers. Listeners named listener
run on nodes 1 and 2, respectively. The listener
attribute in the DISPATCHERS
parameter has been configured to allow for service registration of information to both listeners.
Figure 13-1 Load Balancing Environment for a Shared Server Configuration
The listeners_sales
value in (LISTENER=listeners_sales)
can be then resolved through a local tnsnames.ora
file on the both servers as follows:
listeners_sales= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
Based on the environment, the following actions occur. The numbered actions correspond to the arrows shown in Figure 13-2:
PMON processes for instances sales1
and sales2
register with both listeners. The listeners are updated on the load of the instances and dispatchers dynamically. The following load information is registered:
The one minute load average for each instance is 600
for sales1
and 400
for sales2
.
The number of connections to each instance is 200
for sales1
and 300
for sales2
.
The number of dispatcher connections to each instance is 200
for dispatcher1
, 100
for dispatcher2
, and 200
for dispatcher3
.
The load average on sales2-server
(400) is less than the load average on sales1-server
(600). This can happen if more processing is required on sales1-server
. The number of connections to sales1
(200) is the same as that of its only dispatcher, dispatcher1
. The number of connections on sales2
(300) is the sum of the connections on its two dispatchers, dispatcher2
(100) and dispatcher 3
(200). Therefore, sales2
has more connections than sales1
. In this example, sales2-server
is the least loaded node, sales2
is the least loaded instance, and dispatcher2
is the least loaded dispatcher.
The client sends a connect request.
A connect descriptor is configured to try each protocol address randomly until one succeeds:
sales.us.acme.com= (DESCRIPTION= (LOAD_BALANCE=on) (FAILOVER=on) (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)))
The listener on sales1-server
was randomly chosen to receive the client connect request.
The listener on sales1-server
compares the load of the instances sales1
and sales2
. The comparison takes into account the load on nodes sales1-server
and sales2-server
, respectively. Since sales2-server
is less loaded than sales1-server
, the listener selects sales2-server
over sales1-server
.
The listener compares the load on dispatchers dispatcher2
and dispatcher3
. Because dispatcher2
is less loaded than dispatcher3
, the listener redirects the client connect request to dispatcher2
.
The client connects directly to dispatcher2
.
Figure 13-2 Load Balancing Example for a Shared Server Configuration
Figure 13-3 shows an Oracle9i Real Application Clusters dedicated server database with two instances, sales1
and sales2
, of the same service, sales.us.acme.com
. The instances sales1
and sales2
reside on computers sales1-server
and sales2-server
, respectively. Listeners named listener
run on nodes 1 and 2, respectively. The REMOTE_LISTENER
parameter has been configured to allow for service registration of information to both listeners.
Figure 13-3 Load Balancing Environment for a Dedicated Server Configuration
The listener_sales2
value in (REMOTE_LISTENER=listener_sales2)
can be then resolved through a local tnsnames.ora
file on the sales1-server
as follows:
listener_sales2= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
The listener_sales1
value in (REMOTE_LISTENER=listener_sales1)
can be then resolved through a local tnsnames.ora
file on the sales2-server
as follows:
listener_sales1= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)))
Based on the environment, the following actions occur. The numbered actions correspond to the arrows shown in Figure 13-4:
PMON processes for instances sales1
and sales2
register with both listeners. The listeners are updated on the load of the instances dynamically. The following load information is registered:
sales1-server | sales2-server | |
---|---|---|
1 Minute Node Load Average | 450 | 200 |
sales1 | sales2 | |
---|---|---|
Number of Connections to Instance | 200 | 150 |
In this example, sales2-server
is the least loaded node and sales2
is the least loaded instance.
The client sends a connect request.
A connect descriptor is configured to try each protocol address randomly until one succeeds:
sales.us.acme.com= (DESCRIPTION= (LOAD_BALANCE=on) (FAILOVER=on) (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)))
The listener on sales1-server
was randomly chosen to receive the client connect request.
The listener on sales1-server
compares the load of the instances sales1
and sales2
. The comparison takes into account the load on nodes sales1-server
and sales2-server
, respectively. Since sales2-server
is less loaded than sales1-server
, the listener selects sales2-server
over sales1-server
.
The listener on sales1-server
redirects the client connect request to the listener on sales2-server
.
The client connects to the listener on sale2-server
. The listener starts a dedicated server process, and the dedicated server process inherits the connection request from the listener.
Figure 13-4 Load Balancing Example for a Dedicated Server Configuration
Note:
Transparent Application Failover (TAF) is available with Oracle Database 10g Enterprise Edition.TAF instructs Oracle Net to fail over a failed connection to a different listener. This enables the user to continue to work using the new connection as if the original connection had never failed.
TAF involves manual configuration of a net service name that includes the FAILOVER_MODE
parameter included in the CONNECT_DATA
section of the connect descriptor.
This sections covers the following topics:
Transparent Application Failover (TAF) is a client-side feature that allows for clients to reconnect to surviving databases in the event of a failure of a database instance. Notifications are used by the server to trigger TAF callbacks on the client-side.
TAF is configured using either client-side specified TNS connect string or using server-side service attributes. However, if both methods are used to configure TAF, the server-side service attributes will supersede the client-side settings. The server-side service attributes are the preferred way to set up TAF.
TAF can operate in one of two modes, Session Failover and Select Failover. Session Failover will recreate lost connections and sessions. Select Failover will replay queries that were in progress.
When there is a failure, callback functions will be initiated on the client-side via OCI callbacks. This will work with standard OCI connections as well as Connection Pool and Session Pool connections. Please see the OCI manual for more details on callbacks, Connection Pools, and Session Pools.
TAF will work with RAC. For more details and recommended configurations, please see the RAC Administration Guide.
TAF will operate with Physical Data Guard to provide automatic failover.
TAF automatically restores some or all of the following elements associated with active database connections. Other elements, however, may need to be embedded in the application code to enable TAF to recover the connection.
TAF automatically reestablishes the connection using the same connect string or an alternate connect string that you specify when configuring failover.
TAF automatically logs a user in with the same user ID as was used prior to failure. If multiple users were using the connection, then TAF automatically logs them in as they attempt to process database commands. Unfortunately, TAF cannot automatically restore other session properties. These properties can, however, be restored by invoking a callback function.
If a command was completely executed upon connection failure, and it changed the state of the database, TAF does not resend the command. If TAF reconnects in response to a command that may have changed the database, TAF issues an error message to the application.
TAF allows applications that began fetching rows from a cursor before failover to continue fetching rows after failover. This is called "select" failover. It is accomplished by re-executing a SELECT
statement using the same snapshot, discarding those rows already fetched and retrieving those rows that were not fetched initially. TAF verifies that the discarded rows are those that were returned initially, or it returns an error message.
Any active transactions are rolled back at the time of failure because TAF cannot preserve active transactions after failover. The application instead receives an error message until a ROLLBACK
is submitted.
TAF works with the following database configurations to effectively mask a database failure:
Oracle Real Application Clusters
Replicated systems
Standby databases
Single instance Oracle database
See Also:
Oracle Real Application Clusters Installation and Configuration GuideThe FAILOVER_MODE
parameter must be included in the CONNECT_DATA
section of a connect descriptor. FAILOVER_MODE
can contain the subparameters described in Table 13-4.
Table 13-4 Subparameters of the FAILOVER_MODE Parameter
FAILOVER_MODE Subparameter | Description |
---|---|
Specify a different net service name for backup connections. A backup should be specified when using |
|
Specify the type of failover. Three types of Oracle Net failover functionality are available by default to Oracle Call Interface (OCI) applications:
|
|
Determines how fast failover occurs from the primary node to the backup node:
|
|
|
Specify the number of times to attempt to connect after a failover. If Note: If a callback function is registered, then this subparameter is ignored. |
|
Specify the amount of time in seconds to wait between connect attempts. If Note: If a callback function is registered, then this subparameter is ignored. |
Note:
Oracle Net Manager does not provide support for TAF parameters. These parameters must be manually added.Important:
Do not set theGLOBAL_DBNAME
parameter in the SID_LIST_
listener_name
section of the listener.ora
. A statically configured global database name disables TAF.Depending on the FAILOVER_MODE
parameters, you can implement TAF in a number of ways. Oracle recommends the following methods:
Implement TAF with connect-time failover and client load balancing for multiple addresses. In the following example, Oracle Net connects randomly to one of the protocol addresses on sales1-server
or sales2-server
. If the instance fails after the connection, the TAF application fails over to the other node's listener, reserving any SELECT
statements in progress.
sales.us.acme.com= (DESCRIPTION= (LOAD_BALANCE=on) (FAILOVER=on) (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) (FAILOVER_MODE= (TYPE=select) (METHOD=basic))))
TAF also provides the ability to automatically retry connecting if the first connection attempt fails with the RETRIES
and DELAY
parameters. In the following example, Oracle Net tries to reconnect to the listener on sales1-server
. If the failover connection fails, Oracle Net waits 15 seconds before trying to reconnect again. Oracle Net attempts to reconnect up to 20 times.
sales.us.acme.com= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) (FAILOVER_MODE= (TYPE=select) (METHOD=basic) (RETRIES=20) (DELAY=15))))
A backup connection can be pre-established. The initial and backup connections must be explicitly specified. In the following example, clients that use net service name sales1.us.acme.com
to connect to the listener on sales1-server
are also preconnected to sales2-server
. If sales1-server
fails after the connection, Oracle Net fails over to sales2-server
, preserving any SELECT
statements in progress. Likewise, Oracle Net preconnects to sales1-server
for those clients that use sales2.us.acme.com
to connect to the listener on sales2-server
.
sales1.us.acme.com= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) (INSTANCE_NAME=sales1) (FAILOVER_MODE= (BACKUP=sales2.us.acme.com) (TYPE=select) (METHOD=preconnect)))) sales2.us.acme.com= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) (INSTANCE_NAME=sales2) (FAILOVER_MODE= (BACKUP=sales1.us.acme.com) (TYPE=select) (METHOD=preconnect))))
You can query FAILOVER_TYPE
, FAILOVER_METHOD
, and FAILED_OVER
columns in the V$SESSION
view to verify that TAF is correctly configured.
Use the V$SESSION
view to obtain information about the connected clients and their TAF status. For example, query the FAILOVER_TYPE
, FAILOVER_METHOD
, and FAILED_OVER
columns to verify that you have correctly configured TAF as in the following SQL statement:
SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) FROM V$SESSION GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;
The output before failover resembles the following:
MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*) -------------------- ------------- ---------- --- ---------- sales1 NONE NONE NO 11 sales2 SELECT PRECONNECT NO 1
The output after failover is:
MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*) -------------------- ------------- ---------- --- ---------- sales2 NONE NONE NO 10 sales2 SELECT PRECONNECT YES 1
Note:
You can monitor each step of TAF using an appropriately configuredOCI TAF CALLBACK
function.The INSTANCE_ROLE
parameter is an optional parameter for the CONNECT_DATA
section of a connect descriptor. It enables you to specify a connection to the primary or secondary instance of Oracle9i Real Application Clusters configurations.
This parameter is useful when:
You want to explicitly connect to a primary or secondary instance. The default is the primary instance.
You want to use TAF to preconnect to a secondary instance.
INSTANCE_ROLE
supports the following values:
primary
— Specifies a connection to the primary instance
secondary
— Specifies a connection to the secondary instance
any
— Specifies a connection to whichever instance has the lowest load, regardless of primary or secondary instance role
Example: Connection to Instance Role Type
In the following example, net service name sales_primary
enables connections to the primary instance, and net service name sales_secondary
enables connections to the secondary instance.
sales_primary= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) (INSTANCE_ROLE=primary))) sales_secondary= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) (INSTANCE_ROLE=secondary)))
Example: Connection To a Specific Instance
There are times when Oracle Enterprise Manager and other system management products need to connect to a specific instance regardless of its role to perform administrative tasks. For these types of connections, configure (INSTANCE_NAME=
instance_name
)
and (INSTANCE_ROLE=any)
to connect to the instance regardless of its role.
In the following example, net service name sales1
enables connections to the instance on sales1-server
and sales2
enables connections to the instance on sales2-server
. (SERVER=dedicated)
is specified to force a dedicated server connection.
sales1= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) (INSTANCE_ROLE=any) (INSTANCE_NAME=sales2) (SERVER=dedicated))) sales2= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) (INSTANCE_ROLE=any) (INSTANCE_NAME=sales2) (SERVER=dedicated)))
Example: TAF Pre-Establishing a Connection
If Transparent Application Failover (TAF) is configured, a backup connection can be pre-established to the secondary instance. The initial and backup connections must be explicitly specified. In the following example, Oracle Net connects to the listener on sales1-server
and preconnects to sales2-server
, the secondary instance. If sales1-server
fails after the connection, the TAF application fails over to sales2-server
, the secondary instance, preserving any SELECT
statements in progress.
sales1.acme.com= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) (INSTANCE_ROLE=primary) (FAILOVER_MODE= (BACKUP=sales2.acme.com) (TYPE=select) (METHOD=preconnect)))) sales2.acme.com= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) (INSTANCE_ROLE=secondary)))
The following topics describe how to configure connections to non-Oracle database services:
An external procedure is a procedure called from another program, but written in a different language. An example would be a PL/SQL program calling one or more C routines that are required to perform special-purpose processing.
You can configure the listener to listen for external procedure calls. When an application calls an external procedure, the listener starts an external procedure agent named extproc
. Using the network connection established by the listener, the application passes the following information to the agent:
DLL or shared library name
External procedure name
Any parameters
The agent then loads the DLL or the shared library, and runs the external procedure and passes back to the application any values returned by the external procedure.
The agent must reside on the same computer as the application making the external procedure call.
This section contains the following topics:
Oracle Net Configuration Assistant configures a listener to accept connections for both the database and external procedures during a database server installation. In addition, Oracle Net Configuration Assistant configures a net service name for the external procedures in tnsnames.ora
file on the database server. The external procedure agent will only be able to load DLLS from $ORACLE_HOME/lib
on UNIX operating systems and ORACLE_HOME
\bin
on Windows.
Example 13-1 shows the default configuration in the listener.ora
file.
Example 13-1 listener.ora File with Default External Procedure Setup
LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=sales.us.acme.com) (ORACLE_HOME=/oracle) (SID_NAME=sales)) (SID_DESC= (SID_NAME=plsextproc) (ORACLE_HOME=/oracle) (PROGRAM=extproc)))
Example 13-2 shows the default configuration in the tnsnames.ora
file.
To achieve a higher level of security in a production environment, modify the default configuration by performing the following tasks:
Configure and run a separate listener dedicated to servicing external procedure requests.
The extproc
agent spawned by the listener inherits the operating system privileges of the listener. Therefore, configure this listener to run with operating system privileges lower than those of the listener for the database.
Restrict the DLLs that the extproc
agent can load by listing them explicitly in the listener.ora
file.
The details of these tasks follow.
To modify the default configuration for a higher level of security:
Remove the external procedure entries for the default listener configured by Oracle Net Configuration Assistant.
For most installation types, this listener is named LISTENER
.
Access the Oracle Net Administration page in Oracle Enterprise Manager.
See Also:
"Oracle Enterprise Manager"Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
Click Go.
The Listeners page appears.
Select the listener created by Oracle Net Configuration Assistant, and then click Edit.
The Edit Listeners page appears.
In the Addresses section, select the protocol address for external procedures, and then click Remove.
Click the Other Services tab.
Select the row representing the service information for external procedures, and then click Remove.
Create another listener to exclusively handle external procedures:
Navigate back to the Listeners page.
Click Create.
The Create Listener page appears.
In the Listener Name field, enter a unique listener name, such as LISTENEREXTPROC
, in the Listener Name field.
In the Addresses section, configure an IPC protocol address.
Click Add.
The Add Address page appears.
From the Protocol list, select IPC.
In the Key field, enter a key value of extproc
.
Note:
If the computer has more than one Oracle home or more than one listener, each listener must specify a uniqueKEY
. For example, you can use extproc1
for the first listener, extproc2
for the second listener, and so on.See Also:
"Configuring Listening Protocol Addresses" for more information about configuring listener protocol addressesClick OK.
Add service information about extproc
in the listener.ora
file, including the parameters described in Table 13-5.
Table 13-5 External Procedures Settings in listener.ora
To configure service information about extproc
:
Click the Other Services tab.
Click Add.
The Create Other Service page appears.
Enter extproc
in the Program Name field, and the Oracle home where the extproc
executable resides in the Oracle Home Directory field, and a system identifier, such as extproc
, in the SID field.
In the Environment Variables section, click Add Another Row.
Enter the EXTPROC_DLLS
environment variable in the Name field and the directory path and file name of the DLLs in the Value field.
Click OK.
The Create Listener page appears.
Click OK to add the listener.
The listener is added to the Listeners page.
The listener.ora
file updates with information for external procedures, as shown in the following output:
LISTENEREXTPROC= (DESCRIPTION= (ADDRESS= (PROTOCOL=ipc)(KEY=extproc))) SID_LIST_LISTENEREXTPROC= (SID_LIST= (SID_DESC= (PROGRAM=extproc) (ENVS="EXTPROC_DLLS=ONLY:/home/xyz/mylib.so:/home/abc/urlib.so, LD_LIBRARY_PATH=/private/xpm/lib:/private/mylibs, MYPATH=/usr/ucb:/usr/local/packages,APL_ENV_FILE=/apl/conf/env.txt") (SID_NAME=extproc) (ORACLE_HOME=/oracle)))
Start the listener for external procedures from a user account with lower privileges than the oracle
user.
Ensure that this user account does not have general access to oracle
-owned files. Specifically, this user should not have permission to read or write to database files or to the Oracle server address space. In addition, this user should have read access to the listener.ora
file, but must not have write access to it.
Running the listener with lower privileges also prevents you from using Listener Control utility SET
commands to alter the configuration of this listener in the listener.ora
file. For this reason, Oracle Corporation recommends that you complete listener.ora
file configuration prior to running the listener.
See Also:
"Task 1: Start the Listener" for instructions on using the Listener Control utility START
command to start the listener
Oracle Database Application Developer's Guide - Fundamentals for instruction on enabling external procedure calls
Heterogeneous Services are an integrated component within the Oracle database server, and provides the generic technology for accessing non-Oracle systems from the Oracle database server. Heterogeneous Services enable you to:
Use Oracle SQL to transparently access data stored in non-Oracle systems as if the data resides within an Oracle database server
Use Oracle procedure calls to transparently access non-Oracle systems, services, or application programming interfaces (APIs), from your Oracle distributed environment
While Heterogeneous Services provides the generic technology in the Oracle database server, a Heterogeneous Service agent is required to access a particular non-Oracle system.
To initiate a connection to the non-Oracle system, the Oracle database server starts an agent process through the listener on the gateway. For the Oracle database server to be able to connect to the agent, perform the following steps:
Configure the listener on the gateway to listen for incoming requests from the Oracle database server and spawn Heterogeneous Services agents by configuring the parameters described in Table 13-6 in the listener.ora
file.
Table 13-6 Heterogeneous Services Settings in listener.ora
Oracle Enterprise Manager Field | listener.ora Parameter | Description |
---|---|---|
Specify the name of the agent executable. |
||
Specify the Oracle home location of the agent executable. |
||
Specify the Oracle System Identifier (SID). |
To configure the SID
, ORACLE_HOME
, and PROGRAM
parameters:
Access the Oracle Net Administration page in Oracle Enterprise Manager.
See Also:
"Oracle Enterprise Manager"Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
Click Go.
The Listeners page appears.
Select the listener created by Oracle Net Configuration Assistant, and then click Edit.
The Edit Listeners page appears.
In the Addresses section, select the protocol address for external procedures, and then click Remove.
Click the Other Services tab.
Click Add.
The Create Other Service page appears.
Enter the program name in the Program Name field that will be executed to create a gateway, the Oracle home where the agent executable resides in the Oracle Home Directory field, and the SID or service name of the non-Oracle system in the SID field.
Click OK.
The Edit Listener page appears.
Click OK to modify the listener.
The Listeners page appears.
The listener.ora
file updates information about the Heterogeneous Services, as shown in the following:
SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=sybasegw) (ORACLE_HOME=/oracle10g) (PROGRAM=tg4sybs)))
On the computer where the Oracle database resides, set up a net service name to connect to the listener on the gateway. The connect descriptor must also include the HS=ok
clause to make sure the connection uses Heterogeneous Services:
Create a net service name that can be used for connections from the Oracle database server to a non-Oracle system.
See Also:
"Task 1: Configure Net Service Names" for local naming instructions and "Task 2: Create or Modify Net Entries" for directory naming instructionsUse either Oracle Enterprise Manager or Oracle Net Manager to configure HS=ok
.
For Oracle Enterprise Manager, click the Advanced tab in the Create Net Service Name page, and then click the Use for Heterogeneous Services.
For Oracle Net Manager, click Advanced in the Service Identification box. The Advanced Service Options dialog box appears. Click Use for Heterogeneous Services.
Click OK to confirm the change.
The tnsnames.ora
file updates with the new net service name configured for Heterogeneous Services, as shown in the following:
sybase_gtw=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=gate-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sybasegw)
)
(HS=ok)))
)
Oracle Rdb is a database for Digital's 64-bit operating systems. Because Oracle Rdb has its own listener, the client interacts with Rdb in the same manner as it does with an Oracle database.
To initiate a connection to an Oracle Rdb, set up a net service name to connect to the Oracle Rdb database using the parameters described in Table 13-7.
Table 13-7 Oracle RDB Database Settings in a Connect Descriptor
Oracle Enterprise Manager Field | tnsnames.ora Parameter | Description |
---|---|---|
Specify the file name of an Oracle Rdb database. |
||
Specify the type of service to use for an Oracle Rdb database. It is used by Rdb interface tools. This feature should only be used if the application supports both Oracle Rdb and Oracle database services, and you want the application to load balance between the two. |
||
(Optional) Specify the Oracle Rdb database. |
See Also:
Oracle Rdb documentationTo configure a client for an Oracle Rdb database, use Oracle Net Manager:
Create a net service name that can be used for connections from the Oracle server to a non-Oracle system.
See Also:
"Task 1: Configure Net Service Names" for local naming instructions and "Task 2: Create or Modify Net Entries" for directory naming instructionsUse either Oracle Enterprise Manager or Oracle Net Manager to the Oracle Rdb parameters.
For Oracle Enterprise Manager, click the Advanced tab in the Create Net Service Name page.
For Oracle Net Manager, click Advanced in the Service Identification box. The Advanced Service Options dialog box appears.
Enter the file name of an Oracle Rdb database in the Rdb Database field.
Optionally, enter the global database name in the Global Database Name field, and, if needed, specify the type of service in the Type of Service field, and then click OK.
The tnsnames.ora
file updates with the new net service name configured for the Oracle Rdb database, as shown in the following:
alpha5= (DESCRIPTION= (ADDRESS=...) (CONNECT_DATA= (SERVICE_NAME=generic) (RDB_DATABASE=[.mf]mf_personnel.rdb) (GLOBAL_NAME=alpha5)))
In the following example, TYPE_OF_SERVICE
is used to load balance between an Oracle Rdb database service and an Oracle database service:
alpha5= (DESCRIPTION_LIST= (DESCRIPTION= (ADDRESS=...) (CONNECT_DATA= (SERVICE_NAME=generic) (RDB_DATABASE=[.mf]mf_personnel.rdb) (GLOBAL_NAME=alpha5))) (DESCRIPTION= (ADDRESS=...) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com)) (TYPE_OF_SERVICE=oracle9_database))
See Also:
Oracle Rdb documentation