Oracle database TNS Configuration method detailed _oracle

Brief introduction and application of TNS

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

TNS is part of Oracle NET, dedicated to managing and configuring Oracle databases and client connections, and in most cases the client and the database must be configured with TNS, and of course, in a few cases, the Oracle database can be connected without having to configure TNS. such as through JDBC. If Oracle is connected through TNS, the client must install the Oracle client 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, just select the desired server in the client, and then use the account and password to log in. Oracle does not automatically enumerate servers within the network, and you need to read the TNS profile to list the configured server names.

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

The Cgdb and STDCG in the above figure are the corresponding tns,host is to point to the database server IP, of course, the local area network with the computer name is also possible. When you create a connection service that connects 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 configuration file for TNS includes both the server side and the client part. The server side has Listener.ora, Sqlnet.ora, and Tnsnames.ora, and if client connections are managed through OCM (Oracle Connection Manage) and domain name services, The server side may also include files such as Cman.ora; the client has Tnsnames.ora,sqlnet.ora. Default path for all TNS profiles for ORACLE:%oracle_home%\network\admin

Listener.ora: Listener configuration file, which is a service that resides on the server side after it has been successfully started. A listener is a service program that listens for connection requests from clients and establishes client and server-side connection channels. By default, Oracle listens for client connection requests on port 1521.

Sqlnet.ora: for managing and constraining or restricting the configuration of TNS connections, you can manage TNS connections by setting some parameters in the file. Depending on the function of the parameter, you need to configure the server and the client separately.

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

TNS Configuration

TNS can be configured via Oracle Net configuretion assitant, or manually configured. First, after the Oracle server-side installation is complete, you should start configuring Listener,listener to be the primary component of Oracle Communications, followed by installing Oracle client on the client and configuring Tnsnames.ora files.

First the listener consists of two parts: Oracle to listen to the address, port, communication protocol, Oracle to listen to the database instance, non-RAC environment, listener can only listen to the address and instance of the server, in the RAC environment, listener can also listen to remote servers. At least one listener should be configured for each database. (Note: RAC environment, refers to the environment for Oracle Server cluster configuration)

The Listener section is configured with the address and port information that Oracle listens to, and the Sid_list_listener section is included in the file, which is configured with instances that Oracle needs to listen on. (Note: This part is not sid_list_listener in the screenshot above, because Oracle has introduced dynamic Listener service registration from the 9i version and will automatically register the current database instance to the Listener list when the database is started, so no configuration Sid_list_ Listener part of it)

The host parameter can be either an Oracle server host name or an appropriate IP address. On a multiple-IP server, you can configure LISTENER to listen for 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))), or you can configure multiple listeners to listen for different IP addresses, respectively.

Generally speaking TNS configuration is actually the configuration of Tnsnames.ora files, Tnsnames.ora have client configuration, also have server-side configuration. The difference between client and server-side configuration is that the server-side configuration is related to the listener configuration. The following is an example of a simple configuration:

The Tnsnames.ora also includes two parts, the address_list part contains the information of the Oracle database server's listening address, that is to tell TNS database can communicate through this address and client; Connect_data Defines the database to which the client will connect, and how the database is connected (private or shared).

In a multiple 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) (serv ER = dedicated))

Sqlnet.ora is a very important configuration that controls and manages the properties of the Oracle connection, depending on whether the parameters are configured on the client or server side. The configuration of the Sqlnet.ora is global, and it is said that Sqlnet.ora is configured to work on all connections and that if you want to constrain or restrict a particular connection or service, you can configure the corresponding parameters in TNS.