Oracle Database 12c DBA Handbook (2015)

Distributing computing power across servers and sharing information across networks greatly enhances the value of the computing resources available. Instead of being a stand-alone server, the server becomes an entry point for intranets, the Internet, and associated websites.

Oracle’s networking tool, Oracle Net Services (Oracle Net), can be used to connect to distributed databases. Oracle Net facilitates the sharing of data between databases, even if those databases are on different types of servers running different operating systems and communications protocols. It also allows for client/server applications to be created; the server can then function primarily for database I/O while the application can be fielded to a middle-tier application server. Also, the data presentation requirements of an application can be moved to front-end client machines. In this chapter, you will see how to configure, administer, and tune Oracle Net.

The installation and configuration instructions for Oracle Net depend on the particular hardware, operating system, and communications software you are using. The material provided here will help you get the most out of your database networking, regardless of your configuration.

Overview of Oracle Net

Using Oracle Net distributes the workload associated with database applications. Because many database queries are performed via applications, a server-based application forces the server to support both the CPU requirements of the application and the I/O requirements of the database (see Figure 17-1a). Using a client/server configuration (also referred to as a two-tier architecture) allows this load to be distributed between two machines. The first, called the client, supports the application that initiates the request from the database. The back-end machine on which the database resides is called the server. The client bears the burden of presenting the data, whereas the database server is dedicated to supporting queries, not applications. This distribution of resource requirements is shown in Figure 17-1b.

FIGURE 17-1. Client/server architecture

When the client sends a database request to the server, the server receives and executes the SQL statement that is passed to it. The results of the SQL statement, plus any error conditions that are returned, are then sent back to the client. Because of the client resources required, the client/server configuration sometimes is dubbed fat-client architecture. Although workstation costs have dropped appreciably over recent years, the cost impact to a company can still be substantial.

The more common, cost-effective architecture used with Oracle Net is a thin-client configuration (also referred to as a three-tier architecture). The application code is housed and executed using Java applets on a separate server from the database server. The client resource requirements become very low, and the cost is reduced dramatically. The application code becomes isolated from the database. Figure 17-2 shows the thin-client configuration.

FIGURE 17-2. Thin-client architecture

The client connects to the application server. Once the client is validated, display management code is downloaded to the client in the form of Java applets. A database request is sent from the client through the application server to the database server; the database server then receives and executes the SQL statement that is passed to it. The results of the SQL statement, plus any error conditions that are returned, are then sent back to the client through the application server. In some versions of the three-tier architecture, some of the application processing is performed on the application server and the rest is performed on the database server. The advantage of a thin-client architecture is that you have low resource requirements and maintenance on the client side, medium resource requirements and central maintenance on the application server, and high resource but lower maintenance requirements on one or more back-end database servers.

In addition to client/server and thin-client implementations, server/server configurations are often needed. In this type of environment, databases on separate servers share data with each other. You can then physically isolate each server from every other server without logically isolating the servers. A typical implementation of this type involves corporate headquarters’ servers that communicate with departmental servers in various locations. Each server supports client applications, but it also has the ability to communicate with other servers in the network. This architecture is shown in Figure 17-3.

FIGURE 17-3. Server/server architecture

When one of the servers sends a database request to another server, the sending server acts like a client. The receiving server executes the SQL statement passed to it and returns the results plus error conditions to the sender.

When run on the clients and the servers, Oracle Net allows database requests made from one database (or application) to be passed to another database on a separate server. In most cases, machines can function both as clients and as servers; the only exceptions are operating systems with single-user architectures, such as network appliances. In such cases, those machines can only function as clients.

The end result of an Oracle Net implementation is the ability to communicate with all databases that are accessible via the network. You can then create synonyms that give applications true network transparency: The user who submits the query will not know the location of the data that is used to resolve it. In this chapter, you will see the main configuration methods and files used to manage inter-database communications, along with usage examples. You will see more detailed examples of distributed database management in Chapter 18.

Each object in a database is uniquely identified by its owner and name. For example, there will only be one table named EMPLOYEE owned by the user HR; there cannot be two tables of the same name and type within the same schema.

Within distributed databases, two additional layers of object identification must be added. First, the name of the instance that accesses the database must be identified. Next, the name of the server on which that instance resides must be identified. Putting together these four parts of the object’s name—its server, its instance, its owner, and its name—results in a global object name. In order to access a remote table, you must know the table’s global object name. DBAs and application administrators can set up access paths to automate the selection of all four parts of the global object name. In the following sections, you will see how to set up the access paths used by Oracle Net.

