Listener.ora, Sqlnet.ora, Tnsnames.ora, are placed in the $oracle_home\network\admin directory.
--begin Focus: The role and use of three files
#-----------------------
Sqlnet.ora (client and server side)-a nsswitch.conf file that acts like Linux or other Unix, this file determines how to find a connection string that appears in a connection,
For example, our client input
Sqlplus Sys/[email protected]
If my sqlnet.ora is like this,
Sqlnet. Authentication_services= (NTS)
NAMES. Directory_path= (Tnsnames,hostname)
Then, the client will first find the ORCL record in the Tnsnames.ora file. If there is no corresponding record, try to use ORCL as a hostname, through the network to resolve its IP address and then to connect the IP GLOBAL_DBNAME=ORCL this instance, Of course, I'm orcl here. Not a host name
If I were this way
NAMES. Directory_path= (TNSNames)
Then the client will only look for ORCL records from Tnsnames.ora.
There are other options in parentheses, such as LDAP, which are not commonly used.
#------------------------
Tnsnames.ora (client and server side)-This file is similar to the UNIX hosts file, providing tnsname to host name or IP, only if Sqlnet.ora is similar
NAMES. Directory_path= (TNSNames) In this case, that is, the client parses the connection string in the order of TNSNames is, will try to use this file.
Example of two, ORCL corresponding to the local machine, sales corresponding to another IP address, which also defines the use of the main server or shared server mode to connect, a sentence said
Tnsnames.ora manual configuration Add a remote or local connection
SALES = #你所要连接的时候输入得TNSNAME
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (host = 192.168.188.219) (port = 1521)) # This tnsname corresponds to the host, port, protocol
)
(Connect_data =
(server = dedicated) #使用专用服务器模式去连接需要跟服务器的模式匹配, if not, automatically adjusts according to the server's mode
(service_name = sales) # SID
)
)
#----------------------
Listener.ora (server side)--listener Listener process configuration file
As for the listener process, it is not much to say, accept remote access to the database and transfer the server process to Oracle. So if you are not using a remote connection, the listener process is not required, as is the case if shutting down the listener process does not affect the database connection that already exists.
Examples of Listener.ora files
#listener. Ora Network Configuration File: #E: \oracle\product\10.1.0\db_2\network\admin\listener.ora
# Generated by Oracle configuration tools.
#下面定义LISTENER进程为哪个实例提供服务
#这里是ORCL, and it corresponds to Oracle_home and global_dbname
#其中GLOBAL_DBNAME不是必需的除非使用HOSTNAME做数据库连接
Sid_list_listener =
(Sid_list =
(Sid_desc =
(Global_dbname = Boway)
(Oracle_home = E:\oracle\product\10.1.0\Db_2)
(Sid_name = ORCL)
)
)
#监听器的名字, a single database can have more than one listener
#再向下面是监听器监听的协议, IP, port, etc., use the tcp1521 port here, and make # with the host name
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = Boway) (PORT = 1521))
)
The above example is one of the simplest examples, but it is also the most common. A listener process provides services for a instance (SID).
--end Focus: The role and use of three files
--===================================================================
Action commands for listeners
$ORACLE _home/bin/lsnrctl Start, others such as Stop,status. After a specific knock on a lsnrctl see Help.
The three files mentioned above can be configured with a graphical configuration tool.
$ORACLE _HOME/NETCA Wizard-style
$ORACLE _home/netmgr
I am more accustomed to netmgr,
Profile configuration is the way Sqlnet.ora is called name resolution
Service name is configured with the Tnsnames.ora file
Listeners is configured with the Listener.ora file, which is the listener process
Specific configuration you can try it and then look at the configuration file.
So the overall structure is there, when you enter Sqlplus Sys/[email protected]
1. Query Sqlnet.ora look at the name of the parsing method, found to be tnsname
2. Query the Tnsnames.ora file, find the ORCL record from the inside, and find the hostname, port and Service_Name
3. If there is no problem with the listener process, establish a connection to the listener process.
4. Depending on the server mode, such as dedicated server mode or shared server mode, listener takes the next action. The default is the private server mode, and the client connects to the database's server process if there is no problem.
5. Now that the network connection has been established, the historical mission of the listener process has been completed.
#---------------
Forms of command used in several connections
1.sqlplus/as SYSDBA This is a typical operating system certification and does not require a listener process
2.sqlplus Sys/oracle This connection method can only connect to the native database, also does not need to listener process
3.sqlplus Sys/[email Protected] This approach requires the listener process to be in a usable state. Most commonly connected over the Internet.
The above connection mode uses the SYS user or other user authentication through the password file does not need the database to be available, the operating system authentication also does not need the database to be available, the ordinary user because is the database authentication, therefore the database must be in the open state.
Then there is
#-------------
It might be used in a wrong line.
1.lsnrctl Status View the state of the server-side listener process
Lsnrctl> Help
The following operations is 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.tnsping View the configuration of client Sqlnet.ora and Tnsname.ora files correctly or not, and the status of the corresponding server's listener process.
C:\>tnsping ORCL
TNS Ping Utility for 32-bit windows:version 10.1.0.2.0-production on 1 June-August-
2005 09:36:08
Copyright (c) 1997, 2003, 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 (msec)
3.
Sql>show SGA To see if instance has been started
Sql> select Open_mode from V$database; View whether the database is open or mount.
Open_mode
----------
READ WRITE
#-----------------
Example of using hostname to access a database instead of Tnsname
Using Tnsname to access the database is the default, but it also poses a problem, that is, the client needs to configure the Tnsnames.ora file. If your database server address changes, you will need to re-edit the client file. Accessing the database through hostname is not a problem.
Need to modify
Server-Side Listener.ora
#监听器的配置文件listener. Ora
#使用host naming no longer requires Tnsname.ora files 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 = Pl***tproc)
(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))
)
)
Client Sqlnet.ora You can remove tnsnames if you confirm that you are not using Tnsname access
# 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= (NTS)
NAMES. Directory_path= (HOSTNAME)
Tnsnames.ora files do not need to be configured, delete does not matter.
Here is the network and operating system configuration problems, how to resolve the problem of my host name
You can connect by using the following method
Sqlplus Sys/[email protected]
This will connect the Boway server and listener to determine the service_name you want to connect to.
-------------------------------------------------------------------------------
From:http://www.ixdba.net/hbcms/article/ec/231.html
Summarize:
1: Listen file Listener.ora tnsnames.ora The configuration recommendations for host are represented by IP.
2: If monitoring does not start or does not work properly after booting,
(1) First confirm the hostname of your OS, execute hostname command, try to ping "hostname" to see if it can pass,
(2) Then check the Listener.ora of the listener, tnsnames.ora whether the information about the host in the two configuration files is represented by the hostname.
(3) If yes, change to the new hostname, and then add the new hostname to the system's hosts file, Linux for/etc/hosts;
Then ping the new hostname, should be able to pass.
(4) If all are represented by IP, then add the new hostname directly to the system's hosts file.
Then ping the new hostname and it should be able to pass.
3: If the second step will not solve the problem,
(1) Check the instance information of the startup Oracle, select * fromv$instance;
Then look at the host name of the system, which should be equal.
(2) If the query is the old host name, try to "ping the old hostname" should not pass,
Through listener should also be linked not on;
(3) If it is a new hostname, if "ping new hostname" is not known,
Please modify the/etc/hosts file to add a new hostname, confirm that you can ping, and then restart Oracle
4: Note The settings for the TNS and listener files.
Specific procedures:
1) modify hostname to Www.ixdba.net
2) Modify the/etc/hosts, remove the original hostname of the row, increase the row
192.168.60.253 www.ixdba.net
3) Restart the database, query instance information
SELECT * from V$instance;
Get the new host_name for Www.ixdba.net
4) Modify the Listener.ora to change the host to a new hostname
5) Modify the Tnsname.ora, modify the corresponding host name to the new hostname
6) Restart Listener
Then connect Oracle/[email protected] should be able to succeed.
Oracle Sqlnet.ora File Description
(1) What really works is the Sqlnet.ora file, we modify the Sqlnet.ora is actually the best and quickest way.
Add the following sections to the Sqlnet.ora-----------------------------
# # # # # of attributes from Protocol.ora #
Tcp.validnode_checking=yes #允许访问的IP
Tcp.invited_nodes= (ip1,ip2 ...) #禁止访问的IP
Tcp.excluded_nodes= (Ip1,ip2 ...) after restarting the listener you need to be aware of:
1, Tcp.invited_nodes and tcp.excluded_nodes are present, to Tcp.invited_nodes-based
2, must permit or do not prohibit the server native IP address, otherwise through LSNRCTL will not be able to start or stop listening, because the process listener through the local IP access listener, and the IP is forbidden, but through the service startup or shutdown does not affect.
3, after the modification, it is necessary to restart the monitoring to take effect, and do not need to restart the database
4, any platform can be, but only applicable to the TCP/IP protocol
(2) When NET8 uses the TCP/IP protocol, setting the following parameters in the Sqlnet.ora of the oracle9i server can restrict or allow users to connect to the database from a specific client.
Tcp.validnode_checking=yes|no
Tcp.invited_nodes= (Ipaddress|hostname)
Tcp.excluded_nodes= (Ipaddress|hostname) where the tcp.validnode_checking parameter determines whether the client IP address is checked;
The tcp.invited_nodes parameter lists the IP addresses of the clients that are allowed to connect;
The Tcp.excluded_nodes parameter enumerates the IP addresses of clients that are not allowed to connect. (Note: Use the Protocol.ora file in Oracle8i, if you do not have the file, please create one yourself.) )
For example, by setting the oracle9i parameter file, you can control the IP address of the access computer. Added to the configuration file $oracle_home/network/sqlnet.ora on 172.28.65.13 this machine:
#开启对ip地址的检查 Tcp.validnode_checking=yes
#允许访问的ip tcp.invited_nodes= (172.28.65.13)
#禁止访问的ip ip.excluded_nodes= (172.27.65.15) Restart monitoring!
$ lsnrctl Reload Lsnrctl for solaris:version 9.2.0.4.0-production on 14-dec-2005 16:59:19 Copyright (c) 1991, 2002, Ora CLE Corporation. All rights reserved. Connecting to (Description= (address= (PROTOCOL=IPC) (KEY=EXTPROC0))) the command completed successfully. Edit the $oracle_home/network/admin/tnsnames.ora file on the 172.28.65.15 machine: DSF = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (host= 172.28.65.13) (Port = 1521)) (Connect_data = (SID = ORCL))) on 15 tnsping test: $ tnsping dsf TNS Ping Utility for Solaris:v Ersion 9.2.0.4.0-production on 14-dec-2005 17:04:02 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files:used TNSNames adapter to resolve the alias attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (host= 172.28.65.13) (Port = 1521)) (Connect_data = (SID = ORCL))) Tns-12537:tns:connection closed connection test: $ sqlplus Wacos/[email protected] sql*plus:release 9.2.0.4.0-production on Wed Dec 17:04:24 2005 Copyright (c) 1982, 2002 , Oracle CorporatIon. All rights reserved. Error:ora-12537:tns:connection closed
Listener.ora, Sqlnet.ora, Tnsnames.ora relationships and manual configuration
