0

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.

2
  • Is the target database an Oracle RAC (clustered) instance?
    – pmdba
    Jan 28, 2023 at 0:47
  • Yes. It took me a minute to figure out but if dbms_utility.is_cluster_database then gives True. Jan 28, 2023 at 0:56

1 Answer 1

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.

1
  • Awesome. This sounds like what we have. I have three domain names for the cluster and each domain name has three addresses. I'll take this up with the DBA guys and when it works, I'll be back to mark the answer accepted. Thanks. Jan 28, 2023 at 2:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.