The foundation of Oracle Net is the Transparent Network Substrate (TNS), which resolves all server-level connectivity issues. Oracle Net relies on configuration files on the client and the server to manage the database connectivity. If the client and server use different communications protocols, the Oracle Connection Manager (described in a later section of this chapter) manages the connections. The combination of the Oracle Connection Manager and the TNS allows Oracle Net connections to be made independent of the operating system and communications protocol run by each server. Oracle Net also has the capability to send and receive data requests in an asynchronous manner; this allows it to support the shared server architecture.

Connect Descriptors

The server and instance portions of an object’s global object name in Oracle Net are identified by means of a connect descriptor. A connect descriptor specifies the communications protocol, the server name, and the instance’s service name to use when performing the query. Because of the protocol-independence of Oracle Net, the descriptor also includes hardware connectivity information. The generic format for an Oracle Net connect descriptor is shown in the following example, which uses the TCP/IP protocol and specifies a connection to an instance whose service name is LOC on a server named HQ (note that the keywords are protocol specific):

In this connect descriptor, the protocol is set to TCP/IP, the server (HOST) is set to HQ, and the port on that host that should be used for the connection is port 1521 (which is the Oracle default registered port assignment for Oracle Net). The instance name is specified in a separate part of the descriptor as the SID assignment. The instance name or SID can be specified, but neither is required when the service name is specified. When a service name is specified, an instance name is only needed if you want to connect to a specific instance in a RAC database. The SID parameter is used when the service name is not specified as part of the database initialization parameters.

TIP

As part of your security strategy, you can change the default port for the Oracle listener from 1521 to another unused port to potentially thwart hackers. Changing this port may have no impact on legitimate database users, depending on how they connect to the database.

The structure for this descriptor is consistent across all protocols. Also, the descriptors can be automatically generated via the Net Configuration Assistant. As previously noted, the keywords used by the connect descriptors are protocol specific. The keywords to use and the values to give them are provided in the operating system-specific documentation for Oracle Net.

Net Service Names

Users are not expected to type in a connect descriptor each time they want to access remote data. Instead, the DBA can set up net service names (or aliases), which refer to these connect descriptors. Service names are stored in a file called tnsnames.ora. This file should be copied to all servers on the database network. Every client and application server should have a copy of this file as well.

On the server, the tnsnames.ora file should be located in the directory specified by the TNS_ADMIN environment variable. The file is usually stored in a common directory, such as the $ORACLE_HOME/network/admindirectory on Unix or Linux systems. For a Windows server or client, this would be in the \network\admin subdirectory under your Oracle software home directory.

A sample entry in the tnsnames.ora file is shown in the following listing. This example assigns a net service name of LOC to the connect descriptor with the same name given earlier:

A user wishing to connect to the LOC instance on the HQ server can now use the LOC net service name, as shown in this example:

The “@” tells the database to use the net service name that follows it to determine which database to log into. If the username and password are correct for that database, a session is opened there and the user can begin using the database.

Net service names create aliases for connect descriptors, so you do not need to give the net service name the same name as the instance. For example, you could give the LOC instance the service name PROD or TEST, depending on its use within your environment. The use of synonyms to further enhance location transparency will be described in the section “Using Database Links” later in this chapter.

Replacing tnsnames.ora with Oracle Internet Directory

A directory is a specialized electronic database in which you store information about one or more objects. Your e-mail address book is an example of a directory. Within each of your e-mail address entries is information about the contact’s name, e-mail address, home and business addresses, and so on. You can use the address book to locate a specific person with whom you want to correspond.

Oracle provides an electronic database tool called the Oracle Internet Directory (OID) for use in resolving user, server, and database locations as well as password and other important information storage. To support the deployment and maintenance of thousands of clients, the emphasis has moved from supporting many separate tnsnames.ora files on distributed machines to supporting one or more directories on centralized machines. See the section “Directory Naming with Oracle Internet Directory,” later in this chapter, for more information about OID.

Listeners

Each database server on the network must contain a listener.ora file. The listener.ora file lists the names and addresses of all the listener processes on the machine and the instances they support. Listener processes receive connections from Oracle Net clients.

A listener.ora file has four parts:

Header section

Protocol address list

Instance definitions

Operational parameters

The listener.ora file is automatically generated by the Oracle Net Configuration Assistant tool (netca on Linux). You can edit the resultant file as long as you follow its syntax rules. The following listing shows sample sections of a listener.ora file—an address definition and an instance definition:

The first portion of this listing contains the protocol address list—one entry per instance. The protocol address list defines the protocol addresses on which a listener is accepting connections, including an interprocess calls (IPC) address-definition section. In this case, the listener is listening for connections to the service identified as loc.world as well as any requests coming from the HR machine on PORT 1521 using the TCP/IP protocol. The .world suffix is the default domain name for Oracle Net connections.

NOTE

Using SID_LIST_LISTENER is not required in Oracle Database 10g or later; it is required in previous versions of Oracle Net only if you monitor and manage the instance with Oracle Enterprise Manager.

