This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jan 13, 2013 8:23 AM by sb92075 RSS

ORA-12505 TNS:listener does not currently know of SID given in connect desc

982381 Newbie
Currently Being Moderated
Hi,

I am new to oracle. Installed oracle 11g.

When i try creating a new connection i have SID=xe; Hostname=localhost; port=1521. Also in orcal tab i have Role set to default and Connection type set to Basic( Need more infor on what these are?)

I am getting this error when creating connection in sql developer. I understood that listner.ora is not having the above default connection details in it. but i dint get to know much what to do on this.

--------------------
C:\Users\Ranjan>lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-JAN-2013 22:54
:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:3 refused:0
LOCAL SERVER
Service "orcl.home" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:308 refused:0 state:ready
LOCAL SERVER
Service "orclXDB.home" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: RANJAN-PC, pid: 2148>
(ADDRESS=(PROTOCOL=tcp)(HOST=Ranjan-PC)(PORT=49158))
The command completed successfully
--------------------------
My Listner.ora file content

# listener.ora Network Configuration File: W:\app\Ranjan\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = W:\app\Ranjan\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:W:\app\Ranjan\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)

ADR_BASE_LISTENER = W:\app\Ranjan
------------------
tnsnames.ora file content

# tnsnames.ora Network Configuration File: W:\app\Ranjan\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.home)
)
)
------------

when i give "tnsping orcl", I get below result and sometimes i get 0msec

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-JAN-2
013 22:56:08

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
W:\app\Ranjan\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.home)
))
OK (20 msec)

------------------

