CHAPTER 14

Oracle Networking

Exam Objectives

• 062.4.1    Configure Oracle Net Services

• 062.4.2    Use Tools for Configuring and Managing the Oracle Network

• 062.4.3    Configure the Client-Side Network

• 062.4.4    Understand Database Resident Connection Pooling

• 062.4.5    Configure Communication Between Databases

Oracle Net is the enabling technology for Oracle’s client-server architecture. It is the mechanism for establishing sessions against a database instance. Several tools can be used for setting up and administering Oracle Net, although it can be done with nothing more than a simple text editor. The end result is a set of files that control a process (the database listener) that launches server processes in response to connection requests and defines the means by which a user process will locate the listener.

Configure Oracle Net Services

Oracle’s client-server architecture uses the Oracle Net protocol to establish and maintain the session between the client and the server. It is possible to use this with no configuration at all, but all sites will, in practice, configure it.

Oracle Net and the Client-Server Paradigm

There are many layers between the user and the database. In the Oracle environment, no user ever has direct access to the database—nor does the process that the user is running. Client-server architecture guarantees that all access to data is controlled by the server.

A user interacts with a user process. This is the software running on the user’s local terminal. For example, it could be Microsoft Access plus an ODBC driver on a Windows PC, it could be something written in C and linked with the Oracle Call Interface (OCI) libraries, and it could even be your old friend SQL*Plus. Whatever it is, the purpose of the user process is to prompt the user to enter information that the process can use to generate SQL statements. In the case of SQL*Plus, the process merely waits for you to type something in; a more sophisticated user process will paint a proper data-entry screen and validate your input, and then when you click the Submit button, it will construct the statement and send it off to the server process.

The server process is a process running on the database server machine that executes the SQL it receives from the user process. This is the client-server split: a user process generating SQL, a server process executing SQL.

Oracle Net provides the mechanism for launching a server process to execute code on behalf of a user process. This is establishing a session. Then Oracle Net is responsible for maintaining the session, meaning transmitting SQL from the user process to the server process and fetching results from the server process back to the user process.

Figure 14-1 shows the various components of a session. A user interacts with a user process; a user process interacts with a server process, via Oracle Net; a server process interacts with the instance; and the instance, via its background processes, interacts with the database.

Figure 14-1    The database is protected from the user by several layers.

The client-server split between the user process and the server process will usually be physical as well as logical. There will be a network between the machines hosting the user processes and the machine hosting the server side, implemented by Oracle Net running on top of whatever communications protocol is supported by your operating system. The supported protocols are Transmission Control Protocol/Internet Protocol (TCP/IP) version 4 and 6, with or without Secure Sockets Layer (SSL); Windows named pipes; and the Sockets Direct Protocol (SDP) designed for InfiniBand networks. All operating systems also have an Inter-Process Communication (IPC) protocol proprietary to the operating system. This, too, is available to Oracle Net for local connections where the user process is on the same machine as the server.

Establishing a Session

When a user, through their user process, wants to establish a session against an instance, the user will issue a command such as this:

What actually happens when that command is processed? First, break down the command into its components. There is a database user name (SCOTT), followed by a database password (TIGER, which is case sensitive), and the two are separated by a delimiter (/). Then there is an @ symbol, followed by a connect string (ORCL12C). The @ symbol indicates to the user process that a network connection is required. If the @ symbol and the connect string are omitted, the user process will assume that the instance the user wants to connect to is running on the local machine and that the always-available IPC protocol can be used. If the @ symbol and a connect string are included, the user process will assume that the user is requesting a network connection to an instance on a remote machine—although, in fact, the user could be bouncing off the network card and back to the machine onto which they are logged.

Connecting to a Local Instance

Even when you connect to an instance running on your local machine, you still use Oracle Net. All Oracle sessions use a network protocol to implement the separation of user code from server code, but for a local connection the protocol is IPC. This is the only type of connection that does not require a database listener; indeed, local connections do not require any configuration at all. The only information needed is to tell your user process which instance you want to connect to. Remember that several instances could be running on your local computer. You give the process this information through an environment variable. Figure 14-2 shows examples of this on Windows.

Figure 14-2    Local database connections on Windows

Name Resolution

When connecting over a network, the first stage is to work out exactly what it is you want to connect to. This is the process of name resolution. If your connect statement includes the connect string @orcl12c, Oracle Net has to work out what is meant by orcl12c. This means the string has to be resolved into certain pieces of information: the protocol you want to use (assume that this is TCP), the IP address on which the database listener is running, the port that the listener is monitoring for incoming connection requests, and the name of the instance (which need not be the same as the connect string) to which you want to connect. There are variations; rather than an IP address, the connect string can include a hostname, which then gets further resolved to an IP address by the Domain Name System (DNS) resolution mechanism. You can configure a number of ways of resolving connect strings to address and instance names, but one way or another the name resolution process gives your user process enough information to go across the network to a database listener and request a connection to a particular instance.

Launching a Server Process

The database listener, running on the server machine, monitors one or more ports on one or more network interface cards for incoming connection requests. When it receives a connect request, the listener must first validate whether the instance requested is actually available. Assuming that it is, the listener will launch a new server process to service the user process. Thus, if you have a thousand users logging on concurrently to your instance, you will be launching a thousand server processes.

