Detailed explanation of oracle database tns configuration method

Brief introduction and application of TNS

The complete definition of TNS in Oracle: transparency Network Substrate transparent network bottom layer, the listening service is an important part of it, not all, don't think of TNS as just a listener.

TNS is part of Oracle Net. It is a tool specially used to manage and configure the connection between Oracle database and client. In most cases, if the client and database want to communicate, TNS must be configured. Of course, in a few cases, , you can connect to the Oracle database without configuring TNS, such as through JDBC. If you connect to Oracle through TNS, the client must install the Oracle client program.

In Oracle, if you want to access a server, you must set up TNS. It does not automatically list all online servers in the LAN on the client like SQL SERVER. You only need to select the required server on the client. , and then log in using your account and password. Oracle cannot automatically list the servers in the network. It needs to read the TNS configuration file to list the configured server names.

The configuration file name is generally: tnsnames.ora, the default path is: %ORACLE_HOME%\network\admin\tnsnames.ora

CGDB in the above picture STDCG corresponds to TNS, and HOST points to the IP of the database server. Of course, the computer name can also be used in the LAN. When creating a connection service to the database server through the client Net Manager, a TNS content is actually added to the tnsnames.ora file.

Detailed configuration file of TNS

The configuration file of TNS includes two parts: server side and client side. The server side has listener.ora, sqlnet.ora and tnsnames.ora. If the client connection is managed through OCM (Oracle Connection Manage) and domain name service, the server side may also include cman.ora and other files; the client side has tnsnames.ora, sqlnet .ora. The default path of all Oracle TNS configuration files: %ORACLE_HOME%\network\admin

listener.ora: listener configuration file, which is a service that resides on the server after successful startup. A listener is a service program used to listen to client connection requests and establish a connection channel between the client and the server. By default Oracle listens on port 1521 for client connection requests.

sqlnet.ora: used to manage and restrict or limit the configuration of tns connections. By setting some parameters in this file, TNS connections can be managed. Depending on the role of the parameters, they need to be configured on the server and client respectively.

tnsnames.ora: Configure the connection service from the client to the server, including the configuration information of the server and database the client wants to connect to.

TNS configuration

TNS can be configured through Oracle Net Configurement Assitant or manually. First, after the installation of the Oracle server is completed, you should first configure LISTENER. LISTENER is the primary component for Oracle communication. Then install the Oracle client on the client and configure the tnsnames.ora file.

First of all, the listener includes two parts: the address, port, and communication protocol that Oracle wants to monitor; the database instance that Oracle wants to monitor. In a non-RAC environment, LISTENER can only monitor the address and instance of this server. In a RAC environment Next, LISTENER can also listen to remote servers. At least one listener must be configured for each database. (Note: RAC environment refers to the environment in which Oracle server cluster is configured)

LISTENER part configures the address and port information that Oracle wants to listen to; this file will also include SID_LIST_LISTENER section, this section configures the instance that Oracle needs to monitor. (Note: There is no SID_LIST_LISTENER part in the above screenshot. This is because Oracle has introduced dynamic listening service registration since version 9i. When the database starts, it will automatically register the current database instance to the listening list, so there is no need to configure the SID_LIST_LISTENER part)

The HOST parameter can be the Oracle server host name or the corresponding IP address. On a multi-IP server, you can configure the listener to listen to multiple addresses at the same time, such as the following configuration: LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.11)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.11) (PORT=1521))) ), or you can configure multiple listeners to listen to different IP addresses.

Generally speaking, TNS configuration is actually the configuration of the tnsnames.ora file. tnsnames.ora has client-side configuration and server-side configuration. The difference between client-side and server-side configuration is that the server-side configuration is related to the LISTENER configuration. Here is a simple configuration example:

tnsnames.ora also includes two parts. The ADDRESS_LIST part contains the listening address information of the Oracle database server, which means to tell the TNS database that it can communicate with the CLIENT through this address; CONNECT_DATA defines the database that the CLIENT wants to connect to, and the database Connection method, (dedicated or shared).

In a multi-IP environment, TNS can also configure multiple remote IP addresses: CGDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.55)(PORT = 1521 )) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.56)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CGDB) (SERVER = DEDICATED) ) )

sqlnet.ora is a very Important configuration, it can control and manage the properties of the Oracle connection, and decide whether to configure it on the client side or on the server side according to the different functions of the parameters. The configuration of sqlnet.ora is global, which means that the configuration of sqlnet.ora is effective for all connections. If you want to restrict or restrict a special connection or service, you can configure the corresponding parameters in TNS.

For more detailed explanations of Oracle database tns configuration methods and related articles, please pay attention to the PHP Chinese website!