12 Replies Latest reply: Oct 9, 2012 11:38 AM by EdStevens RSS

    Sqlplus command is connecting with local host, but cannot connect with  IP

    966840
      Hello,

      Sqlplus command is connecting with local host, but cannot connect with IP
      Below is the sqlplus comand which i am trying to connect.

      sqlplus system/XXX@192.168.1.XXX:1521/orcl

      It give me this error:- ERROR:
      ORA-12541: TNS:no listener

      Below is my listner status:-
      cmd -> lsnrctl status

      xyz@Server:~$ lsnrctl status

      LSNRCTL for Linux: Version 11.2.0.1.0 -

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Server.com)(PORT=1521)))
      STATUS of the LISTENER
      ------------------------
      Alias LISTENER
      Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
      Start Date 08-OCT-2012 17:50:32
      Uptime 0 days 0 hr. 4 min. 40 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
      Listener Log File /u01/app/oracle/diag/tnslsnr/server/listener/alert/log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
      Services Summary...
      Service "orcl" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
      Service "orclXDB" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
      The command completed successfully


      Troubleshooting done :-

      Edited ORACLE_HOME//network/admin/listener.ora file

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.XXX:1521)(PORT = 1521))
      )
      )

      ADR_BASE_LISTENER = /u01/app/oracle

      Server Os :-
      Ubuntu 10.04.4 LTS \n \l

      Tcp connection is not getting Established from telnet, on server locally i can able to do telenet, But from IP i cannot.

      Created new listnener and created new database, but error is still there.
      Please help me to get out of this issue, i am trying to resolve it from more than 15 hrs behind this, I appreciate if any one help me to reslove this issue. Remotely i am not able to connect machine. Please help.

      Thanks in Advance.
      - Amol Shinde.
        • 1. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
          952768
          What is secret about 192.168.1.XXX ?

          What is the name and ip of your host?

          can you perform this statement and show the output?
          netstat -an | grep 1521 | grep LISTEN
          • 2. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
            812975
            can you use server ip while creating listener?
            your listener is using local ip. try reconfiguring it and see if its working.
            • 3. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
              EdStevens
              963837 wrote:
              Hello,

              Sqlplus command is connecting with local host, but cannot connect with IP
              Below is the sqlplus comand which i am trying to connect.

              sqlplus system/XXX@192.168.1.XXX:1521/orcl

              It give me this error:- ERROR:
              ORA-12541: TNS:no listener

              Below is my listner status:-
              cmd -> lsnrctl status

              xyz@Server:~$ lsnrctl status

              LSNRCTL for Linux: Version 11.2.0.1.0 -

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

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Server.com)(PORT=1521)))
              STATUS of the LISTENER
              ------------------------
              Alias LISTENER
              Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
              Start Date 08-OCT-2012 17:50:32
              Uptime 0 days 0 hr. 4 min. 40 sec
              Trace Level off
              Security ON: Local OS Authentication
              SNMP OFF
              Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
              Listener Log File /u01/app/oracle/diag/tnslsnr/server/listener/alert/log.xml
              Listening Endpoints Summary...
              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
              Services Summary...
              Service "orcl" has 1 instance(s).
              Instance "orcl", status READY, has 1 handler(s) for this service...
              Service "orclXDB" has 1 instance(s).
              Instance "orcl", status READY, has 1 handler(s) for this service...
              The command completed successfully


              Troubleshooting done :-

              Edited ORACLE_HOME//network/admin/listener.ora file

              LISTENER =
              (DESCRIPTION_LIST =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.XXX:1521)(PORT = 1521))
              )
              )

              ADR_BASE_LISTENER = /u01/app/oracle

              Server Os :-
              Ubuntu 10.04.4 LTS \n \l
              Oracle is not certified to run on Ubuntu.

              Tcp connection is not getting Established from telnet, on server locally i can able to do telenet, But from IP i cannot.

              Created new listnener and created new database, but error is still there.
              created a new database? Would you tear your house down and rebuild it just because you misplaced the key to the front door?

              Please help me to get out of this issue, i am trying to resolve it from more than 15 hrs behind this, I appreciate if any one help me to reslove this issue. Remotely i am not able to connect machine. Please help.

              Thanks in Advance.
              - Amol Shinde.
              It's really not clear exactly what you are doing on what machine. Let's start at the beginning.
              1) what OS your database running on? I guess that must be your Ubuntu machine.
              2) What os are you trying to connect from?

              Now that you've 'rebuilt' everything ...
              On the database server, issue
              id
              env | egrep 'ORA | PATH' | sort
              cat $ORACLE_HOME/network/admin/listener.ora
              lsnrctl status
              Copy the entire session - commands and full results, and past back to here. Once we see that we can know how to proceed to the next step of troubleshooting.

              And you might want to 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) )


              Also, for future reference, this forum is named 'SQL and PL//SQL' and deals with issues related to those languages. Your issue is more properly placed in the General Questions forum. It has nothing to do with SQL or PL/SQL

              Edited by: EdStevens on Oct 8, 2012 9:21 AM (forgot the 'status' argument for the 'lsnrctl' command)

              Edited by: EdStevens on Oct 8, 2012 9:47 AM
              • 4. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
                966840
                Thanks for you reponse.

                Below is the output :-

                administrator@Server:~$ netstat -an | grep 1521 | grep LISTEN
                tcp 0 0 127.0.0.1:1521 0.0.0.0:* LISTEN
                • 5. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
                  952768
                  What you see is that the listener is bind to your local host. So none of the network interfaces will connect to 1521 when you connect remote.

                  If you configure your listener with your hostname the listener will bind to all network interfaces.

                  This will look like:
                  tcp 0 0 *.1521
                  The * will indicate that it will listen to all network interfaces (all ip's)

                  Your configuration is now only listening to: 127.0.0.1:1521
                  • 6. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
                    966840
                    Hi,

                    Thanks for your reply i have used, Server ip and hostname but not no use.
                    its is giving same error.
                    • 7. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
                      952768
                      Can you show the output of your /etc/hosts file?
                      • 8. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
                        966840
                        EdStevens wrote:
                        963837 wrote:
                        Hello,

                        Sqlplus command is connecting with local host, but cannot connect with IP
                        Below is the sqlplus comand which i am trying to connect.

                        sqlplus system/XXX@192.168.1.XXX:1521/orcl

                        It give me this error:- ERROR:
                        ORA-12541: TNS:no listener

                        Below is my listner status:-
                        cmd -> lsnrctl status

                        xyz@Server:~$ lsnrctl status

                        LSNRCTL for Linux: Version 11.2.0.1.0 -

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

                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Server.com)(PORT=1521)))
                        STATUS of the LISTENER
                        ------------------------
                        Alias LISTENER
                        Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
                        Start Date 08-OCT-2012 17:50:32
                        Uptime 0 days 0 hr. 4 min. 40 sec
                        Trace Level off
                        Security ON: Local OS Authentication
                        SNMP OFF
                        Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
                        Listener Log File /u01/app/oracle/diag/tnslsnr/server/listener/alert/log.xml
                        Listening Endpoints Summary...
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
                        Services Summary...
                        Service "orcl" has 1 instance(s).
                        Instance "orcl", status READY, has 1 handler(s) for this service...
                        Service "orclXDB" has 1 instance(s).
                        Instance "orcl", status READY, has 1 handler(s) for this service...
                        The command completed successfully


                        Troubleshooting done :-

                        Edited ORACLE_HOME//network/admin/listener.ora file

                        LISTENER =
                        (DESCRIPTION_LIST =
                        (DESCRIPTION =
                        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.XXX:1521)(PORT = 1521))
                        )
                        )

                        ADR_BASE_LISTENER = /u01/app/oracle

                        Server Os :-
                        Ubuntu 10.04.4 LTS \n \l
                        Oracle is not certified to run on Ubuntu.

                        Tcp connection is not getting Established from telnet, on server locally i can able to do telenet, But from IP i cannot.

                        Created new listnener and created new database, but error is still there.
                        created a new database? Would you tear your house down and rebuild it just because you misplaced the key to the front door?

                        Please help me to get out of this issue, i am trying to resolve it from more than 15 hrs behind this, I appreciate if any one help me to reslove this issue. Remotely i am not able to connect machine. Please help.

                        Thanks in Advance.
                        - Amol Shinde.
                        It's really not clear exactly what you are doing on what machine. Let's start at the beginning.
                        1) what OS your database running on? I guess that must be your Ubuntu machine.
                        2) What os are you trying to connect from?

                        Now that you've 'rebuilt' everything ...
                        On the database server, issue
                        id
                        env | egrep 'ORA | PATH' | sort
                        cat $ORACLE_HOME/network/admin/listener.ora
                        lsnrctl status
                        Copy the entire session - commands and full results, and past back to here. Once we see that we can know how to proceed to the next step of troubleshooting.

                        And you might want to 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) )


                        Also, for future reference, this forum is named 'SQL and PL//SQL' and deals with issues related to those languages. Your issue is more properly placed in the General Questions forum. It has nothing to do with SQL or PL/SQL

                        Edited by: EdStevens on Oct 8, 2012 9:21 AM (forgot the 'status' argument for the 'lsnrctl' command)

                        Edited by: EdStevens on Oct 8, 2012 9:47 AM
                        Thanks for your response. As i am learning Oracle, i have installed oracle 11g successfully, while resolving this kinda of issue, i thought there would be issue with database,so i have created new database and along with that i have created new listener.
                        just to check.

                        Below are the output of the comands.

                        # id
                        uid=1000(administrator) gid=1000(administrator) groups=4(adm),7(lp),20(dialout),24(cdrom),33(www-data),46(plugdev),109(lpadmin),110(sambashare),111(admin),127(libvirtd),128(vboxusers),1000(administrator)

                        #cat /etc/hostname
                        backupserver
                        # hostname -d
                        XXXX.net

                        Does hostname is creating issue.?


                        # lsnrctl status

                        LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-OCT-2012 17:04:37

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

                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=backupserver.XXXX.net)(PORT=1521)))
                        STATUS of the LISTENER
                        ------------------------
                        Alias LISTENER
                        Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
                        Start Date 09-OCT-2012 17:03:58
                        Uptime 0 days 0 hr. 0 min. 39 sec
                        Trace Level off
                        Security ON: Local OS Authentication
                        SNMP OFF
                        Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
                        Listener Log File /u01/app/oracle/diag/tnslsnr/Server/listener/alert/log.xml
                        Listening Endpoints Summary...
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
                        The listener supports no services
                        The command completed successfully


                        oracle@Server:~$ cat $ORACLE_HOME/network/admin/listener.ora
                        # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
                        # Generated by Oracle configuration tools.

                        LISTENER =
                        (DESCRIPTION_LIST =
                        (DESCRIPTION =
                        (ADDRESS = (PROTOCOL = TCP)(HOST = backupserver.XXXX.net)(PORT = 1521))
                        )
                        )

                        ADR_BASE_LISTENER = /u01/app/oracle


                        Below are the answers :-
                        1) what OS your database running on? I guess that must be your Ubuntu machine.
                        Yes ,My database is running on Ubuntu OS
                        2) What os are you trying to connect from?
                        I am trying to connect from Mac (with oracle instant client) and also from Ubuntu OS

                        EdStevens I will take while posting in Forum's Category.

                        Best Regards,
                        Amol Shinde.
                        • 9. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
                          966840
                          Yes Sure,

                          192.168.1.212 backupserver.XXXX.net backupserver orcl.XXXX.net orcl foodpair.XXXX.net
                          127.0.0.1 localhost orcl.XXXX.net backupserver.XXXX.net
                          • 10. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
                            952768
                            Why pointing backupserver.xxx.net twice in your /etc/hosts.
                            Please use only localhost after 127.0.0.1

                            /etc/hosts
                            192.168.1.212 backupserver.XXXX.net backupserver orcl.XXXX.net orcl foodpair.XXXX.net
                            127.0.0.1 localhost
                            • 11. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
                              sb92075
                              963837 wrote:
                              Hello,

                              Sqlplus command is connecting with local host, but cannot connect with IP
                              Below is the sqlplus comand which i am trying to connect.

                              sqlplus system/XXX@192.168.1.XXX:1521/orcl
                              Not as above but as below

                              sqlplus system/XXX@'192.168.1.XXX:1521/orcl'
                              • 12. Re: Sqlplus command is connecting with local host, but cannot connect with  IP
                                EdStevens
                                >
                                <snip>>
                                Below are the output of the comands.

                                # id
                                uid=1000(administrator) gid=1000(administrator) groups=4(adm),7(lp),20(dialout),24(cdrom),33(www-data),46(plugdev),109(lpadmin),110(sambashare),111(admin),127(libvirtd),128(vboxusers),1000(administrator)
                                Why are you logged on as 'administrator' and not 'oracle'? As part of the installation you should have created a user 'oracle' and a group 'dba' to which 'oracle' belongs'. Possibly also a group 'oinstall' to which user 'oracle' also belongs. This was detailed in the Installation Guide. And then you should be logged on as this user 'oracle' to do all oracle stuff.
                                #cat /etc/hostname
                                backupserver
                                # hostname -d
                                XXXX.net

                                Does hostname is creating issue.?
                                Not that I know of. I'd be interested in /etc/hosts. That's not the same file as /etc/hostname.
                                >
                                >
                                # lsnrctl status

                                LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-OCT-2012 17:04:37

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

                                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=backupserver.XXXX.net)(PORT=1521)))
                                STATUS of the LISTENER
                                ------------------------
                                Alias LISTENER
                                Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
                                Start Date 09-OCT-2012 17:03:58
                                Uptime 0 days 0 hr. 0 min. 39 sec
                                Trace Level off
                                Security ON: Local OS Authentication
                                SNMP OFF
                                Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
                                Listener Log File /u01/app/oracle/diag/tnslsnr/Server/listener/alert/log.xml
                                Listening Endpoints Summary...
                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
                                Does 'backupserver.XXXX.net' resolve to 127.0.0.1? (check your /etc/hosts file). Requests to 127.0.0.1 never leave the machine from which the request originates. So this listener will never hear a request that originates on any other machine. (And your Apple and the virtualized Ubuntu are two different 'machines', even though they are running on the same slab of silicone.
                                The listener supports no services
                                No databases are registered with this listener. Even if a request reached the listener, it would be rejected.
                                The command completed successfully


                                oracle@Server:~$ cat $ORACLE_HOME/network/admin/listener.ora
                                Did you change users? The prompt suggest you are now user 'oracle' ....
                                # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
                                # Generated by Oracle configuration tools.

                                LISTENER =
                                (DESCRIPTION_LIST =
                                (DESCRIPTION =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = backupserver.XXXX.net)(PORT = 1521))
                                )
                                )

                                ADR_BASE_LISTENER = /u01/app/oracle


                                Below are the answers :-
                                1) what OS your database running on? I guess that must be your Ubuntu machine.
                                Yes ,My database is running on Ubuntu OS
                                2) What os are you trying to connect from?
                                I am trying to connect from Mac (with oracle instant client) and also from Ubuntu OS

                                EdStevens I will take while posting in Forum's Category.

                                Best Regards,
                                Amol Shinde.