Oracle7 Server Distributed Systems Manual, Vol. 1 Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence

Network Administration

This chapter introduces networking concepts and terminology that anyone planning to implement a distributed system must be familiar with. It also discusses the tasks that network administrators must perform to set up client-server systems and integrate Oracle's network products into a distributed system.

For related information of interest to DBAs, see Chapter 4, "Database Administration". Chapter 3, "Network Administration Tools", provides an introduction to Oracle's network administration tools.

It is assumed that you have read and are familiar with the concepts and terms in Chapter 1, "Understanding Distributed Systems", and in Understanding SQL*Net.

Note: This chapter is not intended to be a step-by-step guide to implementing networked distributed systems. References in most sections are provided to the relevant Oracle Network Products and Oracle7 Server documentation which does contain step-by-step instructions and detailed reference material.

Oracle's Network Products

Oracle's Network Products provide all the tools necessary and an ideal architecture with which to implement client-server, server-to-server, and distributed systems. This section describes the Oracle's Network Products and how they work together to provide the basis for distributed systems.

Oracle's Network Products Architecture

Oracle's Network Products architecture enables integration with most network services, including the transport, naming, and security services provided at the network level. User applications are insulated from these encapsulated services so programmers need not write special code to take advantage of them.

This architecture insures that user code developed in one network environment can be redeployed in a different network environment, without the need for changes. This flexibility provides you with the freedom to adopt any type of new computing or networking platform (or integrate existing systems) without regard to connectivity issues at the user or application level.


SQL*Net uses the communication protocols or application program interfaces (APIs) supported by a wide range of networks to provide for a distributed Oracle7 system. A communications protocol is a set of standards, implemented in software, that govern the transmission of data across a network. An API is a set of program functions or calls that allow an application to make use of, or communicate with, an underlying program or system. In the case of networks, the API provides the means to establish remote process-to-process communication over a communication protocol

SQL*Net's networking products transparently integrate disparate clients, servers, and gateways into a unified information resource, using any combination of industry-standard or proprietary network protocols.

Additional Information: See Understanding SQL*Net for more detailed conceptual information about SQL*Net.

MultiProtocol Interchange

A MultiProtocol Interchange is a node that acts as a translator of communication protocols using protocol adaptors. SQL*Net version 2 works with the MultiProtocol Interchange to allow clients and servers to communicate transparently across networks running dissimilar protocols. A single node anywhere on the network that is loaded with two or more protocol stacks and a MultiProtocol Interchange enables all nodes on the attached networks to transparently connect to services on the other side of the Interchange. The MultiProtocol Interchange is discussed in more detail [*].

SQL*Net's layered architecture, shown in Figure 2 - 1, allows standard applications to run transparently over any type of network by simply using the appropriate Oracle Protocol Adapter.

Figure 2 - 1. Oracle's Network Products Architecture

Other Services

Oracle's Network Products include several services that are critical for managing large-scale distributed environments, such as an enterprise-wide distributed naming service--Oracle Names.

Also provided are Native Naming Adapters for use with existing name services such as NIS (Network Information Services), DCE's CDS (Distributed Computing Environment's Cell Directory Service), Novell's NDS (NetWare Directory Services), and Banyan's StreetTalk.

These naming adapters enable customers to integrate Oracle into their existing naming environment, while preserving their existing network infrastructure.

A centralized configuration management facility--Oracle Network Manager--provides both a topological and hierarchical view of the network, enabling administrators to easily configure services, such as databases and name servers in the network.

Where network security is required, the optional Secure Network Services product adds full data-stream encryption and integrity checking to SQL*Net. Included with Secure Network Services version 2.0 are authentication adapters such as Kerberos V5, which allow users who have the appropriate credentials to automatically and securely access any Oracle application or server without specifying a user name or password.

Oracle Network Products and Distributed Systems

In today's highly distributed information systems, networking is one of the most important architectural components. This section describes some of the ways that Oracle network products can be implemented to create an efficient distributed system.

Transparency in a Distributed System

The functionality of a distributed system must be provided in such a manner that the underlying complexities of the system are transparent to both users and administrators. A network implementation in a distributed system must be able to deliver:

Network Transparency

SQL*Net's layered architecture allows standard applications to run transparently over many types of protocols by simply using the appropriate Oracle Protocol Adapter. Thus, Oracle applications developed with a local database can be distributed across a network to access the same, or a similarly formatted, Oracle database with no changes to the application.

SQL*Net is responsible for forwarding application requests for data from an Oracle client or server to a server and returning the results to the initiator of the query. From the application developer's or application user's perspective, all data interaction using SQL*Net is invisible to the application and user.

Additionally, it is possible to change the network structure beneath the application without changing the application. This capability is known as network transparency.

Figure 2 - 2 illustrates a possible distributed systems scenario.

Figure 2 - 2. Possible Distributed Systems Scenario

Location Transparency: Database Links, Synonyms, and Service Names

Oracle7 server provides the means to make data objects such as tables in remote databases look to an application developer or user of that data object like they are in the local database. This is called location transparency. The database link and synonym allow the database in which they are created to identify a remote data object and make the location transparent. Establishing and maintaining location transparency is a joint function of the network administrator and the DBA. The network administrator is typically responsible for database links which are discussed later in this chapter, while views, synonyms and other forms of transparency are the responsibility of the DBA. They are discussed[*], "Database Administration".

Location transparency removes all need for references to the location of the data from applications when the synonym is used. Should the location of the remote table be moved to another machine or database, only the synonym and database link need be updated to reference the new location. No changes to applications are required.

If the database link connection is specified as a service name (or symbolic name) in the network configuration file (TNSNAMES.ORA), the database links accessing the data do not have to be changed if the remote database is moved. The only update required is to the TNSNAMES.ORA file. Similarly, if Oracle Names is used, only the central network definition needs to be changed.

TNSNAMES.ORA, and other configuration files are created using Oracle Network Manager. The configuration files are described in Appendix A of Understanding SQL*Net. For detailed instructions on creating the network definition and the configuration files for SQL*Net and other Oracle networking products, see the Oracle Network Manager Administrator's Guide.

Application Transparency

Another benefit of SQL*Net's encapsulated network architecture is that client-server applications can be built on one class of system and deployed on another. An application system back-end developed on a PC server can, for example, be redeployed on a large minicomputer server without the end-users needing to know that the location of the application has changed.

This flexibility means that server systems can be selected for current requirements, rather than for some large future need. Likewise, a system developed on a mainframe can be moved down to smaller, more cost-effective servers without reworking any of the code. This flexibility also allows exactly the same application code that is running on a large, central processor complex to be run on small, workgroup servers on remote PC LANs.

SQL*Net and Network Environment Independence

Just as SQL*Net connects clients and servers that operate on different nodes on a network, it also connects database servers across networks to facilitate distributed transactions. For example, when an application requests data from a remote database, the local database server communicates with the remote database through the network, using network communications software and Oracle's SQL*Net.

SQL*Net's advantage is that it runs on most networks. The particular type of network protocol, brand, or topology does not matter. In fact, it is feasible for a distributed system implemented using SQL*Net to work over different types of communication networks simultaneously.

Media/Topology Independence

SQL*Net supports most third-party network software packages, which in turn, support a wide variety of network hardware devices. On some platforms, a single Oracle Protocol Adapter can operate on hundreds of different network interface cards. This compatibility allows you to deploy applications in virtually any network environment, including Ethernet, Token-Ring, FDDI (Fiber Distributed Data Interface), ATM (Asynchronous Transfer Mode), wireless, and others.

When a request for a connection is made successfully, SQL*Net passes control of the connection to the underlying protocol. At that point, all media and/or topologies supported by the underlying network protocol are indirectly inherited by SQL*Net. SQL*Net allows the network protocol to use any means of data transmission, such as Ethernet, Token Ring, FDDI, or SDLC, to accomplish the low-level data link transmission between the two computers.

In addition, because SQL*Net connects to the network infrastructure through standard, high-level protocols, it also works with network components at lower levels such as bridges, routers, gateways, and packet switches.

Protocol Independence

SQL*Net provides protocol independence to its applications. Any application built on any computer running any protocol can be distributed without change to other computers running other protocols. An application using SQL*Net can run over any network protocol. SQL*Net's architecture provides the industry's broadest support for network transport protocols, including TCP/IP (Transmission Control Protocol/Internet Protocol), Novell SPX/IPX (Sequenced Packet Exchange/Internet Packet Exchange), IBM LU6.2, DECnet, OSI, and others.

In addition to supporting different protocols, SQL*Net also supports many vendor's protocol stacks, eliminating the need to purchase and install additional protocol support hardware or software. Without changing your existing infrastructure, you can transparently connect any combination of PC, UNIX, legacy, and other systems, using the network software you already have.

Any connection that works reliably at the protocol level will work with, and be transparent to SQL*Net, regardless of the number of physical connections and transformations the packets go through between the two machines.

When connectivity is required between different high-level protocols, such as from SPX/IPX to TCP/IP, the Oracle MultiProtocol Interchange can provide automatic protocol conversion, a task that cannot be performed at lower levels in the network stack. This means that networks running different protocols can communicate with each other.

The MultiProtocol Interchange

Oracle's client-server and server-to-server models provide connectivity across multiple network protocols.

Data Access Across Transfer Protocols

Oracle's MultiProtocol Interchange can be used with SQL*Net to enable transparent data access across protocols, allowing a client using one protocol to communicate with a server using a different protocol. This way, clients and servers running different network protocols can communicate using only their native protocols. This eliminates the need to purchase and maintain multiple protocol stacks.

All of the advanced Oracle7 capabilities, such as basic replication, the advanced replication option, stored procedure calls, and automatic transaction recovery mechanisms, can operate transparently across any number of protocol boundaries.

Multiple Interchanges can be combined to provide multistage protocol conversion, all transparent to programmers and users alike. Applications simply ask for services by name, and SQL*Net automatically calculates the most efficient route to take through the network and establishes the connection.

When the network topology changes, such as when a new server is added to the network, users and applications are completely unaware of the change, because SQL*Net transparently calculates a new route automatically at request time. Where there are multiple possible routes, SQL*Net will use the most efficient route based on high-level weighting provided by the network administrator.

This product is described in detail in the Oracle MultiProtocol Interchange Administrator's Guide. For information on configuring the MultiProtocol Interchange, see the Oracle Network Manager Administrator's Guide.

MultiProtocol Interchanges in the Client-Server Configuration

With SQL*Net version 2, the client and server can belong to different communities connected by one or more MultiProtocol Interchange(s). A community is a group of computers that can communicate using the same transport level protocol, such as TCP/IP. That is, computers that use the same protocol are members of the same community.

For example, a MultiProtocol Interchange can be installed on a node that is loaded with two protocol stacks, TCP/IP and DECnet. Then, it can enable a network running TCP/IP to communicate with a network running DECnet. The result is a higher-level application network in which any two applications can communicate. Using an Interchange as an intermediary, applications on the client and server machines can communicate even though they are using different protocols. Any data exchanged in the client-server applications is forwarded through the Interchanges along the path.

Figure 2 - 3 shows a connection between a client (protocol A) and a server (protocol B) in adjacent communities. A MultiProtocol Interchange joins the two networks. SQL*Net and an Oracle Protocol Adapter specific to Protocol A are installed on the client while SQL*Net and an Oracle Protocol Adapter specific to Protocol B are installed on the database server. The Interchange has adapters for both Protocol A and Protocol B. In a sense, it is bilingual (or poly-lingual). When communication is requested between the two communities, the MultiProtocol Interchange translates between the two protocols.

