Differences between sqlnet. TNS. listner in Oracle

1. Overview
   In the Oracle installation directory $ home/Network/admin, sqlnet is often seen. ora tnsnames. ora listener. ora files except tnsnames. ora, the detailed usage of the other two files is not well understood by many people.

   Sqlnet. ora is used on the Oracle client to configure relevant parameters for connecting to the Oracle server.
   Tnsnames. ora is used on the Oracle client. You can configure the alias parameters for connecting to the database, just like the hosts file in the system. Listener. ora is used on the Oracle server to configure listening methods for Oracle server programs, such as limiting certain IP addresses and other parameters.

   Under the installation directory $ home/Network/admin/samples, you will see the example file of the previous file, which contains descriptions and usage of relevant parameters. If you encounter any problems, you can solve the problem by referring to the parameter descriptions in these example files.
   If there is any problem connecting to the database, the steps to find the problem are as follows:
   1) Check sqlnet. ora and tnsnames. ora in the client sequence.
   2) Check the listener. ora configuration on the server side, ensure that the listener starts, and load the database service.

2. Know sqlnet. ora
   The following describes how to configure common parameters. For more information, see the example sqlnet. ora.
   Sqlnet. ora can be deleted, so that when the Oracle client connects to the database, the configuration in tnsnames. ora is used by default.

   1). Names. default_domain
   Domain Name definition. When you use sqlplus to access the database, ". Domain" will be added after the TNS alias"

   Example:
   In sqlnet. ora:
   Names. default_domain = com
   The TNS definition in tnsnames. ora is as follows:
   Local_dev =
   (Description =
   (Address = (Protocol = TCP) (host = 192.168.1.111) (Port = 1521 ))
   (CONNECT_DATA =
   (Server = dedicated)
   (SERVICE_NAME = linuxdb)
   )
   )
   When you run the command sqlplus username/password @ local_dev on the client, the following error message is displayed:
   "ORA-12154: TNS: unable to handle error messages like service name" or "ORA-12154: TNS: cocould not resolve service name.
   When sqlplus username/password @ local_dev, the TNS alias "local_dev" is converted to "local_dev.com". Therefore, if local_dev.com cannot be found in tnsnames. ora, an error is returned.

   Modify the definition in tnsnames. ora as follows:
   Local_dev.com =
   (Description =
   (Address = (Protocol = TCP) (host = 192.168.1.111) (Port = 1521 ))
   (CONNECT_DATA =
   (Server = dedicated)
   (SERVICE_NAME = linuxdb)
   )
   )
   Run sqlplus username/password @ local_dev. The connection is successful.

   2) names. directory_path
   It defines the matching method used when the client connects to the database.
   Example
   The content of sqlnet. ora is as follows:
   Names. directory_path = (tnsnames, onames, hostname)
   When the client executes sqlplus username/password @ local_dev to connect to the database
   First, use the alias tnsnames. ora to connect to the database;
   Use onames for resolution, and finally use the host name for resolution;

   OnamesIndicates that Oracle uses its own name server (Oracle Name Server) for parsing. Currently, Oracle recommends using the Lightweight Directory Access Protocol LDAP to replace onames;

   HostnameIndicates that the host file, DNS, NIS, and so on are used for parsing;

   3) sqlnet. authentication_services
   Define the authentication method for logging on to the database.
   None indicates the identity authentication of the Oracle database, and NT indicates the identity authentication of the operating system.

   You can set it to sqlnet. authentication_services = (none, CNT) to indicate the authentication priority.

   Example
   The content of sqlnet. ora is as follows:
   Sqlnet. authentication_services = (none)
   Run the following command on the command line:
   Sqlplus "/As sysdba", the execution fails.
   Error message ORA-01031: insufficient privileges
   This parameter is not set by default.

3. Understand tnsnames. ora
   Provides detailed information about the client connection to a database, host address, port, database instance name, and so on.

   The following is an example to illustrate the problem:
   Local_dev =
   (Description =
   (Address = (Protocol = TCP) (host = 192.168.1.111) (Port = 1521 ))
   (CONNECT_DATA =
   (Server = dedicated)
   (SERVICE_NAME = linuxdb)
   )
   )

   The Protocol parameter is generally TCP. You can select a configuration method based on the server situation.
   Generally, a host is an IP address or a host name. You only need to ping the host name. Generally, the ing between the host name and the IP address is configured on the host file of the client system.
   The ORT standard is 1521, depending on the listening port on the server. S
   Ervice_name is the service name of the database. After logging on to the database as a system user, run the sqlplus> show parameter SERVICE_NAME command.

   This configuration file is usually used by many people, and most people will, I believe there will be no big problem.

