Solved

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

Posted on 2001-07-24
21
8,419 Views
Last Modified: 2007-11-27
I tried install Oracle8i on my machine and everything looks fine. After that, i created a new database called CKWB. Everything seems fine and i can connect to the CKWB database using either SQLPlus or DBA Studio and the application runs well on the database.
However after i reboot my machine, the OracleServiceCKWB cannot be started. I tried disable it and restart it but i got this error message, "Could not start OracleServiceCKWB on local computer, Error 1503:The service did not respond to the start or control request in a timely fashion."
When i tried to test the connection using Net8 Assistant, i got the following message, "ORA-12514:TNS:listener could not resolve SERVICE_NAME given in connect descriptor. There may be an error in the fields entered, or the server may not be ready for connection."

Following is the content of my tnsnames.ora file in the D:\ORANT8I\network\admin

CKWB.EXCEL_SG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = meilee)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ckwb)
    )
  )

EXTPROC_CONNECTION_DATA.EXCEL_SG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

And this is the listener.ora file in my D:\ORANT8I\network\admin

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = meilee)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\ORANT8I)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ckwb)
      (ORACLE_HOME = D:\ORANT8I)
      (SID_NAME = ckwb)
    )
  )

I have been struggling for the whole day trying to resolve the problem but failed. Really appreciate if anybody lend me a hand in this problem.
Thank you very much.


Jane
0
Comment
Question by:Janelau
  • 7
  • 2
  • 2
  • +9
21 Comments
 
LVL 2

Expert Comment

by:RMZ
Comment Utility
hi
8I SERVER OR PERSONAL (nt/win2000)
---rmz---
0
 

Expert Comment

by:aminmohsalih
Comment Utility
hi
open listener.ora file.

LISTENER =
 (ADDRESS_LIST =
       (ADDRESS =
         (PROTOCOL = TCP)
         (Host = Default)
         (Port = 1521)
       )
       (ADDRESS =
         (PROTOCOL = TCP)
         (Host = 127.0.0.1)
         (Port = 1521)
       )
#        (ADDRESS =
#          (PROTOCOL = SPX)
#          (Service = Default_lsnr)
#        )
 )
etc.etc.

What i didn't realise was that the listener would only look at the top address - at least that was what
it was doing on my machine. Once I amended the file to:
LISTENER =
 (ADDRESS_LIST =
       (ADDRESS =
         (PROTOCOL = TCP)
         (Host = 127.0.0.1)
         (Port = 1521)
       )
#        (ADDRESS =
#          (PROTOCOL = SPX)
#          (Service = Default_lsnr)
#        )
 )

---rmz---
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
I hope You will derive helpfull hints in my generic comment for these problems.

You can use the "Oracle Net 8 assistant", "Oracle Net 8 Easy config" to see if you have the appropriate entry to the server in the local tnsnames.ora file (find it!). I mean that one in the Forms&Reports home, not that one in the Oracle_Home.
Under local I mean the file tnsnames.ora  in your application, not at the Oracle Server - this is a common mistake.

You have to have there an entry like:

REKS816.RILA.us =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bigbluenew.rila.us)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = REKS816)
     )
  )

It is a bad practice, but you can add such entry using simple text editor. But before that make a copy of tnsnames.ora in order to restore it if you do not succeed.

After that go to "control panel" and run "services".
Look at your Oracle_Home_TNSlistener80 process. Stop it! Start it again!

If you work on Unix find the relevant processes and notions of the Unix OS.

On NT server: The listener has a corresponding 'service' running. To be able to connect from the network, the service had to be configured to "Log on as: This account ......." and the account had to have administrator privileges on the network.


Two common areas of misconfiguration are responsible for these log-in errors:

(1) Erroneous entries in the TNSNAMES.ORA file, and  
(2) An erroneous default domain setting in the SQLNET.ORA file

A successful client log-in requires that:

1. The database is running.
2. The listener on the host machine is running and is configured correctly.
3. The network is correctly routing TCP/IP packets.
4. The client machine has TCP/IP installed correctly.
5. The client machine has SQL*Net installed correctly.
6. The TNSNAMES.ORA entry for that connection has the correct information.
7. The SQLNET.ORA file is configured correctly.

This solution covers common SQL*Net misconfiguration issues on the client side, and assumes that all
other necessary aspects of the connection are correctly configured and operating properly.

Please refer to the attached files for configuration information.

######################################################

The TNSNAMES.ORA file

The log-in dialog box of the client asks for a connect
string to identify the database in which the client account is located.
The TNSNAMES.ORA file is then searched for this connect string, and the actual  
connect information is found.

