Shulou(Shulou.com)06/01 Report--

Today, I saw a post on the forum. The problem is that the client sqlplus is very slow to connect to the database: http://www.itpub.net/thread-2090550-1-1.html. Then, according to the description and analysis of the problem, I did a test in the test environment and added my virtual host to the domain environment for testing. Then I thought of a similar problem I encountered last year: http://www.itpub.net/thread-2072574-1-1.html.

Experimental environment

Operating system: Windows Server 2008 R2, join the AD domain environment

Database: Oracle 11.2.0.4

Problem description:

1. When you connect to the database by using sqlplus xxx/ssss@yyy from the client, it is very slow to connect to the database for the first time. Basically, it takes dozens of seconds to connect to the database. The next few connections are normal, but it becomes slow to connect to the database later.

2. There are a large number of WARNING: inbound connection timed out (ORA-3136) error messages in the log files of the database.

Exclusion process:

1. Check the error of ORA-03136 and add the following parameters to the monitoring of the database service according to the information found on the Internet.

LSNRCTL > set inbound_connect_timeout 0

And save, at this time, the ORA-03136 error is no longer reported in the database, but there is still a slow problem of connecting from the client to the oracle database server

2. When the client connects to the database service, the hardware load of the host of the database is not high.

3. Both the listening file of the server and the host address in the tnsname.ora of the client use the ip address.

4. Try to connect with sqlplus xxx/ssss@yyy on the server for many times, and the speed is normal. It feels like there is no problem on the server side.

5. There is no packet loss from the listening address of the client ping server.

6. Using tnsping on the client side to test the resolution to the server is also very fast, within milliseconds.

7. Through the tests of 5 and 6, it feels that the client-to-server network is normal.

8. Continue to query and find that this article is similar to mine on the Internet. Http://blog.csdn.net/jaray/article/details/36008871 later changed the value of SQLNET.AUTHENTICATION_SERVICES in sqlnet.ora on the server to none according to the method in this article.

9. Use sqlplus to test the connection from the client to the server again. The speed is normal, and the connection is successful in a few seconds.

10. Try to change that value to NTS, and the fault occurs again

Problem analysis:

In the SQLNET.AUTHENTICATION_SERVICES setting of sqlnet.ora, if you set NTS, Windows authentication is preferred, then Oracle authentication is used, while none is set, only Oracle authentication is used. Since the environment I tested joins the AD domain, if Windows authentication is given priority, Oracle will verify through the remote AD domain server, and then Oracle verification will be performed after verification failure, so the speed will be very slow.

Resolve the problem:

Set the SQLNET.AUTHENTICATION_ services value of sqlnet.ora to none

At this point, the problem analysis reminds me of a similar problem I encountered last year: when I remotely connect to an Oracle database through sqlplus, sometimes I can connect, but what I can't connect to? (as shown in the following figure)

The following is a description of sqlnet.ora in the official Oracle11g documentation:

*

The sqlnet.ora file is the profile configuration file. It resides on the client machines and the database server. Profiles are stored and implemented using this file. The database server can be configured with access control parameters in the sqlnet.ora file. These parameters specify whether clients are allowed or denied access based on the protocol.

The sqlnet.ora file enables you to do the following:

Specify the client domain to append to unqualified names

Prioritize naming methods

Enable logging and tracing features

Route connections through specific processes

Configure parameters for external naming

Configure Oracle Advanced Security

Use protocol-specific parameters to restrict access to the database

By default, the sqlnet.ora file is located in the ORACLE_HOME/network/admin directory. The sqlnet.ora file can also be stored in the directory specified by the TNS_ADMIN environment variable.

*

As can be seen from the description, the file controls the client's access to the database server, and a variety of parameters can be set for access control. For more information on the configuration of other parameters, please refer to the official documentation. The most frequently contacted parameter is sqlnet.authentication_services. The official description is as follows:

*

SQLNET.AUTHENTICATION_SERVICES

Purpose

To enable one or more authentication services. If authentication has been installed, then it is recommended that this parameter be set to either noneor to one of the authentication methods.

Default

None

Note:

When installing the database with Database Configuration Assistant (DBCA), this parameter may be set to nts in the sqlnet.ora file.

Values

Authentication Methods Available with Oracle Net Services:

O none for no authentication methods, including Microsoft Windows native operating system authentication. WhenSQLNET.AUTHENTICATION_SERVICES is set to none, a valid user name and password can be used to access the database.

O all for all authentication methods.

O nts for Microsoft Windows native operating system authentication.

Authentication Methods Available with Oracle Advanced Security:

O kerberos5 for Kerberos authentication.

O radius for RADIUS authentication.

O tcps for SSL authentication.

*

You can learn from the description of the parameters that two services are provided, the net service and the advanced security service. Advanced security service parameters are not discussed here, but the functions of the three parameters none / all / nts are discussed.

Set the sqlnet.authentication_services:

None: does not allow users to log in to the database through the os system, and needs to provide a user name and password; all: allows all login methods; nts: user authentication of the local operating system that functions as windows

Note: it should be stated that the user name and password refer to the user with sysdba authority; if you log in to the database with the system user oracle on linux, you need to set it to all or leave this field unadded.

For questions about restricting access to IP addresses through SQLNET.ora files, please refer to Master Gai's blog:

Http://www.eygle.com/archives/2008/01/sqlnetora_ip_limit.html

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

ITPUB: http://blog.itpub.net/31015730/

51CTO: http://seiang.blog.51cto.com/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.