Comparison between the tnsping command of Orale and the ping command of TCP/IP

Compare the ping commands of tnsping and TCP/IP of Orale:

-----------------------------------------------------------------------------
Oracle Net tool (command) tnsping is an OSI Session Layer tool used:

1) Verify the name resolution (name resolution, of course, the name of the Oracle Network Service)

2) whether the remote listener is enabled

In terms of verifying the above two features, it is a handy tool on hand for DBAs. Oracle network interfaces support different network and transmission protocols, among which we are most familiar with TCP/IP. this article only describes TCP/IP protocol families. However, with other protocols supported by Oracle network interfaces, tnsping functions are the same.

Tnsping can be used on multiple network protocols, but this article only discusses the TCP/IP network protocol.

-----------------------------------------------------------------------------
Oracle tnsping
-----------------------------------------------------------------------------

The tnsping test program of Oracle uses the TCP protocol for communication. TCP is the connection-oriented OSI transport layer protocol. The connection-oriented Protocol needs to exchange the initial serial number in the initial connection establishment phase. This is what we usually refer to as three handshakes. That is, the tnsping test program will produce three handshakes when communicating with listener.

After the tnsping command is issued in the command line, the Oracle network alias (Network Service name, host connection string) is parsed. This resolution will be performed in the local tnsnames. ora file, Oracle command server, or Oracle LDAP (directory service. The purpose of resolution is to obtain the machine name (IP address) of the target listener and the port number of the listener.

Once you obtain the name and port number of the listener machine, you can open a TCP connection to the target machine and port. To enable this TCP connection, the listener machine name must be resolved to an IP address (of course, this is only done when the parsed listener connection address is the machine name ), then, the TCP/IP performs three handshakes to complete the connection.

After the connection is established, the tnsping tool then sends an Oracle TNS connection package to the Oracle listener. Then, the listener responds to a tns reject packet (refuse packet ), the TCP/IP connection between the two machines is over. The total time consumed from parsing the Oracle network alias to ending the TCP/IP connection is displayed in the output of the tnsping command.

Example:

C:> tnsping v817 4

TNS Ping utility for 32-bit windows:

Version 8.1.7.0.0-production on 18-may-2001 14:27:57

(C) copyright 1997 Oracle Corporation. All rights reserved.

Attempting to contact

Address = (Protocol = TCP) (host = abadah.us.oracle.com) (Port = 1521 ))

OK (1770 msec)

OK (10 msec)

OK (0 msec)

OK (10 msec)

The above example shows that the first tnsping takes 1770 milliseconds, and these time ranges from tnsnames. the time required to parse the v817 network alias in the ora file, and the time required to use DNS to parse the listener machine name "abadah, the time required for three TCP/IP handshakes, the time required for TNS connect and refuse packets transmission, and the time required to disconnect the TCP/IP connection. The second tnsping only took 10 milliseconds. This is because all the information (v817 alias and IP address) is already in the cache. However, the tnsping program still performs TCP connection and disconnection.

-----------------------------------------------------------------------------
TCP/IP Ping
-----------------------------------------------------------------------------

Transmission Control Protocol/Internet Protocol family (TCP/IP) has a tool called Ping. It is a command line interface of the ICMP (Internet Control Message Protocol) protocol in the TCP/IP protocol family.

According to RFC 792:

"Sometimes, a gateway or destination address needs to communicate with the same source address, for example, in order to give the source address an error about the process of processing the datagram. For this purpose, you need to use the ICMP protocol. ICMP requires the support of the Internet Protocol (IP), which makes it look like a higher-level protocol. However, ICMP is actually a part of the IP address, it must be implemented in each module of the IP address.

One of the functions of the ping command is to collect IP data packets of different sizes to transmit a round-trip over the network. This can be used to estimate the overall performance and response time of the network.