The second portion of the listing, beginning with the SID_LIST_LISTENER clause, identifies the global database name as defined in the init.ora file for that database, the Oracle software home directory for each instance the listener is servicing, and the instance name or SID. The GLOBAL_DBNAME comprises the database name and database domain. The SID_LIST descriptor is retained for static database registration, for backward compatibility with earlier versions, and for use by Oracle Enterprise Manager. Databases dynamically register with the listener on database startup; a default installation of Oracle Database 12c on Linux only includes a listener.ora file with the LISTENER parameter, as in this sample listener.ora file from the RPT12C database used in examples throughout this book:

For the listener on the server dw (for the database instance RPT12C), this listener.ora file does not even need to exist unless you want to add additional listeners or provide static registration entries: if there is no listener.ora file, the default listener name is LISTENER, the default value for PROTOCOL is TCP, the HOST parameter defaults to the server’s host name, and the default value for PORT (the TCP/IP port number) is 1521. If you are using Oracle Cloud Control 12c to monitor this server and its databases, the agent software will add lines to this file as you can see in the previous example.

NOTE

If you change the Oracle software home directory for an instance, you need to change the listener.ora file for the server.

listener.ora Parameters

The listener.ora file supports a large number of parameters. The parameters should each be suffixed with the listener name. For example, the default listener name is LISTENER, so the LOG_FILE parameter is named LOG_FILE_LISTENER. The parameters in Table 17-1 apply whether you’re using the Automatic Diagnostic Repository (ADR) or not.

TABLE 17-1. listener.ora Parameters, ADR or non-ADR

You can modify the listener parameters after the listener has been started. If you use the SAVE_CONFIG_ON_STOP option, any changes you make to a running listener will be written to its listener.ora file. Examples of controlling the listener behavior are presented later in this chapter.

If you are using ADR (by setting DIAG_ADR_ENABLED_listener_name to ON), then the parameters in Table 17-2 apply and non-ADR debugging parameters are ignored.

TABLE 17-2. listener.ora Parameters, ADR

As you can see, using ADR means you have to specify fewer parameters in listener.ora in addition to letting Oracle manage your log and trace files for you. If you are not using ADR, then the listener.ora tracing-related parameters listed in Table 17-3 apply.

TABLE 17-3. listener.ora Parameters, non-ADR

Using the Oracle Net Configuration Assistant

The Oracle Net Configuration Assistant performs the initial network configuration steps after the Oracle software installation and automatically creates the default, basic configuration files. You can use the Oracle Net Manager tool to administer network services. The tools have graphical user interfaces for configuring the following elements:

Listener

Naming methods

Local net service names

Directory usage

Figure 17-4 shows the initial screen of the Oracle Net Configuration Assistant. As shown in Figure 17-4, Listener Configuration is the default option.

FIGURE 17-4. Oracle Net Configuration Assistant: Welcome screen

Configuring the Listener

Using the Oracle Net Configuration Assistant, you can configure a listener easily and quickly. When you select the Listener configuration option on the Welcome screen and click Next, you are given the choice to add, reconfigure, delete, or rename a listener. Select the Add option and click Next. The next step is to select a listener name. Figure 17-5 shows the Listener Name screen with the default listener name, LISTENER, displayed.

FIGURE 17-5. Listener Configuration, Listener Name screen

After selecting a listener name and clicking Next, you must select a protocol, as shown in Figure 17-6. The default protocol selected is TCP.

FIGURE 17-6. Listener Configuration, Select Protocols screen

After you select a protocol and click Next, you must designate a port number on which the new listener will listen. The default port number presented is 1521, but you are given the option to designate another port. After you click Next, the next three screens include a prompt to configure another listener, a request to indicate a listener you want to start, and a confirmation that the listener configuration is completed for this listener.

Naming Methods Configuration

Choosing the Naming Methods Configuration option of the Oracle Net Configuration Assistant (refer to Figure 17-4) enables you to configure net service names. There are many options available for naming methods. A couple of them are listed here:

If you accept the Host Name option, you see an informational screen advising you that Host Name naming does not require any additional configuration “at this time.” You are instructed that any time you add a database service in the future, you must make an entry in your TCP/IP host name resolution system.

Once you have selected the naming methods, the Oracle Net Configuration Assistant displays a confirmation screen.

Local Net Service Name Configuration

You can choose the Oracle Net Configuration Assistant’s Local Net Service Name Configuration option (refer to Figure 17-4) to manage net service names. Five options are available for the Local Net Service Name Configuration tool:

Add

Reconfigure

Delete

Rename

Test

For the Add option, you must first specify the database version you are going to access and the service name. Once you have entered the global service name or SID, you are prompted to enter the protocol. You must specify the machine name of the host and designate the listener port.

