Re: Multi-homed interface and Oracle listener

From: Jeff Traigle <traigle_at_si.umich.edu>
Date: Mon, 20 Jan 2003 15:44:18 -0500
Message-ID: <BA51C9D2.3ECA5%traigle@si.umich.edu>

Ok. I think I understand how this is working now. Results of netstat -an:

tcp        0      0  19.63.4.222.1521       19.63.50.184.1220   ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.50.152.1049   ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.51.102.3197   ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.50.109.1071   ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.91.31.1048    ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.91.31.1044    ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.91.31.1040    ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.50.176.1039   ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.50.176.1035   ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.51.160.1087   ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.51.157.1035   ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.91.188.2506   ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.5.123.1673    ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.51.160.1060   ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.51.213.1277   ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.5.123.1659    ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.51.53.1279    ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.50.98.3723    ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.91.41.1081    ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.91.236.1282   ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.50.85.1250    ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.90.110.1033   ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.50.156.1032   ESTABLISHED
tcp        0      0  136.17.216.21.1521     19.63.51.53.1036    ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.51.216.1118   ESTABLISHED
tcp        0      0  19.63.4.222.1521       19.63.91.154.1035   ESTABLISHED
tcp        0      0  *.1521                 *.*                 LISTEN

As Rick suspected, it looks like the listener is watching on any address configured on the system. (This is, in fact, a 7.3.2.3.0 database, but this seems to be consistent on my two other servers running 7.3.4.5.0 also.) As you can see, there are connections from both PCs that got their tnsnames.ora file updated to the new address and ones that did not.

The confusion in behavior stems from the configuration of addresses in the listener apparently having nothing to do with the addresses it will actually listen on. The address configuration in listener.ora appears to only verify that the addresses configured are, in fact, active addresses on some interface on the server. (If one isn't present on the server, the listener fails to start.) However, once the listener starts successfully, it listens on any address configured on the system just like any other generic UNIX daemon.

Not sure if that's true for 8.X and above of Oracle RDBMS since I don't have anything that new running. Or if there is some parameter in the listener.ora file that will restrict the listener to only the addresses configured in the listener.ora file. (If there is and someone knows it offhand, it would be nice to know what it is so feel free to share the knowledge.) It's been a few years since I took over DBA functions in my current position and took the 8.0 classes so don't even recall if this was something touched on in the networking class back then or not.

On 1/20/03 12:03 AM, in article BA50ED36.3D351%traigle_at_si.umich.edu, "Jeff Traigle" <traigle_at_si.umich.edu> wrote:

> On 1/19/03 6:12 PM, in article b0fbdb$kcn$1_at_web1.cup.hp.com, "Rick Jones"
> <foo_at_bar.baz.invalid> wrote:
>

>> In comp.sys.hp.hpux Jeff Traigle <traigle_at_si.umich.edu> wrote:
>>> Normally, the Oracle listener must be configured to watch port 1521
>>> on each IP address configured on a system (at least when the IP
>>> addresses are assigned to different interfaces from previous
>>> configurations with multi-NIC systems I set up, as best I can
>>> recall). On this system, however, even though the listener
>>> configuration wasn't changed, clients are still able to connect to
>>> the database using the new IP address.
>> 
>>> The only thing I can think of as to why this is working is the OS is
>>> doing some transparent translation since the addresses are assigned
>>> to the same interface. Would be nice to better understand the
>>> dynamic that is allowing this to function. Anyone care to take a
>>> stab at it?
>> 
>> Since HP-UX (10.20 at least) has no support for NAT, it shouldn't be
>> translating any IP addresses. Same NIC or not.
>> 
>> I would check the output of netstat -an | grep 1521 and make sure that
>> the Oracle listener isn't listening on INADDR_ANY (wildcard IP), or on
>> both IPs.  The other possibility is that the client config change
>> didn't really "take" and they are still using the old IPs. That would
>> appear in the netstat -an output.
>> 
>> rick jones

>
> Well, the listener.ora file appears to be only configured for the old IP
> address just as it always had been (actually the old fully qualified
> hostname to be completely accurate). I was going to configure it to listen
> to both addresses during the transition period, but that change was removed
> by someone else after I had it working (they panicked when the monitoring
> utilities showed things down and were mucking around after me), which is why
> I was amazed everything still worked.
>
> As for the client config not taking, that definitely isn't the case. The
> clients are Win98 PCs and the fully qualified hostname was certainly changed
> from the old to the new in their tnsnames.ora files so they are definitely
> resolving to the new address on that end.
>
> Will check out netstat tomorrow to see what that shows and get a copy of the
> listener.ora file for you so you can maybe spot something I'm missing that
> explains this.

-- 
Jeff Traigle
traigle_at_si.umich.edu
http://www-personal.si.umich.edu/~traigle/

Received on Mon Jan 20 2003 - 14:44:18 CST