This discussion is archived
8 Replies Latest reply: Feb 7, 2013 2:12 PM by user2068122 RSS

Can't connect with sqlplus, sqldeveloper and toad are fine

user2068122 Newbie
Currently Being Moderated
I'm having problems connecting to my local instance in my laptop through sql plus.
I have installed:
-win 7 64 bits
-Oracle 11gr2 64bits
-Oracle instant client 32bits
-SQL Developer 3.2.0 64bits
-Toad 10

Using toad and sql developer works fine but if I want to use sqlplus like this I got an error:

C:\Users\yomerol>sqlplus hr/oracle
SQL*Plus: Release 11.2.0.1.0 Production on MiÚ Feb 6 22:59:11 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR: ORA-12560: TNS:protocol adapter error.

Strange thing is if I write sqlplus hr/oracle@orcl everything works fine.
Another thing I found was that if it set the ORACLE_SID variable on msdos I can work with that session using sqlplus hr/oracle but if I close the window and open a new one nothing works.

What am I missing in order to be able to work with any of these tools?
Thanks
  • 1. Re: Can't connect with sqlplus, sqldeveloper and toad are fine
    sb92075 Guru
    Currently Being Moderated
    user2068122 wrote:
    I'm having problems connecting to my local instance in my laptop through sql plus.
    I have installed:
    -win 7 64 bits
    -Oracle 11gr2 64bits
    -Oracle instant client 32bits
    -SQL Developer 3.2.0 64bits
    -Toad 10

    Using toad and sql developer works fine but if I want to use sqlplus like this I got an error:

    C:\Users\yomerol>sqlplus hr/oracle
    SQL*Plus: Release 11.2.0.1.0 Production on MiÚ Feb 6 22:59:11 2013
    Copyright (c) 1982, 2010, Oracle. All rights reserved.
    ERROR: ORA-12560: TNS:protocol adapter error.

    Strange thing is if I write sqlplus hr/oracle@orcl everything works fine.
    Another thing I found was that if it set the ORACLE_SID variable on msdos I can work with that session using sqlplus hr/oracle but if I close the window and open a new one nothing works.

    What am I missing in order to be able to work with any of these tools?
    Thanks
    ORACLE_SID registry entry is missing or incorrect
  • 2. Re: Can't connect with sqlplus, sqldeveloper and toad are fine
    Krishna-Oracle Pro
    Currently Being Moderated
    Hi,
    Set ORACLE_SID environment variable in windows
    Right click my computer, then select properties
    In properties windows, choose advanced system settings
    in that you can see tab "environment variables" choose that and add oracle_sid environment variable
    It makes this setting permanent

    Thanks,
    Krishna
  • 3. Re: Can't connect with sqlplus, sqldeveloper and toad are fine
    EdStevens Guru
    Currently Being Moderated
    user2068122 wrote:
    I'm having problems connecting to my local instance in my laptop through sql plus.
    I have installed:
    -win 7 64 bits
    -Oracle 11gr2 64bits
    -Oracle instant client 32bits
    -SQL Developer 3.2.0 64bits
    -Toad 10

    Using toad and sql developer works fine but if I want to use sqlplus like this I got an error:

    C:\Users\yomerol>sqlplus hr/oracle
    SQL*Plus: Release 11.2.0.1.0 Production on MiÚ Feb 6 22:59:11 2013
    Copyright (c) 1982, 2010, Oracle. All rights reserved.
    ERROR: ORA-12560: TNS:protocol adapter error.

    Strange thing is if I write sqlplus hr/oracle@orcl everything works fine.
    The "@orcl" means the connection request is being routed through the listener, and the value (or lack thereof) of ORACLE_SID is irrelevant.
    Without the "@orcle", you are requesting a 'bequeath' connection, and the value of ORACLE_SID becomes very relevant as it is used to build a connection key.
    Another thing I found was that if it set the ORACLE_SID variable on msdos I can work with that session using sqlplus hr/oracle but if I close the window and open a new one nothing works.
    Because any environment variables you set in a session are local to that session only. Close the window and that session ceases to exist.
    What am I missing in order to be able to work with any of these tools?
    see: [url http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/] Help! I can’t connect to my database
    see: [url http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/] Help! I can’t connect to my database (part duex)
    Thanks
  • 4. Re: Can't connect with sqlplus, sqldeveloper and toad are fine
    user2068122 Newbie
    Currently Being Moderated
    So if I install more than one instance of Oracle DB in my laptop do I have to set the ORACLE_SID depending on which instance I want to connect to with sqlplus?
    Is that the only way?
  • 5. Re: Can't connect with sqlplus, sqldeveloper and toad are fine
    Max Seleznev Explorer
    Currently Being Moderated
    Providing you have a tnsnames.ora file configured in your TNS_ADMIN destination (that defaults to ORACLE_HOME/network/admin) you can always specify desired database as part of tha connection string:
    SQL> connect username/password@database
  • 6. Re: Can't connect with sqlplus, sqldeveloper and toad are fine
    EdStevens Guru
    Currently Being Moderated
    user2068122 wrote:
    So if I install more than one instance of Oracle DB in my laptop do I have to set the ORACLE_SID depending on which instance I want to connect to with sqlplus?
    Is that the only way?
    I say again:
    The "@orcl" means the connection request is being routed through the listener, and the value (or lack thereof) of ORACLE_SID is irrelevant.
    This is what Max stated in his latest response.
    Without the "@orcle", you are requesting a 'bequeath' connection, and the value of ORACLE_SID becomes very relevant as it is used to build a connection key.
    So, no setting ORACLE_SID depending on which instance you want to connect to is NOT "the only way".

    However, setting oracle_sid is not such a bad thing, and use of that type of connection is sometimes required. Why not just write a little command script to set it for you. Call the script 'oraenv.cmd'.
    rem oraenv.cmd
    rem usage 'oraenv <dbname>'
    set ORACLE_SID=%1
    This will work as long as you only have one oracle_home to deal with. If you have more, you'll have to make the script a bit more complex.
  • 7. Re: Can't connect with sqlplus, sqldeveloper and toad are fine
    sb92075 Guru
    Currently Being Moderated
    user2068122 wrote:
    So if I install more than one instance of Oracle DB in my laptop do I have to set the ORACLE_SID depending on which instance I want to connect to with sqlplus?
    Is that the only way?
    TWO_TASK environmental variable is a different way to connect.

    SET TWO_TASK=<tns_alias>
  • 8. Re: Can't connect with sqlplus, sqldeveloper and toad are fine
    user2068122 Newbie
    Currently Being Moderated
    Well I guess I can just connect using hr/oracle@myinstance or setting the ORACLE_SID variable, either way both work.
    Thank you all for the prompt responses.

Legend

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