The next screen offers you the option to verify that the Oracle database you have specified can be successfully reached. You can choose to skip or perform the connection test. Once you have either chosen to test the connection, and it has completed successfully, or opted to skip the test, you are prompted to specify the service name for the new net service. By default, the service name you entered earlier is used, but you can specify a different name if you so choose. Finally, you are notified that your new local service name has been successfully created, and you are asked if you want to configure another one.

You can use the Reconfigure option to select and modify an existing net service name. You are prompted to select an existing net service name. The Database Version screen, the Service Name screen, and the Select Protocols screen are used as well as the TCP/IP Protocol screen. The option to test the database connection is offered, as well as a screen to enable you to rename the net service you are reconfiguring.

The Test option enables you to verify that your configuration information is correct, that the database specified can be reached, and that a successful connection can be made.

Directory Usage Configuration

A directory service provides a central repository of information for the network. The most common directory forms support the Lightweight Directory Access Protocol (LDAP). An LDAP server can provide the following features:

Store net service names and their location resolution

Provide global database links and aliases

Act as a clearinghouse for configuration information for clients across the entire network

Aid in configuring other clients

Update client configuration files automatically

House client information such as usernames and passwords

The Oracle Net Configuration Assistant’s Directory Usage Configuration option supports both Oracle Internet Directory and Microsoft Active Directory. The Directory Type screen is shown in Figure 17-7 in a Linux environment; you would see an option for Microsoft Active Directory if you were running Oracle on Windows Server.

FIGURE 17-7. Directory Usage Configuration, Directory Type screen

Next, as shown in Figure 17-8, you are prompted to supply the directory service location host name, port, and SSL port. By default, the port is 389 and the SSL port is 636. Once you have specified this information, the tool attempts to connect to your directory repository and verify that you have already established a schema and a context. If you have not, you will receive an error message instructing you to do so.

FIGURE 17-8. Specifying an LDAP directory service

Using the Oracle Net Manager

There is some overlap between the Oracle Net Configuration Assistant described in the preceding section and the Oracle Net Manager utility. Both tools can be used to configure a listener or a net service name. Both provide ease in configuring a Names service, local profile, and directory service. The Oracle Net Manager is not quite as user friendly but provides a more in-depth configuration alternative. You start the Oracle Net Manager on Linux with the netmgr command.

As shown in Figure 17-9, the opening screen of the Oracle Net Manager lists the basic functionality it provides, as follows:

FIGURE 17-9. Oracle Net Manager Console configuration window

Naming Allows you to define simple names to identify the location of a service

Naming Methods Allows you to define the way the simple names map to connect descriptors

Listeners Supports the creation and configuration of listeners

You can use Oracle Net Manager to manage your configuration files and test your connections. Options such as Oracle Advanced Security can be managed via the Oracle Net Manager. The Oracle Advanced Security option provides end-to-end encryption of data in a distributed environment. By default, your data will travel in clear text across the network unless you use Oracle’s encryption or a hardware-based encryption.

You can create a new net service name for your tnsnames.ora file via the Oracle Net Service Names Wizard. Once you have specified a net service name, you are prompted to select the network protocol you want to use. The options are as follows:

TCP/IP (Internet Protocol)

TCP/IP with SSL (Secure Internet Protocol)

Named Pipes (Microsoft Networking)

IPC (Local Database)

The Oracle Net Manager will prompt you for each of the parameters required to establish a database connection and will modify your local tnsnames.ora file to reflect the information you provide. The information you will be prompted for is host, port number, service or SID name (depending on the Oracle version), and the connection type (either database default, shared server, or dedicated server). Finally, you are given the opportunity to test the new service name. You can also test existing net service names by selecting the net service name from the displayed list of services and then selecting the Test Connection option from the menu options.

The simpler you keep your client and server configurations, and the closer you adhere to the default values, the simpler the management of your configuration files will be. The Oracle Net Manager simplifies your configuration file administration. One word of caution: If you are using your listener to listen for connections from the Internet through a firewall, be sure that you do not leave a listener listening on the default port, 1521, because a hole through your firewall can leave you open to potential remote listener reconfiguration. An unsecured listener using default values can enable a hacker to obtain database information that could compromise your site.

Starting the Listener Server Process

The listener process is controlled by the Listener Control utility, executed via the lsnrctl command. The options available for the lsnrctl command are described in the next section. To start the listener, use this command:

This command will start the default listener (named LISTENER). If you wish to start a listener with a different name, include that listener’s name as the second parameter in the lsnrctl command. For example, if you created a listener called ANPOP_LSNR, you could start it via the following command:

In the next section you will find descriptions of the other parameters available for the Listener Control utility.

After starting a listener, you can check that it is running by using the status option of the Listener Control utility. The following command can be used to perform this check:

If the listener is named anything other than LISTENER in the listener.ora file, you must add the name of the listener to the status command. For example, if the listener is named ANPOP_LSNR, the command is

The status output will show if the listener has been started and the services it is currently supporting, as defined by its listener.ora file. The listener parameter file and its log file location will be displayed.

If you wish to see the operating system-level processes that are involved, use the following command. This example uses the Unix ps -ef command to list the system’s active processes. The grep tnslsnr command then eliminates those rows that do not contain the term “tnslsnr.”

This output shows two processes: the listener process and the process that is checking for it. The first line of output is wrapped to the second line and may be truncated by the operating system.

Controlling the Listener Server Process

You can use the Listener Control utility, lsnrctl, to start, stop, and modify the listener process on the database server. Its command options are listed in Table 17-4. Each of these commands may be accompanied by a value; for all except the set password command, that value will be a listener name. If no listener name is specified, the default (LISTENER) will be used. Once within lsnrctl, you can change the listener being modified via the set current_listener command.

TABLE 17-4. Listener Control (lsnrctl) Utility Commands

TIP

Oracle best practices dictate not using a listener password in Oracle Database 12c. The default authentication mode for the listener is local OS authentication, which requires the listener administrator to be a member of the local dba group.

You can enter the lsnrctl command by itself to enter the lsnrctl utility shell, from which all other commands can then be executed.

The command options listed in Table 17-4 give you a great deal of control over the listener process, as shown in the following examples. In most of these examples, the lsnrctl command is first entered by itself. This places the user in the lsnrctl utility (as indicated by the LSNRCTL prompt). The rest of the commands are entered from within this utility. The following examples show the use of the lsnrctl utility to stop, start, and generate diagnostic information about the listener.

To stop the listener:

To show status information for the listener:

To list the status of a listener on another host, add a service name from that host as a parameter to the status command. The following example uses the HQ service name shown earlier in this chapter:

To list version information about the listener:

To list information about the services supported by the listener:

The Oracle Connection Manager

The Oracle Connection Manager portion of Oracle Net acts as a router used to establish database communication links between otherwise incompatible network protocols as well as to take advantage of multiplexing and access control.

The advantage of the Oracle Connection Manager is that all servers do not have to use the same communications protocol. Each server can use the communications protocol that is best suited to its environment and will still be able to transfer data back and forth with other databases. This communication takes place regardless of the communications protocols used on the remote servers; the Oracle Connection Manager takes care of the differences between the protocols. The protocols supported by the Oracle Connection Manager are IPC, Named Pipes, SDP, TCP/IP, and TCP/IP with SSL.

You can use multiple access paths to handle different client requests. The Oracle Connection Manager will select the most appropriate path based on path availability and network load. The relative cost of each path is specified via the Network Manager utility when the Oracle Connection Manager is set up.

In an intranet environment, the Oracle Connection Manager can be used as a firewall for Oracle Net traffic. You can establish filtering rules to enable or disable specific client access using the Oracle Connection Manager. The filtering rules can be based on any of the following criteria:

Destination host names or IP addresses for servers

Destination database service name

Source host names or IP addresses for clients

Whether the client is using the Oracle Advanced Security option

The Oracle Connection Manager is used to enhance your firewall security by filtering out client access based on one or more aspects of the filtering rules you create. For example, you could specify that an IP address is to be refused access using the CMAN_RULES parameter within the cman.ora file.

The file sqlnet.ora may be used to specify additional diagnostics beyond the default diagnostics provided.

Using the Oracle Connection Manager

Oracle Net uses the Oracle Connection Manager to support connections within homogenous networks, reducing the number of physical connections maintained by the database. Two main processes and a control utility are associated with the Oracle Connection Manager, as follows:

The CMGW Process

The Connection Manager Gateway (CMGW) process registers itself with the CMADMIN process and listens for incoming connection requests. By default, this process listens on port 1630 using the TCP/IP protocol. The CMGW process initiates connection requests to listeners from clients and relays data between the client and server.

The CMADMIN Process

The multithreaded Connection Manager Administrative (CMADMIN) process performs many tasks and functions. The CMADMIN processes CMGW registrations and registers source route addressing information about the CMGW and listeners. The CMADMIN process is tasked with identifying all listener processes that support at least one database. Using Oracle Internet Directory, the CMADMIN performs the following tasks:

Locates local servers

Monitors registered listeners

Maintains client address information

Periodically updates the Connection Manager’s cache of available services

The CMADMIN process handles source route information about the CMGW and listeners.

Configuring the Oracle Connection Manager

The cman.ora file, located by default in the $ORACLE_HOME/network/admin directory on a Unix system and in %ORACLE_HOME%\network\admin on a Windows system, contains the configuration parameters for the Oracle Connection Manager. The file contains protocol addresses of the listening gateway process, access control parameters, and profile or control parameters.

