Testing Oracle Net Connectivity

Here's how you can test if your Oracle client is connecting to an Oracle 13c database using Shared Server mode:

1. Verify Shared Server Configuration:
   • Server-side: Check the following database parameters:
     
     • SHARED_SERVERS: This parameter must be set to a value greater than 0 to enable shared servers.
     • DISPATCHERS: This parameter should be configured to handle network communication for shared servers.
2. Examine Client Connections:
   • Use the v$session view: Query the `v$session` dynamic view on the database. Look specifically at the following columns:
     • SERVER: If the value is "SHARED", the connection is using a shared server. If it's "DEDICATED," it's a dedicated connection.
     • PROGRAM: This can provide clues about the client tool being used for the connection.
   
   Example Query:
   

   SELECT sid, serial#, username, server, program 
   FROM v$session
   WHERE username = 'YOUR_USERNAME'; -- Replace with the connected user
   

3. Additional Tools
   • Listener Control Utility: The `lsnrctl services` command can display a summary of dispatched and connected shared server processes.
   • Operating System-Specific Tools: You can also use OS-level tools (like `ps` on Linux/Unix or Task Manager on Windows) to view processes and identify shared server processes related to the database.
   
   Important Note: Even if shared servers are enabled, it doesn't guarantee all client connections will use them. Client tools and connection string configurations can influence whether shared server or dedicated connections are established.

If your underlying protocol is TCP/IP, it is very easy to test your connectivity to the database server. Simply use the ping^[1] utility.
View the Code below to see what the ping process looks like.

C:\>ping prodsvr 
Pinging prodsvr [10.11.49.239] with 32 bytes of data:
Reply from 10.11.49.239: bytes=32 time=1ms TTL=128
Reply from 10.11.49.239: bytes=32 time<10ms TTL=128
Reply from 10.11.49.239: bytes=32 time=1ms TTL=128
Reply from 10.11.49.239: bytes=32 time=1ms TTL=128
Ping statistics for 10.11.49.239:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum =  1ms, Average =  0ms

The ping command works from both Windows and Unix, and is issued from the command prompt. Pass your database server name as an argument to the ping command. The ping command shown above was successful. You know this because you recieved replies back from the server. Had the ping failed to get a response from the server, you would see several "request timed out" messages. If you get timeouts, or do not get replies, the first things that you should check are the 1) host name and 2) TCP/IP address. Make sure that you are pinging the correct host. If you have the correct host name, and pinging still fails, then you have a network issue and should probably go see your LAN administrator for more help. Before you do that however, be sure the network cable is physically connected to your computer and try the ping command on your own database.
Assuming that you can ping the database server, the next logical thing to check is the Oracle Net connectivity. Oracle supplies a utility named tnsping^[2] for this purpose. With it, you can test communication between your PC and the listener that is running on the server. You run tnsping from the command prompt and pass an "Oracle Net Service name" as an argument. Here is an example showing how to use tnsping to verify connectivity for the COIN service:

C:\>tnsping coin
TNS Ping Utility for 32-bit Windows:
Version 8.1.5.0.0 - Production on 16-MAY-99
 13:53:10
(c) Copyright 1997 Oracle Corporation.
All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)
(Host=10.11.49.239)(Port=1521))
OK (180 msec)

Tnsping simply verifies that it can contact the "Oracle listener process" running on the host machine. In this case, it was successful, and displayed the brief message "OK" to tell you that. If the listener process cannot be contacted, you will get an error message like this:

Attempting to contact (ADDRESS=(PROTOCOL=TCP)
(Host=10.11.49.239)(Port=1521))
TNS-12541: TNS:no listener

This is a good indication that the listener may not be running on the server and you may need to start it. This message might also indicate a problem with the underlying network connectivity. That is why you should run ping first to rule that out. Here is an example showing tnsping's response to what is perhaps the most common Oracle Net configuration error:

C:\>tnsping coin
TNS Ping Utility for 32-bit Windows:
Version 8.1.5.0.0 - Production on 16-MAY-99
 14:00:26
(c) Copyright 1997 Oracle Corporation.
All rights reserved.
TNS-03505: Failed to resolve name

This is probably the most common error that people encounter. This is telling you that Oracle Net was unable to find an entry in the tnsnames.ora file for a service named coin. To resolve this, double-check the tnsnames.ora entry and make certain that what you are typing matches what is in the tnsnames.ora file.

The `tnsping` command is still available and fully supported in Oracle 13c. Here's why it's an important tool:
Purpose

• Basic Connectivity Test: tnsping acts as a network troubleshooting utility. It tests whether a client machine can establish a basic connection to an Oracle database listener using a provided Net Service Name.
• Verifying Configuration: This helps to verify issues like incorrect tnsnames.ora entries, network misconfigurations, or listener problems.

How to Use It

1. Open Command Prompt/Terminal: Open a command-line interface.
2. Set Oracle Environment: You may need to set environment variables (like ORACLE_HOME, ORACLE_SID) with a script like . oraenv.
3. Execute tnsping:

      tnsping <Net Service Name>
      

   • Replace <Net Service Name> with the name defined in your tnsnames.ora file.

Example Output

TNS Ping Utility for 64-bit Windows: Version 13.0.0.0.0 - Production on 22-MAR-2024 12:11:01

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\Oracle\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver.mydomain.com)(PORT = 1521)) 
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydatabase.mydomain.com)))
OK (20 msec)

If you are running multiple releases of Oracle client software on your PC, you may find yourself with several tnsnames.ora files.
I try to keep all these files in sync, but sometimes I get in a hurry, and I add an entry to one file, but not to the others. When that happens, and I try to connect to the service in question using a different release of the client software, I get the dreaded "Failed to resolve service name" error. It is easy enough to fix, but you need to be aware that different releases of Oracle client software on the same machine use separate tnsnames.ora files.
Now, try the tnsping command on your own database. You should also check the value for the default domain, and should verify that the syntax of the tnsnames.ora entry is correct. You will learn about checking the default domain in the next lesson.

Attempting to connect to a server using the wrong `tnsnames.ora` file can absolutely result in an Oracle error similar to "Failed to resolve service name" or the more specific TNS-03505 error. Here's the breakdown of why:

• tnsnames.ora Purpose: The `tnsnames.ora` file is essentially a mapping between user-friendly service names used for connection and the actual network addresses of your database servers (hostname, port, service name/SID).
• Wrong File = Wrong Mapping: If you reference a service name in your connection string, and that service name exists in a `tnsnames.ora` file that Oracle Net isn't looking at, it has no way of finding the corresponding network address for your database.
• Result: The TNS layer won't be able to resolve the service name, leading to the "Failed to resolve service name" error.

Important Factors:

• TNS_ADMIN Environment Variable: If you have the `TNS_ADMIN` environment variable set, Oracle Net will look for the `tnsnames.ora` file in the directory specified by that variable.
• Default Location: If `TNS_ADMIN` isn't set, Oracle Net will typically check in the
  1. $ORACLE_HOME/network/admin` directory (Linux/Unix) or
  2. %ORACLE_HOME%\network\admin` (Windows).

To resolve this issue:

1. Correct TNS Entry: Make sure the service name you're using is defined in the appropriate `tnsnames.ora` file and the details are accurate.
2. TNS_ADMIN Check: Ensure the `TNS_ADMIN` environment variable points to the directory containing the correct `tnsnames.ora `file, if you're using it.

[1]ping: A utility used to verify TCP/IP connectivity between two nodes on a network.

[2]tnsping: A Oracle Net utility used to verify connectivity between a client and a remote listener.