Below is a sample TNSNAMES.ORA entry for connecting to a machine named  
"oas.this.com" in DNS. The connection is via TCP/IP to port 1521, and  
the SID of the database containing the server is V732.

oas =  
  (description=  
     (address=(protocol=tcp)(host=oas.this.com)(port=1521))  
     (connect_data=(sid=V732))  
  )  
 
Note: the parentheses are extremely important - omitted or extraneous  
parentheses will result in an invalid entry. On the other hand, white space,
such as tabs, spaces, or newlines, are ignored.

The "host=" entry shown above implies DNS availability; if DNS is not
available, the IP address number may be substituted.


The SQLNET.ORA file:

Before being searched for in the TNSNAMES.ORA file, the connect string  
supplied in the log-in dialog box may be modified if the NAMES.DEFAULT_DOMAIN
variable has a value.

During login, the connect string is parsed to determine whether or not it is
fully-qualified, i.e. that it has full domain information. If it does not, and
the NAMES.DEFAULT_DOMAIN variable is set, then that value is appended to the  
connect string.  

For example, using a connect string "conn" and a NAMES.DEFAULT_DOMAIN  
setting of "oas.this.com" will result in a final connect string of  
"conn.oas.this.com". It is this final value "conn.oas.this.com" that will be  
searched for in the TNSNAMES.ORA file; thus, your entry in the TNSNAMES.ORA
file should start with

conn.oas.this.com =  


Using the TNSPING utility to test connect strings:

To test whether or not a connect string is valid, the utility TNSPING is
provided in the $ORACLE_HOME\bin directory. Run TNSPING with the connect
string you wish to test.  

Example 1:

D:\ORAWIN95\BIN>tnsping test

TNS Ping Utility for 32-bit Windows: Version 2.3.3.0.0 - Production on 02-JUN-
97 18:39:09

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

Attempting to contact  
(ADDRESS=(COMMUNITY=tcp.oas.this.com)(PROTOCOL=TCP)(Host=
conn.oas.this.com)(Port=1521))
OK (170 msec)

<end example>


From this result, we see that the connect string "test" resolves correctly to
an entry in the TNSNAMES.ORA file to valid connect information for an Oracle  
database.

On the other hand, the following is a test of an invalid connect string:


Example 2:

D:\ORAWIN95\BIN>tnsping notgood

TNS Ping Utility for 32-bit Windows: Version 2.3.3.0.0 - Production on 04-JUN-
97 09:13:32

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

TNS-03505: Failed to resolve name

<end example>

Using TNSPING as a test ensures that your TNSNAMES.ORA entry syntax is
correct, and that there is a listener on the host machine listening for
requests for that port.


~~~~~     ~~~~~     ~~~~~     ~~~~~     ~~~~~     ~~~~~     ~~~~~     ~~~~~     ~~~~~     ~~~~~

What follows here are several common problem descriptions & their solutions.


Problem Description:
====================
       You receive an ORA-12162 "TNS:service name is incorrectly
specified" when attempting a Sqlplus (804)/Net8 login with the following
syntax:
       sqlplus <userid>@<alias>
Enter password: <password>

Problem Explanation:
====================
Due to a parsing error with Sqlplus (8.0.4 only) the connect descriptor
is not beeing read properly.

Parsing error does not occur using other utilities, ie. tnsping,
svrmgrl or other versions of Sqlplus.
Using the tnsping utility you are able to resolve the connect string
and verify a listener process is responding.

For example:

[Alpha]> tnsping N804

TNS Ping Utility for Solaris: Version 8.0.4.0.0 - Production on 15-AUG-99 08:03:
04

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

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(Host=Alpha)(Port=1521))
OK (60 msec)



Solution Description:
=====================
Try using a full command line syntax for Sqlplus to avoid this parsing
problem.  For example, at the command prompt use the following syntax:

         sqlplus system/manager@N804

Versus the previous syntax where you were prompted for the password:

       sqlplus system@N804
Enter password: manager    <password is not visible when using this syntax>


If you are able to connect with using the full line syntax, you are
running into Base Bug:611696 for Sqlplus Version 8.0.4.  This bug is fixed in Sqlplus
release 8.0.5, and serveral backports are available, depending on you platform.


Problem Description
-------------------

TNSPING works fine but connecting via SQL*PLUS fails with an ORA-12545. Normally this indicates some

syntax issue with the TNSNAMES.ORA which is not the cause.

Turn on client tracing in the sqlnet.ora file TRACE_CLIENT_LEVEL = 16

nscall: connecting...
nsc2addr: entry
nttbnd2addr: entry
nttbnd2addr: port resolved to 2929
nttbnd2addr: looking up IP addr for host: tcp2.oas.this.com
nttbnd2addr:  *** hostname lookup failure! ***
nttbnd2addr: exit

The problem in this case the tnsnames.ora, sqlnet.ora and hosts file had the following entries that
caused the the
lookup of the IP addr to fail when resolving the address in the host file.

TNSNAMES.ORA
~~~~~~~~~~~~~
HOST = 196.6.122.28 or tcp2

Calling address: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(Host=196.6.122.28)(Port=1521))(ADDRESS=(PROTOCOL=TCP)

SQLNET.ORA
~~~~~~~~~~~
names.default_domain = oas.this.com
name.default_zone = oas.this.com

HOST File (WINNT/system32/hosts)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
196.6.122.28     tcp2.oas.this.com


Solution Description
--------------------

To correct the problem ensure the following:

TNSNAMES.ORA
~~~~~~~~~~~~
HOST = tcp2.oas.this.com

SQLNET.ORA
~~~~~~~~~~
names.default_domain = oas.this.com
name.default_zone = oas.this.com

HOSTS File
~~~~~~~~~~
196.6.122.28     oas.this.com

Explanation
-----------
The Calling address in the tnsnames.ora and host file has a different domain_name then in the sqlnet.ora
thus
causing ORA-12545.


Problem Description
-------------------

The connection is refused. You know that the user exists and the password is
correct and the database is up.

You cannot connect from sql*plus on the client to your database. You get
the following error message;

ORA-12705 Invalid or unknown NLS parameter value specified.

Solution Description
--------------------

Check your NLS_LANG settings.
The value entered is incorrect or there is a typo error.
For example, the NLS_LANG value for the United States English should be:

NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1
You do the following:

Create domain user accounts on Windows NT using Windows scripting, Enterprise
Administrator, or a third party administration tool.

Problem
=========
Certain users cannot authenticate successfully with any Oracle OLAP client
product, such as Express Spreadsheet Add-in (XSA), Express Admininstrator, or
Express Analyzer (OEA), but can authenticate successfully using the Express
Connection Utility if they do the following:

-  Open the Express Connection Utility.

-  Select Options...Set Domain Identity, and enter the User ID, Domain, and
Password.

-  Select Options...Set Authentication Level, and choose Connect.

-  Select File...Open, choose Connect to remote Express Server 5 or 6, and
enter the following information:

Object UUID:    <Your OES UUID>
Transport:      ncacn_ip_tcp
Host:           <Your host name>

Solution summary
================
In User Manager for Domains, check the Profile for the failing user and make
sure the Home Directory does not have a UNC address specified in the "Local
path:" field.
       
Authentication failure can occur if Oracle Express Server is not able to get a
valid Home Directory.  This can occur if, in the Windows NT User Environment
Profile, Home Directory groupbox, the "Local path:" option is chosen, but a UNC
path is referenced in the field, e.g.:

\\Servername\Sharename.

Only a local path on the machine is valid for this field.

You can check this by doing the following:

-  Login to the Domain Controller as an Administrator.

-  Run the User Manager for Domains utility in the Administrative Tools.

-  Double-click on a failing user to open the User Properties dialog box.

-  Click on the Profile button.

If the "Local path:" option is selected in the Home Directory groupbox,  a UNC
path such as \\Servername\Sharename is not valid.

Normally, if you select the "Local path:" option and try to enter a UNC path,
you will get an error message as follows:

\\Servername\Sharename is an invalid path name.  Please enter a valid path name.

If you are using Windows scripting or an administration tool such as Enterprise
Administrator to create user accouts, it may be possible to create a user
profile with an invalid UNC path in the "Local path:" field.
         
0
 
LVL 6

Expert Comment

by:M-Ali
Comment Utility
If your database is version 8.1.7 on Windows 2000, it is a bug for autostart in services. Check if u can start the database manually i.e. using DBA Studio or Server Manager.

The available patch for the file "orannts8.dll" is at:

ftp://oracle-ftp.oracle.com/server/patchsets/wgt_tech/server/windowsNT/network/net8/81701/

Download, stop services, copy the new file, and restart services. It should work.

Ali
0
 

Expert Comment

by:cz0763
Comment Utility
If the OracleServiceCKWB is not starting then a good place to check for more specific errors is in the NT/2K event viewer.  Check the Application log and the System log for any errors related to the service.
0
 

Expert Comment

by:mharoon
Comment Utility
replace (SERVICE_NAME = ckwb) with
(SERVICE_NAME = CKWB.EXCEL_SG)
   
0
 

Expert Comment

by:vsync007
Comment Utility
Hi,
You just rename the tnsnames parameter,

service_name  as sid

and this should solve your problem.

regds
Vijay
0
 

Author Comment

by:Janelau
Comment Utility
Thanks everybody. I tried all the ways out as what described but still failed to get my OracleService running. I tried to create a new database with a new OracleService name and it works before i reboot the system. But once i reboot, the same problem happens again in which the OracleService couldn't be started and i was still prompted Oracle Not Available.
Just for RMZ information, i am using Win2000 platform and Oracle8i server.
0
 
LVL 6

Expert Comment

by:M-Ali
Comment Utility
Hi Janelau,
I also faced the same problem that you describe and I am using the Oracle 8.1.7, you have not mentioned the version that u r using.

But my problem was solved once I downloaded the patch that I talked about earlier. Did you try it out?

Ali
0
 

Author Comment

by:Janelau
Comment Utility

this approach doesn't work.

M-Ali,
i didn't try your suggestion because i can't find any patch for 8.1.6 version. anyway, i reinstalled evetyhing and it is working fine now.
0
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.

 

Expert Comment

by:certifieddba
Comment Utility
Hi Janelau

Go to Oracle Administration asistant for Windows NT/oracle managed objects/computers/databases/yourdbname.On this right click and check out the startup/shutdown options.
and the oracle NT service.Configure them and I think it should work properly.Else you can do one more thing
Go to command prompt and try this
1)First delete your existing service with