The ping command uses IP instead of TCP, so that the three handshakes of TCP are not required. When the ping command is run, it only sends and receives one IP packet, this requires less data packets than Oracle's tnsping program.

The first response time of Ping is often longer than the average response time, because for the first time, it is generally necessary to parse the name Of the ping machine. This resolution can be implemented through the local hosts file, DNS server, or other methods.

An example of Ping:

Pinging abadah.us.oracle.com [144.25.223.156] with 32 bytes of data:

Reply from AAA. BBB. ccc. DDD: bytes = 32 time <40 ms TTL = 255

Reply from AAA. BBB. ccc. DDD: bytes = 32 time <10 ms TTL = 255

Reply from AAA. BBB. ccc. DDD: bytes = 32 time <10 ms TTL = 255

Reply from AAA. BBB. ccc. DDD: bytes = 32 time <10 ms TTL = 255

The above example shows that the first ping time takes 40 milliseconds, including the DNS resolution time.

From the above introduction, we can conclude that:

1. tnsping requires TCP, so it requires three handshakes to establish a connection, while ping only uses IP addresses, so it does not need three handshakes. This explains why some machines cannot be pinged, however, you can use tnsping to test the connectivity.

2. tnsping does not indicate that the client can establish a connection with the database. Because

Ping can only indicate that the client can parse the name of the listener machine, and the Lister has been started, but it does not indicate that the database has been opened, and the tsnping process is inconsistent with the real client connection process.

However, if tnsping cannot be used, the database cannot be connected.

2nd errors can be explained in tns-12545:

TNS-12545 (ORA-12545): connect failed because target host or object does not exist

Cause:

The client cannot correctly parse the server machine name. This error occurs when the client is not set or the domain name server is not correctly set.

Solution:

Q: In this case, you can use the tnsping test program to test the network service name, but you still cannot use the program to connect to the database. Sometimes, even if you change the server name of the client's tnsnames. ora server to an IP address, an error is reported, which makes you more confused. Will the system have a problem?

To solve this problem, you need to know the data flow between the client and the server when establishing a connection. You need to understand the Redirect session concept.

When a client connects to a database on a window or a database connected to a Unix database in a shared connection mode (in this case, the database is in the MTS mode), the client connection will be redirected, that is, after Listener accepts the connection from the client, it sends a redirection package to the client, and then the client uses the information provided in the redirection package (Server IP address (or machine name) and port information) re-initiate a real connection to the database. When you change the server machine name in tnsnames. ora of the client to an IP address, the client connection still reports ora-12545 error, which is the content of the redirection package.

When the client connects to the database on the window or connects to the database on UNIX in the shared connection mode. the IP address of the server in ora, so there is no name resolution problem. The client connection request will reach the listener, which is the reason why the tnsping test program can pass the network service name test, because the tnsping test program will not cause redirection issues. After the Listener accepts the client connection, it will implement the socket based on the connection mode (dedicated connection or shared connection) requested by the customer and the operating system, determines whether to redirect the client connection. If redirection is required, a redirection package is generated. The server address information contained in the package is from listener. the listener listening address in the ora file (according to the listener. the configuration in ora may be the name of the server or the IP address of the server ), this package also contains the port information that the client should redirect the connection (the same as the port listened by listener may not be the same). After the client receives the redirection package, parse the server address (machine name or IP address) and port that should be reconnected, and re-use the parsed information to create a new connection, at this point if the client gets the server's machine name without configuring domain name resolution, it will be because the server's IP address cannot be resolved, resulting in a ora-12545 error.

So, if you want to completely resolve ora-12545 errors, You need:

1) configure a Domain Name Server and correctly set the Domain Name Server of the client machine

2) Configure the server machine name and IP address in the hosts file of the client.

3) change the addresses in the client tnsnames. ora and listener. ora to IP addresses instead of machine names.

4) change the client connection to a dedicated connection to avoid redirect. (Applicable when tnsnames. ora is the Server IP address)