please guide me what i am supposed to do further
Thanks in advance
  • 1. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    sb92075 Guru
    Currently Being Moderated
    979378 wrote:
    Hi,

    I am new to oracle. Installed oracle 11g.

    When i try creating a new connection i have SID=xe; Hostname=localhost; port=1521.
    ORACLE_SID=orcl
  • 2. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    REDO LOG Newbie
    Currently Being Moderated
    Hi

    add the ORACLE_SID to the environment variables with the value = <the sid of your database>

    make sure you have started the listener before : cmd> lsnrctl start

    good luck
  • 3. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    sb92075 Guru
    Currently Being Moderated
    REDO LOG wrote:
    Hi

    add the ORACLE_SID to the environment variables with the value = <the sid of your database>

    make sure you have started the listener before : cmd> lsnrctl start

    good luck
    SQL*Net does NOT use ORACLE_SID environmental variable!
  • 4. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    93804 Newbie
    Currently Being Moderated
    Seems you have wrong SID. Your listener says:
    Service "orcl.home" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    but you are trying to connect with XE.
    Also make sure you use ORCL if using SID, OR orcl.home if using Service Name on SQLDeveloper connection tab
  • 5. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    Ashutosh Newbie
    Currently Being Moderated
    Try with " ORACLE_SID=orcl.home" , as this is the service name registered with the listener.

    Ashutosh.
  • 6. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    sb92075 Guru
    Currently Being Moderated
    Ashutosh wrote:
    Try with " ORACLE_SID=orcl.home" , as this is the service name registered with the listener.

    Ashutosh.
    post verifiable proof that above is valid solution; since it is nothing but speculative drivel.
  • 7. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    980508 Newbie
    Currently Being Moderated
    sb92075 is right. Verify ORACLE_SID=orcl

    If you connect with SQL Developer you have several options. For example:

    1. Connection type TNS: Only select Network alias from list. in your case you should select orcl

    2. Connection type Basic: you have to write the followings fields:

    - HostName: localhost (in your case)
    - Port: 1521
    - SID: orcl
  • 8. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    EdStevens Guru
    Currently Being Moderated
    Ashutosh wrote:
    Try with " ORACLE_SID=orcl.home" , as this is the service name registered with the listener.

    Ashutosh.
    REDO LOG erroneously made that same suggestion 10 hours prior to your posting, and was rebutted 10 minutes later.

    In case you missed the point that SQLNET does NOT use ORACLE_SID, take a look at this, and then explain how ORACLE_SID comes into play on a sqlnet connection. You should see that I have 3 successfu connections to 'mydev', each with a different value for ORACLE_SID.
    C:\Users\estevens\Documents\sql>set ORACLE_SID=fubar
    
    C:\Users\estevens\Documents\sql>sqlplus estevens@mydev
    
    SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 06:54:01 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Enter password:
    
    Connected to:
    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
    With the Automatic Storage Management option
    
    SQL> exit
    Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
    With the Automatic Storage Management option
    
    C:\Users\estevens\Documents\sql>set ORACLE_SID=orcl
    
    C:\Users\estevens\Documents\sql>sqlplus estevens@mydev
    
    SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 06:54:25 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Enter password:
    
    Connected to:
    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
    With the Automatic Storage Management option
    
    SQL> exit
    Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
    With the Automatic Storage Management option
    
    C:\Users\estevens\Documents\sql>set ORACLE_SID=xe
    
    C:\Users\estevens\Documents\sql>sqlplus estevens@mydev
    
    SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 06:54:50 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Enter password:
    
    Connected to:
    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
    With the Automatic Storage Management option
    
    SQL>
  • 9. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    982381 Newbie
    Currently Being Moderated
    Hi Stevens,

    Please suggest me what i need to do on my post. I am not having access to write listner.ora( Its telling access DENIED, Also do i really have to change anything in this?)
    As you see in my post, listner.ora is having only (SID_NAME = CLRExtProc) this one.
    i have also posted all active service name and instance available

    My simple question. What should i do to get connected to any/all of the instance posted in my question
    I am asked for Connection_Name, User Name, Password , Host Name, Port, SID in my New Connection Diagog box.
    I never have set any password other than Database administrative password, now i am not sure what password i should key in here with what username.

    I have Oracle-OraDb11g_home1 with sql developer 1.5.5 on my PC

    Anything u need from me pls let me know.

    Thanks in advance
    Ranjan

    Edited by: Ranjan-Oracle on Jan 2, 2013 9:34 PM
  • 10. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    EdStevens Guru
    Currently Being Moderated
    Ranjan-Oracle wrote:
    Hi Stevens,

    Please suggest me what i need to do on my post. I am not having access to write listner.ora( Its telling access DENIED, Also do i really have to change anything in this?)
    As you see in my post, listner.ora is having only (SID_NAME = CLRExtProc) this one.
    i have also posted all active service name and instance available

    My simple question. What should i do to get connected to any/all of the instance posted in my question
    I am asked for Connection_Name, User Name, Password , Host Name, Port, SID in my New Connection Diagog box.
    Is this in the 'New Databse Connection' dialog in SQL Developer?
    If so, it sounds like you have selected the default 'Connection Type' of 'Basic'.
    Notice that 'Connection Type' is a drop-list. Open it an select 'TNS'. The input options will change, and you will get a drop list labeld 'Network Alias'. Open that and you will see your entries from your tnsnames.ora file. Select the correct one.
    I never have set any password other than Database administrative password, now i am not sure what password i should key in here with what username.
    You give the password for whatever username you provide. It is generally bad practice to save that information with the connection definition, as that would allow someone with access to your machine to then access the database without providing any credentials. Even if this particular database is your personal test db, it's a bad habit to get into. Leave them blank and provide them at connect time.
    >
    I have Oracle-OraDb11g_home1 with sql developer 1.5.5 on my PC

    Anything u need from me pls let me know.
    Please read
    http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/ (Help! I can’t connect to my database )
    http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/ (Help! I can’t connect to my database (part duex) )
    http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/
    http://edstevensdba.wordpress.com/2011/03/19/ora-12514/
    http://edstevensdba.wordpress.com/2011/03/05/ora-12545/
    http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/

    Thanks in advance
    Ranjan

    Edited by: Ranjan-Oracle on Jan 2, 2013 9:34 PM
  • 11. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    982381 Newbie
    Currently Being Moderated
    Hi Stevens,

    First of all Thanks a ton for your valuable time in explaining in a way it has to be understood. Lot of repeated questions would hve bugged u do this i guess. Anyways, extraordinary way you have showed up concepts in there. My sincere appreciation on this expecially on explaining possible outcomes in a crystal clear sentances.

    I went through the links[& links within:-)] you posted except dynamic registration concept which i am going thorugh.

    Sorry to tell still i have problems connecting. I am new but gottu know alot from your film:-)
    I have correct connect identified and connect descriptor.
    I understood concepts u explained. I have my server in localhost and client is also localhost.(Review my first post query for any further info)

    My Problem is that what should i give in username/password fields while making new connection through sql developer? you said to leave blank and give while establishing connection.But, i am getting an error ora-01017: Invalid username/password; logon denied. If i try keying in something or leave blank. (Connection name would be new name i will have to give is what i believe,correct me if i am wrong.)

    Also what is SYSDBA in Role options. I have selected default in this drop down list.

    Thanks again for your assistance and mentoring.

    Ranjan

    Edited by: Ranjan-Oracle on Jan 4, 2013 10:01 PM
  • 12. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    EdStevens Guru
    Currently Being Moderated
    Ranjan-Oracle wrote:
    Hi Stevens,

    First of all Thanks a ton for your valuable time in explaining in a way it has to be understood. Lot of repeated questions would hve bugged u do this i guess. Anyways, extraordinary way you have showed up concepts in there. My sincere appreciation on this expecially on explaining possible outcomes in a crystal clear sentances.

    I went through the links[& links within:-)] you posted except dynamic registration concept which i am going thorugh.

    Sorry to tell still i have problems connecting. I am new but gottu know alot from your film:-)
    I have correct connect identified and connect descriptor.
    I understood concepts u explained. I have my server in localhost and client is also localhost.(Review my first post query for any further info)

    My Problem is that what should i give in username/password fields while making new connection through sql developer? you said to leave blank and give while establishing connection.But, i am getting an error ora-01017: Invalid username/password; logon denied. If i try keying in something or leave blank. (Connection name would be new name i will have to give is what i believe,correct me if i am wrong.)

    Also what is SYSDBA in Role options. I have selected default in this drop down list.

    Thanks again for your assistance and mentoring.

    Ranjan

    Edited by: Ranjan-Oracle on Jan 4, 2013 10:01 PM
    At the very least, when you created your database you HAD to supply a password for user SYSTEM. So at the very least, you can use 'SYSTEM' as your user and whatever you specified as password.

    If all else fails, go to a command prompt and do the following:
    c:> set ORACLE_SID=orcl
    c:> sqlplus / as sysdba
    sql> alter user system identified by fubar;
    sql> exit
    Now you can log on as system/fubar
  • 13. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    982381 Newbie
    Currently Being Moderated
    Awesome. I wonder how i missed this one out of many probable checks i did keying in.
    Anyways thanks for all you did. Keep up your work.

    Bye
    Ranjan
  • 14. Re: ORA-12505 TNS:listener does not currently know of SID given in connect desc
    982381 Newbie
    Currently Being Moderated
    Hi ,

    I am trying to create a new database using Database Configuration Assistant. At last its throughing Unable to resolve TNS Service name.
    I understand that there should be a TNS entry in tnsnames.ora but i dont see an entry created automatically by this assistant tool for specified SID

    I was trying to create a sandbox database with sandbox.home as global database name and sandbox as SID. I dont see entry for this in tnsnames.ora
    Also i am unable to edit this file adding the TNS entry. Its keep saying see if other application is using this file. Even if i stop the services and try editing, iam unable to edit.

    What could be the possible reason.

    Thanks,
    Ranjan
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points