Very long SQL connection opening time

Recently, We have migrated our Oracle 9.2.0.6 production database from Windows 32bit RAC (2 nodes) to Linux 64 bit environment. Linux 64 bit environment has 8 times more memory than Windows 32 bit environment and Linux 64 bit has faster disk ( RAID 10 compare to RAID 5 of windows).

However, after this migration, we have noticed sql connection time has been increased significantly. Some time, it is taking 16 seconds to open a connection. We have used different driver (JDBC, ODBC, OCI) and SQLPlus. But, we have noticed no difference in sql connection time. We have investigated network, but noticed no problem with net开发者_运维知识库work.

Some time, it is taking around 16 seconds to open a single connection. This database has around 50000 user schema.

Can anybody tell me what can be done to reduce the connection opening time? Your help would be appreciated.

if suddenly the tnsping is become very slow(means it's a all right system before), but ping to the same database server is fast, The most possible reason is the listener.log file is too large:

$ORACLE_HOME/network/log/listener.log

Some OS (SunOS in my experience) will meet writing to a very large text file performance issue(exceed 4G?), so tail/clean the log file will immediately solve the tnsping takes very long time problem.

It was DNS in our case. After our admin commented out all DNS entries on the side of the server the system started responding without delay.

I also experienced very slow connection establishments with SQL Developer 4.1.5.21 on Mac OS against Oracle 12.1.0.2.0.

To analyze this I installed Oracle Instant Client to check connectivity directly with sqlplus. When I tried to connect with sqlplus it gave me the following error:

$ sqlplus <USER>/<PASSWORD>@<HOST>:<PORT>/<SERVICE>
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 16 10:50:44 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-21561: OID generation failed

I then found blog entry Oracle ORA-21561 : OID generation failed and added the hostname (as returned by the hostname command) to the 127.0.0.1-line in /etc/hosts:

127.0.0.1 localhost MacBook-Pro.local

This fixed the ORA-21561 error in sqlplus as well as the slow connection establishment in SQL Developer.

There are many possible reasons for this, and without more information its difficult to determine the root cause.

Having said that, check your sqlnet.ora file located on the machine your connecting from (i.e. <oracle_home>/network/admin/sqlnet.ora) and see if you have a line such as:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

If so, try changing it to:

SQLNET.AUTHENTICATION_SERVICES= (NONE)

Save and retry your connection via SQLPlus.

Also, check the value you have set for NAMES.DIRECTORY_PATH. Make sure the method you're using to connect to your instance is the first in the list. For example, we use TNSNAMES and ours reads:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Hope this helps.

Things that might indicate problem areas:

From the client:

• are all clients exhibiting the problem?
• is ping server reasonably fast?
• is tnsping dbname slow?
• can you connect normally with telnet?

From the server:

• is ping client reasonably fast?
• is nslookup of client by name and IP fast?
• is tnsping dbname slow?
• is sqlplus user@dbname fast or slow?
• is a direct connection on server fast? I.E., export ORACLE_SID=db;sqlplus user/pw