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

I've downloaded Oracle 10 from oracle.com for testing and have a problem to start sqlplus.
I can login as: sqlplus "/ as sysdba"
but when I wanted to test an example and try to start sqlplus and login as SCOTT/TIGER with Host String "orcl" I've got and error:
ORA-12514 TNS: listener does not currently know of service requested in connect descriptor

Any ideas?
LVL 2
serg111Asked:
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.

NievergeltSenior SW DevCommented:
Do you have a database instance named orcl?
If yes, you have to define it in TNSNAMES.ORA, best by using the Net Manager or the Net Assistant.

Share and Enjoy   Christoph
0
serg111Author Commented:
When I open Net Manager there is "orcl" under "service naming"
Every thing looks OK , but SID is greyed
0
serg111Author Commented:
When I run "Test" on "oracle" it gives me an error:

Initializing first test to use userid: scott, password: tiger
Attempting to connect using userid:  scott
The test did not succeed.
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

There may be an error in the fields entered,
or the server may not be ready for a connection.
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.

EladlaCommented:
Do you have more than one instance? Or More than one listener?
I had this happen when the instance I was trying to connect to was registered in one of two listeners and that listener was down. So sqlplus goes to the listener and the listener is not configured to work with that instance, so....no connection.

Try to see if the instance is defined for that listener in the net manager.
0
EladlaCommented:
Here is a little link that says what I wanted to say, they just word it better.

http://ora-12514.ora-code.com/

I think that for some reason your instance doesn`t dynamiclly register itself with the listener, so you should configure it staticly.
0
NievergeltSenior SW DevCommented:
The Oracle message says:

- Check which services are currently known by the listener by executing: lsnrctl services <listener name>

- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

- Check for an event in the listener.log file.

Did you check these?

You can also get this message, if you change your systems clock to the past. You did not do that?
0
serg111Author Commented:
Net manager shows me following:
"Service Naming":
1) extproc_connection_data
2)orcl




When I run "lsnrctl services LISTENER":

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 21-NOV-2004 18:25:09

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully



How can find "SERVICE_NAME parameter in the connect descriptor "  ?



I've found listener.log in "NETWORK/LOG" directory but I can't open it - it is being used...

0
pettmansCommented:
Can you supply the following infomation to assist with diagnosis please?

1. Start a command prompt. Type: TNSPING ORCL
  What result do you get?

2. At a command prompt get into the Listener management utility by typing LSNRCTL. Then:
> show current_Listener
> show status
> exit.

3. What are the contents of the TNSNAMES.ORA and LISTENER.ORA that are found at %Oracle_home%\network\admin\

4. Login to the database using: sqlplus "/ as sysdba" (as you have already succeeded in doing)
What is the result of the following query?
Select host_name, instance_name from V$instance;

With all that data available we should be able to target the problem more accurately.

Regards,
Scott Pettman.


0
serg111Author Commented:
OK,

1) TNSPING result:
Used parameter files:
C:\oracle\product\10.1.0\Db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Bobik)(PORT = 2000)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)



2)LSNRCTL result:
Current listener is LISTENER

LSNRCTL> Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
Start Date                19-NOV-2004 23:20:06
Uptime                    1 days 20 hr. 2 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\10.1.0\Db_1\network\admin\listener.ora
Listener Log File         C:\oracle\product\10.1.0\Db_1\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Bobik)(PORT=2000)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully



3) TNSNAMES.ORA and LISTENER.ORA :
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Bobik)(PORT = 2000))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Bobik)(PORT = 2000))
    )
  )



4)Select host_name, instance_name from V$instance;
   BOBIK      orcl
0
EladlaCommented:
As I was saying, the listener is not configured to listen for the active instance.

You can solve this in one of two ways:

1. Go to the orcale net manager and update the SID for this listener to orcl

2. edit the listener.ora file and change (SID_NAME = PLSExtProc)
to (SID_NAME = orcl).

I think that both should work, but I would go with the first since it should be easyer.

Good luck!
0
serg111Author Commented:
I have problem to change SID in  orcale net manager
Under "Service naming" "orcl" field SID is grayed :-(
but I can enable it if clilck on "Use oracle 8 release Compatible Identification"
should I enable it?
0
pettmansCommented:
I agree with Eladla as to the cause but suggest a new db address be added to the listener configuration rather than modifying an existing entry.

You can do this through a GUI interface (8i and 9i use Net8 Assistant so I would hope there is an equivalent in 10G).

Or edit listener.ora and add the db manually:

Change:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
      (PROGRAM = extproc)
    )
  )
 to

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
     (SID_NAME = ORCL)
     (ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
    )
  )

Then you'll need to stop and restart the listener

Lsnrctl
> Set current_listener listener
>stop
>start
>show status (does ORCL appear now?)
>exit.

I prefer to use the GUI as it's harder to miss a step.

Regards,
Scott
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
makhanCommented:
We have a gui utility for configuring oracle net services.
use the net8 easy config or net8 configuration assitant for doing the above job.

It is always safe bec. u never know when u will commit a typing mistake.

Also the configuration files posted show that this was the default configuration.

We need not fiddle with the PLSextProc thing.

What u need to do is just add a listener service for sid = ORCL and service name = <my_service>

u can then use tnsping command to check if you are able to ping using the service
tnsping <my_service>

once this is through we can go ahead and connect using
sqlplus system/manager@<my_service>

By default if u use sqlplus without the @<my_service>

Oracle tries to connect with the bequeath adapder with its default settings from the configuration files posted above. I suspect tinkering that would mess up the default config and you may end up with an unuseable system.

Also one more thing.

The difference between SID and SERVICE_NAME. Prior to oracle 8i the default way to connect was using SID. After oracle 8i and above you have to use SERVICE_NAME. Backward compatibility is maintained though.

Regards,

makhan

0
serg111Author Commented:
Now it looks better :-)
but still cant connect - new error:

ORA-28000: the account is locked
0
EladlaCommented:
Unlock the account.
ALTER USER scott ACCOUNT UNLOCK;
login as sqlplus "/ as sysdba" and run this command.
0
serg111Author Commented:
still problem... now it say "Password expired"
I did run
update sys.user$ set exptime '15-JUNE-05' where name='scott';
0 rows updated :-(

How can I change password expire date for scott?
0
EladlaCommented:
I think doing a:

alter user scott identiffied by (whatever you want the password to be)
will work.
0
serg111Author Commented:
OK, it is now working - the field responsible for password expire actually was
'ptime" from sys.user$

Now I have somehow split these point ...
0
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.