In the TCP environment, each dedicated server process launched by a listener will acquire a unique TCP port number. This will be assigned at process startup time by your operating system’s port-mapping algorithm. The port number gets passed back to the user process by the listener (or on some operating systems, the socket already opened to the listener is transferred to the new port number), and the user process can then communicate directly with its server process. The listener has now completed its work and waits for the next connect request.

Creating a Database Listener

A listener is defined in a file, the listener.ora file, whose default location is in the ORACLE_HOME/network/admin directory. At a minimum, the listener.ora file must include a section for one listener that states its name, the protocol, and the listening address it will use. You can configure several listeners in the one file, but they must all have different names and addresses.

This is an example of a listener.ora file:

The first section of this file defines a listener called LISTENER, monitoring the local hostname on the default port, 1521. The second section defines another listener called LIST2. This listener is monitoring port 1522 on both the hostname address and a loopback address.

To create a listener, you need do nothing more than create an entry in the listener.ora file and then start it. Under Windows, the listener will run as a Windows service, but there is no need to create the service explicitly; it will be created implicitly the first time the listener is started. From then on, if you want, it can be started and stopped like any other Windows service.

Dynamic Service Registration

To launch sessions against an instance, the listener needs to know what instances are currently running on the server. This is the final part of the puzzle. It is possible to hard-code a list of instances in the listener.ora file, but this is not considered best practice. The approved technique is to rely on dynamic service registration.

Every database offers one or more services. A service is a logical name to which sessions can attach through the instance. Different services within the database can have different characteristics (for instance, for fault tolerance). There will always be a default service named after the database, which is often the same name as the instance, which is often the same name as the Net service alias defined in a tnsnames.ora file and used in connect strings. When an instance starts, by default it will look for a listener on the address to which the machine’s hostname resolves using port 1521. If there is indeed a listener running on that address:port, the database will register its service name (or names) with the listener, and the listener will then be able to connect users. When the database is shut down, it will deregister itself from the listener. This is the process of dynamic registration.

During the lifetime of the instance, the LREG process repeatedly reregisters with the listener. Thus, if the listener is stopped and started, it will become aware of the database the next time LREG attempts to register.

Shared Server

The network architecture described so far is the dedicated server architecture. Each user process is connected to a server process, launched by the listener for servicing that one session. An alternative is the shared server architecture, where a relatively small pool of server processes services a much larger number of user sessions.

The Limitations of Dedicated Server Architecture

As more users log on to your instance, more server processes get launched. This is not a problem as far as Oracle is concerned. The database listener can launch as many processes as required, although there may be operating system limits on the speed with which it can launch them. Then, once the sessions are established, there is no limit to the number that the process monitor (PMON) can manage (although your operating system may have limits on the number of processes that it can support related to context switches and memory).

A computer can do only one thing at a time unless it is a symmetric multiprocessing (SMP) machine, in which case each central processing unit (CPU) can do only one thing at a time. The operating system simulates concurrent processing by using an algorithm to share CPU cycles across all the currently executing processes. This algorithm, often referred to as a time-slicing or time-sharing algorithm, takes care of allocating a few CPU cycles to each process in turn. The switch of taking one process off the CPU to put another process on the CPU is called a context switch. Context switches are expensive; the operating system has to do a lot of work to restore the state of each process as it is brought on to the CPU and then save its state when it is switched off the CPU. As more users connect to the instance, the operating system has to context-switch between more and more server processes. Depending on your operating system, this can cause a severe degradation in performance. A decent mainframe operating system can context-switch between tens of thousands of processes without problems, but newer (and simpler) operating systems such as Unix and Windows may not be good at running thousands, or even just hundreds, of concurrent processes. Performance can degrade dramatically because a large proportion of the computer’s processing capacity is taken up with managing the context switches, leaving a relatively small amount of processing capacity available for actually doing work.

Also, memory problems may occur as more sessions are established. The actual server processes themselves are not an issue because all modern operating systems use shared memory when the same process is loaded more than once. So, launching a thousand server processes should take no more memory than launching one. The problem comes with the Program Global Area (PGA). The PGA is a block of memory associated with each server process to maintain the state of the session, and is a work area for operations such as sorting rows. Clearly, the PGAs cannot be in shared memory; they contain data unique to each session.

Therefore, in the dedicated server environment, performance may degrade if your operating system has problems managing a large number of concurrent processes, and the problem will be exacerbated if your server machine has insufficient memory. Note that it doesn’t really matter whether the sessions are actually doing anything. Even if the sessions are idle, the operating system must still bring them on and off the CPU and possibly page the appropriate PGA into main memory from swap files according to its time-slicing algorithm. There comes a point when no matter what you do in the way of hardware upgrades, performance begins to degrade because of the operating system inefficiencies in managing context switches and paging. These are not Oracle’s problems, but to overcome them, Oracle offers the option of the shared server architecture. This allows a large number of user processes to be serviced by a relatively small number of shared server processes, thus reducing dramatically the number of processes that the server’s operating system has to manage. As a fringe benefit, memory usage may also reduce.

