8 Replies Latest reply: Feb 7, 2013 4:12 PM by user2068122 RSS

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

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