Database Connectivity issue on Net8 (Oracle 8.1)

A long standing VB application is used to connect to a database which logs web pages visited by network users.

This VB application has worked, and occasionally stops connecting. The last time it failed to connect the server had to be restarted.

This time, the server is working fine, and the database is up and running.  A connection was established on the management console to the database using the program.

The TNSNAMES.ora file is a long standing file which is setup correctly.  TNS_ADMIN is set as an environment variable.


When TNSPING is attempted, the following is displayed:
TNS Ping Utility for 32-bit Windows: Version 8.1.7.0.0 - Production on 03-A
06 12:21:35

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Attempting to contact (ADDRESS=(COMMUNITY=tcp.world)(PROTOCOL=TCP)(Host=151
144.111)(Port=1521))
Message 3509 not found; No message file for product=NETWORK, facility=TNS

A level 16 trace log produces a large file as follows (only the first few lines are shown):
Message 245 not found; No message file for product=NETWORK, facility=NLT: Message 18300 not found; No message file for product=NETWORK, facility=NLT
Message 245 not found; No message file for product=NETWORK, facility=NLT: Message 18301 not found; No message file for product=NETWORK, facility=NLT
Message 245 not found; No message file for product=NETWORK, facility=NLT: Message 18302 not found; No message file for product=NETWORK, facility=NLT
Message 245 not found; No message file for product=NETWORK, facility=NLT: Message 18303 not found; No message file for product=NETWORK, facility=NLT
Message 245 not found; No message file for product=NETWORK, facility=NLT: Message 18304 not found; No message file for product=NETWORK, facility=NLT

The log produced by the VB app is as follows:
An Error has Occured in module = DBAccess unit = ysnConnect. With the following Error: 0:- at 03/04/2006 11:59:24.
An Error has Occured in module = DBAccess unit = adoconDBAccess_ConnectComplete. With the following Error: -2147218999:Error Occurred: ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
 at 03/04/2006 12:32:30.
An Error has Occured in module = DBAccess unit = ysnConnect. With the following Error: -2147467259:-2147467259: ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
 from Microsoft OLE DB Provider for Oracle
-2147467259: ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor


An Error has Occured in module = DBAccess unit = ysnConnect. With the following Error: 0:- at 03/04/2006 12:32:30.

I am not a database expert - Can anyone help?
markrj1970Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
i'm missing some numbers here:

...Host=151
144.111....

could it be an copy error?

meikl ;-)
0
markrj1970Author Commented:
kretzschmar - thanks for the quick reply.

Yes that was a copy error.

Since I initially posted the question, TNSPING has mysteriously started returning an OK message.  I have'nt made any changes, I just kept trying the hostname and pinged the ip address of the server, as well as the hostname of the server.

I get the message, "ORA-12514: TNS:listner could not resolve SERVICE_NAME given in connect descriptor", when attempting a basic SQL*PLUS connection, and still get an application error message which indicates the object (i.e. the oracle database) is closed.

Extract from TNSNAMES.ora:
logger.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = <ip_address>)
          (Port = 1521)
        )
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = <ip_address>)
          (Port = 1526)
        )
    )
    (CONNECT_DATA = (SID = logger)
    )
  )
0
marperCommented:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
                descriptor
--------------------------------------------------------------------------------


      Symptoms :
      ----------
      While logging in iSQL*Plus, the following error is encountered :-

      ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
               descriptor

      Note : In Oracle 10g, the error encountered is :-

             ORA-12514: TNS:listener does not currently know of service
                    requested in connect descriptor

      Cause :
      -------
      In the "<OracleHome>/network/admin/listener.ora" file (in this case,
      "<OracleHome>" refers to the Oracle Home where the Database is installed)
      of the server that hosts the Database (corresponding to the TNS Alias),
      the SID description of that Database does not exist.

      Fix :
      -----
                For better understanding, let us assume the following in this guide :

               Hostname : OraDB
               Domainname : oracle.com
               iSQL*Plus Port (in Oracle9i) : 80
               iSQL*Plus Port (in Oracle 10g) : 5560
              TNS Alias of the Oracle9i Database : Ora9iDB
              TNS Alias of the Oracle 10g Database : Ora10gDB
               Oracle Home (in Oracle9i) : /oracle/Ora9iDB
               Oracle Home (in Oracle 10g) : /oracle/Ora10gDB

           

      In order to fix this error, execute the following steps :-

          1. Open the '<OracleHome>/network/admin/listener.ora' file of the
             server that hosts the Database (corresponding to the TNS Alias)
             and go to the section that contains the following lines -

             SID_LIST_LISTENER =
               (SID_LIST =
                 (SID_DESC =
                   (SID_NAME = PLSExtProc)
                   (ORACLE_HOME = /oracle/Ora9iDB)
                   (PROGRAM = extproc)
                 )
               )

          2. Add the following lines after the 7th line in the above excerpt -

                 (SID_DESC =
                   (GLOBAL_DBNAME = Ora9iDB)
                   (ORACLE_HOME = /oracle/Ora9iDB)
                   (SID_NAME = Ora9iDB)
                 )

          3. Save the aforesaid change made in the '<OracleHome>/network/admin/
             listener.ora' file.
          4. Restart the TNS Listener corresponding to the Database.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

markrj1970Author Commented:
Thankyou for your comment, marper.

Does it apply to Oracle SQL*Plus v8.1.7 as well.
0
marperCommented:
yes it does
0
markrj1970Author Commented:
I will check with the DBA and find out what the listener.ora file contains.

thanks
0
markrj1970Author Commented:
I have discovered it was due to DNS names in the host and a single zone that the application was using.  Outside of this zone, the application did not connect.

Unsure how to give points for help.  Please advise.
0
CetusMODCommented:
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.