Re: ADO/Oracle connection problems in ONames environment

From: Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl>
Date: Tue, 16 Dec 2003 20:23:01 +0100
Message-ID: <51mutvov3tj4s3i0ksido4b46atpp017sm@4ax.com>

On 16 Dec 2003 04:53:36 -0800, kallhands_at_redskytech.com (Ken Allhands) wrote:

>Hello Sybrand,
>
> Here is the sqlnet.ora from the client machine...
>
># SQLNET.ORA Network Configuration File:
>D:\oracle\ora92\NETWORK\ADMIN\sqlnet.ora
># Generated by Oracle configuration tools.
>NAMES.INITIAL_RETRY_TIMEOUT = 30
>TRACE_DIRECTORY_CLIENT = c:\temp
>LOG_FILE_CLIENT = sqlnet
>NAMES.PREFERRED_SERVERS =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = RSTTST1)(PORT = 1575))
> )
>LOG_DIRECTORY_CLIENT = c:\temp
>NAMESCTL.NOCONFIRM = TRUE
>TRACE_FILE_CLIENT = sqlnet
>TRACE_LEVEL_CLIENT = SUPPORT
>#NAMES.DIRECTORY_PATH= (ONAMES)
>#NAMES.DEFAULT_DOMAIN = world
>#SQLNET.AUTHENTICATION_SERVICES= (NONE)
>NAMES.REQUEST_RETRIES = 2
>SQLNET.EXPIRE_TIME = 30
>NAMES.DIRECTORY_PATH= (ONAMES)
>BEQUEATH_DETACH = YES
>NAMES.MAX_OPEN_CONNECTIONS = 10
>
>
> ...here is names.ora..
>
>7:57 AM 12/16/2003# NAMES.ORA Network Configuration File:
>D:\oracle\ora92\NETWORK\ADMIN\names.ora
># Generated by Oracle configuration tools.
>NAMES.SERVER_NAME = RSTTST1.REDSKYTECH.COM
>NAMES.ADDRESSES =
> (ADDRESS = (PROTOCOL = TCP)(HOST = rsttst1)(PORT = 1575))
>#----ADDED BY names 12-DEC-2003 15:02:42---
>names.log_file = names.log
>names.trace_file = names_1432.trc
>names.trace_level = 0
>names.domains = (DOMAIN_LIST=(DOMAIN=(NAME=)(MIN_TTL=86400)))
>#------------------------------------------
>
>
> ...the lines that really confuse me from the trace are as
>follows...
>
>niqname: Using nnfsn2a() to build connect descriptor for (possibly
>remote) database.
> ...If the esixting database ID info is to be cached, why is the
>second connection not sure that the database is remote?
>
>nncpsrq_send_request: attempt 1,
>name server with metric 0 at
>(ADDRESS=(PROTOCOL=TCP)(HOST=RSTTST1)(PORT=1575)) (connection was
>open)...
> ...Why the second attempt to contact the names server? This does
>not show up in activity for second connecton when not using
>ADO/MSDAORA to instantiate the connection.
>
>nncpsrq_send_request: name server timeout, no response in 2000 csecs
> ...average response time from the Names server on initial
>connection (or all connections from SQLPLUS instances) is in the
>single-milisecond range.
>
>Thanks again for your time and your help.
>Ken Allhands
>
>

Hi Ken,
this is getting more and more confusing. Especially the names.ora definitely doesn't look ok.
If you look at
http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96581/names.htm you see in figure 10-1 a sample names.ora Normally the connection info is in an Oracle database, it is dumped to a cache file, and the cache file is refreshed at regular intervals.

NAMES.SERVER_NAME=namesvr2.us.acme.com
NAMES.ADDRESSES=
(ADDRESS=(PROTOCOL=tcp)(HOST=namesvr2-server)(PORT=1575)) NAMES.ADMIN_REGION=
(REGION=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))     (CONNECT_DATA=
      (SERVICE_NAME=sales.us.acme.com))
  (USERID=system)
    (PASSWORD=manager)
    (NAME=local_region)

  (REFRESH=86400)
  (RETRY=60)
  (EXPIRE=600))

NAMES.DOMAIN_HINTS=
(HINT_DESC=
(HINT_LIST=

     (HINT= 
      (NAME=namesvr1)
      (ADDRESS=(PROTOCOL=tcp)(HOST=namesvr1-server)(PORT=1575))

NAMES.DOMAINS=
(DOMAIN=
(NAME=us.acme.com)

(MIN_TTL=86400)) You will see that under names.admin_region, and the refresh keyword. This seems to be lacking from your definition, hence you have one single flat file which is never refreshed (ie you don't have a 'region database' as the docs call it)
The second connection attempt is made courtesy of sqlnet.ora, apparently there is no response in a timely fashion. Right now (and I would need to recall the syntax myself) my advice would be to use the namesctl program to verify whether you can resolve the address using query commands. The output of the query command (IIRC the syntax is
query <service_name> a.smd)
will also show how the request was resolved. apart from that you could use DOS ping and tracert to isolate any plain network problems.
I would also try setting the trace_level to 16 in sqlnet.ora, it should show you more (IIRC again the keyword SUPPORT translates to 15) We still need to see any pertinent error message. Anyway, as the names server is up and running, regardless of having other connection methods in names.directory_path, the connection attempt won't fall back to those other connection methods. In other words: the Names problem needs to be resolved.

Hth

--
Sybrand Bakker, Senior Oracle DBA

Received on Tue Dec 16 2003 - 13:23:01 CST