4. Listener. ora
   The tnslsnr process is a listening process that listens to and accepts requests from a remote database connection. listener. ora is the configuration file of the tnslsnr process. All the listening parameters are read from this configuration file.
   This file is located on the server. If you only install the Oracle client, this file does not exist.
   If you only need to connect to the database locally and do not accept remote connection, you do not need to start the tnslsnr process or maintain the listener. ora file.
   Start the listening process command. In command mode, run the LSNRCTL start command to start the listening process tnslsnr.
   There can be multiple listeners, that is, you can listen to multiple Oracle instances at the same time, you can define multiple listeners in listener. ora to listen.

   The following is a simple example.
   Contents in listener. ora:

   Listener_csb =
   (Description_list =
   (Description =
   (Address_list =
   (Address = (Protocol = TCP) (host = 10.70.9.14) (Port = 1621 ))
   )
   )
   )
   Sid_list_listener_csb =
   (Sid_list =
   (Sid_desc =
   (ORACLE_HOME =/u02/Oracle/APP/Oracle/product/10.2.0/DB)
   (Sid_name = zjdevcsb)
   )
   )
   ######################################## #####
   Listener_csa =
   (Description_list =
   (Description =
   (Address_list =
   (Address = (Protocol = TCP) (host = 10.70.9.14) (Port = 1521 ))
   )
   )
   )
   Sid_list_listener_csa =
   (Sid_list =
   (Sid_desc =
   (ORACLE_HOME =/u02/Oracle/APP/Oracle/product/10.2.0/DB)
   (Sid_name = zjdevcsa)
   )
   )

   Above:
   1). listener_csb, listener_csa defines two listener names, and the parameters of these two listeners: IP and port.
   2 ). sid_list_listener_csb and sid_list_listener_csa define two listening services. The service name format is sid_list _ <lsnrname>, and the lsnrname is the name of the above two listeners, if this service is specified, the accepted service will be submitted to the listener. The Listener Instance name is zjdevcsb, and zjdevcsa is in tnsnames. the SERVICE_NAME parameter to be specified in ora corresponds.

   For detailed parameter descriptions, you can view the example listener. ora file.

5. Start Database
   Let's talk about the definition terms of the database.
   DatabaseIt refers to the file data on a physical disk, which is a set of tablespace files. It can only have one file and is unique. It is like the definition of a class.
   InstanceIs a service or process that loads database files. For the same database, you can have multiple instances, such as the RAC mode. Multiple instances load the same physical data.

   Oracle user logon Host:
   1) Start a database instance
   First, execute the command sqlplus "/As sysdba" to log on with the database system administrator role, then execute startup to start the database instance, close the database instance, and run the shutdown command. If you do not need to remotely connect to the database, you can log on to the database from the local machine. Run sqlplus username/password to log on to the database correctly.
   2) Listen
   Start the listener and execute the command LSNRCTL start. You can use LSNRCTL status to view the listener status. If the listener is started successfully, you can connect to the database remotely and run the command sqlplus username/password @ tnsname;
   Close the listener and execute the command LSNRCTL stop.

6. Remote database connection
   When you execute the command sqlplus username/password @ tnsname, the general process is as follows:
   1) query sqlnet. ora and check the resolution method of the name. It is tnsname or hostname.
   2) query the tnsnames. ora file, find the tnsname record from the file, and find the host name, port, and SERVICE_NAME.
   3) if there is no problem with the listener process, establish a connection with the listener process.
   4) according to different server modes, such as dedicated server mode or shared server mode, listener takes the next action. The default mode is dedicated server. If there is no problem, the client will connect to the server process of the database.
   5) at this time, the network connection has been established, and the historical mission of the listener process has been completed.
   If any errors occur when you connect to the database, you can find the corresponding configuration in the above order to solve the problem.

   Commands used to connect to databases
   Sqlplus "/As sysdba" is a typical operating system authentication. You do not need the listener process and can log on to the database even if it is unavailable.
   Sqlplus username/password does not require the listener process. log on to the local database and start the database instance.
   Sqlplus username/password @ tnsname requires the listener process. The most common remote logon mode is to start the database instance and listener process.

7. Terms involved in this article
   Db_domain: Defines the domain where a database is located. The domain name has nothing to do with the Internet 'region', but the database administrator decides to better manage the distributed database based on the actual situation. Of course, to facilitate management, it can be equal to the domain of the Internet.

   Global_name: The unique identifier of a database (Oracle Database). We recommend that you use this method to command the database. This value is determined when you create a database. The default value is db_name. db_domain. Any modifications to the parameters of db_name and db_domain In the parameter file will not affect the value of global_name. To modify global_name, you can only use the alter database rename global_name to <db_name.db_domain> command to modify it, modify the parameters.

   SERVICE_NAME: This parameter is newly introduced by Oracle8i. Before 8i, we used Sid to identify an instance of the database. However, in the parallel environment of Oracle, a database corresponds to multiple instances, so that multiple network service names are required, configuration is cumbersome. To facilitate the setting in the parallel environment, the SERVICE_NAME parameter is introduced, which corresponds to a database rather than an instance, and has many other advantages. The default value of this parameter is db_name. db_domain, which is equal to global_name. A database can correspond to multiple service_names for more flexible configuration. This parameter has no direct relationship with Sid, that is, the service is not required.
   Name must be the same as Sid.

8. Summary
   After learning to use the above three files, you can start the database, remotely connect to the database, and develop the system as needed.