C:\oradim -delete -sid(sidname)

2)Recreate new sid
C:\oradim -new -sid (sidname) -intpwd oracle -startmode auto -pfile 'Path for init.ora file'

I think this should solve your problem
Lemme know if it doesn't
0
 

Expert Comment

by:certifieddba
Comment Utility
hey I'm sorry that I specified the answer for SID.Please use the option of SRVC instead of SID.
0
 

Author Comment

by:Janelau
Comment Utility
certifieddba,

In case i tried to delete and recreate a service name for the database using Net8Assistant. It seems fine before rebooting my machine. The problem resumes after i reboot the system.
0
 

Author Comment

by:Janelau
Comment Utility
certifieddba,

In case i tried to delete and recreate a service name for the database using Net8Assistant. It seems fine before rebooting my machine. The problem resumes after i reboot the system.
0
 
LVL 1

Expert Comment

by:hgkeh
Comment Utility
What Oracle version you're using??? For version 8.1.6 and 8.1.7, there is a problem restarting Oracle database after restarting. Try getting the patch from Oracle site.
0
 

Author Comment

by:Janelau
Comment Utility
Since i have uninstall and reinstall the Oracle 8i and recreate a database, the previous problem solved. I create another new database with its SID "ckwb2". I am facing client connection problem to this new database. I can connect to this database using Oracle 8i's SQLPlus but couldn't connect to it using the Forms and Reports's SQLPlus. The error message that i get is "ERROR: ORA-12154: TNS:could not resolve service name". By using the tnsping utility in Forms and Reports, the error message i got was "TNS-03505:Failed to resolve name".
I did check both tnsnames.ora in the oracle_home and forms and reports home and the new entry is exactly following the same format of the previous one, which is ckwb and the ckwb's connection works.
I really cannot understand why and feel frustrated as i keep facing this kind of problems.
Really hope you all can help me resolve the problem. Thanks.


Jane
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
Hi Janelau,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. If there is no objection or further activity, I will suggest to:

    Refund points and save as a 0-pt PAQ.

EXPERTS: Post a comment if you think somebody deserves credit here!

Please do not accept this comment as an answer!
==========
DanRollins -- EE database cleanup volunteer
0
 

Author Comment

by:Janelau
Comment Utility
Hi DanRollins,

i don't received appropriate solution but there is no option for me to close the question. Anyway, you can close the question.

sorry for the incovenient caused.


Jane
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
Hi Janelau,
To delete the question, you must first Reject the answer proposed by certifieddba (look for the [Reject Answer] button).  

When that is done, a
   (  ) Delete Question...
radio button will appear above the comment area. Select it and submit, then in the next screen, click the [Delete] button at the bottom.

-- Dan
0
 

Author Comment

by:Janelau
Comment Utility
As i mentioned in above comment. I really appreciate all your afford.
0
 
LVL 6

Accepted Solution

by:
Mindphaser earned 100 total points
Comment Utility
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **
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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and 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

771 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

12 Experts available now in Live!

Get 1:1 Help Now