Oracle instant client, tnsping, tnsnames. ora and ORACLE_HOME, oracletnsping

Oracle instant client, tnsping, tnsnames. ora and ORACLE_HOME, oracletnsping

Some time ago, I had to remotely connect to the oracle database, but I didn't want to install the oracle client on my computer. So I downloaded the lightweight client instant client from the oracle official website. There is no graphic interface, and sqlplus is used to remotely connect to the server. Therefore, this is just enough to meet the needs of obsessive-compulsive patients who are pursuing "simple.

However, it may be that no listening port is opened on the server. I tried various configurations on my machine, including tnsnames. ora and sqlnet. ora. The remote connection fails. To troubleshoot the problem, I first pinged the Internet address of the server and found that there was no problem. The Internet says that ping is not enough. We need to try tnsping again.

Tnsping is also a type of ping. However, this tool is only included in the Complete oracle client. If you install instant client, you cannot use tnsping. I happen to use the instant client. Is it a tragedy? However, after walking around OTN Community, I found that tnsping is not that mysterious. OTN Community has a post on tnsping, which I intend to repeat here. Original post in https://community.oracle.com/thread/2434899? Start = 0 & tstart = 0. If you are interested, see.

What tnsping can do is read the HOST and PORT parameters from the TNS connection string, open a socket connection, send a ping to the listener, and the listener responds with a pong. Tnsping cannot take effect for other parameters in the TNS connection string, determine the availability of database instances and database services, or determine whether parameters such as SID, SERVICE_NAME, or INSTANCE_NAME are valid. It can be seen that tnsping has never been a very high-end tool. Many people use it to test the TNS connection string. It is just useless.

Does instant client have a tnsping tool? It doesn't matter, because you can use telnet

I 've been talking about how tnsping reads HOST and PORT parameters. The question is: where are these parameters? To solve this problem, you have to submit a crucial configuration file: tnsnames. ora.

Tnsnames. ora is a configuration file that defines the various addresses required to establish a connection to a database. If you have installed the Complete oracle client, the configuration file should be in the ORACLE_HOME \ NETWORK \ ADMIN directory. The format is as follows:

<addressname> = (DESCRIPTION =   (ADDRESS_LIST =     (ADDRESS = (PROTOCOL = TCP)(Host = 

A complete tnsnames. ora example is as follows:


ORA11 = (DESCRIPTION =    (ADDRESS_LIST =     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))   ) (CONNECT_DATA =   (SERVICE_NAME = ORA11) ))



Of course, if you install the instant client, this file does not exist. However, you can create a tnsnames. ora file in a specific directory. Tnsnames. ora has a copy on each client and server. For more information, see http://orafaq.com/wiki/tnsnames.ora. So what is the role of tnsnames. ora?
In fact, this configuration file is a bit like the hosts file in our computer. This file is actually a configuration file. It maps the domain names of some common URLs to their IP addresses. when you enter a domain name in your browser, press Enter, the system first automatically finds the IP address of the domain name from the hosts file, and then opens the webpage corresponding to the address. If the corresponding IP address is not found, the system submits the website address to the DNS server, and the server resolves the corresponding IP address.
The hosts file resolves the website that is easy to remember as an IP address, and tnsnames. ora resolves the TNS alias that is easy to remember as a connection string. The format of the connection string is usually "(DISCRIPTION = (ADDRESS = <address details>) (CONNECT_DATA = <database details> )". Comparing the above tnsnames. ora example, we can see that tnsnames. ora actually contains the so-called connection string. Therefore, you can log on to the database remotely using sqlplus in two ways. One way is to completely write the connection string, as shown below:

sqlplus scott/tiger@"(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=my-dev.mydomain.com)(PORT=1521)) (CONNECT_DATA= (SID=orcldev) (SERVER=dedicated)))"

The other is to write the TNS alias directly. The tnsnames. ora shown above is used as an example:


sqlplus scott/tiger@ORA11
Obviously, the second method is simpler. Especially when you need to log on to the database frequently, using configuration files will undoubtedly greatly improve your efficiency. This is the same as writing makefile files for compilation convenience.

As mentioned earlier, if you have installed the instant client without the tnsping tool, you only need to understand what tnsping is. In fact, it only does three things: 1. if you specify a TNS alias, it resolves the alias as a connection string (skip this step if you directly use the complete connection string when logging on); 2. test the IP address of the listener to be connected Based on the HOST parameter of the connection string. 3. test the corresponding PORT of the listener to be connected Based on the PORT parameter of the connection string. Obviously, you can use telnet 
The instant client is not very convenient. As mentioned earlier, it does not contain the tnsnames. ora file. However, you can create a tnsnames. ora file by yourself. So how does the oracle client find this file? In this case, you need to add an environment variable named TNS_ADMIN to save the tnsnames. ora address. The system will find the corresponding tnsnames. ora File Based on the address given by this variable.
Finally, let's briefly talk about ORACLE_HOME. This variable indicates the installation directory of oracle, or an environment variable pointing to the installation directory of oracle. In windows, if you want to view the current ORACLE_HOME, use the echo command. To add or set the ORFACLE_HOME environment variable, use the set command. Of course, ORACLE_HOME can also be found in the Registry, which may be HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE. For more details, see http://orafaq.com/wiki/oracle_home.

What are the attributes of the tnsnamesora file in the instantclient_11_2 \ network \ admin directory of the oracle client?

How does one install the Oracle client on Windows 7? You must configure tnsnamesora and use P/L SQL to connect to other databases.
The method is as follows:
Install the Oracle client in Windows XP Vista Win7

1. Download the "Instant Client Package" software from the Oracle official website to better implement the Oracle Client greening solution.
Www.oracle.com/...t.html
Downloadable packages include:
Download package:
Instantclient-basic-win32-11.1.0.x.0.zip (available versions: 11.1.0.6.0 and 11.1.0.7.0)
Or
Instantclient-basic-win32-10.2.0.x.zip (available versions: 10.2.0.3 and 10.2.0.4)
Or
Instantclient-basic-win32-10.1.0.5-20060419.zip

2. decompress the package to the installation directory, for example, D: \ ORA10, create a two-tier network/admin folder under this directory, and then create a tnsnames. ora file in admin,
The input format is as follows:
 
ZTRIOA_192.168.0.17 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.17) (PORT = 1521 ))
)
(CONNECT_DATA = (SID = ZTRIOA) (SERVER = DEDICATED ))
)
Here, you can modify it based on your actual situation. Pay attention to the space issue in the middle. If there are more or fewer spaces, errors will occur.

3. configure system environment variables
Write D: \ ORA10 at the beginning of the Path variable value;
And create a variable: variable name: oracle_home variable value: D: \ ORA10
 
4. Install PLSQL Developer 7.1 or another version. Be sure not to install the tool in the directory with "(x86)" during installation, which is prone to errors.
5. Run "PLSQL Developer" and try again. Normally, the connection is successful.

Garbled characters After PLSQL Developer Connection Processing
Problem description: After you connect to other Oracle databases using the Oracle green client, garbled information is displayed.
Solution:
6. Open the installation directory of PLSQL Developer and see the directory with plsqldev.exe,
Create a "PLSql_run.bat" file in the PLSQL Developer folder and enter the following content in the file:
Bytes ---------------------------------------------------------------------------------------------

Set nls_lang = SIMPLIFIED CHINESE_CHINA.ZHS16GBK
PLSQLDev.exe

Bytes ---------------------------------------------------------------------------------------------

7. Make the remaining full text of ......>