Figure 2 - 3. Heterogeneous Networking with a Client-Server Connection

MultiProtocol Interchanges in the Server-to-Server Configuration

In a server-to-server configuration, this same heterogeneous network capability is extended to include database-to-database communications. Two types of server-to-server connections are possible using SQL*Net:

Figure 2 - 4. Heterogeneous Networking in a Distributed Database Transaction

The example in Figure 2 - 4 shows both types of connections.

In this example, Server 1 is a member of two communities, Community A and Community B. A client application in Community A accesses the database server (Server 1) within the same community. Server 1 determines that the transaction must be distributed further to retrieve data from tables in Server 2 and Server 3. Server 1 initiates a connection to Server 2 in Community B to which Server 1 also belongs. Server 1 also initiates a connection to Server 3 through the MultiProtocol Interchange installed between Community B and Community C.

Server 1 does not have to use an MultiProtocol Interchange to initiate an additional request for data from Community B since it belongs to both Community A and Community B, but it must use an Interchange to access a server in Community C.

Note that using the Interchange imposes no new restrictions on a SQL*Net connection. If used in a client-server connection, clients have a standard peer-to-peer connection between the client and server although they are in different communities.

Similarly, if a server initiates a connection with another server through an Interchange using a database link, the standard database link restrictions apply.

SQL*Net Version 2 Architecture

This section provides a more detailed discussion of SQL*Net and the role it plays in distributed systems.

SQL*Net version 2 uses the Transparent Network Substrate (TNS) and industry-standard network protocols to connect a client to a server and establish an Oracle session.

The next few sections describes the following architectural concepts:

Transparent Network Substrate (TNS)

Forming the basis for Oracle networking products, the Transparent Network Substrate (TNS) enables Oracle to provide a network of applications above all existing networks of computers. With TNS, peer-to-peer application connectivity is possible where no direct machine-level connectivity exists. Peer-to-peer is an architecture in which two or more nodes can communicate with each other directly, without the need for any intermediary devices. In a peer-to-peer system, a node can be both a client and a server.

TNS provides two key features to a TNS-based network product and, in turn, any application built using TNS:

TNS is the foundation component of all current and planned network products from Oracle. Today, TNS networks connect Oracle clients and servers through SQL*Net version 2. In the future, Oracle Corporation will provide additional TNS-based application connectivity tools.

SQL*Net's Communication Role

In a distributed transaction, SQL*Net is responsible for sending information across various networks on behalf of a client application or database server. In such a configuration, there are commonly two types of computers acting as the client and server. Two-Task Common (see page 2 - 14) ensures that all differences between clients and servers, such as internal datatype representations or NLS character sets, are resolved, allowing the client and server to communicate transparently. SQL*Net relays all communication tasks to TNS through its common entry points. SQL*Net is unaffected by the specific communication mechanism used underneath TNS (for example, TCP/IP, DECnet, shared memory, and so on).

Communication between client and server proceeds in a stack-like fashion with corresponding levels communicating in a peer relationship. The logical exchange unit at each layer of the stack conveys the level of generalization employed at that level. The Oracle client and server exchange SQL statements and data rows. At the UPI/OPI (User/Oracle Program Interface) layers, these exchanges translate into series of calls to SQL routines such as logon, parse, execute, and fetch. The SQL*Net layer handles these calls as a series of Oracle send/receive messages, and TNS in turn processes the packets over the network. The network protocol, not provided by Oracle (typically provided with each particular platform by its vendor), guarantees a reliable means of communication between the two tasks.

Figure 2 - 5. Oracle Client-Server Components

SQL*Net in Distributed Processing

SQL*Net is responsible for enabling communications between the cooperating partners in a distributed transaction, either client-server or server-to-server. Specifically, SQL*Net enables clients and servers to connect to each other, send data such as SQL statements and data responses, initiate interrupts from the client or server, and disconnect when the session is complete. During the life of the connection, SQL*Net resolves all differences between the internal data representations and/or character sets of the computers being used.

When a client or server makes a connection request, SQL*Net receives the request. If more than one machine is involved, SQL*Net passes the request to the TNS, to be transmitted over the appropriate communications protocol to the appropriate server. On the server, SQL*Net receives the request from TNS and passes it to the database as a network message with one or more parameters (that is, a SQL statement).

Except for the initial connection, the local and remote applications generate the same requests whether they run on the same computer or are distributed across multiple computers. The initial connection differs only in that the application or user must specify the name of the remote database.

Components Involved in Distributed Processing

Several software components complete a distributed transaction, whether it is a client-server or server-server transaction. Figure 2 - 5 shows the components of a client-server session. These components are described in the following sections.

Client Side Interaction

The following paragraphs discuss the components of the client-server transaction process, beginning with the client application and concluding with the Oracle Server.

Client Application

The client application provides all user-oriented activities, such as character or graphical user display, screen control, data presentation, application flow, and other application specifics. The application identifies any SQL database operations to send to the server database and passes them through the User Program Interface (UPI).

User Program Interface (UPI)

The UPI code that contains all information required to initiate a SQL dialogue between the client and the server. It defines calls to the server to:

The client application uses some combination of these calls to request activity within the server. Often, all UPI calls can be combined into a single message to the server, or they may be processed one at a time through multiple messages to the server, depending on the nature of the client application. Oracle products attempt to minimize the number of messages sent to the server by combining many UPI calls into a single message to the server. When a call is performed, control is passed to SQL*Net to establish the connection or transmit the request to the server.

Two-Task Common

Two-Task Common provides character set and data type conversion between different character sets or formats between client and server. This layer is optimized to perform conversion only when required on a per connection basis.

At the time of initial connection, SQL*Net version 2 is responsible for evaluating differences in internal data and character set representations and determining whether conversions are required for the two computers to communicate.


The role of SQL*Net is to establish and maintain a connection between the client application and the server and exchange messages between them. The network listener receives connection requests for a particular database and passes control to the server.

Transparent Network Substrate (TNS)

TNS receives requests from network applications, in this case SQL*Net, and settles all generic machine-level connectivity issues, such as:

The generic set of TNS functions (open, close, send, receive) passes control to an Oracle Protocol Adapter to make a protocol-specific call.

Additionally, TNS optionally provides encryption and sequenced cryptographic message digests to protect data in transit. See Secure Network Services Administrator's Guide for more information.

Oracle Protocol Adapter

The Oracle Protocol Adapter is responsible for mapping TNS functionality to any industry-standard protocol used in the client-server connection. The adapters are responsible for mapping the equivalent functions between TNS and a specific protocol.

Network-Specific Protocols

All Oracle software in the client-server connection process requires an existing network protocol stack to make the machine-level connection between the two machines. The network protocol is responsible only for getting the data from the client machine to the server machine, at which point the data is passed to the server-side Oracle Protocol Adapter.

Server-Side Interaction

Going up the process stack on the server side is the reverse of what occurred on the way down the client side. See the right side of Figure 2 - 5.

The one operation unique to the server side is the act of receiving the initial connection. The server has a process (the network listener) that regularly checks for incoming connections and evaluates their destination.

The network listener is a process on a server that listens for connection requests for one or more databases on one or more protocols. It is discussed in "SQL*Net and the Network Listener" [*]. Based on the Oracle Server ID (SID) specified, the connection is passed to the Oracle Server.

The components above SQL*Net, the OPI and the Oracle Server, are different from those on the client side.

Oracle Program Interface (OPI)

The OPI has a complementary function to that of the UPI. It is responsible for responding to each of the possible messages sent by the UPI. For example, a UPI request to fetch 25 rows would have an OPI response to return the 25 rows once they have been fetched.

Oracle Server

The Oracle Server side of the connection is responsible for receiving dialog requests from the client UPI code and resolving SQL statements on behalf of the client application. Once received, a request is processed and the resulting data is passed to the OPI for responses to be formatted and returned to the client application.

Server-to-Server Interaction

When two servers are communicating to complete a distributed transaction, the process and dialogues are the same as in the client-server scenario, except that there is no client application. See Chapter 5, "Distributed Updates" for more information. The server has its own version of UPI, called NPI. The NPI interface can perform all of the functions that the UPI does for clients, allowing a coordinating server to construct SQL requests for additional servers. Figure 2 - 6 shows a server-to-server connection and all associated layers.

Figure 2 - 6. Oracle Server-Server Components

SQL*Net Operations

SQL*Net provides functions, described in the following sections, that belong to the following classifications:

All the functions work with tools and databases that use SQL*Net for distributed processing, although none of them are visible to the user.

Note: The information contained in the following summary is for the benefit of the network administrator, who needs to understand what role SQL*Net version 2 plays within the network.

Connect Operations

SQL*Net supports two basic connect operations:

Connecting to Servers

The connect operation is initiated during any standard database login between the client application and the server, with information such as the client machine name and user name being passed to the remote machine. This information is required to support externally- identified logins.

A client application initiates a request for a connection to a remote database (or other network service) by providing a short name for its desired destination. That short name, called a service name, is mapped to a network address contained in a connect descriptor stored in the network configuration file TNSNAMES.ORA or in a database for use by Oracle Names. For more information on service names and connect descriptors, see "Global Naming Issues" [*]. See also Understanding SQL*Net.

Note: If the network includes Oracle Names, the service names and associated connect descriptors are stored in a database that is accessed by the Names servers, and the TNSNAMES.ORA file is not needed. Similarly, if a native names adapter (such as NIS) is being used, this information will be stored in the corresponding native name service.

Disconnecting from Servers

Requests to disconnect from the server can be initiated in the following ways:

User-Initiated Disconnect A user can request a disconnection from the server when a client-server transaction completes. A server can also disconnect from a second server when all server-server data transfers have been completed, and no need for the link remains (the simplest case).

Additional Connection Request If a client application is connected to a server and requires access to another user account on the same server or on another server, most Oracle tools will first disconnect the application from the server to which it is currently connected. Once the disconnection is completed, a connection request to the new user account on the appropriate server is initiated.

Abnormal Connection Termination Occasionally, one of the components below SQL*Net will be disconnected or will abort communications and SQL*Net will not be immediately informed.

During the next SQL*Net data operation, the TNS module will recognize the failure and give SQL*Net a notice to clean up client and server operations, effectively disconnecting the current operation.