The complete set of cman.ora parameters is shown in Table 17-5.

TABLE 17-5. cman.ora Parameters

Using the Connection Manager Control Utility (CMCTL)

The Connection Manager Control Utility provides administrative access to CMADMIN and CMGW. The Connection Manager is started via the cmctl command. The command syntax is

The default startup command from an operating system prompt is as follows:

The commands are broken into four basic types:

Operational commands such as start

Modifier commands such as set

Informational commands such as show

Command utility operations such as exit

Using the parameter REMOTE_ADMIN, you can control, but not start, remote managers. Unlike the Listener utility discussed earlier in this chapter, you cannot interactively set a password for the Oracle Connection Manager. To set a password for this tool, you put a plain-text password in the cman.ora file. The available command options for the cmctl command are shown in Table 17-6.

TABLE 17-6. cmctl Command Options

If the Connection Manager has been started, any client that has SOURCE_ROUTE set to YES in its tnsnames.ora file can use the Connection Manager. The Connection Manager reduces system resource requirements by maintaining logical connections while reusing physical connections.

Directory Naming with Oracle Internet Directory

Oracle Internet Directory facilitates support for LDAP-compliant directory servers for centralized network names resolution management in a distributed Oracle network. For localized management, you can still use the tnsnames.ora file.

Oracle Internet Directory Architecture

The file ldap.ora, located in the $ORACLE_HOME/network/admin directory on a Unix system and in %ORACLE_HOME%\network\admin in a Windows environment, stores the configuration parameters to access a directory server. Oracle supports both the Oracle Internet Directory and Microsoft Active Directory LDAP protocols.

To resolve a connect descriptor using a centralized directory server, the steps are as follows:

1. Oracle Net, on behalf of the client, contacts the directory server to obtain the resolution for the connect identifier to a connect descriptor.

2. The directory server takes the connect identifier, locates the associated connect descriptor, and returns the descriptor to Oracle Net.

3. Oracle Net uses the resolved descriptor to make the connection request to the correct listener.

The directory server uses a tree structure in which to store its data. Each node in the tree is an entry. A hierarchical structure of entries is used, called a directory information tree (DIT), and each entry is identified by a unique distinguished name (DN) that tells the directory server exactly where the entry resides. DITs can be structured to use an existing Domain Name System (DNS), organizational or geographical lines, or Internet naming scheme.

Using a DIT that is organized along organizational lines, for example, the DN for the HR server could be this: (dn: cn=HR, cn=OracleContext, dc=us, dc=ourcompany, dc=com). The lowest component of a DN is placed at the leftmost location of the DIT and moved progressively up the tree. The following illustration shows the DIT for this example.

The commonly used LDAP attributes are as follows:

CommonName (cn) Common name of an entry

Country (c) Name of the country

Domain component (dc) Domain component

Organization (o) Name of organization

OrganizationalUnitName (ou) Name of unit within the organization

NOTE

The value cn=OracleContext is a special entry in the directory server that supports directory-enabled features such as directory naming. The Oracle Context is created using the Oracle Net Configuration Assistant discussed earlier in this chapter.

Setting Up an Oracle Internet Directory

As detailed earlier, you can use the Oracle Net Configuration Assistant or the Oracle Net Manager to perform the initial configuration tasks. Once the directory schema and Oracle Context have been established, you can begin to register service names with the directory service using the Oracle Net Manager. The Oracle Context area is the root of the directory subtree where all information relevant to Oracle software is stored.

When the Oracle Context is installed, two entities are created: OracleDBCreators and OracleNetAdmins. The OracleDBCreators entity with a DN of (cn=OracleDBCreators, cn=OracleContext) is created. Any user who is a member of OracleDBCreators can register a database server entry or directory client entry using the Oracle Database Configuration Assistant. A user assigned as a member of OracleNetAdmins can create, modify, and delete net service names and modify Oracle Net attributes of database servers using the Oracle Net Manager. If you are a directory administrator, you can add users to these groups.

Clients who want to look up information in the directory must meet the following minimum requirements:

They must be configured to use the directory server.

They must be able to access the Oracle Net entries in the Oracle Context.

They must have anonymous authentication with the directory server.

The clients can use the common names of database servers and net service entries to perform the lookups, or additional directory location information may be required in the connection string.

Using Easy Connect Naming

Starting with Oracle Database 10g, you can use the easy connect naming method to eliminate the need for service name files in a TCP/IP environment; in fact, you may not need a tnsnames.ora file at all. Clients can connect to a database server by specifying the full connection information in their connect strings in this format as follows with the SQL*Plus CONNECT command:

The connection identifier elements are as follows:

For example, you can connect to the LOC service with this syntax:

In order to use easy connect naming, you must have Oracle Net Services 10g (or later) software installed on your client. You must be using the TCP/IP protocol, and no features requiring a more advanced connect descriptor are supported.

CAUTION

Oracle Database 11g and 12c clients and database no longer support the use of Oracle Names; however, earlier versions of the client can still use Oracle Names to resolve naming for an Oracle Database 10g database.

For URL or JDBC connections, prefix the connect identifier with a double slash (//):

Easy connect naming is automatically configured at installation. In your sqlnet.ora file, make sure EZCONNECT is added to the list of values in the NAME.DIRECTORY_PATH parameter listing; the default contents of sqlnet.ora for client installations of Oracle Database 11g and later have these two lines:

In other words, when resolving service names, the Oracle client will first attempt a lookup using the tnsnames.ora file, then use Easy Connect.

Using Database Links

You should create database links to support frequently used connections to remote databases. Database links specify the connect descriptor to be used for a connection, and they may also specify the username to connect to in the remote database.

A database link is typically used to create local objects (such as views or synonyms) that access remote databases via server/server communications. The local synonyms for remote objects provide location transparency to the local users. When a database link is referenced by a SQL statement, it opens a session in the remote database and executes the SQL statement there. The data is then returned, and the remote session may stay open in case it is needed again. Database links can be created as public links (by DBAs, making the links available to all users in the local database) or as private links.

The following example creates a private database link called HR_LINK:

The CREATE DATABASE LINK command, as shown in this example, has three parameters:

The name of the link (HR_LINK, in this example)

The account to connect to

The net service name

A public database link can be created by adding the keyword PUBLIC to the CREATE DATABASE LINK command, as shown in the following example:

NOTE

Best practices for public database links would favor including the USING clause but not the CONNECT TO clause. You could then create a private database link with the same name that includes the CONNECT TO clause but not the USING clause. Subsequent changes to the service name for the data would require re-creating only the public link, while the private links and the user passwords would be unchanged.

If the LOC instance is moved to a different server, you can redirect the database links to LOC’s new location simply by distributing a tnsnames.ora file that contains the modification or by revising the listing in the directory server. You can generate the revised entry for the tnsnames.ora file or directory server by using either the Oracle Net Configuration Assistant tool or the Oracle Net Manager, described previously in this chapter.

To use these links, simply add them as suffixes to the table names in commands. The following example creates a local view of a remote table, using the HR_LINK database link:

The FROM clause in this example refers to EMPLOYEE@HR_LINK. Because the HR_LINK database link specifies the server name, instance name, and owner name, the global object name for the table is known. If no account name had been specified, the user’s account name would have been used instead. If HR_LINK was created without the CONNECT TO clause, the current username and password would be used to connect to the remote database.

In this example, a view was created in order to limit the records that users could retrieve. If no such restriction is necessary, a synonym can be used instead. This is shown in the following example:

Local users who query the local public synonym EMPLOYEE will automatically have their queries redirected to the EMPLOYEE table in the LOC instance on the HQ server. Location transparency has thus been achieved.

By default, a single SQL statement can use up to four database links. This limit can be increased via the OPEN_LINKS parameter in the database’s SPFILE or init.ora file. If this value is set to 0, no distributed transactions are allowed.

Tuning Oracle Net

Tuning Oracle Net applications is fairly straightforward: Wherever possible, reduce the amount of data that is sent across the network, particularly for online transaction-processing applications. Also, reduce the number of times data is requested from the database. The basic procedures that should be applied include the following:

The use of distributed objects, such as materialized views, to replicate static data to remote databases.

The use of procedures to reduce the amount of data sent across the network. Rather than data being sent back and forth, only the procedure’s error status is returned.

The use of homogenous servers wherever possible to eliminate the need for connection managers.

For OLTP applications only, the use of shared servers to support more clients with fewer processes.

The buffer size used by Oracle Net should take advantage of the packet sizes used by the network protocols (such as TCP/IP). If you send large packets of data across the network, the packets may be fragmented. Because each packet contains header information, reducing packet fragmentation reduces network traffic.

You can tune the size of the service layer buffer. The specification for the service layer data buffer is called SDU (Session Data Unit); if it is changed, this must be specified in your client and server configuration files. Oracle Net builds data into buffers the size of the SDU, so altering that size may improve your performance. The default size for the SDU is 8192 in Oracle Database 11g, and 2048 in earlier versions. For Oracle Database 12c, the default SDU size is 8192 for the client and a dedicated server; for a shared server it is 65535. If you will frequently be sending messages that are much larger than that, you can increase the SDU (up to a maximum of 2MB).

To configure the client to use a non-default SDU, add the new SDU setting to the client configuration files. For the change to apply to all connections, add the following parameter to the sqlnet.ora file:

For the change to apply to only specific service names, modify their entries in the tnsnames.ora file:

On the database server, configure the default SDU setting in the sqlnet.ora file:

For shared server processes, add the SDU setting to the DISPATCHERS setting in the instance initialization parameter file:

For dedicated server processes, edit the entries in the listener.ora file:

Oracle Net Services provides support for the Reliable Datagram Sockets (RDS) and Socket Direct Protocol (SDP) protocols on InfiniBand high-speed networks (such as those found in Oracle Exadata and Exalogic appliances). Applications using SDP place most of the messaging burden on the network interface card, thus reducing the CPU requirements of the application. If you are using an InfiniBand high-speed network (such as for communications among your application tiers), see the Oracle documentation for hardware and software configuration details.

Limiting Resource Usage

To limit the impact of unauthorized users on your system, you can reduce the duration for which resources can be held prior to authentication. The time-limiting parameters listed earlier in this chapter help to mitigate the performance problems caused by these unauthorized accesses. In the listener.ora file, set the INBOUND_CONNECT_TIMEOUT_listener_name parameter to terminate connections that are not authenticated by the listener within the specified time period. Failed connections will be logged to the listener log file. In the server-side sqlnet.ora file, set the SQLNET.INBOUND_CONNECT_TIMEOUT parameter to terminate connection attempts that cannot establish and authenticate connections within the specified interval. Set the server-side SQLNET.INBOUND_CONNECT_TIMEOUT parameter to a higher value than the INBOUND_CONNECT_TIMEOUT_listener_nameparameter in the listener.ora file.

Using Compression

Once you’ve tuned the amount of data that needs to flow from client to server and vice versa, you can leverage a new feature of the Advanced Compression package in Oracle Database 12c: Advanced Network Compression. If you have additional CPU resources on both the client and server, then compressing the actual data stream will improve throughput and reduce elapsed time for Oracle Net messaging.

The settings you can use in sqlnet.ora to implement Advanced Network Compression are listed in Table 17-7.

TABLE 17-7. Advanced Network Compression Settings in sqlnet.ora

Because the Advanced Network Compression feature is included as part of the Oracle Advanced Compression option, you must license the Advanced Compression option to leverage the Advanced Network Compression features.

Debugging Connection Problems

Oracle Net connections require that a number of communication mechanisms be properly configured. The connections involve host-to-host communication, proper identification of services and databases, and proper configuration of the listener server processes. In the event of connection problems when using Oracle Net, it is important to eliminate as many of these components as possible.

Start by making sure that the host the connection is trying to reach is accessible via the network. This can be checked via the ssh command:

If this command is successful, you will be prompted for a username and password on the remote host. If the ping command is available to you, you may use it instead. The following command will check to see if the remote host is available and will return a status message:

If the host is available on the network, the next step is to check if the listener is running; you can use the tnsping utility provided by Oracle to verify Oracle Net connectivity to a remote database listener. The tnsping utility has two parameters: the net service name (from tnsnames.ora) to connect to, and the number of connections to attempt. The output from tnsping will include a listing showing the time required to connect to the remote database.

For example, to determine if the Linux Oracle database server tettnang is accessible from a Windows client, use the tnsping command as follows:

Note how tnsping under Windows used Easy Connect to obtain the TCP/IP address of the server tettnang, filled in default values, and located the listener on the Linux server successfully.

In addition to tnsping, you can use the trcroute utility to discover the path a connection takes to a remote database. The trcroute utility (similar to the Linux utility traceroute) reports on the TNS addresses of every node it travels through and reports any errors that occur. The command format is as follows:

In client/server communications, the same principles for debugging connection problems apply. First, verify that the remote host is accessible; most communications software for clients includes a telnet or ping command. If the remote host is not accessible, the problem may be on the client side. Verify that other clients are able to access the host on which the database resides. If they can, the problem is isolated to the client. If they cannot, the problem lies on the server side, and the server, its listener processes, and its database instances should be checked.

Summary

Using Oracle over a network is hard to avoid unless you’re doing all your work on the database console. Therefore, it’s imperative that you understand all components of the network infrastructure that connect Oracle Database to other Oracle Databases or clients.

Depending on the architecture of your network you may choose one of several different methods to connect to your database, whether by using a local tnsnames.ora file, the Easy Connect syntax, or an enterprise LDAP server. In any case you’ll configure your sqlnet.ora file on the client to specify the allowed connect methods.

On the other side of the connection is the database whose listener hands off connection requests from the dispatcher(s) running on the server. In a clustered environment you will have a single address mapped in your DNS server to each node of the cluster. This ensures availability and reliability of your client connections.

With Oracle Database 12c you can dramatically improve throughput by specifying a compression level on both the client and server side. The Advanced Network Compression feature is similar in implementation to Advanced Compression in the database: less bandwidth used with minimal CPU overhead to perform the compress and decompress operations.