The Shared Server Architecture

One point to emphasize immediately is that shared server is implemented purely on the server side. The user process and the application software have no way of telling that anything has changed. The user process issues a connect string that must resolve to the address of a listener and the name of a service (or of an instance). In return, it will receive the address of a server-side process that it will think is a dedicated server. It will then proceed to send SQL statements and receive result sets; as far as the user process is concerned, absolutely nothing has changed. But the server side is different.

Shared server is implemented by additional processes that are part of the instance. They are background processes launched at instance startup time. There are two process types: dispatchers and shared servers. There are also some extra queue memory structures within the System Global Area (SGA), and the database listener modifies its behavior for shared server. When an instance that is configured for shared server starts up, in addition to the usual background processes, one or more dispatcher processes start. The dispatchers, like any other TCP process, run on a unique TCP port allocated by your operating system’s port mapper. They contact the listener and register with it. One or more shared server processes also start. These are conceptually similar to a normal dedicated server process, but they are not tied to one session. They will receive SQL statements, parse and execute them, and generate a result set. However, they will not receive the SQL statements directly from a user process; instead, they will read them from a queue that will be populated with statements from any number of user processes. Similarly, the shared servers don’t fetch result sets to a user process directly; instead, they put the result sets onto a response queue.

The next questions are how do the user-generated statements get onto the queue that is read by the server processes, and how do results get fetched to the users? This is where the dispatchers come in. When a user process contacts a listener, rather than launching a server process and connecting it to the user process, the listener passes back the address of a dispatcher. If there is only one dispatcher, the listener will connect it to all the user processes. If there are multiple dispatchers, the listener will load-balance incoming connection requests across them, but the end result is that many user processes will be connected to each dispatcher. Each user process will be under the impression that it is communicating with a dedicated server process, but it isn’t. It is sharing a dispatcher with many other user processes. At the network level, many user processes will have connections multiplexed through the one port used by the dispatcher.

When a user process issues a SQL statement, it is sent to the dispatcher. The dispatcher puts all the statements it receives onto a queue. This queue is called the common queue because all dispatchers share it. No matter which dispatcher a user process is connected to, all statements end up on the common queue.

All the shared server processes monitor the common queue. When a statement arrives on the common queue, the first available shared server picks it up. From then on, execution proceeds through the usual parse-bind-execute cycle, but when it comes to the fetch phase, it is impossible for the shared server to fetch the result set to the user process; there is no connection between the user process and the shared server. So, instead, the shared server puts the result set onto a response queue that is specific to the dispatcher that received the job in the first place. Each dispatcher monitors its own response queue, and whenever any results are put on it, the dispatcher will pick them up and fetch them back to the user process that originally issued the statement.

A result of the mechanism of dispatchers and queues is that any statement from any user process could be executed by any available shared server. This raises the question of how the state of the session can be maintained. It would be quite possible for a user process to issue, for example, a SELECT FOR UPDATE, a DELETE, and a COMMIT. In a normal dedicated server connection, this isn’t a problem because the PGA (which is tied to the one server process that is managing the session) stores information about what the session was doing, and therefore the dedicated server will know what to COMMIT and what locks to release. The PGA for a dedicated server session will store the session’s session data, its cursor state, its sort space, and its stack space. But in the shared server environment, each statement might be picked off the common queue by a different shared server process, which will have no idea what the state of the transaction is. To get around this problem, a shared server session stores most of the session data in the SGA, rather than in a PGA. Then, whenever a shared server picks a job off the common queue, it will go to the SGA and connect to the appropriate block of memory to find out the state of the session. The memory used in the SGA for each shared server session is known as the User Global Area (UGA) and includes all of what would have been in a PGA, with the exception of the session’s stack space. This is where the memory saving will come from. Oracle can manage memory in the shared pool much more effectively than it can in many separate PGAs.

The following session memory structures are stored in the SGA when shared server is implemented:

•  Sort area

•  Hash area

•  Bitmap creation area

•  Bitmap merge area

•  Cursor state

•  User session data

The following session memory structures remain outside the SGA. These may be thought of as the run-time information for a call in progress:

•  Stack space

•  Local variables

The part of the SGA used for storing UGAs is the large pool. This can be configured manually with the large_pool_size parameter, or it can be automatically managed.

It is impossible to use a shared server session to start up or shut down the instance. This is because the processes needed to support shared server (dispatchers and shared server processes) are themselves part of the instance.

Configuring Shared Server

Being a server-side capability, there is no need for client configuration at all beyond perfectly normal client-side Oracle Net (the tnsnames.ora and sqlnet.ora files), as detailed previously. On the server side, shared server has nothing to do with the database—only the instance. The listener will be automatically configured for shared server through dynamic instance registration. It follows that shared server is configured through instance initialization parameters. There are a number of relevant parameters, but two are all that are usually necessary: shared_servers and dispatchers.