Timer Initiated Disconnect or Dead Connection Detection (SQL*Net release 2.1 and later only). Dead connection detection (Keep Alive or Dead Man's Handle) is a feature that allows SQL*Net to identify connections that have been left hanging by the abnormal termination of a client. On a connection with Dead Connection Detection enabled, a small probe packet is sent from server to client at a user-defined interval (usually several minutes). If the connection is invalid (usually due to the client process or machine being unreachable), the connection will be closed when an error is generated by the send operation, and the server process will exit.

This feature minimizes the waste of resources by connections that are no longer valid. It also automatically forces a database rollback of uncommitted transactions and locks held by the user of the broken connection.

Data Operations

SQL*Net supports four sets of client-server data operations:

The concept of sending and receiving data between client and server on behalf of the UPI and OPI is relatively straightforward. A SQL dialogue request is forwarded from the UPI using a send request in SQL*Net. On the server side, SQL*Net processes a receive request and passes the data to the database. The opposite occurs in the return trip from the server.

All send and receive requests are synchronous. That is, when the client initiates a request, it waits for the server to respond with the answer. It can then issue an additional request.

SQL*Net version 2 adds the capability to send and receive data requests asynchronously. This capability was added to support the Oracle7 multi-threaded server, which requires asynchronous calls to service incoming requests from multiple clients.

Exception Operations

SQL*Net supports three exception operations:

Of these three operations, only the initiation of a break can be controlled by the user. When the user presses the Interrupt key (Ctrl-c on some machines), the application calls this function. Additionally, the database can initiate a break to the client if an abnormal operation occurs, such as during an attempt to load a row of invalid data using SQL*Loader.

The other two exception operations are internal to some products using SQL*Net to resolve network timing issues. SQL*Net can initiate a test of the communication channel, for example, to see if new data has arrived. The reset function is used to resolve abnormal states, such as getting the connection back in synchronization after a break operation has occurred.

SQL*Net and the Network Listener

TNS includes a protocol independent application listener that receives connections on behalf of any TNS application, over any underlying protocol. Referred to as a network listener, it runs as a single process or task and can service the needs of all TNS applications over all protocols available on a machine.

Network Listener and Native Listeners

The network listener is available for all standard transport protocols supported by TNS. In addition, there are protocols that have application generic listeners or connection acceptance methods, such as DECnet and APPC/LU6.2, that may receive TNS connections.

Additional Information: For information on SQL*Net version 2 connections with a native connection acceptance method, see the Oracle operating system-specific documentation for that protocol and platform.

SQL*Net and the Network Listener

SQL*Net version 2, as a TNS-based product, uses the network listener on a server to receive incoming connections from SQL*Net clients. The network listener listens for SQL*Net connections on a specific port or socket, which is defined in the ADDRESS portion of the connect descriptor.

Prestarted Dedicated Server Processes

SQL*Net release 2.1 and later provides the option of automatically creating dedicated server processes. With this option, when the listener starts, it creates Oracle server processes which are then available to service incoming connection requests. These processes may last for the life of the listener, and they can be reused by subsequent connection requests.

Note: Prespawned dedicated servers requires SQL*Net release 2.1 or later, and requires Oracle7 Server release 7.1 or later.

Prestarted dedicated server processes reduce connect time by eliminating the need to create a dedicated server process for each new connection request as it comes to the listener. They also provide better use of allocated memory and system resources by recycling server processes for use by other connections without having to shut down and recreate a server. The use of prestarted dedicated server processes is particularly useful in systems where the Oracle7 Multi-Threaded Server is unsupported, or where the creation of a new server process is slow and resource-intensive.

Figure 2 - 7 shows the role of the network listener in a SQL*Net connection to a server connected to two communities.

Figure 2 - 7. Network Listener in SQL*Net Connection

The steps involved in establishing a connection (as shown in Figure 2 - 7) are:

Step 1. A connection request is made by any client in the TNS network and arrives through one of the communities to which the listener is attached.

Step 2. The network listener identifies that a connection request has arrived in one of its communities.

Step 3. a. The network listener spawns a dedicated server process and passes control of the incoming connection to it, or, b. the address of a shared dispatcher process (multi-threaded server) is provided, and the incoming connection is directed to it, or, c. the incoming connection is redirected to one of the prespawned dedicated server processes.

At the completion of a connection, the network listener continues to listen for additional incoming connections.

How SQL*Net Establishes Connections to a Prespawned Dedicated Server

Prestarted (commonly referred to as "prespawned") Oracle7 Servers are server processes that are pre-started by the Listener before any incoming connection request. They improve the time it takes to establish a connection on servers where the Multi-Threaded Server is not used or not supported on a given machine. Their use in a heavily loaded distributed system can be beneficial.

The following parameters must be specified for each SID to be prespawned and are located in their respective SID_DESC in the LISTENER.ORA file. They control how the server is spawned.


The maximum number of prespawned servers the listener creates. This value should be a large number and at least the sum of the POOL_SIZE for each protocol.


The number of unused prespawned server processes for the listener to maintain on the selected protocol. The number must be greater than zero, but no larger than the PRESPAWN_MAX value. Set this value to the average expected number of connections at any given time.


The time that an inactive server process should wait for the next connection before it shuts down. This parameter is used to prevent server processes from being immediately shut down after a client disconnects. For greatest efficiency, provide a short time value for this parameter.

An additional feature of prespawned servers is the ability to set specific parameters for each SID. Thus, systems with heavy use can be tailored to accommodate the larger number of connection requests by setting PRESPAWNED_MAX and POOL_SIZE to large values. Similarly, when systems require mostly shared connections, the number of prestarted servers can be set to a low value.

Following is the sequence of events that occur when you are using prestarted servers to service client connection requests.

The above sequence of events continues until the PRESPAWN_MAX is reached, at which point the listener will cease spawning new servers.

When clients disconnect, the prespawned server associated with the client is returned to the idle pool. If then waits the length of time defined in the TIMEOUT parameter to be assigned to another client. If no client is handed to the pre-spawned server before TIMEOUT expires, the pre-spawned server shuts itself down.

See the Oracle Network Manager Administrator's Guide for more information.

How SQL*Net Establishes Connections to a Multi-Threaded Server

A multi-threaded server enables many clients to connect to the same server without the need for dedicated server processes for each client. Using the Multi-Threaded Server enables you to minimize the memory and processing resources needed on the server side as the number of connections to the database increases.

The sequence of events that occurs with the Oracle7 multi-threaded server can occur in two stages:

What Happens When an MTS and Listener are Started

During initial startup of the Oracle7 multi-threaded server and the listener, the following sequence occurs:

Note: A wildcard listen is where the server process listens, but informs the underlying protocol stack (or operating system in the case of the IPC Protocol Adapter) that it has no preference as to what address it listens for other than specifying the protocol on which it wishes to perform the operation. As a result, many operating systems will choose a free listening address and automatically assign this to the requesting server process.

Note: If step 2 is performed before step 1, the dispatchers will be unable to contact the listener in step 3. If this occurs, each dispatcher loops and attempts to reconnect to the listener every 60 seconds. Meanwhile, incoming connection requests will be handled through other means (prespawned dedicated or newly-spawned dedicated server processes).

The listener and the Oracle7 multi-threaded server should be ready for incoming connections, at this point. You can check which dispatchers have registered with the Listener by typing

lsnrctl services listener_name 

How a Multi-Threaded Server Connection Request is Handled

The following is how a multi-threaded server connection request is handled:

When an Oracle7 Server has been configured as a multi-threaded server, incoming connections are always routed to the dispatcher unless the connection request specifically requests a dedicated server (by having SERVER=DEDICATED in the CONNECT_DATA portion of the connect descriptor) or no dispatchers are available.

Global Naming Issues

The following sections explain Oracle's naming scheme and how references to network objects are resolved within a distributed system.

Global Database Names (Service Names)

Every database in an Oracle network has a global database name, more commonly referred to as a service name. The global database name uniquely identifies each database in the global network. A global database name typically consists of a database name (DB_NAME) and a hierarchical domain name (DB_DOMAIN).

For example, HR.US.ACME.COM is a global database name for the "HR" database, which is located in the US.ACME.COM domain. From the viewpoint of SQL*Net and Oracle Names, HR.US.ACME.COM is also the service name. The network domain component of a global database name must follow standard Internet conventions. Levels in domain names are separated by dots, and the order of domain names is from leaf to root, left to right.

For example, Figure 2 - 8 illustrates a representative hierarchical arrangement of databases throughout a network and how a global database name is formed.

Note: Do not confuse Oracle global database names with SQL*Net community names. A SQL*Net community is a group of machines and network services that communicate using the same protocol. A global database name consists of a database name and a domain name. Domains only exist for naming and administrative purposes, and have no functional relationship to community names.

Figure 2 - 8. Network Directories and Global Database Names

Notice that throughout the network there are several databases with the same name (such as SALES). However, also notice that each database has a unique global database name because of its location within the network domain structure. From left to right, global database names are as follows: 

Because each database has a unique global database name, each database and its objects can be uniquely identified with the objects' global object name. For example, notice that each HQ database contains a table named EMP. However, each EMP table can be uniquely identified with its global object name. In Figure 2 - 8, the global names for the two EMP tables are: 

Each local data dictionary in an Oracle distributed system stores object names and names of containing schemas only, not complete global object names. However, because each database can have a unique name within a network, and because each object name is guaranteed to be unique within the scope of a single database; each object in a database in the distributed system has a unique global object name.

Network Domains and Network Naming Services

Figure 2 - 8 illustrated a fictional network domain structure that follows standard Internet conventions. Network domains are similar to the file directories used by many operating systems (such as UNIX). However, unlike file systems, network domains may or may not correspond to any physical arrangement of databases and other structures in a network. Network domains might simply be name spaces. The availability and functionality of a network naming service dictate what is possible.

If Oracle Names servers are available, they can be configured into your network to perform name resolution. For example, Network Manager creates and drops network domains, controls access to network domains, creates and drops network objects (such as databases) within the network structure, and enforces the unique naming of objects within the network.

Oracle's architecture uses network naming services, such as Oracle Names, Network Information Services (NIS), and Domain Name System (DNS). Whether you are using a network naming service, such as Oracle Names or TNSNAMES.ORA name lookup files, to resolve names to addresses, you still need to follow global database naming conventions. Also keep in mind the following:

You can define a database link so that the user accessing remote data connects to the remote database either with the local username and password or an explicitly-specified username and password. In other words, a database link can be defined so that all users of the link connect to the remote database with either a central, explicitly-specified remote account or an implied individual remote accounts.

Local Object Names

In a distributed or non-distributed environment, Oracle guarantees that each database has a unique set of schemas. Within each schema, an object name is unique within its name space. Therefore, each schema object's name is guaranteed to be unique within the context of any given Oracle database, and the local Oracle node easily can resolve any references to objects within the local database. Each local data dictionary stores only the names of local objects (and synonyms), not remote object names.

Database Links

Oracle uses database links to facilitate connections between the individual databases of a distributed system. A database link defines a path to a remote database by uniquely identifying and specifying the location of a remote database.

Note: Remember that a global database link is created automatically for each database defined in Network Manager. However, public and private database links are typically created by users or database administrators.

Database Link Name same as Global Database Name

A database link defines a path to a remote database. The two components of a path are a remote account and a database string. Database links are essentially transparent to users of a distributed system, because the name of a database link is the same as the global name of the database to which the link points. For example, the following statement creates a database link in the local database. The database link named SALES.DIVISION3.ACME.COM describes a path to a remote database of the same name:


At this point, any application or user connected to the local database can access data in the SALES database by using the global object name (SALES.DIVISION3.ACME.COM). The SALES.DIVISION3.ACME.COM database link implicitly facilitates the connection to the SALES database. For example, consider the following remote query that references the remote table SCOTT.EMP in the SALES database:


National Language Support (NLS) and Database Links

When a user session connects to an instance, the values of NLS parameters used by the instance for that user session are defined by the value of the initialization parameter NLS_LANG for that session. This applies to direct and indirect connections.

If the values of the NLS parameters are changed during a session by an ALTER SESSION statement, the changes are automatically propagated to all instances to which the user session is connected, either directly or indirectly. For more information on National Language Support features, see the Oracle7 Server SQL Reference.

Types of Database Links

Oracle uses several types of database links to resolve users' references to global object names:

private database link

Created on behalf of a specific user. A private database link can be used when the owner of the link specifies a global object name in a SQL statement, or in the definition of the owner's views or procedures.

public database link

Created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

global database link

Created and managed by a global naming service such as Oracle Names. A global database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

Public and private database links are stored in the data dictionary of a database. Global database links are not.

Each type of database link has advantages and disadvantages, as compared to the other types. For example, you can maintain tighter security with private than with public or global database links. The use of a private database link is at the discretion of the owner of the link.

Database Links in SQL Statements

The owner of a private database link can use the link in his/her own SQL statements and selectively allow other users to use the private link by creating views, procedures, or synonyms that reference the link in their definitions. Otherwise, there is no way to restrict the use of a public database link selectively (that is, any local user can connect to the remote database specified by the public database link).

Database Links and Security

In a distributed system, application developers and individual users are often allowed to create private database links. However, you must account for the extra security responsibilities required in a distributed system. See page 6 - 11 for more information on security issues to consider when implementing a distributed system.

Database Links and Connection Qualifiers

Connection qualifiers provide a way to have several database links of the same type (for example, public) that point to the same remote database, yet establish those connections using different communications pathways.

A connection qualifier is a method of aliasing a database link to a particular communication pathway (or instance in the case of the Oracle Parallel Server). The connection qualifier is an identifying string appended to the database link name It is preceded by an at sign (@) (for example, emp.scott@HQ.ACME.COM@DBMS1).

For example, you have a database that is connected to the HQ.ACME.COM database DBS2 by an ethernet link and by a slower modem link. You want to access the DBS2 by both communication links allowing higher priority applications to use the faster ethernet link. You could define the following database links:

    USING '';
    USING '';

Note that in the above examples, the connection qualifiers (@ethernet, @modem) are appended to the database link name. The connection qualifier does not necessarily specify how the connection is to be established; this information is specified by the USING clause.

Based on the connection qualifiers specified above, the following statement would use the ethernet connection to HQ.ACME.COM:


Connection qualifiers can also be defined to use different instances at a node where the remote database is managed by the Oracle Parallel Server.

Additional Information: For more information about database links and connection qualifiers, see the Oracle7 Server SQL Reference and your operating system-specific SQL*Net documentation. Oracle Names can also be used to define database links (except those in a replicated environment). See the Oracle Names Administrator's Guide for more information.

Oracle Names

Oracle Names is a distributed name service that resolves database service names and database links to network addresses, and makes them available to all clients in the network. When Oracle Names servers are used, it is no longer necessary to update every TNSNAMES.ORA file on every client whenever a change is made to an existing server or a new server is added to the network. Oracle Names is configured through Oracle Network Manager, so changes to an environment only need to be made at a single point for them to be available to all clients and servers.

The advantages of using Oracle Names servers are:

For more information on Oracle Names, see the Oracle Names Administrator's Guide.

Automatic Creation of Global Database Links with Network Manager

When you define a network that includes Oracle Names, Network Manager automatically creates a global database link to every database server you define from every other database server in the network. These database links do not reside in the data dictionary, but in the network definition to which the Names servers refer. The default database links created do not initially include a CONNECT TO clause (that is, a username and password), so users reach the linked database using the same usernames and passwords as they use to reach the first database.


Explicitly Defined Database Links

You can edit global database links to include a username and password using Network Manager. When you edit a database, you can specify a single default username and password for the database link. See the Oracle Network Manager Administrator's Guide for details on how to edit global database links.

Connection Qualifiers

You can also define connection qualifiers to global database links through Network Manager. Connection qualifiers provide a way to create more than one link to a given database. These alternate links are a useful way to access different accounts on the same database with different sets of access privileges. The alternate link created by a connection qualifier must include a reference to a database by its global database name (or service name). See the Oracle Network Manager Administrator's Guide for details on how to create connection qualifiers using Network Manager.

How SQL*Net Resolves Service Names

When a user types in a service name, SQL*Net resolves it to an address using a variety of mechanisms in the following order:

If all attempts to resolve the name fail, Oracle issues the error message ORA-12154 TNS: could not resolve database name.

How Oracle Names Resolves Service Names

Service names (also called global database names) are translated to addresses in SQL*Net using the following method:

Consider the following example:

SQLPLUS scott/tiger@hr

will connect to the database HR.US.ACME.COM if the client profile contains a default domain of US.ACME.COM.

SQLPLUS scott/

is fully qualified and properly identifies the database HR.US.ACME.COM.

How Oracle Names Resolves Database Links

Similarly, for database links, the database looks at any defined private or public database link definitions and if not fully-qualified, the database domain (the GLOBAL_NAME minus the part preceding the first dot) is tacked on the database name of the link. If no USING clause is specified in the private or public database link definitions, and the database's client profile specifies one or more Oracle Names servers, these servers are called to resolve the database link name.

SQL*Net then receives either the database link's USING clause or the information returned by the Oracle Names server. If the USING clause contains a name, the name resolution process described above is then used to get the address. If the USING clause contains an address, the database link definition returned by Oracle Names is passed to SQL*Net, and name resolution is bypassed because an address has been directly provided.

Note: Though an address (SQL*Net connect descriptor) could conceivably be specified in the USING clause, a global database name is typically specified.

Consider the following example on the database MFG.US.ACME.COM:

A public database link HR@FIN exists and a user performs:


The database will translate the database link name to HR.US.ACME.COM@FIN and call Oracle Names for link resolution because no USING clause was specified on the created link.

See Understanding SQL*Net and the Oracle Names Administrator's Guide for more information on service name and database link resolution.

Using a SELECT Statement across a Database Link

When you issue a select of a table across a database link, you acquire a transaction lock and a slot in the transaction table for the rollback segment for the local database. The lock can be released only by a commit or rollback.

Network Issues to Consider When Implementing a Distributed Database System

This section describes some things the network administrator must consider before deciding on the structure of a distributed system. Read this section before using the Oracle Network Manager to configure the network.

Planning your Network

When planning your SQL*Net network, think about future needs as well as present requirements. Select a layout that is flexible and expandable. If you foresee your network growing, select computers that have the capacity to handle additional connections. When naming the components in your system, think about how your naming conventions can be extended to handle future components.

Draw the Network Layout

It is a good idea to draw a picture of your network layout as you decide about its composition. Especially if your network includes multiple communities, Interchanges and Names servers, it is much easier to understand and modify if you have a diagram. Two types of diagrams are useful:

Physical diagrams show every component in a network, including the physical connections among them. A physical diagram can help show what pieces are required and demonstrate the connections between components.

Logical diagrams show the relationships between network components without going into detail about their physical placement. The figures throughout this book are good examples of the sort of graphical representation that is needed. In general, the more complex the network, the more necessary a visual mapping.

Select Network Protocols to be Used

The first decision to make when designing a network is whether it will include only one protocol or more than one protocol.

As explained in "SQL*Net version 2 Architecture" [*], SQL*Net runs above TNS, which in turn runs over a transport level protocol, with an Oracle Protocol Adapter acting as an interface between TNS and the protocol of choice. The specific hardware below the transport layer is irrelevant to SQL*Net's functioning.

You may be able to choose a single transport level protocol that works well on all the components in your network. Protocol adapters are available for most of the major protocols on many platforms. If you have only one protocol in your network, as shown in Figure 2 - 9, then all the components are members of the same community.

Figure 2 - 9. A Single Community Network

For reasons of necessity or efficiency, you may choose to have more than one protocol running in your network. You may do this by having multiple protocol adapters on the computers in your network, or, more efficiently, you may have an Interchange between the computers running one protocol and the computers running another.

Using SQL*Net and the MultiProtocol Interchange, individual computers can communicate across the protocols that are most compatible with their operating systems. For example, you can have personal computers running Novell's SPX/IPX connected to a VAX server that uses the DECnet protocol. If your network uses one or more Interchanges, as shown in Figure 2 - 10, it is a multicommunity network.

Figure 2 - 10. A Multicommunity Network

Choose Nodes as Interchanges

If you decide on a multicommunity network, you must choose what nodes to use for Interchanges to connect the communities. Considerations include:

For more information about Interchanges, see the Oracle MultiProtocol Interchange Administrator's Guide.

Choose a Node to Run Network Manager

Select a location for Network Manager from which it is relatively easy to transfer configuration files to other network components. The Network Manager includes a utility, NetFetch, which helps you do this, but a SQL*Net network (either version 2 or release 2.1) must be up and running before it can be used.

For more information about using the Oracle Network Manager to create SQL*Net configuration files, see the Oracle Network Manager Administrator's Guide.

Decide on the Structure of Network Administration

Most networks have one central point of administration, that is, one installation of the Oracle Network Manager. However, if you are using Oracle Names and your network is quite large or widely distributed geographically, you may choose to have several regions of network administration.

If your enterprise-wide network includes both the United States and Europe, you might want to have administrative decisions about the network made locally.

For example, it would be more efficient if a network administrator in Chicago had jurisdiction over the names and locations of US network services, while an administrator in Brussels was responsible for decisions about a European network.

For more information about centralized and decentralized administration, see the Oracle Names Administrator's Guide.

Decide which Nodes will Run Oracle Names Servers

If you use Oracle Names to provide a centralized naming service for your network, you must decide what nodes should contain Names servers, which provide name and address information to enable connections throughout the network. Currently, you must have a name server in every community. In general, Oracle recommends that Names servers in a multicommunity network be placed on MultiProtocol Interchange nodes, thereby minimizing the number of Names servers required.

For more information about Oracle Names, see the Oracle Names Administrator's Guide.

Decide on a Organizational Naming Standard

When you name entities such as nodes in a networked environment, you should ensure that object names are unique within the network. This can be a challenge if your organization is large, and network administration is not handled centrally.

You may be able to guarantee that all services in your building or jurisdiction have unique names, but that does not guarantee that the name is unique within the organization. Your goal should be to avoid the occurrence of duplicate names if multiple independent TNS communities are joined by installing an Interchange between them.

Selecting Domain Names

A recommended network naming technique is to use hierarchical groups or domains in which each administrative unit is assigned to a unique domain based on the function it provides. Many of the examples in this guide feature the fictitious company, ACME Inc., which has segregated its naming domains as shown in Figure 2 - 11.

Figure 2 - 11. Naming Domains at ACME

In this figure, each of the boxes represents a separate domain. The domains are related hierarchically; that is, FIN and HR are the children of HQ, which is one of the children of ACME. A network object (such as an Interchange or a server) within a given domain has a unique name within that domain. This is generally manageable because one or at most a few people have authority to pass out names for that domain. The global name for that object includes the parent domains.

For example, consider the corporation shown in Figure 2 - 11. The sales organization (the SALES domain) could have a server named VAULT. The human resources group (the HR domain) could also have a server named VAULT. The global names of these servers would be unique. The finance group's server would have the global name


while the global name of the human resources server would be


This structure is especially important if you are using a Oracle Names server to access any of the addresses or other information used within the network.

Names can go to the company level (the ACME stem) or can go to the inter-network level (for example, the ACME.COM stem) in support of inter-company communications such as mail or Electronic Data Interchange (EDI). If your organization belongs to the Internet, or you expect that it might join the Internet in the future, the domain names should include the appropriate stem (such as COM, GOV, or EDU).

If your organization already has global naming conventions, your network components should follow those conventions.

Network Manager automatically appends the default domain .WORLD to the name of all network components unless you provide alternative domain names. See the Oracle Names Administrator's Guide for more information.

Default Domain

Every client and server has a default domain listed in its SQLNET.ORA file. The default domain is the domain to which most of the clients' connection requests are directed. The service names of databases in the default domain do not need to be fully qualified; that is, the domain name does not need to be included.

For example, if a client wanted to make a connection to a database in its default domain with the service name PROFITS.SALES.ACME.COM, it could do so using the following command:

% sqlplus scott/tiger@profits

However, if the PROFITS database were not in the client's default domain, the command would be:

% sqlplus scott/

Note: The default domain is not necessarily the same as the domain of the client itself. For example, clients in one domain may frequently access Oracle servers in another domain.

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index