Where is Oracle TNS redirection configured?

Question

The short version,

I need to connect to an Oracle server and I am getting redirected to an IP address that is blocked by the firewall. What do I need to ask to find out what IP addresses I will be redirected to?

The long version,

I have demonstrated that my Oracle connections are being redirected to another, blocked, IP address and the Oracle administrators are too busy to research this but if I ask to see one of their configuration files, they may cooperate.

Where is the information about where our connections are being redirected to held such that I can ask to see it?

The longer version,

We have a Production system that has firewalls protecting it from everything in sight and we have to connect to an Oracle server. We were told to connect to Oracle with a tnsnames.ora something like this:

THEDATA=(
    description=
    (failover=on)
    (load_balance=off)
    (address_list=
        (address=(protocol=tcp)(host=machine1.ourinteral.com)(port=1521))
        (address=(protocol=tcp)(host=machine2.ourinteral.com)(port=1521))
        (address=(protocol=tcp)(host=machine3.ourinteral.com)(port=1521))
    )
    (connect_data=(service_name=theservice.name)
        (failover_mode=(type=session)(method=basic))
    )
)

I did an nslookup of all the domain names given and requested the firewall be opened up for all the addresses using TCP port 1521. If I use tnsping to the database, it works but if I try to use sqlplus, it fails after three minutes with

ERROR:
ORA-12170: TNS:Connect timeout occurred

Not being one to sit back and watch, I started my Oracle VirtualBox with Linux strace with wireshark to see what was going on. In the network trace, I see a Protocol "TNS" message of type "Response, Redirect" that is followed by a "TNS" message of type "Response, Data" that contains the following (with my formatting and sanitization):

(DESCRIPTION=
    (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=another.ourinternal.com)
        (PORT=1532)))
(DESCRIPTION=
    (failover=on)
    (address=
        (protocol=tcp)
        (host=machine1.ourinternal.com)
        (port=1521))
        (connect_data=
            (service_name=TheServiceName)
            (failover_mode=(type=none))
            (SERVER=dedicated)
            (CID=
                (PROGRAM=sqlplus)
                (HOST=bigdatalite.localdomain)
                (USER=oracle))
            (INSTANCE_NAME=SOMETHING)))

This is followed by a disconnect from the original machine with a new connection to the new machine. For my VM, everything continues as expected but in Production, it fails horribly because it can't get to the second machine since I never heard of it and have not opened the firewall.

I can open the ports to this other machine but how do I know they won't redirect me to yet another machine tomorrow? I have to ask how their system is configured and don't know what to ask for.

Where is the redirection information contained?

Thank you once for the question in all three versions.

Answer 1

RAC presents a set of IP addresses called SCAN addresses that are a set of three virtual IPs for the cluster. These are usually the IP addresses given to users or applications to connect to. Your connection goes to one of them and gets redirected to a second virtual IP allocated to a specific cluster host (each host will have one). Both sets of IP addresses (at least 5 in total) need to be open at the firewall.

Confirm with your DBA team the SCAN addresses and dedicated virtual IP addresses and all related SCAN and local listener ports (sounds like maybe they're not all using 1521) for the cluster and make sure the firewall is open to all of them.