Configure listener. ora, sqlnet. ora, and tnsnames. ora in the Oracle network.

Source: Internet
Author: User
Tags dedicated server

 

The listener. ora, sqlnet. ora, and tnsnames. ora configuration files of the Oracle network are all stored in the $ ORACLE_HOME \ Network \ ADMIN directory.

1. sqlnet. ora ----- is similar to the nsswitch. conf file of Linux or other UNIX. It is used to determine 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.

2. tnsnames. ora ------ 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.
Protocol: the protocol for communications between the client and the server, which is generally TCP.
HOST: The name or IP address of the machine on which the database listens. The database listens on the same machine as the database, so when I say that the machine where the database listens is generally the machine where the database is located. In Unix or windows, you can use the hostname command at the command prompt of the machine on which the database listens, or use ipconfig (for Windows) or ifconfig (for UNIX) command to obtain the IP address. Note that, whether using the machine name or IP address, you must ping the Host Name of the machine where the database is listening on the client, otherwise, the host name of the machine where the database listening is located must be added to the hosts file.
Port: the port on which the database listens. You can view the listener. ora file on the server or run the lnsrctl status [listener name] command at the command prompt on the machine where the database listens. The port value must be the same as the port on which the database listens.
SERVICE_NAME: on the server side, run the "sqlplus> show parameter SERVICE_NAME" command after logging on to the system.
The host corresponding to orcl and another IP address corresponding to sales, which also defines whether to use the master server or 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 =
# The dedicated server mode must match the server mode to connect. If not, it is automatically adjusted according to the server mode.
(Server = dedicated)
# View the corresponding SERVICE_NAME, sqlplus>; show parameter SERVICE_NAME;
(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)
)
)
Note: If the database server uses MTS, it is best to explicitly specify the dedicated direct connection mode when the client program needs to use database link. Otherwise, there will be many Oracle bugs related to the distributed environment. Generally, direct connection to the database server is better, unless your real-time database connection is close to 1000.

3. listener. ora ------ listener process configuration file
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 the service. Here it is orcl, And it corresponds to ORACLE_HOME and global_dbname. global_dbname is not required unless
# Using 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.
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. Yes
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.
The Influence of remote_login_passwordfile in init. ora on Identity Authentication
Three Optional values:
None: Default Value, indicating that the Oracle system does not use password files. Privileged users who perform identity authentication through the operating system have sysora and sysoper Permissions
Exclusive:
1. indicates that only one database instance can use a password file.
2. Grant the sysora and sysoper permissions to users other than sys.
Shared:
1. indicates that multiple database instances can use password files.
2. Do not assign the sysora and sysoper permissions to users other than sys.
Therefore, to log on as an operating system, set remote_login_passwordfile to none.
When the logged-on user is not a member of the ora_dba group or the ora_scheduler group, you must create the same user name for the current operating system user in Oracle. If the current user is a domain user, the user name is: domainname \ yourname: computername \ yourname
Creation method:
Create "domainname \ yourname" identified externally;
In the operating system, add auth_prefix_domain under HKEY_LOCAL_MACHINE \ SOFTWARE \ oracle \ home0 with the value set to false. You can ignore the domain name when creating an oracle user.
In this way, the init. ora parameter will affect how the database matches a Windows user and an oracle user OS _authent_prefix = ""
The default value is null. If this parameter is not set before Oracle8i, OPS $ is used as the username prefix. (the maximum length of the Oracle username is 30 characters)

About domain name (host name) Resolution
/Etc/hosts (UNIX)
Or windows \ hosts (Win98) winnt \ system32 \ drivers \ etc \ hosts (Win2000)
The client needs to write the correspondence between the IP address of the database server and the host name.

127.0.0.1 localhost
192.168.0.35 oracledb
192.168.0.45 Tomcat
202.84.10.193 bj_db

Sometimes, after configuring step 1, The tnsping Database Server alias is displayed successfully,
However, sqlplus username/password @ servicename does not work, and JDBC thin link does not work either,

Do not forget to do this step on the client, probably because the DNS server does not set the ing between the Server IP address and the host name.
If there are both private IP addresses and public IP addresses on the Internet, private IP addresses are written first, and public IP addresses are written later.
It is recommended to keep a backup before editing. When adding a line, you should also copy and paste it to avoid the error of editing the hosts space-time grid or Tab character.

For Oracle multi-database environments in UNIX, the OS client needs to configure the following two Environment Variables
Oracle_sid = appdb; export oracle_sid
Two_task = appdb; export two_task
To specify the default target database

