A detailed description of the Oracle database TNS configuration method

TNS Brief Introduction and application

The complete definition of TNS in Oracle: Transparence network substrate transparent networks, monitoring service is an important part of it, not all, do not regard TNS as just a listener.

TNS is part of Oracle NET, a tool dedicated to managing and configuring Oracle Database and client connections, and in most cases the client and database must be configured for TNS, but in rare cases, you can connect to an Oracle database without having to configure TNS. For example, through JDBC. If you connect to Oracle through TNS, the client must install the Oracle clients program.

Oracle, if you want to access a server, you must set up TNS, it does not like SQL Server in the client automatically enumerate all the online servers in the LAN, simply select the required server on the client, and then use the account and password to log in. Oracle does not automatically enumerate the servers in the network and needs to read the TNS configuration file to list the configured server names.

The profile name is typically: Tnsnames.ora, default path:%oracle_home%\network\admin\tnsnames.ora

In the Cgdb and STDCG is the corresponding tns,host is to point to the database server IP, of course LAN with the computer name is also possible. When you create a connection service to a database server through client Net Manager, you actually add a TNS content to the Tnsnames.ora file.

Detailed configuration file for TNS

The TNS configuration file includes both the server side and the client part.

Server side has Listener.ora, Sqlnet.ora and Tnsnames.ora, if through OCM (Oracle Connection Manage) and Domain Name Service Management client connection, server side may also include files such as Cman.ora;

The client has Tnsnames.ora,sqlnet.ora.

Default path for all TNS configuration files for ORACLE:%oracle_home%\network\admin

Listener.ora: Listener configuration file, after successful startup, is a service that resides on the server side. A listener is a service program that listens for client connection requests and establishes client and server-side connection channels. By default, Oracle listens for client connection requests on port 1521.

Sqlnet.ora: A configuration used to manage and constrain or restrict TNS connections, which can be managed by setting some parameters in the file. Depending on the role of the parameter, it needs to be configured separately on the server and client:

Tnsnames.ora: Configure client-to-server connection services, including configuration information for the server and database to which the client is connecting.

TNS Configuration

TNS can be configured through the Oracle Net configuretion assitant or manually.

First, after the Oracle server-side installation is complete,

You should start by configuring Listener,listener to be the primary component for Oracle Communications.

Immediately after installing the Oracle client on the clients, configure the Tnsnames.ora file.

The listener first consists of two parts:

The address, port, and communication protocol to be monitored by Oracle;

The DB instance to be monitored by Oracle,

In a non-RAC environment, the listener can only listen to the address and instance of the server, and in the RAC environment, listener can also listen to the remote server. You must configure at least one listener per database. (Note: RAC environment, refers to the environment of the Oracle Server cluster configuration)

The listener section configures the address and port information that Oracle listens on, and it also includes the Sid_list_listener section, which configures the instances that Oracle needs to listen on. (Note: There is no sid_list_listener this part in the above, this is because Oracle has introduced the Dynamic Listener Service registration since the 9i release, when the database is started, the current DB instance is automatically registered to the listening list, so there is no need to configure Sid_list_ Listener part of it)

The host parameter can be either the Oracle Server host name or the corresponding IP address.

On a multi-IP server, you can configure 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, respectively.

The general TNS configuration is actually the configuration of the Tnsnames.ora file, Tnsnames.ora has the client configuration, also has the server side configuration.

The difference between client and server-side configuration is because the server-side configuration is related to the configuration of listener. Here is a simple example of a configuration:

Tnsnames.ora also includes two parts,

The Address_list section contains the listener address information for the Oracle database server, which is to tell the TNS database to communicate with the client via this address;

Connect_data defines the database to which the client connects, and how the database is connected (private 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 that can control and manage the properties of an Oracle connection, depending on whether the parameters are configured on the client or server side. Sqlnet.ora configuration is global, it is said that Sqlnet.ora configuration is to all the connection, if you want to restrict or limit a particular connection or service, you can configure the appropriate parameters in TNS.

A detailed description of the Oracle database TNS configuration method