This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Dec 2, 2012 11:35 AM by BillyVerreynne Go to original post RSS
  • 15. Re: use ip address in listener.ora not working
    Girish Sharma Guru
    Currently Being Moderated
    Hi Ed,

    Here are required info please :
    Microsoft Windows [Version 6.1.7600]
    Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
    
    C:\Windows\system32>ping serverroom-PC.local.lan
    
    Pinging serverroom-PC.local.lan [192.168.0.20] with 32 bytes of data:
    Reply from 192.168.0.20: bytes=32 time<1ms TTL=128
    Reply from 192.168.0.20: bytes=32 time<1ms TTL=128
    Reply from 192.168.0.20: bytes=32 time<1ms TTL=128
    Reply from 192.168.0.20: bytes=32 time<1ms TTL=128
    
    Ping statistics for 192.168.0.20:
        Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
    Approximate round trip times in milli-seconds:
        Minimum = 0ms, Maximum = 0ms, Average = 0ms
    
    C:\Windows\system32>ping serverroom-PC
    
    Pinging serverroom-PC [fe80::d868:1963:9057:d91%11] with 32 bytes of data:
    Reply from fe80::d868:1963:9057:d91%11: time<1ms
    Reply from fe80::d868:1963:9057:d91%11: time<1ms
    Reply from fe80::d868:1963:9057:d91%11: time<1ms
    Reply from fe80::d868:1963:9057:d91%11: time<1ms
    
    Ping statistics for fe80::d868:1963:9057:d91%11:
        Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
    Approximate round trip times in milli-seconds:
        Minimum = 0ms, Maximum = 0ms, Average = 0ms
    
    C:\Windows\system32>netstat -b -n -p tcp
    
    Active Connections
    
      Proto  Local Address          Foreign Address        State
      TCP    192.168.0.20:7588      74.125.135.125:5222    ESTABLISHED
     [googletalk.exe]
      TCP    192.168.0.20:8663      173.222.65.55:443      ESTABLISHED
     [chrome.exe]
      TCP    192.168.0.20:8666      66.235.132.118:443     ESTABLISHED
     [chrome.exe]
      TCP    192.168.0.20:8691      173.222.65.55:443      ESTABLISHED
     [chrome.exe]
      TCP    192.168.0.20:8692      173.222.65.55:443      ESTABLISHED
     [chrome.exe]
      TCP    192.168.0.20:8693      173.222.65.55:443      ESTABLISHED
     [chrome.exe]
      TCP    192.168.0.20:8694      173.222.65.55:443      ESTABLISHED
     [chrome.exe]
      TCP    192.168.0.20:8695      66.235.138.44:443      ESTABLISHED
     [chrome.exe]
      TCP    192.168.0.20:8699      173.194.38.142:443     ESTABLISHED
     [chrome.exe]
    
    C:\Windows\system32>
    I did a thorough test something like this :
    =====================================================================================================================================================================
    Case No.   In LISTENER.ORA                           In TNSNAMES.ORA                  Result after 2 minutes (sqlplus scott/tiger@orcl)
    =====================================================================================================================================================================
    1.         HOST = serverroom-PC.local.lan            HOST = localhost                 Working
    2.         HOST = 192.168.0.20                       HOST = localhost                 ORA-12541: TNS:no listener.
    3.         HOST = localhost                          HOST = localhost                 ORA-12514: TNS:listener does not currently know of service requested in connect
    4.         HOST = serverroom-PC                      HOST = localhost                 Listener could not stop/start
    5.         HOST = 192.168.0.20                       HOST = serverroom-PC.local.lan   Listener could not stop/start
    6.         HOST = 192.168.0.20                       HOST = 192.168.0.20              Listener could not stop/start
    7.         HOST = 192.168.0.20                       HOST = serverroom-PC             Listener could not stop/start
    8.         HOST = localhost                          HOST = serverroom-PC.local.lan   ORA-12541: TNS:no listener
    9.         HOST = localhost                          HOST = 192.168.0.20              ORA-12541: TNS:no listener
    10.        HOST = localhost                          HOST = serverroom-PC             ORA-12541: TNS:no listener
    11.        HOST = serverroom-PC                      HOST = serverroom-PC.local.lan   Listener could not stop/start
    12.        HOST = serverroom-PC                      HOST = 192.168.0.20              Listener could not stop/start
    13.        HOST = serverroom-PC                      HOST = serverroom-PC             Listener could not stop/start
    14.        HOST = serverroom-PC.local.lan            HOST = 192.168.0.20              Listener could not stop/start
    15.        HOST = serverroom-PC.local.lan            HOST = serverroom-PC             Listener could not stop/start
    16.        HOST = serverroom-PC.local.lan            HOST = serverroom-PC.local.lan   Listener could not stop/start
    But, when I again tried to configure Case No. 1 which was working, I could not, so I did :
    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 stop
    
    LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 01-DEC-2012 12:08:49
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serverroom-PC.local.lan)(PORT=1521)))
    TNS-12541: TNS:no listener
     TNS-12560: TNS:protocol adapter error
      TNS-00511: No listener
       64-bit Windows Error: 61: Unknown error
    
    C:\Windows\system32>lsnrctl start
    
    LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 01-DEC-2012 12:10:29
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    
    Starting tnslsnr: please wait...
    
    Service OracleOraDb11g_home1TNSListener already running.
    TNS-12560: TNS:protocol adapter error
     TNS-00530: Protocol adapter error
    
    
    C:\Windows\system32>net stop OracleOraDb11g_home1TNSListener
    The OracleOraDb11g_home1TNSListener service is stopping.
    The OracleOraDb11g_home1TNSListener service was stopped successfully.
    
    
    C:\Windows\system32>lsnrctl start
    
    LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 01-DEC-2012 12:12:21
    
    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                01-DEC-2012 12:12:26
    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 01-DEC-2012 12:14:09
    
    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                01-DEC-2012 12:12:26
    Uptime                    0 days 0 hr. 1 min. 46 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 Sat Dec 1 12:26:44 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>
    I don't know where and how listener service at OS level got stopped, so I just started, waited for 2 min and then now I am again on the Case No. 1.

    I have done 16 cases i.e. different HOST= (serverroom-PC.local.lan, 192.168.0.20, localhost and serverroom-PC) in listener and tnsnames.ora. I think I should not paste that very text file (around 2000 lines in which I have paste the command prompt outputs).

    Ed, I don't know I am doing correct testing or not because as you see above, listener's OS service got stopped; so lsnrctl command's response seems bit confusing me though. I am just sharing test case's result for your information please.

    Regards
    Girish Sharma
  • 16. Re: use ip address in listener.ora not working
    Girish Sharma Guru
    Currently Being Moderated
    Hi Billy,

    I am really thankful to you for your such a nice explanation as always. I am doing testing on my test box and database to get into what exactly happens if I just plays with HOST parameter of listener and tnsnames.ora with different values.

    Regards
    Girish Sharma
  • 17. Re: use ip address in listener.ora not working
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Take note that hostname serverroom-PC resolves to an IPv6 address and serverroom-PC.local.lan resolves to an IPv4 address.

    Not a good idea to have this type of name resolution. Different hostnames should be used for IPv4 and IPv6.

    Also, do not change the listener.ora prior to shutting the Listener down. First shut it down and then change the config file (in case you accidentally try it the other way around).
  • 18. Re: use ip address in listener.ora not working
    EdStevens Guru
    Currently Being Moderated
    Girish Sharma wrote:
    Hi Ed,
    <snip>
    Ed, I don't know I am doing correct testing or not because as you see above, listener's OS service got stopped; so lsnrctl command's response seems bit confusing me though. I am just sharing test case's result for your information please.

    Regards
    Girish Sharma
    At this point I need to defer to Billy. He's pointed out a couple of things I was not aware of (host name referring to muliple IP's; the IPv6 address). All I can say at this point is I never use 'localhost' or it's ip address at either end of the connection, so I'd start with eliminating that from the equation. Also, I've never worked with IPv6 addresses and know absolutely nothing about them beyond the fact they exist and were designed to create a larger address space than is possible with the IPv4 architecture.
  • 19. Re: use ip address in listener.ora not working
    Lukecui Newbie
    Currently Being Moderated
    thanks for the reply,
    the same machine which i said means: in the virtual machine, in other word, I did all test inside the virtual machine which installed an oracle server, no oracle installed in the host computer of the virtual machine. I really need to improve my english also to describe things more precisely ;-)
  • 20. Re: use ip address in listener.ora not working
    Lukecui Newbie
    Currently Being Moderated
    Thanks Billy, this is really helpful!
  • 21. Re: use ip address in listener.ora not working
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    EdStevens wrote:

    All I can say at this point is I never use 'localhost' or it's ip address at either end of the connection, so I'd start with eliminating that from the equation.
    XE uses localhost as a security measure I believe. A local HTTP/web browser connection is configured via localhost and Oracle XE is not exposed to the network as a potential means of attack

    But as you said - this does not always makes sense when you want client-server across your local subnet.
    Also, I've never worked with IPv6 addresses and know absolutely nothing about them beyond the fact they exist and were designed to create a larger address space than is possible with the IPv4 architecture.
    Am in the same boat. IPv6 is still some distance away on the horizon. One thing, no make it two things, are certain.

    It is going to be hell to remember "raw" IP addresses as we do now. And this is going to break all Oracle APEX applications that want to pass an IPv6 address via the URL - as APEX uses colon as separator characters and chokes on a URL string value with colons in it... :-)
1 2 Previous Next

Legend

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