This discussion is archived
12 Replies Latest reply: Oct 9, 2012 9:38 AM by EdStevens RSS

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

966840 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    <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.

Legend

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