Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Unhappy Unanswered: Unable to connect to Oracle with SID

    Hi Guys,
    I have a problem in connecting to oracle with my SID.

    Below are the details.

    OS : Windows 7
    Oracle:11.2.0.3.0
    Server and Client both are on My local machine.

    I am not able to connect to oracle server using first approach. However, i am able to connect with 2nd approach.

    1. C:\Users\username>sqlplus DB_USER/DBPassword@orcl

    SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 13 15:11:07 2013

    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    ERROR:
    ORA-12154: TNS:could not resolve the connect identifier specified


    2.C:\Users\username>sqlplus /nolog

    SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 13 15:15:28 2013

    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    SQL> conn DB_USER/DBPassword;
    Connected.
    SQL>

    DBURL : jdbcracle:thin:@localhost:1521rcl
    =====

    tnsnames.ora
    ============
    ORCL =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
    )
    Listener.ora
    ============
    # listener.ora Network Configuration File: E:\app\username\product\11.2.0\dbhome_1\network\ad min\listener.ora
    # Generated by Oracle configuration tools.
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = E:\app\username\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:E:\app\username\product\11.2.0\ dbhome_1\bin\oraclr11.dll")
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = msi-l210)(PORT = 1521))
    )
    )

    ADR_BASE_LISTENER = E:\app\username
    SQLNET.ORA
    ==========
    SQLNET.AUTHENTICATION_SERVICES= (NONE)

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
    TNSPING OUTPUT
    ==============


    C:\Users\username>tnsping orcl

    TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 13-JUL-2013 14:37:05

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

    Used parameter files:
    E:\app\username\product\11.2.0\dbhome_1\network\ad min\sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
    OK (0 msec)



    Listener Status
    ===============
    C:\Users\username>lsnrctl status

    LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 13-JUL-2013 15:09:24

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC15 21)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
    Start Date 13-JUL-2013 14:12:47
    Uptime 0 days 0 hr. 56 min. 37 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File E:\app\username\product\11.2.0\dbhome_1\network\ad min\listener.ora
    Listener Log File E:\app\username\diag\tnslsnr\msi-l210\listener\alert\log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\ pipe\EXTPROC1521ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=msi-l210.metricstream.com)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0. 1)(PORT=1521)))
    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "orcl.metricstream.com" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orclXDB.metricstream.com" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    The command completed successfully

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    Do you realize that localhost & msi-l210 are different hostnames & utilize different IP#s?

    >(ADDRESS = (PROTOCOL = TCP)(HOST = msi-l210)(PORT = 1521))

    You might have better success if you are consistent with how you configure things.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have a problem in connecting to oracle with my SID.
    Problem statement is not correct.
    "@orcl" below is a TNS Alias; which just happens to be the same as the Oracle SID; but it could be an entirely different string.
    >1. C:\Users\username>sqlplus DB_USER/DBPassword@orcl
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >(SERVICE_NAME = orcl)
    above is from tnsnames.ora file
    Below is what the listener knows as a valid SERVICE_NAME
    >Service "orcl.metricstream.com" has 1 instance(s).

    The difference will result in more problems after you resolve the ORA-12154 error
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2013
    Posts
    4
    Thanks for your prompt response.
    I was bit busy with other activities so, couldn't respond back immediately. I apologize for the same.

    I resolved the issue by starting the listener first and instance later. I got this information from below forum.
    oracle11g - ORA 12514 error:TNS listener error - Stack Overflow

    I am on my way to work.....



    Adepu

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by adepu View Post
    Thanks for your prompt response.
    I was bit busy with other activities so, couldn't respond back immediately. I apologize for the same.

    I resolved the issue by starting the listener first and instance later. I got this information from below forum.
    oracle11g - ORA 12514 error:TNS listener error - Stack Overflow

    I am on my way to work.....



    Adepu
    curious since you initially reported the error to be ORA-12154; not ORA-12514 stated above
    ORA-12154 is always client side error
    ORA-12514 is always server side error
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2013
    Posts
    4
    Yes, i posted what i tried at my place.

    Later, i googled in with Listener message "status UNKNOWN, has 1 handler(s) for this service...". Got Lucky to resolve.

    Thanks
    Adepu

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •