?
Solved

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

Posted on 2004-11-21
18
Medium Priority
?
17,555 Views
Last Modified: 2010-05-18
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?
0
Comment
Question by:serg111
  • 8
  • 5
  • 2
  • +2
18 Comments
 
LVL 7

Expert Comment

by:Nievergelt
ID: 12639809
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
 
LVL 2

Author Comment

by:serg111
ID: 12640026
When I open Net Manager there is "orcl" under "service naming"
Every thing looks OK , but SID is greyed
0
 
LVL 2

Author Comment

by:serg111
ID: 12640032
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
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!

 
LVL 6

Expert Comment

by:Eladla
ID: 12640317
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
 
LVL 6

Expert Comment

by:Eladla
ID: 12640325
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
 
LVL 7

Expert Comment

by:Nievergelt
ID: 12640349
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
 
LVL 2

Author Comment

by:serg111
ID: 12640443
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
 
LVL 7

Expert Comment

by:pettmans
ID: 12640526
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
 
LVL 2

Author Comment

by:serg111
ID: 12640641
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
 
LVL 6

Expert Comment

by:Eladla
ID: 12640670
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
 
LVL 2

Author Comment

by:serg111
ID: 12640738
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
 
LVL 7

Accepted Solution

by:
pettmans earned 200 total points
ID: 12641008
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
 
LVL 6

Assisted Solution

by:makhan
makhan earned 200 total points
ID: 12641400
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
 
LVL 2

Author Comment

by:serg111
ID: 12643273
Now it looks better :-)
but still cant connect - new error:

ORA-28000: the account is locked
0
 
LVL 6

Expert Comment

by:Eladla
ID: 12643468
Unlock the account.
ALTER USER scott ACCOUNT UNLOCK;
login as sqlplus "/ as sysdba" and run this command.
0
 
LVL 2

Author Comment

by:serg111
ID: 12645311
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
 
LVL 6

Assisted Solution

by:Eladla
Eladla earned 200 total points
ID: 12647482
I think doing a:

alter user scott identiffied by (whatever you want the password to be)
will work.
0
 
LVL 2

Author Comment

by:serg111
ID: 12647880
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
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.
Suggested Courses
Course of the Month16 days, 6 hours left to enroll

850 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