Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Database Connectivity issue on Net8 (Oracle 8.1)

Posted on 2006-04-03
9
Medium Priority
?
1,064 Views
Last Modified: 2011-09-20
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?
0
Comment
Question by:markrj1970
8 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 16359945
i'm missing some numbers here:

...Host=151
144.111....

could it be an copy error?

meikl ;-)
0
 

Author Comment

by:markrj1970
ID: 16360166
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
 
LVL 4

Expert Comment

by:marper
ID: 16361449
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:markrj1970
ID: 16361675
Thankyou for your comment, marper.

Does it apply to Oracle SQL*Plus v8.1.7 as well.
0
 
LVL 4

Expert Comment

by:marper
ID: 16361888
yes it does
0
 

Author Comment

by:markrj1970
ID: 16362084
I will check with the DBA and find out what the listener.ora file contains.

thanks
0
 

Author Comment

by:markrj1970
ID: 16751804
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
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 16826232
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question