Introduction to the three configuration files of ORACLE

The three configuration files of ORACLE are described in the installation directory $ HOME/network/admin of oracle. 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. The user configures 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 something goes wrong with the database connection, when the network is normal and there is no firewall interference, follow these steps: 1) Check sqlnet in the client order. ora, tnsnames. ora is faulty. 2) Check the listener. ora configuration on the server side, ensure that the listener starts, and load the database service. For more information about sqlnet. ora, 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 domain definition. When you use sqlplus to access the database ". domain "Example: sqlnet. in ora: NAMES. DEFAULT_DOMAIN = comtnsnames. the tns in ora is defined as follows: local_dev = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.111) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = linuxdb) when executing the command on the client: sqlplus [email = username/password @ local_dev] username/password @ local_dev [/email], the following error message is displayed: "ORA-12154: TNS: failed to handle error messages like service name "or" ORA-12154: TNS: cocould not resolve service name. Because sqlplus [email = username/password @ local_dev] username/password @ local_dev [/email] converts the tns alias "local_dev" to "local_dev.com", local_dev.com is in tnsnames. if not found in ora, an error is returned. Modify tnsnames. the definition in ora is as follows: local_dev.com = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.111) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = linuxdb) then execute sqlplus [email = username/password @ local_dev] username/password @ local_dev [/email]. The connection is successful. 2) NAMES. DIRECTORY_PATH defines the matching method used when the client connects to the database. Example: sqlnet. ora contains the following content: NAMES. DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME), when you connect to the database using sqlplus [email = username/password @ local_dev] username/password @ local_dev [/email] on the client, tnsnames is used first. the alias of ora is configured to connect to the database. If the database is connected, ONAMES is used for parsing, and the host Name is used for parsing. ONAMES indicates that Oracle uses its own Name Server (Oracle Name Server) for parsing, currently, Oracle recommends using Lightweight Directory Access Protocol LDAP to replace ONAMES; HOSTNAME indicates using host file, DNS, NIS, etc. for parsing; 3) SQLNET. AUTHENTICATION_SERVICES defines 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. The content of the example sqlnet. ora is as follows: SQLNET. AUTHENTICATION_SERVICES = (NONE) When you execute 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. Tnsnames. ora provides detailed information about the client connection to a database, including the host address, port, and database instance name. 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. SERVICE_NAME is the service name of the database. After logging in with the system user, run the sqlplus> show parameter service_name command to view the service name. This configuration file is usually used by many people, and most people will, I believe there will be no big problem. The listener. oratnslsnr process is a listener that listens to and accepts requests from a remote database connection. listener. ora is the configuration file of the tnslsnr process, and all the listening parameters are read from the 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 of listener. content in 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 and LISTENER_CSA define the two listener names of the listener and the parameters of the 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. Start the database first, let's talk about the definition of the database. The term database refers to the file data on the physical disk, which is a set of tablespace files and can only have one file and is unique. It is like the definition of a class. An instance is 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) to start the database instance, first execute the command sqlplus "/as sysdba" to log on as the database system administrator, and then execute startup to start the database instance, shut down 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 to 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, run the sqlplus [email = username/password @ tnsname] username/password @ tnsname [/email] command to disable the listener and run the lsnrctl stop command. Remotely connect to the database when you execute the command sqlplus [email = username/password @ tnsname] username/password @ tnsname [/email], the general process is as follows: 1) query sqlnet. ora looks at the name resolution method and finds 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. The Command Format sqlplus "/as sysdba" is used to connect to the database. This is a typical operating system authentication and does not require the listener process. You 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 [email = username/password @ tnsname] username/password @ tnsname [/email] requires the listener process. The most common remote logon mode is to start the database instance and listener process. The term Db_domain involved in this article: defines the domain where a database is located. The domain name has nothing to do with the 'region' on the Internet, it is determined by the database administrator based on the actual situation to better manage distributed databases. 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 name must be the same as the SID.