Re: ADO/Oracle connection problems in ONames environment

Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message news:<51mutvov3tj4s3i0ksido4b46atpp017sm_at_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

Here's the latest on this issue...first some more background on the symptoms, then the results of my investigations.

  Some particulars regarding the multiple connection issue...this is only a problem when a single application is trying to open up multiple connections. I can open 30 applications requiring a single connection each with no problems. Using a test VB-app which simply generates multiple connections with any provided ADO connection string, I was able to conclude that this is not a problem with with the Oracle OLEDB provider or the Oracle ODBC provider. this is only a problem with the Microsoft providers for Oracle.

  After contacting Microsoft and Oracle, I was instructed to update both the client machine and the Names server with the 9.2.0.4 patch from Oracle. After finding that this did not solve the problem, Microsoft did some further digging and came up with documentation from the tomes of the arcane stating that NONE of the Microsoft-written ADO providers for Oracle support Oracle Names (TNSNames only), and also don't officially support Oracle platforms past 8i. This is a known issue, and they don't have any plans to fix the problem.

  Since Seattle took it upon themseves to stop official support past 8i, this means that developers who have problems with Internet Directory (or any other Oracle directory service) are on their own...use Oracle-written ADO providers if you want support, and don't call Seattle if you have problems with them.

  I appreciate your help in attempting to track down what was uptimately a phantom.

Ken Allhands Received on Wed Dec 17 2003 - 13:51:49 CST