Relationship between listener. ora, sqlnet. ora, and tnsnames. ora

Three configuration files
　　
Listener. ora, sqlnet. ora, and tnsnames. ora are all stored in the $ ORACLE_HOME \ network \ admin directory.
　　
-- Begin focus: Roles and usage of the three files
　　
#-----------------------
Sqlnet. ora (client and server) -- used in a file similar to linux or other unix nsswitch. conf. This file determines how to find a connection string that appears in the connection,
　　
For example, we enter
　　
Sqlplus sys/oracle @ orcl
　　
Assume that my sqlnet. ora is like the following:
　　
SQLNET. AUTHENTICATION_SERVICES = (CNT)
　　
NAMES. DIRECTORY_PATH = (TNSNAMES, HOSTNAME)
　　
Then, the client will first be in tnsnames. orcl records in the ora file. if there is no corresponding record, try to use orcl as a host name, resolve its ip address through the network, and then connect to the instance GLOBAL_DBNAME = orcl on this ip address, of course, here, orcl is not a host name.
　　
If I look like this
　　
NAMES. DIRECTORY_PATH = (TNSNAMES)
　　
Then the client will only search for orcl records from tnsnames. ora
　　
There are other options in brackets, such as LDAP, which are not commonly used.
　　
#------------------------
Tnsnames. ora (client and server) -- this file is similar to the unix hosts file. It provides the corresponding tnsname to the host name or ip address, only when sqlnet. ora is similar
　　
NAMES. DIRECTORY_PATH = (TNSNAMES), that is, the client will try to use this file only when TNSNAMES is in the order in which the client parses the connection string.
　　
In this example, there are two local IP addresses corresponding to ORCL and another IP address corresponding to SALES, which also defines whether to use the master server or the Shared Server Mode for connection.
　　
# Enter TNSNAME when you want to connect
　　
ORCL =
　　
(DESCRIPTION =
　　
(ADDRESS_LIST =
　　
# The host, port, and Protocol corresponding to this TNSNAME
　　
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521 ))
　　
)
　　
(CONNECT_DATA =
　　
# Use the dedicated server mode to connect to the server. The connection mode must match the server mode. If not, the connection is based on the server mode.
　　
# Automatic Adjustment
　　
(SERVER = DEDICATED)
　　
# Corresponding service_name, SQLPLUS> show parameter service_name;
　　
# View
　　
(SERVICE_NAME = orcl)
　　
)
　　
)
　　
# The following is similar
　　
SALES =
　　
(DESCRIPTION =
　　
(ADDRESS_LIST =
　　
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.188.219) (PORT = 1521 ))
　　
)
　　
(CONNECT_DATA =
　　
(SERVER = DEDICATED)
　　
(SERVICE_NAME = sales)
　　
)
　　
)
　　
#----------------------
Listener. ora (server side) -- configuration file of listener process
　　
The listener process is not much said. The listener process accepts remote database access requests and transfers them to the oracle server process. Therefore, if you do not use a remote connection, the listener process is not required. Similarly, disabling the listener process does not affect the existing database connection.
　　
Example of Listener. ora File
　　
# Listener. ora Network Configuration File: # E: \ oracle \ product \ 10.1.0 \ Db_2 \ NETWORK \ ADMIN \ listener. ora
　　
# Generated by Oracle configuration tools.
　　
# The following defines the instance for which the LISTENER process provides services
　　
# Here Is ORCL, And it corresponds to ORACLE_HOME and GLOBAL_DBNAME
　　
# GLOBAL_DBNAME is not required unless you use HOSTNAME for database connection
　　
SID_LIST_LISTENER =
　　
(SID_LIST =
　　
(SID_DESC =
　　
(GLOBAL_DBNAME = boway)
　　
(ORACLE_HOME = E: \ oracle \ product \ 10.1.0 \ Db_2)
　　
(Sid_name = orcl)
　　
)
　　
)
　　
# Listener name. A database can have more than one listener.
　　
# Next we will talk to the listener about the protocol, IP address, port, etc. Here we use the tcp1521 port and use the # Host Name
　　
Listener =
　　
(Description =
　　
(Address = (Protocol = TCP) (host = boway) (Port = 1521 ))
　　
)
　　
The above example is the simplest, but also the most common. A listener process provides services for an instance (SID.

-- End: the role and use of the three files

-- ===================================================== ======================================
　　
Listener Operation Command
　　
$ ORACLE_HOME/bin/LSNRCTL start, others such as stop and status. After you have typed an LSNRCTL, you can view the help information.
　　
The three files mentioned above can be configured through the graphical Configuration tool.
　　
$ ORACLE_HOME/netca wizard
　　
$ ORACLE_HOME/netmgr
　　
I am used to netmgr,
　　
The profile is configured with sqlnet. ora, that is, the name resolution method.
　　
The service name is configured with the tnsnames. ora file.
　　
Listeners configures the listener. ora file, that is, the listener process.
　　
For more information about the configuration, see the configuration file.
　　
In this way, the overall structure is available when you enter sqlplus sys/Oracle @ orcl.
　　
1. query sqlnet. ora and check the name resolution method. It is tnsname.
　　
2. query the tnsnames. ora file, find the orcl 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. Depending on 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.

#---------------
　　
Commands used for several connections
　　
1. sqlplus/As sysdba this is a typical operating system authentication and does not require the listener process
　　
2. sqlplus sys/Oracle can only connect to the local database, and the listener process is not required.
　　
3. sqlplus sys/Oracle @ orcl requires the listener process to be available. The most common connection is through a network.
　　
The preceding connection method does not require the database to be available, but does not require the database to be available for operating system authentication, because the common user is a database authentication, therefore, the database must be in the open state.
　　
Then
　　
#-------------
　　
Usually troubleshooting may be used
　　
1. LSNRCTL status check the status of the listener process on the server
　　
LSNRCTL> help
　　
The following operations are available
　　
An asterisk (*) denotes a modifier or extended command:
　　
Start Stop status
　　
Services version reload
　　
Save_config trace change_password
　　
Quit exit Set *
　　
Show *
　　
LSNRCTL> Status
　　
2. Check whether the sqlnet. ora and tnsname. ora files on the client are correctly configured and the status of the listener process on the corresponding server.
　　
C: \> tnsping orcl
　　
TNS Ping utility for 32-bit windows: Version 10.1.0.2.0-production on 16-8 months-
　　
09:36:08 2005
　　
Copyright (c) 1997,200 3, Oracle. All rights reserved.
　　
Used parameter files:
　　
E: \ oracle \ product \ 10.1.0 \ db_2 \ Network \ admin \ sqlnet. ora
　　
Used tnsnames adapter to resolve the alias
　　
Attempting to contact (description = (address_list = (address = (Protocol = TCP)
　　
(Host = 127.0.0.1) (Port = 1521) (CONNECT_DATA = (Server = dedicated) (Service _
　　
Name = orcl )))
　　
OK (20 msec)
　　
3.
　　
SQL> show SGA check whether the instance has been started
　　
SQL> select open_mode from V $ database; check whether the database is on or on.
　　
Open_mode
　　
----------
　　
Read Write
　　
#-----------------
　　
Example of using hostname to access the database rather than tnsname
　　
Using tnsname to access the database is the default method, but it also brings about some problems, that is, the client needs to configure the tnsnames. ora file. If the address of your database server changes, you need to re-edit the file on the client. Accessing the database through hostname does not cause this problem.
　　
Need to modify
　　
Server-side listener. ora
　　
# Listener Configuration File listener. ora
　　
# Using host naming, The tnsname. ora file is no longer required for local Parsing
　　
# Listener. ora Network Configuration File: d: \ oracle \ product \ 10.1.0 \ db_1 \ NETWORK \ ADMIN \ listener. ora
　　
# Generated by Oracle configuration tools.
　　
SID_LIST_LISTENER =
　　
(SID_LIST =
　　
(SID_DESC =
　　
# (SID_NAME = PLSExtProc)
　　
(SID_NAME = orcl)
　　
(GLOBAL_DBNAME = boway)
　　
(ORACLE_HOME = d: \ oracle \ product \ 10.1.0 \ db_1)
　　
# (PROGRAM = extproc)
　　
)
　　
)
　　
LISTENER =
　　
(DESCRIPTION_LIST =
　　
(DESCRIPTION =
　　
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC ))
　　
)
　　
(DESCRIPTION =
　　
(ADDRESS = (PROTOCOL = TCP) (HOST = boway) (PORT = 1521 ))
　　
)
　　
)
　　
If the client sqlnet. ora does not use TNSNAME for access, remove TNSNAMES.
　　
# Sqlnet. ora Network Configuration File: d: \ oracle \ product \ 10.1.0 \ db_1 \ NETWORK \ ADMIN \ sqlnet. ora
　　
# Generated by Oracle configuration tools.
　　
SQLNET. AUTHENTICATION_SERVICES = (CNT)
　　
NAMES. DIRECTORY_PATH = (HOSTNAME)
　　
The Tnsnames. ora file does not need to be configured, and deletion does not matter.
　　
The following is a problem with the network and operating system configuration. How can I resolve my host name?
　　
You can connect through the following method:
　　
Sqlplus sys/oracle @ boway
　　
In this case, the boway server will be connected and the listener will be used to determine the service_name you want to connect.

-------------------------------------------------------------------------------
From: http://www.ixdba.net/hbcms/article/ec/231.html
Summary:
1: ip addresses are recommended for host configuration in the listener file listener. ora tnsnames. ora,
2: If the listener cannot be started or cannot be used properly after startup,
(1) first confirm the hostname of your OS, execute the hostname command, and try to ping "hostname" to see if it works,
(2) check whether the host information in the listener. ora and tnsnames. ora configuration files of the listener is represented by the host name.
(3) If yes, change it to the new host name and add the new host name to the system hosts file. in Linux, the new host name is/etc/hosts;
Then ping the new host name, which should be accessible.
(4) If all are expressed by IP addresses, simply add the new host names to the system hosts file.
Then ping the new host name.

3: If the problem persists in step 2,

(1) Check the information of the Oracle instance that is started. Select * fromv $ instance;
Then, check the host names of the current system. The two names should be equal.
(2) If the old host name is queried, it is impossible to try to "ping the old host name,
The listener interface cannot be connected;
(3) if it is a new host name, if "Ping new host name" fails,
Modify the/etc/hosts file to add a new host name, make sure the host can be pinged, and restart Oracle

4: Pay attention to the settings of the TNS and listener files.

Procedure:

1) Change the hostname to www.ixdba.net.
2) Modify/etc/hosts, remove the line of the original host name, and add the line
192.168.60.253 www.ixdba.net
3) restart the database and query instance information.
Select * from V $ instance;
The new host_name is www.ixdba.net.
4) Modify listener. ora and change host to the new host name.
5) Modify tnsname. ora and change the corresponding HOST to the new HOST name.
6) Restart listener.
Connect oracle/oracle @ standby should be successful.