1 2 Previous Next 21 Replies Latest reply: Dec 2, 2012 1:35 PM by Billy~Verreynne RSS

    use ip address in listener.ora not working

    Lukecui
      Hello,

      A strange problem, in listener.ora, if i use localhost, or computer name, then everything is ok, but when i try to use ip address, then I got a error ORA-12541: TNS:no listener

      the listener.ora file which working well


      # listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
      # Generated by Oracle configuration tools.

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
      )
      (SID_DESC =
      (GLOBAL_DBNAME = HMIHD)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (SID_NAME = HMIHD)
      )

      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
      )

      the listener.ora not working

      # listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
      # Generated by Oracle configuration tools.

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
      )
      (SID_DESC =
      (GLOBAL_DBNAME = HMIHD)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (SID_NAME = HMIHD)
      )

      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.183.7.89)(PORT = 1521))
      )
      )

      the only change is use the ipaddress of the server 10.183.7.89 instead of localhost, and if I change the ip address to the computer name, then its also working well.

      The server is running on vistual box, i don't know if the vistual machine cause this problem,
      I just want to confirm: I can use the ip address directly right?

      thanks inadvance!
        • 1. Re: use ip address in listener.ora not working
          sb92075
          Lukecui wrote:
          Hello,

          A strange problem, in listener.ora, if i use localhost, or computer name, then everything is ok, but when i try to use ip address, then I got a error ORA-12541: TNS:no listener
          error results when IP# does not match hostname resolution.

          The listener can be started when no listener.ora file exists.
          • 2. Re: use ip address in listener.ora not working
            Lukecui
            Thanks for the reply, I think i found the reason.

            if you use ip address in listener.ora like

            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.183.7.89)(PORT = 1521))
            )

            then you also need to use ip address in tnsnames.ors like

            DB1 =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.183.7.89)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = DB1)
            )
            )

            I don't know if it's offical solution, but it really solve my problem, hope get more explanations from expert ;-)

            Edited by: Lukecui on Nov 29, 2012 9:14 PM
            • 3. Re: use ip address in listener.ora not working
              Billy~Verreynne
              Lukecui wrote:
              Thanks for the reply, I think i found the reason.

              if you use ip address in listener.ora like
              then you also need to use ip address in tnsnames.ors like
              Not the reason and an incorrect statement.

              When a TCP port is bind as a listener endpoint, then this is done in one of two ways:
              - using the INADDR_ANY flag that creates a listening endpoint on ALL IP addresses (current and future) on that kernel
              - using a specific IP address that creates a listening endpoint on that IP address only

              When you use a hostname for an Oracle Listener config, the Listener uses the INADDR_ANY flag and creates a listening endpoint for the specified TCP port (e.g. 1521) on all IP addresses of that server.

              When you use an IP address for an Oracle Listener config, the Listener uses that IP address only as listening endpoint.

               
              The localhost hostname is an exception. This refers to IP address 127.0.0.1. Using either localhost or 127.0.0.1 means that no external IP connection can be made to the listener endpoint - as that IP address/hostname can only be referenced locally.

               
              Hostnames are used as it is easier to remember a meaningful name, than a series of numbers (IP address). When software (client or server) uses a hostname, it first resolves that hostname into an IP address. IP connectivity happens with IP addresses - the server listens on an IP address, the client connects to an IP address.

              So if hostnaming do not work, it means that hostname resolution on that kernel is not working. This requires DNS services to be configured for that kernel, or a hosts file to be maintained for hostname resolution.
              • 4. Re: use ip address in listener.ora not working
                Girish Sharma
                Hi Billy,

                Please have a look on a cut and paste of my Windows 7 64 bit test box on which I am having one test db. I just replaced HOST=ip address of the PC then I got ORA-12541, but when I again replaced that ip address with older HOST=value then, it worked...
                C:\Windows\system32>whoami
                serverroom-pc\serverroom
                
                C:\Windows\system32>TYPE C:\Windows\System32\drivers\etc\HOSTS
                # Copyright (c) 1993-2009 Microsoft Corp.
                #
                # This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
                #
                # This file contains the mappings of IP addresses to host names. Each
                # entry should be kept on an individual line. The IP address should
                # be placed in the first column followed by the corresponding host name.
                # The IP address and the host name should be separated by at least one
                # space.
                #
                # Additionally, comments (such as these) may be inserted on individual
                # lines or following the machine name denoted by a '#' symbol.
                #
                # For example:
                #
                #      102.54.94.97     rhino.acme.com          # source server
                #       38.25.63.10     x.acme.com              # x client host
                
                # localhost name resolution is handled within DNS itself.
                        127.0.0.1       localhost
                #       192.168.0.20    serverroom-PC
                #       ::1             localhost
                #localhost
                C:\Windows\system32>IPCONFIG /ALL
                
                Windows IP Configuration
                
                   Host Name . . . . . . . . . . . . : serverroom-PC
                   Primary Dns Suffix  . . . . . . . :
                   Node Type . . . . . . . . . . . . : Hybrid
                   IP Routing Enabled. . . . . . . . : No
                   WINS Proxy Enabled. . . . . . . . : No
                   DNS Suffix Search List. . . . . . : local.lan
                   System Quarantine State . . . . . : Not Restricted
                
                
                Ethernet adapter Local Area Connection:
                
                   Connection-specific DNS Suffix  . : local.lan
                   Description . . . . . . . . . . . : Intel(R) 82578DC Gigabit Network Connection
                   Physical Address. . . . . . . . . : 70-71-BC-F4-ED-BD
                   DHCP Enabled. . . . . . . . . . . : Yes
                   Autoconfiguration Enabled . . . . : Yes
                   Link-local IPv6 Address . . . . . : fe80::d868:1963:9057:d91%11(Preferred)
                   IPv4 Address. . . . . . . . . . . : 192.168.0.20(Preferred)
                   Subnet Mask . . . . . . . . . . . : 255.255.255.0
                   Lease Obtained. . . . . . . . . . : 30 November 2012 10:25:50
                   Lease Expires . . . . . . . . . . : 30 November 2012 22:25:50
                   Default Gateway . . . . . . . . . : 192.168.0.1
                   DHCP Server . . . . . . . . . . . : 192.168.0.1
                   DHCPv6 IAID . . . . . . . . . . . : 191918524
                   DHCPv6 Client DUID. . . . . . . . : 00-01-00-01-14-AD-FE-1B-70-71-BC-E2-7D-7A
                   DNS Servers . . . . . . . . . . . : 192.168.0.1
                   NetBIOS over Tcpip. . . . . . . . : Enabled
                
                Tunnel adapter isatap.{306A0543-AAC1-4310-8BD6-8C48C29340D6}:
                
                   Media State . . . . . . . . . . . : Media disconnected
                   Connection-specific DNS Suffix  . :
                   Description . . . . . . . . . . . : Microsoft ISATAP Adapter
                   Physical Address. . . . . . . . . : 00-00-00-00-00-00-00-E0
                   DHCP Enabled. . . . . . . . . . . : No
                   Autoconfiguration Enabled . . . . : Yes
                
                Tunnel adapter 6TO4 Adapter:
                
                   Media State . . . . . . . . . . . : Media disconnected
                   Connection-specific DNS Suffix  . :
                   Description . . . . . . . . . . . : Microsoft 6to4 Adapter
                   Physical Address. . . . . . . . . : 00-00-00-00-00-00-00-E0
                   DHCP Enabled. . . . . . . . . . . : No
                   Autoconfiguration Enabled . . . . : Yes
                
                Tunnel adapter Teredo Tunneling Pseudo-Interface:
                
                   Media State . . . . . . . . . . . : Media disconnected
                   Connection-specific DNS Suffix  . :
                   Description . . . . . . . . . . . : Teredo Tunneling Pseudo-Interface
                   Physical Address. . . . . . . . . : 00-00-00-00-00-00-00-E0
                   DHCP Enabled. . . . . . . . . . . : No
                   Autoconfiguration Enabled . . . . : Yes
                
                Tunnel adapter Reusable Microsoft 6To4 Adapter:
                
                   Media State . . . . . . . . . . . : Media disconnected
                   Connection-specific DNS Suffix  . :
                   Description . . . . . . . . . . . : Microsoft 6to4 Adapter #2
                   Physical Address. . . . . . . . . : 00-00-00-00-00-00-00-E0
                   DHCP Enabled. . . . . . . . . . . : No
                   Autoconfiguration Enabled . . . . : Yes
                
                Tunnel adapter Local Area Connection* 11:
                
                   Media State . . . . . . . . . . . : Media disconnected
                   Connection-specific DNS Suffix  . :
                   Description . . . . . . . . . . . : Microsoft 6to4 Adapter #3
                   Physical Address. . . . . . . . . : 00-00-00-00-00-00-00-E0
                   DHCP Enabled. . . . . . . . . . . : No
                   Autoconfiguration Enabled . . . . : Yes
                
                C:\Windows\system32>
                
                
                
                C:\Windows\system32>TYPE E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\LISTENER.ORA
                # listener.ora Network Configuration File: E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                # Generated by Oracle configuration tools.
                
                PASSWORDS_LISTENER = 1EA2C756B9196542
                
                #--------------------------------------------
                
                #----ADDED BY TNSLSNR 12-SEP-2012 16:17:32---
                
                SID_LIST_LISTENER =
                  (SID_LIST =
                    (SID_DESC =
                      (SID_NAME = CLRExtProc)
                      (ORACLE_HOME = E:\app\serverroom\product\11.2.0\dbhome_1)
                      (PROGRAM = extproc)
                      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\serverroom\product\11.2.0\dbhome_1\bin\oraclr11.dll")
                    )
                  )
                
                LOGGING_LISTENER = ON
                
                LISTENER =
                  (DESCRIPTION_LIST =
                    (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = serverroom-PC.local.lan)(PORT = 1521))
                    )
                  )
                
                ADR_BASE_LISTENER = E:\app\serverroom
                
                
                C:\Windows\system32>TYPE E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\TNSNAMES.ORA
                # tnsnames.ora Network Configuration File: E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
                # Generated by Oracle configuration tools.
                
                ORACLR_CONNECTION_DATA =
                  (DESCRIPTION =
                    (ADDRESS_LIST =
                      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                    )
                    (CONNECT_DATA =
                      (SID = CLRExtProc)
                      (PRESENTATION = RO)
                    )
                  )
                
                ORCL =
                  (DESCRIPTION =
                    (ADDRESS_LIST =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                    )
                    (CONNECT_DATA =
                      (SERVICE_NAME = ORCL)
                    )
                  )
                
                C:\Windows\system32>lsnrctl status
                
                LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:35:59
                
                Copyright (c) 1991, 2010, Oracle.  All rights reserved.
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serverroom-PC.local.lan)(PORT=1521)))
                STATUS of the LISTENER
                ------------------------
                Alias                     LISTENER
                Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                Start Date                26-NOV-2012 11:53:52
                Uptime                    3 days 23 hr. 42 min. 10 sec
                Trace Level               off
                Security                  ON: Password or Local OS Authentication
                SNMP                      OFF
                Listener Parameter File   E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                Listener Log File         e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverroom-PC)(PORT=1521)))
                Services Summary...
                Service "CLRExtProc" has 1 instance(s).
                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                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
                
                C:\Windows\system32>sqlplus scott/tiger@orcl
                
                SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 30 11:26:50 2012
                
                Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                
                
                Connected to:
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options
                
                SQL> exit
                
                Ok, now try to change HOST=ip address in listener.ora
                
                C:\Windows\system32>NOTEPAD E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\LISTENER.ORA
                
                C:\Windows\system32>TYPE E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\LISTENER.ORA
                # listener.ora Network Configuration File: E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                # Generated by Oracle configuration tools.
                
                PASSWORDS_LISTENER = 1EA2C756B9196542
                
                #--------------------------------------------
                
                #----ADDED BY TNSLSNR 12-SEP-2012 16:17:32---
                
                SID_LIST_LISTENER =
                  (SID_LIST =
                    (SID_DESC =
                      (SID_NAME = CLRExtProc)
                      (ORACLE_HOME = E:\app\serverroom\product\11.2.0\dbhome_1)
                      (PROGRAM = extproc)
                      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\serverroom\product\11.2.0\dbhome_1\bin\oraclr11.dll")
                    )
                  )
                
                LOGGING_LISTENER = ON
                
                LISTENER =
                  (DESCRIPTION_LIST =
                    (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 1521))
                    )
                  )
                
                ADR_BASE_LISTENER = E:\app\serverroom
                
                C:\Windows\system32>lsnrctl stop
                
                LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:39:05
                
                Copyright (c) 1991, 2010, Oracle.  All rights reserved.
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.20)(PORT=1521)))
                The command completed successfully
                
                C:\Windows\system32>lsnrctl start
                
                LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:39:14
                
                Copyright (c) 1991, 2010, Oracle.  All rights reserved.
                
                Starting tnslsnr: please wait...
                
                TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                System parameter file is E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                Log messages written to e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.20)(PORT=1521)))
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.20)(PORT=1521)))
                STATUS of the LISTENER
                ------------------------
                Alias                     LISTENER
                Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                Start Date                30-NOV-2012 11:39:19
                Uptime                    0 days 0 hr. 0 min. 5 sec
                Trace Level               off
                Security                  ON: Password or Local OS Authentication
                SNMP                      OFF
                Listener Parameter File   E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                Listener Log File         e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.20)(PORT=1521)))
                Services Summary...
                Service "CLRExtProc" has 1 instance(s).
                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                The command completed successfully
                
                C:\Windows\system32>lsnrctl status
                
                LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:41:11
                
                Copyright (c) 1991, 2010, Oracle.  All rights reserved.
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.20)(PORT=1521)))
                STATUS of the LISTENER
                ------------------------
                Alias                     LISTENER
                Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                Start Date                30-NOV-2012 11:39:19
                Uptime                    0 days 0 hr. 1 min. 55 sec
                Trace Level               off
                Security                  ON: Password or Local OS Authentication
                SNMP                      OFF
                Listener Parameter File   E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                Listener Log File         e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.20)(PORT=1521)))
                Services Summary...
                Service "CLRExtProc" has 1 instance(s).
                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                The command completed successfully
                
                C:\Windows\system32>sqlplus scott/tiger@orcl
                
                SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 30 11:42:59 2012
                
                Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                
                ERROR:
                ORA-12541: TNS:no listener
                
                
                Enter user-name:
                
                Oh... so, its now not working..!!
                
                Ok, lets try with just undo above steps i.e. again putting only HOST=serverroom-PC.local.lan and see what happens ....
                
                C:\Windows\system32>NOTEPAD E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\LISTENER.ORA
                
                C:\Windows\system32>TYPE E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\LISTENER.ORA
                # listener.ora Network Configuration File: E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                # Generated by Oracle configuration tools.
                
                PASSWORDS_LISTENER = 1EA2C756B9196542
                
                #--------------------------------------------
                
                #----ADDED BY TNSLSNR 12-SEP-2012 16:17:32---
                
                SID_LIST_LISTENER =
                  (SID_LIST =
                    (SID_DESC =
                      (SID_NAME = CLRExtProc)
                      (ORACLE_HOME = E:\app\serverroom\product\11.2.0\dbhome_1)
                      (PROGRAM = extproc)
                      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\serverroom\product\11.2.0\dbhome_1\bin\oraclr11.dll")
                    )
                  )
                
                LOGGING_LISTENER = ON
                
                LISTENER =
                  (DESCRIPTION_LIST =
                    (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = serverroom-PC.local.lan)(PORT = 1521))
                    )
                  )
                
                ADR_BASE_LISTENER = E:\app\serverroom
                
                
                C:\Windows\system32>lsnrctl stop
                
                LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:45:13
                
                Copyright (c) 1991, 2010, Oracle.  All rights reserved.
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serverroom-PC.local.lan)(PORT=1521)))
                The command completed successfully
                
                C:\Windows\system32>lsnrctl start
                
                LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:45:22
                
                Copyright (c) 1991, 2010, Oracle.  All rights reserved.
                
                Starting tnslsnr: please wait...
                
                TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                System parameter file is E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                Log messages written to e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverroom-PC)(PORT=1521)))
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serverroom-PC.local.lan)(PORT=1521)))
                STATUS of the LISTENER
                ------------------------
                Alias                     LISTENER
                Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                Start Date                30-NOV-2012 11:45:28
                Uptime                    0 days 0 hr. 0 min. 5 sec
                Trace Level               off
                Security                  ON: Password or Local OS Authentication
                SNMP                      OFF
                Listener Parameter File   E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                Listener Log File         e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverroom-PC)(PORT=1521)))
                Services Summary...
                Service "CLRExtProc" has 1 instance(s).
                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                The command completed successfully
                
                C:\Windows\system32>lsnrctl status
                
                LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:47:56
                
                Copyright (c) 1991, 2010, Oracle.  All rights reserved.
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serverroom-PC.local.lan)(PORT=1521)))
                STATUS of the LISTENER
                ------------------------
                Alias                     LISTENER
                Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                Start Date                30-NOV-2012 11:45:28
                Uptime                    0 days 0 hr. 2 min. 32 sec
                Trace Level               off
                Security                  ON: Password or Local OS Authentication
                SNMP                      OFF
                Listener Parameter File   E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                Listener Log File         e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverroom-PC)(PORT=1521)))
                Services Summary...
                Service "CLRExtProc" has 1 instance(s).
                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                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
                
                C:\Windows\system32>sqlplus scott/tiger@orcl
                
                SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 30 11:49:03 2012
                
                Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                
                
                Connected to:
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options
                
                SQL> exit
                Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options
                
                C:\Windows\system32>
                
                
                Ok, it means, if I says IP address in listener.ora and HOST=localhost in tnsnames.ora; then
                there is ORA-12541: TNS:no listener.
                Kindly comment, what OP is saying seems correct please or I am missing something ?

                Regards
                Girish Sharma
                • 5. Re: use ip address in listener.ora not working
                  EdStevens
                  Girish Sharma wrote:
                  Hi Billy,
                  comments embedded

                  Please have a look on a cut and paste of my Windows 7 64 bit test box on which I am having one test db. I just replaced HOST=ip address of the PC then I got ORA-12541, but when I again replaced that ip address with older HOST=value then, it worked...

                  C:\Windows\system32>whoami
                  serverroom-pc\serverroom

                  C:\Windows\system32>TYPE C:\Windows\System32\drivers\etc\HOSTS
                  # Copyright (c) 1993-2009 Microsoft Corp.
                  #
                  # This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
                  #
                  # This file contains the mappings of IP addresses to host names. Each
                  # entry should be kept on an individual line. The IP address should
                  # be placed in the first column followed by the corresponding host name.
                  # The IP address and the host name should be separated by at least one
                  # space.
                  #
                  # Additionally, comments (such as these) may be inserted on individual
                  # lines or following the machine name denoted by a '#' symbol.
                  #
                  # For example:
                  #
                  # 102.54.94.97 rhino.acme.com # source server
                  # 38.25.63.10 x.acme.com # x client host

                  # localhost name resolution is handled within DNS itself.
                  127.0.0.1 localhost
                  # 192.168.0.20 serverroom-PC
                  # ::1 localhost
                  #localhost
                  So your hosts file has only a single entry, for localhost. So we assume all hostname resolution comes from DNS.
                  C:\Windows\system32>IPCONFIG /ALL

                  Windows IP Configuration

                  Host Name . . . . . . . . . . . . : serverroom-PC
                  Primary Dns Suffix . . . . . . . :
                  Node Type . . . . . . . . . . . . : Hybrid
                  IP Routing Enabled. . . . . . . . : No
                  WINS Proxy Enabled. . . . . . . . : No
                  DNS Suffix Search List. . . . . . : local.lan
                  System Quarantine State . . . . . : Not Restricted


                  Ethernet adapter Local Area Connection:

                  Connection-specific DNS Suffix . : local.lan
                  Description . . . . . . . . . . . : Intel(R) 82578DC Gigabit Network Connection
                  Physical Address. . . . . . . . . : 70-71-BC-F4-ED-BD
                  DHCP Enabled. . . . . . . . . . . : Yes
                  Autoconfiguration Enabled . . . . : Yes
                  Link-local IPv6 Address . . . . . : fe80::d868:1963:9057:d91%11(Preferred)
                  IPv4 Address. . . . . . . . . . . : 192.168.0.20(Preferred)
                  Subnet Mask . . . . . . . . . . . : 255.255.255.0
                  Lease Obtained. . . . . . . . . . : 30 November 2012 10:25:50
                  Lease Expires . . . . . . . . . . : 30 November 2012 22:25:50
                  Default Gateway . . . . . . . . . : 192.168.0.1
                  DHCP Server . . . . . . . . . . . : 192.168.0.1
                  DHCPv6 IAID . . . . . . . . . . . : 191918524
                  DHCPv6 Client DUID. . . . . . . . : 00-01-00-01-14-AD-FE-1B-70-71-BC-E2-7D-7A
                  DNS Servers . . . . . . . . . . . : 192.168.0.1
                  NetBIOS over Tcpip. . . . . . . . : Enabled

                  <snip irrelevent adapters>
                  C:\Windows\system32>



                  C:\Windows\system32>TYPE E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\LISTENER.ORA
                  # listener.ora Network Configuration File: E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                  # Generated by Oracle configuration tools.

                  PASSWORDS_LISTENER = 1EA2C756B9196542

                  #--------------------------------------------

                  #----ADDED BY TNSLSNR 12-SEP-2012 16:17:32---

                  SID_LIST_LISTENER =
                  (SID_LIST =
                  (SID_DESC =
                  (SID_NAME = CLRExtProc)
                  (ORACLE_HOME = E:\app\serverroom\product\11.2.0\dbhome_1)
                  (PROGRAM = extproc)
                  (ENVS = "EXTPROC_DLLS=ONLY:E:\app\serverroom\product\11.2.0\dbhome_1\bin\oraclr11.dll")
                  )
                  )
                  So there is no static registration of the db instances.

                  >
                  LOGGING_LISTENER = ON

                  LISTENER =
                  (DESCRIPTION_LIST =
                  (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = serverroom-PC.local.lan)(PORT = 1521))
                  )
                  )
                  And what IP address does serverroom-PC.local.lan resolve to? Remember, all communication is by IP address. The use of a hostname is just a convenience for humans, but it will be resolved to an IP address.
                  >
                  ADR_BASE_LISTENER = E:\app\serverroom


                  C:\Windows\system32>TYPE E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\TNSNAMES.ORA
                  # tnsnames.ora Network Configuration File: E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
                  # Generated by Oracle configuration tools.

                  ORACLR_CONNECTION_DATA =
                  (DESCRIPTION =
                  (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                  )
                  (CONNECT_DATA =
                  (SID = CLRExtProc)
                  (PRESENTATION = RO)
                  )
                  )

                  ORCL =
                  (DESCRIPTION =
                  (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                  )
                  (CONNECT_DATA =
                  (SERVICE_NAME = ORCL)
                  )
                  )
                  host=localhost will ALWAYS resolve to ip address 127.0.0.1 .....
                  Remember, network routing is always by ip address. If you specify a host name, it just gets translated to an ip address very early in the process.



                  >
                  C:\Windows\system32>lsnrctl status

                  LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:35:59

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

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serverroom-PC.local.lan)(PORT=1521)))
                  STATUS of the LISTENER
                  ------------------------
                  Alias LISTENER
                  Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                  Start Date 26-NOV-2012 11:53:52
                  Uptime 3 days 23 hr. 42 min. 10 sec
                  Trace Level off
                  Security ON: Password or Local OS Authentication
                  SNMP OFF
                  Listener Parameter File E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                  Listener Log File e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                  Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverroom-PC)(PORT=1521)))
                  Services Summary...
                  Service "CLRExtProc" has 1 instance(s).
                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                  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

                  C:\Windows\system32>sqlplus scott/tiger@orcl

                  SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 30 11:26:50 2012

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


                  Connected to:
                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options

                  SQL> exit

                  Ok, now try to change HOST=ip address in listener.ora

                  C:\Windows\system32>NOTEPAD E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\LISTENER.ORA

                  C:\Windows\system32>TYPE E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\LISTENER.ORA
                  # listener.ora Network Configuration File: E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                  # Generated by Oracle configuration tools.

                  PASSWORDS_LISTENER = 1EA2C756B9196542

                  #--------------------------------------------

                  #----ADDED BY TNSLSNR 12-SEP-2012 16:17:32---

                  <snip irrelevent SID LIST>
                  LOGGING_LISTENER = ON

                  LISTENER =
                  (DESCRIPTION_LIST =
                  (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 1521))
                  )
                  )

                  ADR_BASE_LISTENER = E:\app\serverroom

                  C:\Windows\system32>lsnrctl stop

                  LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:39:05

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

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.20)(PORT=1521)))
                  The command completed successfully

                  C:\Windows\system32>lsnrctl start

                  LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:39:14

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

                  Starting tnslsnr: please wait...

                  TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                  System parameter file is E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                  Log messages written to e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                  Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.20)(PORT=1521)))

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.20)(PORT=1521)))
                  STATUS of the LISTENER
                  ------------------------
                  Alias LISTENER
                  Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                  Start Date 30-NOV-2012 11:39:19
                  Uptime 0 days 0 hr. 0 min. 5 sec
                  Trace Level off
                  Security ON: Password or Local OS Authentication
                  SNMP OFF
                  Listener Parameter File E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                  Listener Log File e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                  Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.20)(PORT=1521)))
                  Services Summary...
                  Service "CLRExtProc" has 1 instance(s).
                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                  The command completed successfully

                  C:\Windows\system32>lsnrctl status

                  LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:41:11

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

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.20)(PORT=1521)))
                  STATUS of the LISTENER
                  ------------------------
                  Alias LISTENER
                  Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                  Start Date 30-NOV-2012 11:39:19
                  Uptime 0 days 0 hr. 1 min. 55 sec
                  Trace Level off
                  Security ON: Password or Local OS Authentication
                  SNMP OFF
                  Listener Parameter File E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                  Listener Log File e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                  Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.20)(PORT=1521)))
                  Services Summary...
                  Service "CLRExtProc" has 1 instance(s).
                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                  The command completed successfully
                  But the db instance has not yet registered with the listener. Is it because you didn't wait long enough after restarting the listener, or is it because the instance cannot find the listener due to the new configuration?
                  C:\Windows\system32>sqlplus scott/tiger@orcl

                  SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 30 11:42:59 2012

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

                  ERROR:
                  ORA-12541: TNS:no listener


                  Enter user-name:

                  Oh... so, its now not working..!!
                  Because your tnsnames specified the listener was at 127.0.0.1, but your listener thinks he is at 192.168.0.20. So your request got routed to 127.0.01, and there is no listener on that IP address.
                  Ok, lets try with just undo above steps i.e. again putting only HOST=serverroom-PC.local.lan and see what happens ....

                  C:\Windows\system32>NOTEPAD E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\LISTENER.ORA

                  C:\Windows\system32>TYPE E:\app\serverroom\product\11.2.0\dbhome_1\NETWORK\ADMIN\LISTENER.ORA
                  # listener.ora Network Configuration File: E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                  # Generated by Oracle configuration tools.

                  PASSWORDS_LISTENER = 1EA2C756B9196542

                  #--------------------------------------------

                  #----ADDED BY TNSLSNR 12-SEP-2012 16:17:32---

                  SID_LIST_LISTENER =
                  (SID_LIST =
                  (SID_DESC =
                  (SID_NAME = CLRExtProc)
                  (ORACLE_HOME = E:\app\serverroom\product\11.2.0\dbhome_1)
                  (PROGRAM = extproc)
                  (ENVS = "EXTPROC_DLLS=ONLY:E:\app\serverroom\product\11.2.0\dbhome_1\bin\oraclr11.dll")
                  )
                  )

                  LOGGING_LISTENER = ON

                  LISTENER =
                  (DESCRIPTION_LIST =
                  (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = serverroom-PC.local.lan)(PORT = 1521))
                  )
                  )

                  ADR_BASE_LISTENER = E:\app\serverroom


                  C:\Windows\system32>lsnrctl stop

                  LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:45:13

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

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serverroom-PC.local.lan)(PORT=1521)))
                  The command completed successfully

                  C:\Windows\system32>lsnrctl start

                  LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:45:22

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

                  Starting tnslsnr: please wait...

                  TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                  System parameter file is E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                  Log messages written to e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                  Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverroom-PC)(PORT=1521)))

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serverroom-PC.local.lan)(PORT=1521)))
                  STATUS of the LISTENER
                  ------------------------
                  Alias LISTENER
                  Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                  Start Date 30-NOV-2012 11:45:28
                  Uptime 0 days 0 hr. 0 min. 5 sec
                  Trace Level off
                  Security ON: Password or Local OS Authentication
                  SNMP OFF
                  Listener Parameter File E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                  Listener Log File e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                  Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverroom-PC)(PORT=1521)))
                  Services Summary...
                  Service "CLRExtProc" has 1 instance(s).
                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                  The command completed successfully

                  C:\Windows\system32>lsnrctl status

                  LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 30-NOV-2012 11:47:56

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

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serverroom-PC.local.lan)(PORT=1521)))
                  STATUS of the LISTENER
                  ------------------------
                  Alias LISTENER
                  Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
                  Start Date 30-NOV-2012 11:45:28
                  Uptime 0 days 0 hr. 2 min. 32 sec
                  Trace Level off
                  Security ON: Password or Local OS Authentication
                  SNMP OFF
                  Listener Parameter File E:\app\serverroom\product\11.2.0\dbhome_1\network\admin\listener.ora
                  Listener Log File e:\app\serverroom\diag\tnslsnr\serverroom-PC\listener\alert\log.xml
                  Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverroom-PC)(PORT=1521)))
                  Services Summary...
                  Service "CLRExtProc" has 1 instance(s).
                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                  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
                  And now your instances are registered with the listener
                  C:\Windows\system32>sqlplus scott/tiger@orcl

                  SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 30 11:49:03 2012

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


                  Connected to:
                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options

                  SQL> exit
                  Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options

                  C:\Windows\system32>


                  Ok, it means, if I says IP address in listener.ora and HOST=localhost in tnsnames.ora; then
                  there is ORA-12541: TNS:no listener.
                  No, it means you mis-interpreted what was going on.
                  I'd like to see the result of
                  - ping serverroom-PC.local.lan
                  - ping serverroom-PC
                  Kindly comment, what OP is saying seems correct please or I am missing something ?

                  Regards
                  Girish Sharma
                  Edited by: EdStevens on Nov 30, 2012 7:10 AM
                  • 6. Re: use ip address in listener.ora not working
                    12cdb
                    Dear Billy,

                    thanks for sharing that information.
                    very useful.

                    Regards,
                    Pradeep
                    • 7. Re: use ip address in listener.ora not working
                      Girish Sharma
                      Hi Ed,

                      I am thankful to you for your detailed replies with explanations. At the moment, I am not at above machine, but I shall not forget to post the required output which you asked.
                      But the db instance has not yet registered with the listener. Is it because you didn't wait long enough after restarting the listener, or is it because the instance cannot find the listener due to the new configuration?
                      But, I am remembering that I waited for around 2 minutes between lsnrctl start and lsnrctl status, because I know that listener requires 60 seconds to come to know how many dbs I have to listen.

                      Thanks once again for your good reply as always.

                      Regards
                      Girish Sharma
                      • 8. Re: use ip address in listener.ora not working
                        EdStevens
                        Girish Sharma wrote:
                        Hi Ed,

                        I am thankful to you for your detailed replies with explanations. At the moment, I am not at above machine, but I shall not forget to post the required output which you asked.
                        But the db instance has not yet registered with the listener. Is it because you didn't wait long enough after restarting the listener, or is it because the instance cannot find the listener due to the new configuration?
                        But, I am remembering that I waited for around 2 minutes between lsnrctl start and lsnrctl status, because I know that listener requires 60 seconds to come to know how many dbs I have to listen.
                        Good. now we know that the instance was unable to contact the listener due to the reconfiguration of the listener. See my blog post on LOCAL_LISTENER parameter.
                        >
                        Thanks once again for your good reply as always.

                        Regards
                        Girish Sharma
                        • 9. Re: use ip address in listener.ora not working
                          Lukecui
                          Hello Girish,

                          Greeting from Beijing,

                          Thanks for the step by step explanations, actually i almost did the same, but i'm too lazy to write down all ;-)

                          I forgot to write some background of this issue,

                          1. after install the oracle db in a visual machine (virtual box) for testing, everything is fine.
                          2. all the tests i did are in the same machine
                          3. after the database created, the hostname is used in the listener.ora, and localhost is used in the tnsnames.ora
                          4. then one of my colleague change the hostname to ip address in listener.ora, and then, sqlplus report ORA-12541: TNS:no listener error (Girsh show the detail steps, thanks again), he show me the error, but he never told me what he did ;-(

                          5. I post the question in this form, actually i found another person post a samilar question https://forums.oracle.com/forums/thread.jspa?threadID=1106564, but the answer is: post questions in wrong place, ha, but i think it's different story, since this guy got an error ORA-12514: TNS:listener does not currently know of service requested in connect, but i got ORA-12541: TNS:no listener error

                          6. comeback from lunch, i found somedy use ip address in the listener.ora, and change it to localhost, then sqlplus working again

                          in step 4, I believe if you configure the tns in another computer, and use ip address or hostname of the server, i blieve it will working. only in the same computer, the localhost used in the tnsnames.ora, and no listener found on localhost or 127.0.0.1, since the listener now listen on the ip address.

                          Edited by: Lukecui on Nov 30, 2012 7:48 AM

                          Edited by: Lukecui on Nov 30, 2012 7:52 AM
                          • 10. Re: use ip address in listener.ora not working
                            Lukecui
                            Thanks EdStevens, now I know more about the listener
                            • 11. Re: use ip address in listener.ora not working
                              sb92075
                              Lukecui wrote:
                              Hello Girish,

                              Greeting from Beijing,

                              Thanks for the step by step explanations, actually i almost did the same, but i'm too lazy to write down all ;-)

                              I forgot to write some background of this issue,

                              1. after install the oracle db in a visual machine (virtual box) for testing, everything is fine.
                              2. all the tests i did are in the same machine
                              3. after the database created, the hostname is used in the listener.ora, and localhost is used in the tnsnames.ora
                              4. then one of my colleague change the hostname to ip address in listener.ora, and then, sqlplus report ORA-12541: TNS:no listener error (Girsh show the detail steps, thanks again), he show me the error, but he never told me what he did ;-(
                              5. I post the question in this form, actuall i found another person post the same question
                              6. comeback from lunch, i found somedy use ip address in the listener.ora, and change it to localhost, then sqlplus working again

                              in step 4, I believe if you configure the tns in another computer, and use ip address or hostname of the server, i blieve it will working. only in the same computer, the localhost used in the tnsnames.ora, and no listener found on localhost or 127.0.0.1, since the listener now listen on the ip address.
                              when any system uses "localhost", then no remote client or system can ever connect to "localhost" system
                              • 12. Re: use ip address in listener.ora not working
                                Lukecui
                                yes, you are right, this is a testing environment, so no remote connection. thanks,
                                • 13. Re: use ip address in listener.ora not working
                                  EdStevens
                                  Lukecui wrote:
                                  yes, you are right, this is a testing environment, so no remote connection. thanks,
                                  Ah, but there is .. if things are as you described.

                                  As I understand it you have VirtualBox installed on a Windows system. Then you have a virtual machine created, running under virtual box. And your database is on that virtual machine.

                                  If so, please understand that as far as oracle is concerned ... as far as the network stack is concerned, that virtual machine is remote. the fact that it is running on the same silicone as your windows host is entirely irrelevant. They are two separate machines.
                                  • 14. Re: use ip address in listener.ora not working
                                    Billy~Verreynne
                                    Did respond shortly after you posted, but Jives authentication acted up and my response was lost. Not the first time either that I've lost responses on OTN due to Jives issues or OTN going down. Oh well...

                                    My response was along the following lines.

                                    Your Listener starts of listening using the server's hostname. This binds port 1521 to all IP addresses on the server. Should a new IP be created, that too will automatically be supported and port 1521 connections to it will be accepted by the Listener.

                                    The ORCL tns alias connect to port 1521 on localhost (IP address 127.0.0.1). As the Listener listens on all IP addresses of that server, the client connection succeeds.

                                    The Listener is then changed to listening on a specific IP address. Now the Listener only listens on port 1521 on that IP - and no other IPs of that server.

                                    The ORCL tns alias now attempts to connect using 127.0.0.1 - and this IP is now not supported by the Listener. Thus the client connection fails.

                                    If the ORL tns connection uses the same IP as the Listener, the connection will succeed. The ORCL alias can either use the IP address directly, or a hostname that will resolve into that IP.

                                    The basic rules are:
                                    - if servername is used, the Listener uses INADDR_ANY socket flag and binds the port to all IP addresses of that server
                                    - if IP address 0.0.0.0 is used, the Listener also binds the port to all IP addresses of that server
                                    - if localhost or a specific IP address is used, the Listener binds the port to only that specific address and no other server IP addresses

                                    For the client to connect, it needs to use one of the IP addresses that the Listener is listening on. The client can use:
                                    - the hostname for that IP (the hostname needs to resolve to the correct IP via DNS or hosts file)
                                    - the actual IP address itself

                                    Use the following command on Windows to see what IP address(es) and port(s) the Listener process is listening:
                                    netstat -b -n -p tcp
                                    1 2 Previous Next