Does oracle_fdw support LOAD_BALANCE or FAILOVER

[mellerbeck]

MYDB=(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS=(PROTOCOL=tcp)(HOST=hostname1-vip.domain.com)(PORT=1571))(ADDRESS=(PROTOCOL=tcp)(HOST=hostname2-vip.domain.com)(PORT=1571))(ADDRESS=(PROTOCOL=tcp)(HOST=hostname3-vip.domain.com)(PORT=1571)))(CONNECT_DATA=(SERVICE_NAME=ServiceName)))

[laurenz]

Is there any special support needed? If the Oracle client can understand that connection string, you can use it with oracle_fdw.

I am under the impression that the only thing client code needs to do in the case of a failover is to reconnect when it loses the database connection. oracle_fdw does that:

• At the beginning of a statement, it tests the OCI_ATTR_SERVER_STATUS of the connection (which, according to the documentation, performs "a light weight connection health check"). If that fails, it will silently try to reconnect.
• Then, oracle_fdw starts a database transaction, and if there are certain error messages that indicate connection loss (ORA-01012, ORA-00028, ORA-03113 or ORA-03135), it will also silently try to reconnect.

That list of error messages may not be exhaustive; if you know any other error messages that mean that oracle_fdw should try to reconnect, please let me know.

[philflorent]

Hi, Testing OCI_ATTR_SERVER_STATUS and ORA- network related issues is enough to achieve reconnection, no matter which Oracle (or not) server-side and network redundancy features are used. If you want to know if RAC related features e.g TAF (Transparent Application Failover) or FCF (fast connection failover) are specifically implemented client-side in oracle_fdw it's NO afaik but Laurenz can tell more about that. If RAC (real application cluster) is in use this tnsnames.ora entry seems problematic. You won't achieve any real high availability with that. You need SCAN (single client access name) and I see 3 virtual IPs instead. It's much easier to define the foreign server via "create server" with a SCAN.
That said anyway RAC is not reliable for various reasons and the main one is that Oracle Clusterware itself is a SPOF but it's another debate. Hopefully oracle_fdw will remain simple. If connection is KO, it tries to reconnect and if the service is up it works. Best regards, Phil

[laurenz]

Thanks, @philflorent . Yes, there is no TAF or FCF in oracle_fdw.

[mellerbeck]

Thanks, @philflorent it sounds like we are using tnsnames with 3 virtual IP's. It looks like SCAN would be the ideal way, then oracle_fdw would just connects to what looks like a single server to it.

[philflorent]

Yes and it's not oracle-fdw specific. SCAN has been available and highly recommended since 2009 (Oracle 11gR2) to ease addition/removal of nodes in your architecture. Notice SCAN configuration is normally part of the RAC initial installation and it's almost mandatory in recent versions.

[laurenz]

Can we close this issue? Are your questions answered?

[mellerbeck]

Yes! Thank you very much