0

I am not a DB or even Oracle expert, but I wanted to understand when and why Oracle returns a different non-VIP address to the client, assuming this is correct behaviour?

listener.ora

LISTENER_DB-A =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB-A-VIP)(PORT = 1521)(IP = FIRST))
       # this is the VIP 10.0.0.15
    )
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.10)(PORT = 1521)(IP = FIRST))
    )
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
  )
)

Here, after connecting to the IP address 10.0.0.15, the Oracle server returns the 10.0.0.10 address to the client. This is my network sniff of the said behaviour:

Client to Oracle

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.15)(PORT=1521)))
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)
(SERVICE_NAME=my_db)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=5)(DELAY=5))))

Oracle to Client

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DB-A))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.10)(PORT=1521)))

I know that this behaviour is not mandatory, as I've seen Oracle server respond without said redirection. What is the reason the server is acting this way? Thanks in advance!

  • Check the value of local_listener parameter and corresponding entry in tnsnames.ora file. – Mindaugas Riauba Feb 4 '15 at 12:50
  • local_listener seems to hold the non-VIP hostname. I've looked inside the init.ora files where I assumed the parameter is to be set, but it isn't set there. Is the parameter dynamically generated by Oracle globally for the node? – CyberOptic Feb 4 '15 at 13:09
  • It is generated for each instance. While it is usually not a problem in single node configuration you have to configure it for RAC. That is point it to the proper VIP. – Mindaugas Riauba Feb 4 '15 at 22:19
0

It is even more complex when it comes to 11gR2 and SCAN listeners. But let's say that reason behind is load balancing. The listener will redirect client onto least loaded node.

Another reason can also be workload balancing. Imagine that you have two services (SERVICE_NAME) on our cluster. One called LOADERS and is used by processes which push data into the database. The other is called WEB and is used by application, which fetch data from database. Each service is located on different node. Then this redirect will guarantee that the database is used effectively and all the loaders use the same cluster node even if one of the nodes fails.

BTW: I think that the redirect message does not contain an IP address, but a hostname. Therefore there should not be any NAT between DB client and DB server and also they should use the same DNS server.

  • Can load balancing in oracle still happen if both IPs are on the same interface of the same node? And isn't this against the reason why VIP is necessary in the first place? – CyberOptic Feb 4 '15 at 12:33
  • The load-balancing occurs "only" when you create a new db session. This is why listeners send the redirect. The only-the-fly load-balancing (FAN) works this way. The DB server sends a message to the client - it's just an "offer". When client supports FAN, it closes current TCP connection and then opens a session to the other DB node. There is no way how to transfer a TCP session from one node onto the other node - even if the VIP moves. This would require some tweaking inside Linux kernel. – ibre5041 Feb 4 '15 at 13:12
  • Ah, now I understand what you are meaning, but I don't understand why Oracle suggests a different IP on the same host to the client? – CyberOptic Feb 4 '15 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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