I have two databases on the same machine (ODA X7-S).
A DEV database running 12.2.0.1.180417
A TEST database running 18.3.0.0.180717
Both use the same sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (none)
NAMES.DEFAULT_DOMAIN= localdomain.local
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
# settings to find out why EZCONNECT takes so long when wrong identifier used
TCP.CONNECT_TIMEOUT=5
TNSPING.TRACE_LEVEL=admin
Problem: when a connect identifier can not be resolved it takes ages to give that info on the 18c version. Wheras on 12c it is fast.
Dev = instant
[oracle@ODA01 admin]$ soe XXXDEV
Environment <XXXDEV>:
=====================
Setting Value
--------------- -----------------------------------------
NLS_DATE_FORMAT "YYYY-MM-DD HH24:MI:SS"
NLS_LANG "AMERICAN_GERMANY.AL32UTF8"
ORACLE_BASE /u01/app/oracle
ORACLE_HOME /u01/app/oracle/product/12.2.0.1/dbhome_3
ORACLE_SID XXXDEV
ORACLE_UNQNAME XXXDEV
[oracle@ODA01 admin]$ time tnsping dummy
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 13-MAR-2019 12:32:52
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/dbhome_3/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
real 0m0.009s
user 0m0.005s
sys 0m0.003s
[oracle@ODA01 admin]$
TEST = 20 seconds
[oracle@ODA01 admin]$ soe XXXTEST
Environment <XXXTEST>:
======================
Setting Value
--------------- -----------------------------------------
NLS_DATE_FORMAT "YYYY-MM-DD HH24:MI:SS"
NLS_LANG "AMERICAN_GERMANY.AL32UTF8"
ORACLE_BASE /u01/app/oracle
ORACLE_HOME /u01/app/oracle/product/18.0.0.0/dbhome_1
ORACLE_SID XXXTEST
ORACLE_UNQNAME XXXTEST
[oracle@ODA01 admin]$ time tnsping dummy
TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 13-MAR-2019 12:35:00
Copyright (c) 1997, 2018, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/18.0.0.0/dbhome_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
real 0m20.028s
user 0m0.002s
sys 0m0.007s
[oracle@ODA01 admin]$
The tnsping trace shows that there is a dns lookup made during EZCONNECT, although the connection string does not match the rules for it.
TEST
Here is the relevant section from the trace file
[13-MAR-2019 12:35:00:905] nnfgsrdp: Setting path:
[13-MAR-2019 12:35:00:905] nnfgsrdp: checking element TNSNAMES
[13-MAR-2019 12:35:00:905] nnfgsrdp: checking element EZCONNECT
[13-MAR-2019 12:35:00:905] nnfgsrdp: Path set
[13-MAR-2019 12:35:00:905] nnfun2a: entry
[13-MAR-2019 12:35:00:905] nlolgobj: entry
[13-MAR-2019 12:35:00:905] nnfgrne: entry
[13-MAR-2019 12:35:00:905] nnfgrne: Going though read path adapters
[13-MAR-2019 12:35:00:905] nnfgrne: Switching to TNSNAMES adapter
[13-MAR-2019 12:35:00:906] nnftboot: entry
[13-MAR-2019 12:35:00:906] nlpaxini: entry
[13-MAR-2019 12:35:00:906] nlpaxini: exit
[13-MAR-2019 12:35:00:906] nnftmlf_make_local_addrfile: entry
[13-MAR-2019 12:35:00:906] nnftmlf_make_local_addrfile: construction of local names file failed
[13-MAR-2019 12:35:00:906] nnftmlf_make_local_addrfile: exit
[13-MAR-2019 12:35:00:906] nlpaxini: entry
[13-MAR-2019 12:35:00:906] nlpaxini: exit
[13-MAR-2019 12:35:00:906] nnftmlf_make_system_addrfile: entry
[13-MAR-2019 12:35:00:906] nnftmlf_make_system_addrfile: system names file is /u01/app/oracle/product/18.0.0.0/dbhome_1/network/admin/tnsnames.ora
[13-MAR-2019 12:35:00:906] nnftmlf_make_system_addrfile: exit
[13-MAR-2019 12:35:00:906] nnftboot: exit
[13-MAR-2019 12:35:00:906] nnftrne: entry
[13-MAR-2019 12:35:00:906] nnftrne: Original name: dummy
[13-MAR-2019 12:35:00:906] nnfttran: entry
[13-MAR-2019 12:35:00:906] nnfgrne: Query unsuccessful, skipping to next adapter
[13-MAR-2019 12:35:00:906] nnfgrne: Switching to EZCONNECT adapter
[13-MAR-2019 12:35:00:906] nnfhboot: entry
[13-MAR-2019 12:35:00:906] nnfhboot: exit
[13-MAR-2019 12:35:00:906] snlinGetAddrInfo: entry
[13-MAR-2019 12:35:10:916] snlinGetAddrInfo: getaddrinfo() failed with error -3
[13-MAR-2019 12:35:10:916] snlinGetAddrInfo: exit
[13-MAR-2019 12:35:10:916] snlinGetAddrInfo: entry
[13-MAR-2019 12:35:20:924] snlinGetAddrInfo: getaddrinfo() failed with error -3
[13-MAR-2019 12:35:20:925] snlinGetAddrInfo: exit
[13-MAR-2019 12:35:20:925] nnfgrne: Query unsuccessful, skipping to next adapter
[13-MAR-2019 12:35:20:925] nnfgrne: exit
[13-MAR-2019 12:35:20:925] nnfun2a: address for name "dummy" not found
[13-MAR-2019 12:35:20:925] nnfun2awanm: Getting the path of sqlnet.ora
[13-MAR-2019 12:35:20:925] nnfun2awanm: Getting the path of local and system tnsnames.ora
[13-MAR-2019 12:35:20:925] nnfun2awanm: exit
Can anyone reproduce that issue? Are there any other settings that might influence that behaviour?
I'm not sure if it is a bug, there could be some settings that I overlook.
I do suspect a kind of regression issue. There was a bugfix in 11g with regards to HOSTNAMES/EZCONNECT ( Doc ID 561429.1 ).