Configure the tnsping trace to diagnose the OracleNet connection

Generally, Oracle is connected using the OCI interface of OracleClient (non-JDBC), and the local connection name localname is configured. Once an error occurs during the connection process, we

Generally, we use the Oracle Client (non-JDBC) OCI interface to connect to Oracle and configure the local connection name. Once an error occurs during the connection process, we

The main method to connect a client to an Oracle Server. From the perspective of component stakeholders, the Oracle Net connection process involves many technologies and processes, such as client connection methods, connection strings, listener behavior, and database instances.

Generally, we use the Oracle Client (non-JDBC) OCI interface to connect to Oracle and configure the local connection name. Once an error occurs during the connection process, our diagnostic policy is also from the external to the internal, from the client to the server for gradual diagnosis.

Different users use tnsping + client service name with different results

Analysis of tnsping commands in Oracle

1. Summary of Tnsping

Tnsping is an important method provided by Oracle to diagnose connection actions. We use tnsping xxx (local connection name) on the client to quickly locate the connection errors and determine the problems. In terms of functions, tnsping can help us solve several problems:

Ü local Oracle Net configuration file parsing: sqlnet. ora and tnsname. ora are the two most important configuration files on our client. Due to historical reasons, many basic Oracle configuration files are in text format, especially for the three core configuration files of Oracle Net. Configuration tools such as netca are only a function for writing and reading text files. Many friends simply copy and paste the text in the figure during configuration, which may cause problems and faults. Tnsping can simulate the connection process and pre-parse the name in the configuration file. If the format is incorrect, the resolution will fail;

Ü server listener status diagnosis: When remotely connecting to the database server, the listener is an unavoidable component. Tnsping can be used to determine network access. If the listener is not running or is not running on the specified port, tnsping can be used to determine the prompt information;

Ü registration service limited verification: in the local connection name, service name, host name, listener waiting port, and connection protocol are several elements of connection information. The service name is an object that cannot be verified by tnsping, that is. If there is no connection service name in the listener Registration Information (Dynamic Registration or static registration), tnsping will not report an error;

C: \ Documents ents and Settings \ liuzy> tnsping chdb

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0-Production on 12-5 months-

17:58:21 2014

Copyright (c) 1997,201 0, Oracle. All rights reserved.

Used parameter files:

D: \ app \ Administrator \ product \ 11.2.0 \ client_1 \ network \ admin \ sqlnet. ora

Alias resolved using TNSNAMES Adapter

Try to connect (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.17.4.53) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = chdb )))

OK (30 ms)

For tnsping, in addition to the final result information, we can also use the trace method to view the execution status of each step and the steps in which the problem occurs. This article describes how to track the tnsping process from the client.

2. Configure tracking Parameters

By default, the tnsping tracking function is disabled. We need to perform manual configuration in the Oracle Net configuration file sqlnet. ora. Note: The configuration is on the client, and we execute the tnsping command on the client.

-- Client sqlnet. ora, at $ ORACLE_HOME/network/admin

# Sqlnet. ora Network Configuration File: D: \ app \ Administrator \ product \ 11.2.0 \ client_1 \ network \ admin \ sqlnet. ora

# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if MERs choose

# Install "Software Only", this file wont exist and without the native

# Authentication, they will not be able to connect to the database on NT.

SQLNET. AUTHENTICATION_SERVICES = (CNT)

NAMES. DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

TNSPING. TRACE_LEVEL = SUPPORT

TNSPING. TRACE_DIRECTORY = D: \ app \ Administrator \ product \ 11.2.0 \ client_1 \ network \ trace

The final tnsping. trace_level and tnsping. trace_directory indicate the Trace Level and directory of tnsping. Like other Oracle tracking operations, tnsping also supports different tracking levels and can be adjusted based on our purposes.

Currently, trace_level has the following optional parameters: off, user, admin, and support. In the experiment, we chose the support level, which is a relatively fine tracking granularity.

Trace_directory specifies the location of the tracking directory. The trace file name is tnsping. trc.

3. Execute the tracking process

Use tnsping to connect to a local connection named cogdb.

C: \ Documents ents and Settings \ liuzy> tnsping cogdb

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0-Production on August 5-

12:17:43 2014

Copyright (c) 1997,201 0, Oracle. All rights reserved.

Used parameter files:

D: \ app \ Administrator \ product \ 11.2.0 \ client_1 \ network \ admin \ sqlnet. ora

Alias resolved using TNSNAMES Adapter

Try to connect (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.3.101) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = cogdb )))

OK (30 ms)

In the specified directory, we can see the generated trace file.

D: \ app \ Administrator \ product \ 11.2.0 \ client_1 \ network \ trace

Tnsping. trc

The following is the parsing of the trace file.

For more details, please continue to read the highlights on the next page: