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

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

When I open Net Manager there is "orcl" under "service naming"
Every thing looks OK , but SID is greyed

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.

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.

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.

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?

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...

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.

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

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!

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?

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

ORA-28000: the account is locked

Unlock the account.
ALTER USER scott ACCOUNT UNLOCK;
login as sqlplus "/ as sysdba" and run this command.

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?

OK, it is now working - the field responsible for password expire actually was
'ptime" from sys.user$

Now I have somehow split these point ...