| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Please help: stuck with ORA-12514
"Anonymous Coder" <no_at_email.com> wrote in message
news:40ced087.700609_at_news.individual.de...
> I recently installed Oracle10g Enterprise on my Windows 2000 machine
> to get some coding experience with it. I used the custom installation,
> left most options alone and created a default database called "odb1",
> with a full name of "db1.local".
>
> Now, I'm stuck because I can't login to the DB, neither with SQL*Plus
> nor with the "Administration Assistant for Windows". All I get is an
> ORA-12514 error when I use "odb1" as the connect descriptor. When I
> try anything else, like "db1", "db1.local" or "odb1.local", I get an
> ORA-12154 error and a message which translates to "cannot resolve
> connect descriptor".
>
>
>
> Here's an example from the listener.log file:
> ---
> 15-JUN-2004 12:40:57 *
>
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=odb1)(CID=(PROGRAM=D:\Devel\Da
tabase\Oracle10g\bin\sqlplusw.exe)(HOST=HARDY)(USER=RMA)))
> * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.3)(PORT=1453)) * establish *
> odb1 * 12514
> TNS-12514: TNS: Listener kann in Connect-Deskriptor angegebenen Dienst
> aktuell nicht auflösen
> ---
>
> (Which translates to something like "Listener cannot resolve the
> service specified by the connect descriptor".)
>
>
>
> The original listener.ora file was:
> ---
> # listener.ora Network Configuration File:
> D:\Devel\Database\Oracle10g\network\admin\listener.ora
> # Generated by Oracle configuration tools.
>
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = PLSExtProc)
> (ORACLE_HOME = D:\Devel\Database\Oracle10g)
> (PROGRAM = extproc)
> )
> )
>
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = hardy)(PORT = 1521))
> )
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
> )
> )
> )
>
> ---
>
> After some web research, I suspected there was something missing and
> changed it to:
> ---
> # listener.ora Network Configuration File:
> D:\Devel\Database\Oracle10g\network\admin\listener.ora
> # Generated by Oracle configuration tools.
>
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (GLOBAL_DBNAME=db1.local)
> (SID_NAME=odb1)
> (ORACLE_HOME = D:\Devel\Database\Oracle10g)
> (PROGRAM = extproc)
> )
> (SID_DESC =
> (SID_NAME = PLSExtProc)
> (ORACLE_HOME = D:\Devel\Database\Oracle10g)
> (PROGRAM = extproc)
> )
> )
>
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = hardy)(PORT = 1521))
> )
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
> )
> )
> )
>
> ---
>
>
> This didn't help. My tnsnames.ora file looks like this:
>
> ---
> # tnsnames.ora Network Configuration File:
> D:\Devel\Database\Oracle10g\network\admin\tnsnames.ora
> # Generated by Oracle configuration tools.
>
> ODB1 =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = hardy)(PORT = 1521))
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = odb1)
> )
> )
>
> EXTPROC_CONNECTION_DATA =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
> )
> (CONNECT_DATA =
> (SID = PLSExtProc)
> (PRESENTATION = RO)
> )
> )
> ---
>
>
> As an Oracle newbie, I'm pretty out of ideas. Does anybody see what
> I'm doing wrong?
>
>
> Oh, by the way, I don't know if this is related: In the Win2000
> service list, the services "OracleCSService" and "OracleDBConsoleodb1"
> are permanently in the "is being started"/"starting" state, not
> "started"/"running". Is this normal or does it indicate an error?
It would indicate an error. Not an error that should be causing your ORA-12514, but it certainly doesn't sound like a healthy install, so the 12514 is probably the least of your worries.
First of all, 12514's mean: you are asking for a connection to a service called, say, xxx.domain.com, and the listener is only aware of a service called, say, yyy.domain.com.de. The two need to match.
Now to control what service *you* are asking to connect to, your tnsnames.ora needs to be correct. To control what service the listener is aware of, your init.ora/spfile needs to be correct. So stop mucking around with your listener.ora. It should read:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hardy)(PORT = 1521))
)
)
That's all. We don't need your SID_LIST bit yet, nor do we need the EXTPROC bits and pieces. And I hope "hardy" can be pinged and gets resolved to an IP address correctly.
Stop your listener and re-start it.
Now shutdown and re-start your instance. With that done, type at a command prompt:
lsnrctl services
You should see that the listener is aware of a service, and its status is 'ready'. Whatever name you see there is the service name that you must request a connection to via your tnsnames.ora. For example, here's what my listener mentions when queried with that command:
C:\>lsnrctl services
LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 15-JUN-2004 21:48:57
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mozart)(PORT=1521)))
Services Summary...
Instance "win92.dizwell.com", status READY, has 1 handler(s) for this
service...
Handler(s):
"DEDICATED" established:5 refused:0 state:ready
LOCAL SERVER
Therefore, my tnsnames.ora must look like this:
WIN92 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = win92.dizwell.com)
)
The SERVICE_NAME parameter must match precisely what the listener is aware of.
How does the listener become aware of an instance in the first place? By a process called dynamic instance registration, which causes the PMON background process to register itself with the listener about every five minutes (or, in 9i R2 and above, when you ask it with the 'alter system register' command). PMON uses whatever the INSTANCE_NAME and DB_DOMAIN initialisation parameters are set to as its registration information, unless you over-ride those two by setting SERVICE_NAMES explicitly.
In short: make your listener.ora as simple as it can be, specifying nothing more than the hostname, the port number and the networking protocol to use. Then get dynamic instance registration happening. Then find out what services your listener is actually aware of. Then make your tnsnames.ora match whatever that turns out to be.
Once you've got the basic connectivity sorted out, then you can start worrying about adding the extra bells and whistles that you may or may not be interested in.
But you may want to get yourself an installation where all the relevant services start and stop correctly before bothering too much. I smell a clean Windows installation in the offing.
Regards
HJR
Received on Tue Jun 15 2004 - 06:57:40 CDT
 |
 |