Hello everyone, today we are going to talk about an Oracle tool that we use to know if we have connectivity with the instance of the Oracle database to which we want to connect. It is the tool we use from the TNSPING command line.
This tool is used very often to know if from the client we reach a specific instance of Oracle. It can be considered to be similar to the PING tool of operating systems, to know if a certain machine or IP address is visible over the network, but specific to Oracle DB instances.
File tnsnames.ora
An Oracle server uses the LISTENER to enable remote connectivity to the Oracle database. A listener configures a machine name and port, using a network protocol (usually TCP/IP).
To access an Oracle server from a client, through that LISTENER, the TNSNAMES is used, which is configured in the tnsnames.ora file. This file is saved in the %ORACLE_HOME%\network\admin in Windows environments or $ORACLE_HOME/network/admin in UNIX environments.
This tnsnames.ora file can be configured from the netca tool (Network Configuration Assistant) or manually by changing the file from a text editor such as notepad.
The result of the configuration is saved in the tnsnames.ora, and for each of the databases that we need to access from the client, an entry with this structure is necessary:
BBDD_CONECTAR = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = "host"/"IP")(PORT = "Port")) ) (CONNECT_DATA = (SID = ) ) )
To know all the configuration options of the tnsnames can be found in the official documentation of Oracle in the following link:
https://docs.oracle.com/database/121/NETRF/tnsnames.htm#NETRF007
In this configuration, for access to the database it is necessary to make a call to BBDD_CONECTAR. This “name” that we put to the database is totally personal, so you can access the same database with different names, simply by configuring several TNSNAMES entries, changing only that name. This name is the parameter net_service_name.
TNSPING Tool
The TNSPING tool checks whether a connection can be established with a LISTENER on an Oracle server. The general syntax of the command is as follows:
tnsping net_service_name [veces]
In order to access, the net_service_name parameter must be configured in the TNSNAMES, as we have seen before.
An example of using TNSPING is:
tnsping BBDD_CONECTAR TNS Ping Utility for 32-bit Windows: Version 12.1.0.1.0 - Production on 23-JUN-2020 11:15:30 Copyright (c) 1997, 2013, Oracle. All rights reserved. Parßmeter files used: E:\app\oracle\client\product\12.1.0\client_1\network\admin\sqlnet.ora TNSNAMES adapter used to resolve alias Trying to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = "host"/"IP")(PORT = "Port"))) (CONNECT_DATA = (SID = "DB_SID"))) Done successfully (210 mseg)
There is an exception to the rule, which allows you to do TNSPING without having configured the corresponding TNSNAMES entry, and that is by specifying the complete connection string, without spaces and without line breaks.
tnsping (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST="host"/"IP")(PORT="Port")))(CONNECT_DATA=(SID="DB_SID"))) TNS Ping Utility for 32-bit Windows: Version 12.1.0.1.0 - Production on 23-JUN-2020 11:20:11 Copyright (c) 1997, 2013, Oracle. All rights reserved. Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST="host"/"IP")(PORT="Port")))(CONNECT_DATA=(SID="DB_SID"))) Done successfully (80 mseg)
If the parameter is specified times, then tnsping executes the number of times the parameter says.
tnsping BBDD_CONECTAR 10 TNS Ping Utility for 32-bit Windows: Version 12.1.0.1.0 - Production on 23-JUN-2020 11:16:01 Copyright (c) 1997, 2013, Oracle. All rights reserved. Parßmeter files used: E:\app\oracle\client\product\12.1.0\client_1\network\admin\sqlnet.ora TNSNAMES adapter used to resolve alias Trying to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = "host"/"IP")(PORT = "Port"))) (CONNECT_DATA = (SID= "DB_SID"))) Done successfully (80 mseg) Done successfully (650 mseg) Done successfully (40 mseg) Done successfully (90 mseg) Done successfully (40 mseg) Done successfully (60 mseg) Done successfully (30 mseg) Done successfully (60 mseg) Done successfully (90 mseg) Done successfully (50 mseg)
We hope it has been useful to you.
If you need help in the daily management of your Oracle databases, consult our Oracle support services and calculate your rate.
Greetings.
Database Team
If you don’t want to miss tricks like this and stay up to date with this world, subscribe to our newsletter . We will keep you informed with a single email per month.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn