Solved

ora-12154 Error

Posted on 1998-09-10
4
1,263 Views
Last Modified: 2008-02-20
We are running Oracle8 w/ the SQL v.2.2 from Oracle 7 - I have a user that is using SAS Access w/Oracle to get to our datawarehouse - when he runs the program using SAS he gets an error of ORA-12154-TNS: Could not resolve service name.  I have checked the login; TNS names; applied the ORA8.FIX from Oracle and nothing has worked.  
0
Comment
Question by:boydj
  • 2
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
junfeb earned 200 total points
Comment Utility
Is this your configuration -
Oracle 8 - Server
Oracle sql*net - 2.2 on server ???

What is the  version of sql*net on client ?

net8 is only useful for plus8 and none of the other client products including designer and
developer.

Please make sure that you have a tnsnames.ora under ORACLE_HOME/network/admin
and the host name is right and correct.

If you are using XXX as your connect string, does
the entry in the tnsnames.ora file start with XXX
or XXX.WORLD? If .world is appended to the alias
in tnsnames.ora, do you have
names.default_domain=world in your sqlnet.ora file? Remove the .world if you don't need it.

Also here is a bulletin about 12154
-------------------------------------------------
Summary:
ORA-12154 WITH SQL*NET V2

+=+

Problem Description:  
====================  
 
You are getting this error message: ORA-12154: could not resolve db name
server. This error occurs when trying to connect via SQL*Net Version 2.  
 
 
 
Problem Explanation:
====================
 
ORA-12154 TNS-12154
12154, 00000, "TNS:could not resolve service name"
 
*Cause: The service name specified is not defined correctly in the      
        TNSNAMES.ORA file.
*Action: Make the following checks and correct the error:
        - Verify that a TNSNAMES.ORA file exists and is in the proper place
        and accessible. See the operating system specific manual for details
        on the required name and location.
        - Check to see that the service name exists in one of the  
        TNSNAMES.ORA files and add it if necessary.
        - Make sure there are no syntax errors anywhere in the file.
        Carefully look for unmatched parentheses or stray characters. Any
        error in a TNSNAMES.ORA file will make it unusable.  
        See Chapter 4 in the SQL*Net V2 Administrator's Guide. If possible,
        regenerate the configuration files using the Oracle Network Manager.


+==+

Diagnostics and References:

 * {403.6,Y,100}     USING SQL*NET V2
 * {775.6,Y,100}     ORA-12154 (TNS-12154): TNS:COULD NOT RESOLVE DATABASE NAME


2. Soln# 2014802.6  DIAGNOSING AN ORA-12154 USING SQL*NET V2 ON WINDOWS

Solution ID         : 2014802.6
For Problem         : 1004114.6
Affected Platforms  : Microsoft Windows
Affected Products   : SQL*Net
Affected Components : NET V02.XX
Affected Oracle Vsn : Generic

Summary:
DIAGNOSING AN ORA-12154 USING SQL*NET V2 ON WINDOWS

+=+

Solution Description:
=====================
 
 
                       WIN: ORA-12154 and SQL*Net V2  
   
The ORA-12154 means that SQL*Net could not find the database name    
specified in the connect string defined as an alias in the TNSNAMES.ORA.  
   
If connecting from a Windows client, after verifying your database is turned
on, check the following:  
   
1.  If connecting via a login box, do not begin the field for the connect  
    string with an @ sign.  The @ sign should only be used if specifying  
    the connect string immediately after the password (with no spaces  
    between the password or the @ sign).  
   
    For SQL*Net V2, the field for the connect string should be:  
       
         tns:db_alias   (if the application you are connecting with  
                         uses ora6win.dll)  
  - or -  
 
         db_alias       (if the application you are connecting with  
                         uses ora7win.dll)  
   
    If you are not sure whether your application uses ora6win.dll or  
    ora7win.dll, go ahead and specify the tns: prefix.  Ora7win.dll  
    will accept the tns:prefix as well.  
   
2.  Make sure that you do have a TNSNAMES.ORA defined on    
    the client and that defined in the TNSNAMES.ORA is the  
    descriptor alias that you are specifying in the connect    
    string.  By default, the TNSNAMES.ORA file should be in the  
    ORACLE_HOME\network\admin directory.  
 
3.  Make sure that there are no mismatched parentheses in the  
    TNSNAMES.ORA.  
 
4.  If the configuration files were not created with one of the configuration
    tools, many syntax errors may exist.  The solution is to create the
    configuration files using NET_CONF or Network Manager, depending on what
    version of SQL*Net you're using.  One possible error is not having the
    correct alias name.  If you have 'service_name' instead of the actual
    alias, this would be incorrect.  Usually the alias is set to the SID.
 
5.  If you are not using MPI (MultiProtocol Interchange), remove the  
    tnsnav.ora.
 
6.  Turn on client tracing and re-execute the operation.  The client trace  
    file will show a secondary error code.  To turn on client tracing, add  
    (or change) the variable 'trace_level_client=16' in the file  
     C:\ORAWIN\NETWORK\ADMIN\SQLNET.ORA.
 
7.  If you are using TCP/IP, try replacing the HOST=HOSTNAME with
    HOST=IPADDRESS in the TNSNAMES.ORA file.  
 
*** Note: Before editing any of the configuration files please remember  
to make back up copies.***
 
8.  Check the SQLNET.ORA file for the parameter 'names.default_zone=<world>
    If this exists comment out the following parameter in the SQLNET.ORA  
    file:
 
         name.default_zone=<world>
     
    If you comment out this parameter you must also remove the .world suffix
    in the SQLNET.ORA file and comment out the :names.default_domain=world
     
9.  If this is a single community, try commenting out the 'COMMUNITY'
    parameter in the TNSNAMES.ORA file with the STANDARD '#' symbol.
 
    <Alias> =
      (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS =
    #         (COMMUNITY = SAMPLE_COMMUNITY)
              (PROTOCOL = TCP)
              (HOST = <SERVER>)
              (PORT = 1521)
            )
        )
        (CONNECT_DATA =
          (SID = <SID>)
        )
      )
 
10.  Check for multiple copies of your TNSNAMES.ORA file. Also make sure that  
     there are no duplicate copies of SQLNET.ORA.        
 
11.  Check the SQLNET.ORA file.  If the file contains the parameter
     names.default_domain=<string>, where string is the default domain
     extension for client connections (netman sets it to world by default)
     then the TNSNAMES.ORA service names must have the extension .<string>.
     Otherwise, SQL*Net V2 connection requests will generate ora-12154 errors.
 
     If the TNSNAMES.ORA file service name contains the extension .<string>
     and the user is getting ORA-12154 on connnect attempts with service  
     names without the extension then the names.default_domain=<string> must  
     be added to the SQLNET.ORA file.
 
12.  Add the TNS_ADMIN variable to the oracle.ini file and set it to the
     directory where TNSNAMES.ORA and SQLNET.ORA are located.
 
13.  If you are downloading SQL*Net from a file server to a Windows PC make
     sure you have a vsl.ini file on your PC.  
 
Be sure to reboot the Windows operating system after making any changes to the
 V2 configuration files TNSNAMES.ORA, or SQLNET.ORA.  The changes will then go
into effect.
 
 
***Note: Please refer to RTSS Bulletin #105308.522 for more details on the
TNSNAMES.ORA and an overview of SQL*Net Version 2.***  
-----------------------------------------------------------------------------  
                                            Oracle WorldWide Customer Support  
   
   
 


+==+

References:

 ref: {105.6}     BUL-105308.522
 ref: {638.6}     PRE-1003826.6

3. Soln# 2014830.6  ORA-12154 WITH SQL*NET V2.X ON OS/2

Solution ID         : 2014830.6
For Problem         : 1004114.6
Affected Platforms  : OS/2-V2.x
Affected Products   : SQL*Net
Affected Components : NET V02.XX
Affected Oracle Vsn : Generic

Summary:
ORA-12154 WITH SQL*NET V2 ON OS/2

+=+

Solution Description:
=====================
 
                     OS2: ORA-12154 and SQL*Net V2  
   
   
The ORA-12154 means that SQL*Net could not find the database name    
specified in the connect string defined as an alias in the TNSNAMES.ORA.  
   
If connecting from an OS/2 client, check the following:  
   
1.  If connecting via a login box, do not begin the field for the connect  
    string with an @ sign.   The @ sign should only be used if specifying  
    the connect string immediately after the password (with no spaces  
    between the password or the @ sign).  
   
    For SQL*Net V2, the field for the connect    
    string should just be:  
   
          tns:db_alias  
   
     - or -  
   
          db_alias  
   
2.  Make sure that you do have a TNSNAMES.ORA defined on    
    the client and that defined in the TNSNAMES.ORA is the  
    descriptor alias that you are specifying in the connect    
    string.  By default, the TNSNAMES.ORA should be in the  
    ORACLE_HOME\network\admin directory.    
   
3.  Also make sure that there are no mismatched  
    parentheses in the TNSNAMES.ORA.
 
4.  If you are using TCP/IP, try replacing the HOST=HOSTNAME with
    HOST=IPADDRESS in the TNSNAMES.ORA file.  
 
*** Note: Before editing any of the configuration files please remember  
    to make back up copies.
 
5.  Verify that you have a SQLNET.ORA file and check it for the
    parameter 'names.default_zone=<world>
    If this exists comment out that parameter in the SQLNET.ORA file:
 
             #names.default_zone=<world>
 
6.  If this is a single community, try commenting out the 'COMMUNITY'
    parameter in the TNSNAMES.ORA file with the STANDARD '#' symbol.
 
    <Alias> =
      (DESCRIPTION =
        (ADDRESS_LIST =
        (ADDRESS =
    #         (COMMUNITY = SAMPLE_COMMUNITY)
              (PROTOCOL = TCP)
              (HOST = <SERVER>)
              (PORT = 1521)
            )
        )
        (CONNECT_DATA =
          (SID = <SID>)
        )
      )
 
7.  Check the SQLNET.ORA file.  If the file contains the parameter
    names.default_domain=<string>, where string is the default domain
    extension for client connections (netman sets it to world by default)
    then the TNSNAMES.ORA service names must have the extension .<string>,
    otherwise SQL*NET V2 connection requests will generate ORA-12154 errors.
 
    If the TNSNAMES.ORA file service name contains the extension .<string>
    and the user is getting ORA-12154 on connnects attempts with service names
    without the extension then the names.default_domain=<string> must be
    added to the SQLNET.ORA file.
 
   
NOTE: Please refer to RTSS Bulletin #105308.522 for more  
      details on the TNSNAMES.ORA and an overview of SQL*Net  
      Version 2.  
-----------------------------------------------------------------------------  
                                           Oracle WorldWide Customer Support  


+==+

References:

 ref: {106.6}     BUL-105308.522

4. Soln# 2014833.6  ORA-12154 WITH SQL*NET V2 ON UNIX

Solution ID         : 2014833.6
For Problem         : 1004114.6
Affected Platforms  : Generic: not platform specific
Affected Products   : SQL*Net
Affected Components : NET V02.XX
Affected Oracle Vsn : Generic

Summary:
ORA-12154 WITH SQL*NET V2 ON UNIX

+=+

Solution Description:
=====================
   
                    UNIX: ORA-12154 and SQL*Net V2  
   
   
The ORA-12154 means that SQL*Net could not find the database name specified in  
the connect string defined as an alias in the TNSNAMES.ORA.  
   
If connecting from an Unix client, check the following:  
   
1.  Make sure that you do have a TNSNAMES.ORA file defined on  
    the client and that defined in the TNSNAMES.ORA is the  
    descriptor alias that you are specifying in the connect  
    string.  
 
2.  Also make sure that there are no mismatched parentheses in the
    TNSNAMES.ORA.  
 
3.  Check to make sure that TNS_ADMIN points to the directory where the  
    TNSNAMES.ORA and LISTENER.ORA files are located.  
 
    To do so in cshell:  
 
    % env | grep TNS_ADMIN  
 
    If this does not point to the correct directory, set the TNS_ADMIN  
    environment variable to point to that directory.  
 
    % setenv TNS_ADMIN <directory path>  
 
4.  Check your configuration files to ensure that, when the files were  
    transferred from the client to the server, CTRL-M (^M) characters weren't  
    inserted at the ends of lines. Also make sure CTRL-R (^R) characters  
    weren't insterted at the ends of the lines. They could be in the  
    tnsnames.ora and sqlnet.ora files.
 
5.  Verify that your connect string is correct.  If the TNSNAMES.ORA  
    alias name is different than the SID, be sure that you are using the  
    TNSNAMES.ORA alias and not the SID.  
 
    Sample V2 connect string:  
    sqlplus <user>/<passwd>@<TNSNAMES.ORA alias name>  
 
    Example:  
    sqlplus scott/tiger@V715tcp  
 
    With this example TNSNAMES.ORA file:  
 
    V715tcp =  
      (DESCRIPTION =  
        (ADDRESS_LIST =  
            (ADDRESS =  
              (protocol = TCP)  
              (host = singapore)  
              (port = 2000)  
            )  
        )  
        (CONNECT_DATA =  
          (SID = V715)  
        )  
      )
 
6.  Check for multiple copies of your TNSNAMES.ORA file.
 
7.  Check the SQLNET.ORA file.  If the file contains the parameter
    names.default_domain=<string>, where string is the default domain
    extension for client connections (netman sets it to world by default)
    then the TNSNAMES.ORA service names must have the extension .<string>,
    otherwise SQL*Net V2 connection requests will generate ORA-12154 errors.
 
    If the TNSNAMES.ORA file service name contains the extension .<string>
    and the user is getting ORA-12154 on connnects attempts with sevice names
    without the extension then the names.default_domain=<string> must be
    added to the SQLNET.ORA file.
 
 
 
The TNSNAMES.ORA can be in one of 3 locations:  
 
Search Path Priority for SQL*Net V2 files  
=========================================  
 
This section discusses the search path priority for SQL*Net V2 configuration  
files, LISTENER.ORA and TNSNAMES.ORA.  Oracle does the following to search for  
these configuration files:  
 
1) Checks to see if TNS_ADMIN is set and looks in the directory that  
   environment variable is pointing to.  
2) Checks the /etc directory.
3) Checks the $ORACLE_HOME/network/admin directory.
 
 
-----------------------------------------------------------------------------  
                                            Oracle WorldWide Customer Support  
 
 


+==+

References:

 ref: {1373.6}     PRE-1005550.6

5. Soln# 2025958.6  BUG #211512 ON WINDOWS

Solution ID         : 2025958.6
For Problem         : 1004114.6
Affected Platforms  : Microsoft Windows
Affected Products   : SQL*Net
Affected Components : NET V02.XX
Affected Oracle Vsn : Generic

Summary:
BUG #211512 ON WINDOWS

+=+

Solution Description:  
=====================  
 
An ORA-12154 can also result from bug #211512 - a problem with the file  
handlers being allocated.  One symptom of this problem is that tracing for  
SQL*Net v2 cannot be turned on.  The workaround for this problem is to close  
other Windows applications to allocate more file handlers.  If you're using  
Microsoft Access, a possible workaround would be to ensure the application
does not have a lot of objects and establish a connection.  Afterwards, you
may be able to establish a connection with an application having more objects.  
 


Hope this helps.


0
 

Author Comment

by:boydj
Comment Utility
It Works!!!!!!!!!  Thanks for the wonderful help - you've solved two weeks worth of headaches!!
0
 
LVL 3

Expert Comment

by:junfeb
Comment Utility
Could you please post the details.

Thanks.
0
 

Author Comment

by:boydj
Comment Utility
After reading the info you sent I went back and looked at the address/path the user was using in SAS and then at the connect string in the ODBC - I changed the string to match exactly what we have in the tns.names and what the user had in SAS and he can now connect and run the program.  This was an interesting error - we also use Microsoft Access to connect to Oracle server and it has worked from the beginning.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now