Network Administration Utilities
This chapter discusses some of SQL*Net's administrative capabilities and utilities. It also discusses some of the tasks you perform to configure and maintain your network.
Topics covered include:
- SQL*Net's administrative features
- client-side load balancing
- connection route testing with ping
- diagnostic logging and tracing capabilities
- distributing configuration files
See page 4 - 21 for information on database administration utilities such as Server Manager, which lets a DBA administer and maintain
- testing your configuration
SQL*Net's Administration Utilities
SQL*Net has administrative, performance, and security related utilities, making it even easier to use in large environments. Some of these utilities are:
- client-side load balancing
- connection route testing with ping
- diagnostic logging and tracing capabilities
Encrypting Passwords with SQL*Net
The Listener Control Utility and the Oracle Names server provide for optional passwords to allow only authorized personnel to perform certain administration tasks.
Oracle Network Manager provides the option to encrypt these passwords within the configuration files for enhanced security. These passwords and SQL*Net logon passwords are also encrypted when passed over the network.
See the Oracle Network Manager Administrator's Guide for information on how to configure encrypted passwords for the listener and Names server control utilities. Also see Understanding SQL*Net and the Oracle7 Server Administrator's Guide for information on password encryption.
Monitoring Network Services with SNMP Support
SQL*Net release 2.2 includes support for the Simple Network Management Protocol (SNMP) which allows the Oracle7 Server and the MultiProtocol Interchange, Oracle Names, and SQL*Net Listener to be monitored through SNMP-based platforms such as:
Support for these widely-used, SNMP-based management systems allows you to more easily integrate Oracle into your existing infrastructure. You have the freedom to choose the network management utility you want to use.
To monitor an Interchange, an Oracle7 database, SQL*Net listener, or Oracle Names server using Oracle SNMP support, you configure the server in Network Manager.
For example, if you want an Interchange to be monitored, you enter parameters into the SNMP Interchange Details window to control the Interchange subagent. Additionally, you can enter the name or other identifier of the person who administers this service.
See the Oracle Network Manager Administrator's Guide for more information.
SQL*Net's Diagnostic Logging and Tracing Capabilities
SQL*Net version 2 provides tracing and logging abilities that enable you to record the behavior of each of the components of the network and to troubleshoot problems when they occur.
For detailed information about tracing and logging, see the Oracle Network Products Troubleshooting Guide.
Connection Route Testing with PING
SQL*Net release 2.2 includes the TNSPING utility that allows clients to test connections to the servers. You can use TNSPING to test the status of the connection before actually logging on to the server.
This added functionality allows users to validate network connections before executing SQL statements. See Understanding SQL*Net for
Oracle Network Manager is a utility that assists you in creating the configuration files needed for Oracle networking products. It also provides graphical views of your network.
This section is intended only to provide an overview of Network Manager's capabilities. See the Oracle Network Manager Administrator's Guide for detailed information on how to configure a network using Oracle Network Manager.
Networking products require a number of configuration files. These configuration files, which have a very precise syntax, would be tedious to create manually. Network Manager provides a graphical user interface (GUI) through which administrators can create the configuration files quickly and accurately.
After creating a network definition using Network Manager, the configuration files are generated. Network Manager ensures that all resulting files are free from common syntax and semantic errors often made in manually created files. In fact, Oracle only supports files that are created with Network Manager (except for PROTOCOL.ORA and SQLNET.ORA files, which contain parameters that can be included only through manual editing).
Network Manager is similar to the utilities in Oracle's Cooperative Development Environment (CDE). If you are familiar with CDE, you will quickly be able to use Network Manager. Figure 3 - 1 shows Network Manager's interface running under Microsoft Windows.
Figure 3 - 1. Network Manager Interface
Viewing the Network
SQL*Net release 2.2 includes a new version of Network Manager (V3.0) which provides administrators two ways of visualizing a network. The network definition can be viewed in a "map" (topological) view or in a "tree" (hierarchical) view. See Figure 3 - 2 through Figure 3 - 4 for sample illustrations of map and tree views of a network.
Network components, such as clients, servers, communities, etc. are treated as objects that can be selected, dragged and dropped, linked, among other operations within the network map.
The Tree View
Figure 3 - 2. Two Tree Views of a Network
The Tree View Window provides a tree, or hierarchical view of a network, indicating the relationships among the network services.
In Figure 3 - 2, the window on the left side of the screen displays domains or communities as well as their respective network components. The window on the right displays all network services belonging to the highlighted domain. This parent-child framework allows the administrator to clearly display network components and their relationships.
The administrator configures a network object either by selecting it from the toolbar and entering the appropriate information in its property sheet, or by dragging and dropping the selected toolbar object onto another predefined object. When a network object is dropped onto another one, Oracle Network Manager automatically updates it with the information from the object upon which is has been dropped.
For example, when a node is dropped onto a predefined community, the node is automatically linked to the community.
The Map View
The topological or map view shows the network in a spatial layout connecting the network services as they relate to the user's actual network. It allows users to drag a client (or server) object and drop it in a desired location in the network and the configuration will be
Figure 3 - 3. Map View Window
When you create a network object, an icon representing it appears in the bottom half of the Map View window. At any time you can select the icons in this window and move them to the upper part of
Lines showing the objects' relationships will be displayed between them. Individual icons representing the network services will automatically connect to related nodes when placed on the map.
Figure 3 - 4. Map View with Network Objects Arranged
Network Manager V3.0 includes a context-sensitive help system, which allows you to display information about Network Manager while you are using it.
Controlling Network Services from a Remote Site
You can use Network Manager to control and monitor network services, such as the MultiProtocol Interchange, Oracle Names server, and listener for an Oracle database from a remote site. For example, the administrator can set tracing levels in the network services and reload the Oracle Names server.
Accessing Server Manager to Perform DBA Tasks
Provided Server Manager has been installed, you can access it from Network Manager V3.0. This allows a network administrator to perform certain DBA tasks such as database startup, shutdown, backup, and recovery while configuring the network. See page 4 - 23 for introductory information about Server Manager. For detailed information, see the Oracle Server Manager User's Guide and
Network Manager Migration Utilities
The Network Manager also includes a utility, NETCONV, to convert a SQL*Net version 2 network definition created by the version 2 configuration tool into a release 2.1 or 2.2 network definition that can be stored and edited by Network Manager.
Oracle Network Manager also makes moving from SQL*Net version 1 or SQL*Net version 2 easier. Network Manager can import a file of version 1 connect strings and aliases into a TNSNAMES.ORA file. They can then be recognized and used on operating systems (such as UNIX and VMS) that use TNSNAMES.ORA as their source of information about network destinations once SQL*Net version 2 is installed.
Network Manager provides an easy way to create a service name for a SQL*Net version 1 connect string that does not already have an alias mapped to it.
Using Network Manager to Configure Your Network
You can configure the following network components, or network objects, in Network Manager:
- MultiProtocol Interchanges
- Oracle Transparent Gateways
You will find complete instructions for using Network Manager in the Oracle Network Manager Administrator's Guide.
- SQL*Net version 1 connect strings
There are three major steps to configuring a network using Network Manager.
1. Create a network definition using Network Manager property sheets. This procedure is described in the Oracle Network Manager Administrator's Guide. Use the online help to get detailed information about using the property sheets as you use Network Manager.
The three part process is illustrated in Figure 3 - 5.
Figure 3 - 5. Configuration Process
Know Your Network
To use Network Manager effectively, you must have detailed information about the network at hand. This section describes the information you must have ready.
Network Manager knows the syntax of the configuration files, and it knows the default values for parameters in those files. However, it knows nothing about your network until you supply that information. In fact, supplying accurate information to the utility is your main task in using it.
Choose names for the following:
- all network listeners, and the computers (nodes) on which they run
Note: With SQL*Net release 2.1 and the Oracle Server release 7.1 and later, the service names you provide must match precisely the unique global database names assigned by the database administrator. To achieve this, it may be necessary to change some of the service names you have been using.
- all databases, to act as database service names. Service names are short identifiers for their connect descriptors
For example, if your previously defined SQL*Net version 2 network has service names that do not match the global database names, those service names must be changed. Similarly, if the network includes some databases that were named before you established your current domain names, their global database names and service names must reflect the current domain structure.
- all MultiProticol Interchange nodes (if any)
- all Names servers, if any, and the node(s) on which they run
Even if you have a single-protocol network (a single-community network), you must supply a name for that community.
- all client profiles (A client profile, or client type is a group of clients with the same communication requirements.)
Define addresses for the following:
- all MultiProtocol Interchanges (if any)
The addresses for these components consist of the names of
the communities of which they are a part and any protocol-
- all Oracle Names servers (if any)
Different protocols require different protocol-specific information. The following table summarizes the keywords for the protocols currently supported in a TNS network.
Table 3 - 1. Protocol-Specific Keywords
Additional Information: Network Manager provides
default values for many of these protocol-specific keywords. See your Oracle operating system-specific documentation
for information on what values to supply for the
Servers also require that you provide the system identifiers (SIDs) for their databases.
SQL*Net Version 1 Connect Strings
If your network includes both SQL*Net versions 1 and 2, have available the names of the files that hold the version 1 connect strings and their aliases. Know where in the file system they are stored.
LISTENER.ORA Parameter File
The LISTENER.ORA file includes a number of required and optional parameters that describe the listener. You should gather the parameter information and have it ready to use with Network Manager.
MultiProtocol Interchange Information
Network Manager creates configuration files specific to the MultiProtocol Interchanges in your network, if any, based on information you supply. The configuration files are described in the Oracle MultiProtocol Interchange Administrator's Guide.
If you are using Oracle Names, Network Manager creates a NAMES.ORA configuration file for each of the Names servers in your network. See the Oracle Names Administrator's Guide for information about this file.
Multiple Network Managers
If your network includes Oracle Names, you may want to provide further information about the naming structure of your network. You may want to include delegated administrative regions, so that widely separated parts of your network have some autonomy in their administration. See the Oracle Names Administrator's Guide for
If you are using Oracle Names, you may also want to include information about global database links. See "Entering Component Information" in the Oracle Network Manager Administrator's Guide.
Using Network Manager with Oracle Names
If you are creating a new network, you may need to store the network definition in a file, create and distribute the configuration files, and start the network. Once the network is up, you can then store the network definition in the database used by Oracle Names.
Distributing Configuration Files
On systems running Microsoft Windows 3.1, once the initial configuration has been generated through Network Manager and SQL*Net connections established, the administrator can easily distribute changes and updates to the network configuration through the included NETFETCH utility. See the Oracle Network Manager Administrator's Guide for details. Note that, non-Windows systems must distribute the configuration files manually
From the destination node, NETFETCH retrieves the appropriate network configuration from Network Manager.
Suggestion: NETFETCH can be run during Windows startup so that the destination node's configuration can be kept up to date automatically. .
The utility is intended to be used by either the DBA or network administrator, and is usually used in only one location. Choose a workstation from which it will be relatively easy to transfer files.
After you have created and distributed the configuration files, you can start and use the network.
Testing your Configuration
After configuring the network using Network Manager and distributing the configuration files to the destination machines, each component can be started and tested.
The preferred sequence for testing the network is to:
- start and test each Listener
In addition, if Oracle Names is running on your network, test Oracle Names. For further information on how to do so, see the Oracle Names Administrator's Guide.
- start and test each MultiProtocol Interchange
Start the Listener
From each listener's node, use the Listener Control Utility, LSNRCTL, to start each listener. In command line mode, the command is:
LSNRCTL START listener_name
LSNRCTL should display a status message indicating that it has started the listener process successfully. Check that all expected SIDs for that listener are listed in the services summary in the status message.
Test the Listener
To test the listener, initiate a connection from a client in the same community as the listener to any active database controlled by
The simplest test uses SQL*Plus as follows:
The service_name is found in the TNSNAMES.ORA file before each entry. For more information about testing from a client, see page 3 - 17.
If there are no clients in the same community as the listener, you must start an Interchange before testing the listener.
Repeat these steps for each listener in the network.
Start the MultiProtocol Interchanges
Use the Interchange Control Utility, INTCTL, on the Interchange node to start an Interchange. For example, in command line mode the command is:
INTCTL START INTERCHANGE
INTCTL START INT
INTCTL should display a status message indicating that it has started the interchange successfully. For more information on the
Interchange Control Utility, see the Oracle MultiProtocol Interchange Administrator's Guide.
Test the Interchanges
To test the Interchange, initiate a connection through the Interchange from a client in one community to a database in another. On the client machine type:
The service_name for the database in the other community is found in the TNSNAMES.ORA file. To ensure that the connection went through the Interchange, type:
INTCTL STATUS INTERCHANGE
The Connection Manager should indicate that there is one active connection through the Interchange. The STATUS command can be run on the Interchange machine, or, if the Interchange is listed in the TNSNAMES.ORA file, from any other node.
Test Each Client Type to Make Sure you can Connect to Servers
Make sure that each client type is tested. It is not enough to test that the Interchange works. If there are several different client types in your network, initiate a connection to a server from each of them.
If a connection is unsuccessful, use logging and tracing, the diagnostic utilities, to find the cause of the problem. An error stack in the error log may point to the problem. If not, turn on tracing and repeat the operation. You can find information about error logging and tracing in the Oracle Network Products Troubleshooting Guide.
Common Errors During Testing
If you are unsuccessful in bringing up a listener or Interchange, or fail to make a connection to a database, one of the following common errors may be the cause.
- The listener name in the LSNRCTL START command is invalid. Check for typos. Check the LISTENER.ORA file to ensure that the listener name you are using is valid.
- Files are in the wrong place. Both the listener and the Interchange will indicate that they cannot start because configuration files could not be found.
Additional Information: See your operating system-specific documentation to see that the LISTENER.ORA file has been placed correctly for the listener, and the INTCHG.ORA, TNSNAV.ORA, and TNSNET.ORA files are placed correctly on the Interchange. Be sure that the TNSNAMES.ORA file you access is the one created by Network Manager.
- A specified address is already in use. Another process may already be using the address listed in LISTENER.ORA. On some protocols such as TCP/IP, DECnet, and OSI, each network service on a node must use a unique port or socket. On other network protocols such as SPX/IPX or NetBIOS, each network service name must be unique for the entire network. Another network service may be using the same configuration. Contact your network administrator to evaluate whether the network address is available.
- When trying to connect to a database, you may get the message ORA-12203: "TNS: Unable to connect to destination." Use the LSNRCTL utility to start the listener on the server machine. See Understanding SQL*Net for further information.
- When trying to make a connection from a client, you may get the message ORA-12154:"TNS:Could not resolve service name." The service name you requested is not listed in the TNSNAMES.ORA file, or the TNSNAMES.ORA file has been placed incorrectly. See Understanding SQL*Net for
Other common errors are listed in Understanding SQL*Net. All the error messages generated by SQL*Net, the MultiProtocol Interchange, Oracle Names, and Network Manager (and their underlying layers) can be found in the Oracle Network Products Troubleshooting Guide. This book also contains information about how to interpret log files and how to use the trace facility for trouble-shooting purposes.
- When trying to connect to a database, you may get the message ORA-1034: "Oracle Not Available". The database is not running on the server machine. A listener alone does not provide a database connection, the database instance must also be started.
Initiating a SQL*Net Connection
There are a number of ways to initiate a connection with an Oracle server. Commonly used methods are:
- the operating-system command line
The specifics of use are slightly different in each case. Each of the general methods listed is briefly covered here. To identify the method used by a specific utility, see that utility's user's guide.
- special commands within certain utilities
Connecting from the Operating-System Command Line
The general form of connecting an application to a database server from the command line is:
Specifies the command used to invoke a utility such as SQL*Plus, SQL*DBA, SQL*Forms, etc.
Specifies an Oracle username on the server.
Specifies the corresponding password on
Specifies a service name entered in Oracle Names or the TNSNAMES.ORA file that identifies the connect descriptor for the desired server. If the server is in the client's default domain, the service name does not need to include the domain name. However, if the server is in another domain, the service name must include the domain. (The default domain is determined by a parameter in the client's SQLNET.ORA file.
For example, in a network with only one domain, the default .WORLD domain, it is not necessary to include ".WORLD" in the service name.
% sqlplus scott/tiger@SERVERX
However, if the client's default domain were .EAST and the server's domain were .WEST, then the service name would have to include the domain. For example,
% sqlplus scott/tiger@SERVERX.WEST
Note: To prevent a password from displaying during a logon, you can omit the password parameter on the command line. You will then be prompted to enter your password. It will not be displayed as you enter it
Most Oracle utilities can use the operating-system command line to connect. Others provide alternatives.
Connecting from the Utility Logon Screen
Some utilities provide a logon screen as an alternative form of logon. A user can log on to a database server just as easily by identifying both the username and service name in the username field of the utility logon screen, and typing the password as usual in the password field. Figure 3 - 6 shows a SQL*Forms logon screen where the user SCOTT is connecting to the server SERVERX with a password of TIGER. Notice the password cannot be seen, a standard feature of Oracle utilities' logon screens.
Figure 3 - 6. Connection from Logon Screen
Connecting from a 3GL Application
In applications written using a 3GL, the program must establish a connection to a server using the following syntax:
EXEC SQL CONNECT :username IDENTIFIED BY :password
In this connection request, the :username and :password are 3GL variables that can be set within the program either statically or by prompting the user. When connecting to a database server, the value of the :username variable is in the form:
which is the same as in the logon screen above. The :password variable contains the password for the database account being connected to.
Connecting Using Special Commands within Utilities
Some Oracle utilities have internal commands for database connection, once the utility has been started, that allow an alternative username to be specified without leaving the utility. Both SQL*Plus and SQL*DBA allow the CONNECT command using the following syntax:
SQL> CONNECT username/password@service_name
SQL> CONNECT SCOTT/TIGER@SERVERX
This is very similar to the operating-system command-line method, except that it is entered in response to the utility's prompt instead of the operating-system prompt.
Other Oracle utilities use slightly different methods specific to their function or interface. For example, Oracle CDE utilities use logon buttons and a pop-up window with the username, password, and remote database ID field. For more information on connecting to Oracle with a specific utility, see that utility's user guide.