The first parameter to consider is shared_servers. This controls the number of shared servers that will be launched at instance startup time. Shared server uses a queuing mechanism, but ideally there should be no queuing. There should always be a server process ready and waiting for every job that is put on the common queue by the dispatchers. Therefore, shared_servers should be set to the maximum number of concurrent requests you expect. But if there is a sudden burst of activity, you don’t have to worry too much because Oracle will launch additional shared servers, up to the value specified for max_shared_servers. By default, shared_servers is 1 if dispatchers is set. If the parameter max_shared_servers is not set, it defaults to one-eighth of the processes parameter.

The dispatchers parameter controls how many dispatcher processes to launch at instance startup time and how they will behave. This is the only required parameter. There are many options for this parameter, but usually two will suffice: how many to start and what protocol they should listen on. Among the more advanced options are ones that allow you to control the port and network card on which the dispatcher will listen and the address of the listener (or listeners) with which it will register. However, you can usually let your operating system’s port mapper assign a port and use the local_listener parameter to control which listener the dispatchers will register. The max_dispatchers parameter sets an upper limit to the number of dispatchers you can start, but unlike with shared servers, Oracle will not start extra dispatchers on demand. You can, however, launch additional dispatchers at any time, up to this limit.

For example, to enable the shared server architecture, adjust the two critical parameters as follows:

Tuning the shared server is vital. There should always be enough shared servers to dequeue requests from the common queue as they arrive, and there should always be enough dispatchers that they can service incoming requests as they arrive and return results as they are enqueued to the response queues. Memory usage by shared server sessions in the SGA must be monitored. After converting from dedicated server to shared server, the SGA will need to be substantially larger.

When to Use the Shared Server

You will not find a great deal of hard advice in the Oracle documentation on when to use shared server or how many dispatchers and shared servers you’ll need. The main point to hang on to is that shared server is a facility you use because you are forced to, not something you use automatically. It increases scalability but perhaps at the cost of reducing performance. It is quite possible that any one statement will take longer to execute in a shared server environment than if it were executing on a dedicated server because it has to go via queues. It may also take more CPU resources because of this enqueuing and dequeuing activity. But overall, the scalability of your system will increase dramatically. Even if each request is marginally slower, you will be able to carry out many more requests per second through the instance.

Consider an online transaction processing (OLTP) environment, with hundreds of telephone operators in a call center. Each operator may spend one or two minutes per call, collecting the caller details and entering them into the user process. Then, when the operator clicks the Submit button, the user process constructs an insert statement and sends it off to the server process. The server process might go through the whole parse/bind/execute/fetch cycle for the statement in just a few hundredths of a second. Clearly, no matter how fast the clerks work, their server processes are idle 99.9 percent of the time. But the operating system still has to switch all those processes on and off the CPU according to its time-sharing algorithm. By contrast, consider a data warehouse environment. Here, users submit queries that may run for a long time. The batch uploads of data will be equally long running. Whenever one of these large jobs is submitted, the server process for that session could be working flat out for hours on just one statement.

It should be apparent that shared server is ideal for managing many sessions doing short transactions, where the bulk of the work is on the client side of the client-server divide. In these circumstances, one shared server will be able to service dozens of sessions. But for batch processing work, dedicated servers are much better. If you submit a large batch job through a shared server session, it will work—but it will tie up one of your small pool of shared server processes for the duration of the job, leaving all your other users to compete for the remaining shared servers. The amount of network traffic involved in batch uploads from a user process and in fetching large result sets back to a user process will also cause contention for dispatchers.

A second class of operations that are better done through a dedicated server is database administration work. Index creation, table maintenance operations, and backup and recovery work through Recovery Manager will perform much better through a dedicated server. And it is logically impossible to issue startup or shutdown commands through a shared server; the shared servers are part of the instance and therefore not available at the time you issue a startup command. So, the administrator should always have a dedicated server connection.

The Default Shared Server Configuration

A 12c database instance will run shared server by default, but only for connections through the XDB. These queries show the default configuration, which is adequate for running Database Express (remember from Chapter 13 that Database Express requires XDB to be configured):

Use Tools for Configuring and Managing the Oracle Network

Configuring Oracle Net is nothing more than creating the configuration files. There are three of them:

•  The configuration file listener.ora exists on the server side and defines the operation of the database listener.

•  The configuration file tnsnames.ora is a client-side file used for name resolution. There will usually be a copy on the server as well to facilitate running clients on the server machine.

•  The configuration file sqlnet.ora is an optional file that may exist on both the client and server sides. It sets various defaults that will affect all clients and listeners.

Two graphical tools are provided for creating and editing these files: the Net Manager and the Net Configuration Assistant.

The Net Manager

To launch the Net Manager, run netmgr from a Unix prompt; on Windows you will find it on the Start menu.

The Net Manager navigation tree has three branches. The Profile branch creates or edits the sqlnet.ora file and is used to set options that may apply to both the client and server sides of Oracle. This is where, for example, you can configure detailed tracing of Oracle Net sessions or (as in Figure 14-3) enable certain name-resolution methods. The Service Naming branch is used to configure client-side name resolution in the tnsnames.ora file. The Listeners branch is used to configure database listeners in the listener.ora file.

Figure 14-3    Net Manager’s Profile editor

The Net Configuration Assistant

Launch netca by running the executable $ORACLE_HOME/bin/netca on Linux or by navigating through the Start menu on Windows. Net Configuration Assistant is simple to use. It does not have all the functionality of the Net Manager and can configure only database listeners and tnsnames connect strings.

The Listener Control Utility

The Listener Control utility is the executable $ORACLE_HOME/bin/lsnrctl on Linux or %ORACLE_HOME%inlsnrctl.exe on Windows. lsnrctl commands can be run directly from an operating system prompt or through a simple user interface. For all the commands, you must specify the name of the listener if it is not the default name of LISTENER. Figure 14-4 shows how to check the status of the default listener named LISTENER and start it. Note that the listener starts listening on the address jwvaio on port 1521, but it is not offering a connection to any services. This is because it has only just started, and no database instances have registered with it. The registration will happen automatically within a minute, as instances find that it is started.

Figure 14-4    Starting the default listener

The lsnrctl utility does have a simple user interface. Figure 14-5 shows starting the interface, checking the status of the default listener, shutting it down, and exiting from the tool. Note the output of the STATUS command. The listener is listening on port 1521 for incoming TCP connections. These will be logon requests. It is also listening on port 5500 for Hypertext Transfer Protocol (HTTP) over TCPS. This is for connections from browsers to Database Express.

Figure 14-5    Using the lsnrctl user interface to check the status and then stop the listener named LISTENER

The TNS_ADMIN Environment Variable

Configuring Oracle Net, manually or through the graphical tools, consists of nothing more than creating and editing these three text files: tnsnames.ora, sqlnet.ora, and listener.ora. Where do these files reside? The default location where Oracle processes will look for them is the directory $ORACLE_HOME/network/admin (Linux) or %ORACLE_HOME% etworkadmin (Windows). In some circumstances, you will not want to use this directory. For example, if you have several Oracle products installed on one machine (multiple releases of the database, the Oracle client, and an application server perhaps), each will be in its own Oracle home, and therefore each will have its own copy of the files.

Keeping multiple copies of the files (particularly tnsnames.ora) identical can be awkward. The worst case is when hundreds of client PCs each have their own copy. One answer is to store the files in a central location (which could be a network-mounted file system) and instruct all processes to read the files from this nondefault location. Do this by setting the TNS_ADMIN environment variable. Here is an example on Linux:

And here is an example on Windows:

Configure Client-Side Network

The client side of Oracle Net is configured in a sqlnet.ora file and (typically) a tnsnames.ora file. The sqlnet.ora file is optional, and many sites will never use it. Most sites will, however, use a tnsnames file.

To establish a session against an instance, your user process must issue a connect string. That string resolves to the address of a listener and the name of an instance or service. Oracle provides four methods of name resolution: easy connect, local naming, directory naming, and external naming. It is probably true to say that the majority of Oracle sites use local naming, but there is no question that directory naming is the best method for a large and complex installation.

Easy Connect Name Resolution

The Easy Connect name resolution method is easy to use—it requires no configuration at all. But it is limited to one protocol: TCP. The other name resolution methods can use any of the other supported protocols, such as TCP with secure sockets or named pipes. Another limitation is that Easy Connect cannot be used with any of Oracle Net’s more advanced capabilities, such as load balancing or connect-time failover across different network routes. It is fair to say that Easy Connect is a method you as a database administrator (DBA) will find handy to use, but it is not a method of much use for your end users. Easy Connect is enabled by default. You invoke it with syntax such as the following connect string:

In this example, SQL*Plus will use TCP to go to port 1521 on the IP address to which the hostname jwvaio resolves. Then, if there is a listener running on that port and address, it will ask the listener to spawn a server process against an instance that is offering a service called orclz.

Local Naming Name Resolution

With local naming, the user supplies an alias, known as an Oracle Net service alias, for the connect string, and the alias is resolved by a local file into the full network address (protocol, address, port, and service or instance name). This local file is the infamous tnsnames.ora file, which has caused DBAs much grief over the years. Consider this example of a tnsnames.ora file:

This tnsnames.ora file has two Oracle Net service aliases defined within it: orclz and test. These aliases are what your users will provide in their connect statements. The first entry, orclz, simply says that when the connect string @orclz is issued, your user process should use the TCP protocol to go the machine jwvaio, contact it on port 1521, and ask the listener monitoring that port to establish a session against the instance with the service name orclz. The second entry, test, directs users to a listener on a different machine, serv2.example.com, and asks for a session against the instance called testdb.

Local naming supports all protocols and all the advanced features of Oracle Net, but maintaining tnsnames.ora files on all your client machines can be an extremely time-consuming task. The tnsnames.ora file is also notoriously sensitive to apparently trivial variations in layout. Using the graphical user interface (GUI) tools will help avoid such problems.

Directory Naming and External Naming

Directory naming points the user toward a Lightweight Directory Access Protocol (LDAP) directory server to resolve aliases. LDAP is a widely used standard that Oracle Corporation (and other mainstream software vendors) is encouraging organizations to adopt. To use directory naming, you must first install and configure a directory server somewhere on your network. Oracle provides an LDAP server (the Oracle Internet Directory) as part of the Oracle Application Server, but you do not have to use that—if you already have a Microsoft Active Directory, that will be perfectly adequate. IBM and Novell also sell directory servers conforming to the LDAP standard.

Like local naming, directory naming supports all Oracle Net features—but unlike local naming, it uses a central repository, the directory server, for all your name resolution details. This is much easier to maintain than many tnsnames.ora files distributed across your whole user community.

External naming is conceptually similar to directory naming, but it uses third-party naming services such as Sun’s Network Information Services (NIS+) or the Cell Directory Services (CDS) that are part of the Distributed Computing Environment (DCE).

The use of directories and external naming services is beyond the scope of the Oracle Certified Professional (OCP) syllabus.

Testing Oracle Net Connectivity

An invaluable troubleshooting tool is the tnsping utility. This accepts a connect string and then tests whether it works. It will show the name resolution method used, the Oracle Net configuration files being read, the details of what the string resolves to, and whether there is indeed a listener listening on those details. It does not test whether the database is actually running, but it does attempt to hit the listener and will return a suitable error message if it cannot.

Figure 14-6 shows the use of tnsping to test a connect string—first successfully and then unsuccessfully.

The first example tests the tnsconnect string orclz. The utility resolves the string using files located in the directory C:apporacleproduct12.1.0dbhome_1 etworkadmin, which say that the address is a machine called jwvaio and port 1521. The requested service will be orclz. The test succeeds; there is indeed a listener on that address and port, and the listener does know of a service called orclz. The second test attempts to resolve the name orcla and fails, presumably because there is no such entry in the tnsnames.ora file.

Figure 14-6    Using the tnsping utility to test name resolution

Exercise 14-1: Configure Oracle Net    In this exercise, you will set up a complete Oracle Net environment using graphical and command-line tools. Any differences between Windows and Linux will be pointed out. These are the steps to follow:

1.  Create a directory to be used for the Oracle Net configuration files and then set the TNS_ADMIN variable to point to this. It doesn’t matter where the directory is, as long as the Oracle user has permission to create, read, and write it.

Here’s an example on Linux:

Ensure that all work from now is done from a session where the variable has been set.

Here’s an example on Windows:

Create and set the key TNS_ADMIN as a string variable in the registry in the Oracle Home branch. This will typically be as follows:

HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_OraDB12Home1

Check that the variable is being read by using the TNSPING command from an operating system prompt.

This will return the error “TNS-03505: Failed to resolve name” because there are no files in the TNS_ADMIN directory. On Windows, you may need to launch a new command prompt to pick up the new TNS_ADMIN value from the registry.

2.  Start the Net Manager. On Linux, run netmgr from an operating system prompt; on Windows, launch it from the Start menu. The top line of the Net Manager window will show the location of the Oracle Net files. If this is not the new directory, the TNS_ADMIN variable has not been set correctly.

A.  Create a new listener by expanding the Local branch of the navigation tree, highlighting Listeners, and clicking +.

B.  Enter a listener name, NEWLIST, and click OK.

C.  Click Add Address.

D.  For Address 1, choose TCP/IP as the protocol and enter 127.0.0.1 as the host and 2521 as the port. Figure 14-7 shows this.

Figure 14-7    Creating a listener with the Net Manager

3.  Create a new service name by highlighting Service Naming in the navigation tree and clicking +.

A.  Enter NEW as the net service name and click Next.

B.  Select TCP/IP as the protocol and click Next.

C.  Enter 127.0.0.1 as the hostname and 2521 as the port and then click Next.

D.  Enter SERV1 as the service name and click Next.

E.  Click Finish. If you try the test, it will fail at this time. Figure 14-8 shows this.

Figure 14-8    Creating a tnsnames service name alias with the Net Manager

4.  Save the configuration by clicking File and Save Network Configuration. This will create the listener.ora and tnsnames.ora files in the TNS_ADMIN directory.

Use an editor to check the two files.

LISTENER.ORA will look like this:

TNSNAMES.ora will look like this:

5.  From an operating system prompt, start the listener with lsnrctl start newlist.

6.  From an operating system prompt, test the connect string with tnsping new.

7.  Connect to your database using operating system authentication, bypassing any listener, with sqlplus / as sysdba.

8.  Set the service_names and local_listener parameters for the running instance (memory only, not the parameter file) and register the new service name with the new, nondefault listener.

9.  From an operating system prompt, confirm that the new service has registered with the new listener with lsnrctl services newlist.

10.  Confirm that the new network environment is functional by logging on.

11.  Back out the changes to revert to default operation.

A.  Restart the database to return the parameters changed in step 8 to their default values.

B.  Stop the listener with lsnrctl stop newlist.

C.  Unset the TNS_ADMIN variable. On Linux, export TNS_ADMIN=''. On Windows, remove the TNS_ADMIN registry key.

Understand Database Resident Connection Pooling

The Oracle Net architecture described previously is the dedicated server architecture, where each connect request from a user process goes to a listener, and the listener spawns a server process. The server process persists until the session terminates and is dedicated to serving that one session. This architecture is fine for a client-server environment where users log on and stay connected for a long time, making use of the session repeatedly.

