0

Hi, I have installed Oracle 11g on my laptop for practicing Oracle. I've connected to the HR account. It has been connected for 3 days, but after that I was getting this error when I tried to connect through SQL Developer:

TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
Gryu
  • 2,102
  • 2
  • 16
  • 29
srikanth jetty
  • 11
  • 1
  • 1
  • 1
    please share what error message showing – Samiul Amin Shanto Jun 03 '18 at 05:26
  • 1
    Without the error in question we cannot help you at all, ever. Please share the verbatim text of the error. Thanks! – Max von Hippel Jun 03 '18 at 05:39
  • 1
    error message:TNS-12505: TNS:listener does not currently know of SID given in connect descriptor – srikanth jetty Jun 03 '18 at 05:44
  • What connection method are you using (e.g. JDBC)? Can you connect using SQL\*Plus? – William Robertson Jun 03 '18 at 09:22
  • 1
    Possible duplicate of [ORA-12505, TNS:listener does not currently know of SID given in connect descriptor](https://stackoverflow.com/questions/18192521/ora-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descript) – William Robertson Jun 03 '18 at 09:31
  • I just tried to correct the question title to the actual error message (it's not *'Listener currently doesn't know the sid of given connect descriptor'*, it's *'Listener does not currently know of SID given in connect descriptor'*) but there are already many questions for the same error message and SO requires titles to be unique. Maybe you could looks at some of the others in case they help. – William Robertson Jun 03 '18 at 09:36

2 Answers2

3

Make sure that

  • Your DB is started up ;

    $ sqlplus / as sysdba
SQL> startup

  • ORACLE_SID parameter is set to a path value;

    on Windows, look-up by :

    C:\Users\pc> echo %ORACLE_SID%
mydb

    on Unix, look-up by :

    $ echo $ORACLE_SID
mydb

  • and Listener is running :

    $ lsnrctl status
LSNRCTL for Linux : Version 12.1.0.2.0 - Production on 03-JUN-2018 09:31:07

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                30-MAR-2018 22:29:45
Uptime                    53 days 11 hr. 1 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/mydb/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.15.81)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mycompany.com)(PORT=5505))(Presentation=HTTP)(Session=RAW))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=mycompany.com)(PORT=5506)) 
 (Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0.2/dbhome_1/admin/mydb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  Services Summary...
 Instance "mydb", status READY, has 1 handler(s) for this service...
 The command completed successfully
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • I'm sorry. I have similar problem and want to know what could I do to solve the issue, if my output is: `[oracle@oracl oracle]$ lsnrctl status ... Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracl)(PORT=1521))) ... Listener Parameter File path_to/listener.ora Listener Log File path_to/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracl)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully` – Gryu May 17 '19 at 22:22
  • @Gryu perhaps you need to register the listener by `SQL> alter system register;` – Barbaros Özhan May 17 '19 at 22:32
  • Unfortunately, it did not help. But this answer helped: https://stackoverflow.com/questions/22437910/ora12505tns-listener-does-not-currently-know-of-sid-given-in-connect-descript My listener.ora file was lack of: `SID_LIST_LISTENER = (SID_LIST = (SID_DESC= (GLOBAL_DBNAME=oracl) (ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1) (SID_NAME=orcl) ) ) ADR_BASE_LISTENER=/u01/app/oracle` – Gryu May 17 '19 at 22:48
  • Also, interesting, that now `lsnrctl status` command shows the status as unknown: `Services Summary... Service "oracl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...`. Is it bad? – Gryu May 17 '19 at 23:18
  • 1
    @Gryu might be encountered such a message usually, not important, but restart may be preferred. – Barbaros Özhan May 18 '19 at 01:12
0

You should try the following ways and one of them may work for you.

Way-1: In windows: use this video: restart services OracleOraDB19Home1TNSListener then OracleServiceORCL.

Way-2: connect with system user password to sql plus as shown as:

Enter user-name: system/ as sysdba
Enter password: your_password

SQL> shutdown
SQL> startup nomount
SQL> alter system set db_recovery_file_dest_size=4G scope=both;
SQL> alter database mount;
SQL> alter database open;

And now check your problem fixed? If no set below code then check again.

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)   (HOST=127.0.0.1) (PORT=1521)))';

This work for me.

henrry
  • 486
  • 6
  • 25