#-------------
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
: Em11:
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.
Common troubleshooting methods:
TNS-12154: TNS: cocould not resolve service name
This error indicates that the network service name used for connection does not exist in the tnsnames. ora file. The network service name in tnsnames. ora above is only test. If you use sqlplus for connection
System/manager @ test1 then the TNS-12154 error is returned.
Note that sometimes even in tnsnames. the ora file contains the corresponding network service name, but an error occurs when you use this network service name to connect to the file. The following is a typical configuration (on the client machine ):
Sqlnet. ora file:
Names. directory_path = (tnsnames ,....)
Names. default_domain = server.com
Tnsnames. ora file:
Test =
(Description =
(Address_list = (address = (Protocol = TCP) (host = testserver) (Port = 1521 ))
)
(CONNECT_DATA = (SERVICE_NAME = orcl.testserver.com)
)
)
Basic mechanism of SQL * Plus operation:
After you enter sqlplus system/manager @ test, the sqlplus program automatically goes to sqlnet. find names in the ora file. default_domain parameter. If this parameter exists, the value in this parameter is taken out and added to the end of the network service name. In this example, your input is
System/manager @ test automatically becomes sqlplus
System/manager@test.server.com, and then find the test.server.com network service name in the tnsnames. ora file, which of course cannot be found, because the file only has the test network service name, so the error is reported. The solution is to comment out the names. default_domain parameter in the sqlnet. ora file, as shown in figure
# Names. default_domain = server.com. Suppose names. if the default_domain parameter does not exist, the sqlplus program directly goes to tnsnames. find the name of the test network service in the ora file, and then extract the host, port, TCP, and SERVICE_NAME from the file to send the connection request to the correct database server.
In principle, the configuration in tnsnames. ora is case-insensitive, but I do have a case-sensitive situation, so it is best to use the network service exactly the same as the configuration in tnsnames. ora.
ORA-12514: TNS: Listener cocould not resolve SERVICE_NAME given in connect descriptor.
This error indicates that the network service name can be found in tnsnames. ora, but the SERVICE_NAME specified in tnsnames. ora is inconsistent with the SERVICE_NAME on the server. The solution is to modify SERVICE_NAME in tnsnames. ora.
Confusing terms:
Db_name: Unique identifier of a database (Oracle Database). The database is an Oracle database. This expression is sufficient for a single database, but with the popularization of distributed databases composed of multiple databases, this method of Command database creates a certain burden on database management, because the names of various databases may be the same, resulting in management confusion. To solve this problem, the db_domain parameter is introduced. In this way, the database ID is determined by the two parameters db_name and db_domain, avoiding management confusion caused by database name duplication. This is similar to managing machine names on the Internet. We set db_name and
The db_domain parameters are connected with '.' to indicate a database, and the database name is global_name, that is, it extends db_name. The db_name parameter can only contain letters, numbers, '_', '#', and '$', and can contain a maximum of 8 characters.
Db_domain: 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 in order to better manage the distributed database. 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 command and then modify the corresponding 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.
Net service name: the name of the network service, also known as the database alias (Database alias ). It is required when the client program accesses the database. It shields the details of how the client connects to the server and achieves the location transparency of the database.
How to Use the configured network service name to connect to the database:
Use the sqlplus program to test the network service name, such as sqlplus system/manager @ test. If you cannot connect to the database, the test network service name (net
The db_domain parameter value of the Oracle database is added to the end of the service, which can be viewed by running the sqlplus> show parameter db_domain command. Here, the db_domain parameter value is testserver.com, which is added after the network service name. The modified tnsname. ora contains the following content about the network service name:

It should be changed:
How to Use the configured network service name to connect to the database:
Use the sqlplus program to test the network service name, such as sqlplus system/manager @ test. If you cannot connect to the database, the test network service name (net
Service) followed by sqlnet. names. the value of the default_domain parameter. Here, my parameter value is testserver.com. Add it to the name of the network service and the modified tnsname. the Network Service name in ora is as follows:

Under what circumstances will Oracle automatically set the names. default_domain parameter?
In this case, the typical environment is the Windows client's "my computer", "properties", "computer name", and "others... À the computer's primary DNS suffix 'sets 'Primary DNS suffix', because in this case, when the client is installed. names is automatically set in the ora file. the default_domain parameter may also occur when the computer is added to the domain to install the Oracle client. If you have any conditions, try it.

When setting the Oracle client, I usually manually modify tnsnames. ora file, but many people prefer to use a graphical tool to configure it. The graphic tool eventually modifies tnsnames. ora file, but it sometimes causes other problems:
When you configure the network service name using the Oracle graphics software 'net aser' or 'net configuration asis', if you have set 'Primary DNS suffix ', however, the name of the network service written in the graphic configuration software does not contain 'Primary DNS suffix '. For example, if you only write test, then, the graphic configuration software automatically adds 'Primary DNS suffix 'to test.testserver.com and has tnsnames. ora, regardless of your sqlnet. whether names exists in the ora file. default_domain parameter. In this case, the connection is tested using a graphical tool, but if the sqlnet. ora file does not contain
Names. default_domain parameter, you should use test.testserver.com in tnsnames. ora when using the network service name, instead of the test you typed in the graph configuration software. Solution:
You can set names. default_domain = testserver.com in the sqlnet. ora file. In this case, you can use test or test.testserver.com to connect to the database.
If the names. default_domain parameter is not set in the sqlnet. ora file, remove .testserver.com from test.testserver.com in the tnsnames. ora file. In this case, you can use test to connect to the database.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.