Many applications do not follow this model. They have a large number of sessions that may be very short lived and may not log on/off in an orderly fashion. Web applications typify this situation. Many web applications use connection pooling to manage this situation. The application server middle tier software establishes a relatively small number of persistent sessions and passes temporary use of them to users on demand. Using the Database Resident Connection Pool (DRCP) is a technique for implementing similar functionality for middle tier software that cannot do connection pooling, such as some Apache-based products. A DRCP can scale up to tens of thousands of concurrent sessions, which would be beyond the capability of almost any hardware if the dedicated server architecture is being used.

A DRCP is enabled by default, configured to launch a minimum of 4 server processes, up to a maximum of 40. Idle servers will be terminated after 300 seconds. To make use of the pool with Easy Connect name resolution, simply add POOLED to the end of the connect string. Here’s an example:

Or you can go through a tnsnames.ora alias such as this:

Configure Communication Between Databases

So far, we’ve discussed Oracle Net in the context of users connecting to database instances. Oracle Net can also be used for communications between databases. In other words, a user session against one database can execute SQL statements against another database. This is done through a database link. There are several options for creating database links (all to do with security), but a simple example is shown here:

This defines a database link from the current database to a remote database identified by the tnsnames.ora connect string PROD and has embedded within it the logon credentials to be used when invoking code through the link. The link exists in the current user’s schema, SCOTT, and only SCOTT can use it. When a statement such as the following is issued, the session will transparently launch a session against the remote database, log on to it as the user FRED, and run the query there:

The results will be sent back to the local database session and then returned to the user process. Note that the name resolution occurs on the server, not the client. The client does not need any information regarding the connection details of the remote server; these details will need to be available in a tnsnames.ora file on the server side.

Exercise 14-2: Create and Use a Database Link    This exercise assumes that only one database is available, so the link will be a loopback link (out of the one database and back into it). Follow these steps:

1.  Connected as user SYSTEM, create a database link.

(Substitute a working tnsnames alias for 'orcl'. Note that this must be enclosed in single quotes.)

2.  Test the link.

3.  Troubleshoot.

The most common problems with database links are that the username/password embedded in the link are wrong and that the tnsnames alias does not function. Other possible issues are that the listener is down and that the database dynamic registration with the listener has not yet happened.

Two-Minute Drill

Configure Oracle Net Services

•  Oracle Net is configured with a set of three files: tnsnames.ora on the client side, listener.ora on the server side, and (optionally) sqlnet.ora on both sides. The database listener process spawns server sessions in response to connection requests from clients.

•  The usual architecture is a dedicated server. There is one server process per user process, and the session consists of a persistent connection between the two. All session data is stored in PGA. An alternative is the shared server architecture, where user processes have persistent connections to dispatchers, which pass requests to a pool of shared server processes. In this architecture, most of the session information is stored in SGA rather than PGA.

Use Tools for Configuring and Managing the Oracle Network

•  Two configuration tools are provided: the Net Manager and the simpler Net Configuration Assistant. The listener is controlled with the Listener Control utility, lsnrctl.

Configure Client-Side Network

•  Clients need the ability to resolve names into connection details. The details consist of the listening address of the database listener in the form of a hostname or IP address and a port, as well as a database service name or instance name. The usual technique is to use a network alias, resolved by an entry in a tnsnames.ora file.

Understand Database Resident Connection Pooling

•  A DRCP can be used for managing the problem of a large number of typically short-lived connections, such as those that come in through a web site. Rather than spawning processes for each session, the DRCP passes out temporary use of one of a fixed number of persistent server processes on demand.

Configure Communication Between Databases

•  Database links allow a session against one database to run SQL against another database. In effect, the first database becomes a client to the second. When a query is run through a link, a session is launched over Oracle Net from the first session to the linked database. The name resolution necessary to establish the remote session occurs on the server, not the client.

Self Test

1.  Which protocols can Oracle Net 12c use? (Choose all correct answers.)

A.  TCP

B.  UDP

C.  SPX/IPX

D.  SDP

E.  TCP with secure sockets

F.  Named pipes

G.  LU6.2

H.  NetBIOS/NetBEUI

2.  Where is the division between the client and the server in the Oracle environment? (Choose the best answer.)

A.  Between the instance and the database.

B.  Between the user and the user process.

C.  Between the server process and the instance.

D.  Between the user process and the server process.

E.  The client-server split varies, depending on the stage of the execution cycle.

3.  Which of the following statements about listeners is correct? (Choose the best answer.)

A.  A listener can connect you to one instance only.

B.  A listener can connect you to one service only.

C.  Multiple listeners can share one network interface card.

D.  An instance will accept connections only from the listener specified on the local_listener parameter.

4.  You have decided to use local naming. Which file (or files) must you create on the client machine? (Choose the best answer.)

A.  tnsnames.ora and sqlnet.ora.

B.  listener.ora only.

C.  tnsnames.ora only.

D.  listener.ora and sqlnet.ora.

E.  None. You can rely on defaults if you are using TCP and your listener is running on port 1521.

5.  If you stop your listener, what will happen to sessions that connected through it? (Choose the best answer.)

A.  They will continue if you have configured failover.

B.  They will not be affected in any way.

C.  They will hang until you restart the listener.

D.  You cannot stop a listener if it is in use.

E.  The sessions will error out.

6.  Study this tnsnames.ora file:

Which of the following statements are correct about the connect strings test, prod, and dev? (Choose all correct answers.)

A.  All three are valid.

B.  All three can succeed only if the instances are set up for dynamic instance registration.

C.  The test connection will fail because the connect string doesn’t match the service name.

D.  There will be a port conflict on serv2 because prod and dev try to use the same port.

7.  Consider this line from a listener.ora file:

What will happen if you issue the following connect string?

(Choose the best answer.)

A.  You will be connected to the instance L1.

B.  You will be connected to an instance only if dynamic instance registration is working.

C.  You can’t tell—it depends on how the client side is configured.

D.  If you are logged on to the server machine, IPC will connect you to the local instance.

E.  The connection will fail if the listener is not started.

8.  Which of these tools can configure a listener.ora file? (Choose two answers.)

A.  The Database Configuration Assistant

B.  Database Express

C.  The lsnrctl utility

D.  The Net Configuration Assistant

E.  The Net Manager

9.  Consider this tnsnames.ora net service name:

What will happen if shared server is configured and this net service name is used? (Choose the best answer.)

A.  The connect attempt will fail.

B.  The connect will succeed with a shared server connection.

C.  The connect will succeed with a dedicated server connection.

D.  The connect will succeed only for SYSDBA or SYSOPER logons.

10.  Under what circumstances would a connection through a Database Resident Connection Pool (SERVER=POOLED) connection be suitable?

A.  When an application server needs a pool of persistent connections

B.  When many short-lived connections share a schema

C.  When many short-lived connections connect to different schemas

D.  When many persistent connections make infrequent requests

11.  When updating rows locally and through a database link in one transaction, what must you do to ensure a two-phase commit?

A.  Nothing special because two-phase commit is automatic.

B.  Issue a COMMIT locally first and then through the database link.

C.  Issue a COMMIT through the link and then locally.

D.  It is not possible to interleave local and remote updates.

Self Test Answers

1.      A, D, E, F. TCP, SDP, TCPS, and NMP are the supported protocols with the current release.
    B, C, G, and H are incorrect. B and H are incorrect because UDP and NetBIOS/NetBEUI have never been supported. C and G are incorrect because SPX and LU6.2 are no longer supported.

2.      D. The client-server split is between the user process and server process.
    A, B, C, and E are incorrect. These all misrepresent the client-server architecture.

3.      C. Many listeners can share one address, if they use different ports.
    A, B, and D are incorrect. A is incorrect because one listener can launch sessions against many instances. B is incorrect because a listener can connect you to a registered service. D is incorrect because the local_listener parameter controls which listener the instance will register with dynamically; it will also accept connections from any listener that has it statically registered.

4.      C. This is the only required client-side file for local naming.
    A, B, D, and E are incorrect. A is incorrect because SQLNET.ORA is not essential. B and D are incorrect because they refer to server-side files. E is incorrect because some configuration is always necessary for local naming (though not for Easy Connect).

5.      B. The listener establishes connections but is not needed for their maintenance.
    A, C, D, and E are incorrect. These are all incorrect because they assume that the listener is necessary for the continuance of an established session.

6.      A and B. All three are valid but will work only if the services are registered with the listeners.
    C and D are incorrect. C is incorrect because there doesn’t need to be a connection between the alias used in a connect string and the service name. D is incorrect because many services can be accessible through a single listening port.

7.      C. Some client-side configuration is necessary, and without knowing what it is, you have no idea what will happen.
    A, B, D, and E are incorrect. A is incorrect because the connect string could connect to any instance. B is incorrect because although the listener L1 must use dynamic registration, this is not enough. D is incorrect because the use of IPC to bypass the listener is not relevant. E is incorrect because (although certainly true) you don’t know if it is relevant.

8.      D, E. Both netca and netman offer a graphical interface for editing the listener.ora file.
    A, B, and C are incorrect. A is incorrect because the Database Configuration Assistant (DBCA) cannot edit a listener, though it will edit the tnsnames.ora file. B is incorrect because Database Express has no capability for editing any file. C is incorrect because the lsnrctl utility can control a listener but not configure the listener.ora file.

9.      C. The SERVER=DEDICATED directive requests a dedicated server, even if shared server is configured.
    A, B, and D are incorrect. A and B are incorrect because the client configuration takes precedence over the server configuration. D is incorrect because anyone can use this connect string. It will be necessary to use it rather than a shared server connection for SYSDBA connections; otherwise, startup/shutdown commands will fail.

10.      B. This is exactly the environment for which DRCP is designed: many short connections to a shared schema.
    A, C, and D are incorrect. A is incorrect because an application server would manage the pool itself. C is incorrect because a DRCP will pool connections to the same schema. D is incorrect because persistent connections would tie up the pooled servers.

11.      A. Two-phase commit for distributed transactions is fully automatic.
    B, C, and D are incorrect. B and C are incorrect because a distributed transaction is, syntactically, committed exactly as a local transaction. D is incorrect because distributed transactions are no problem in the Oracle environment.