This is probably a short lived blog post. If you are using ORDS 22.4 (the current version as of this writing) you may encounter problems with the TNS-support for setting up connections.

I believe they manifest when your TNS-entry has multiple hosts in them as you would if you have a setup with one or more standby databases. In such cases it looks something like this.

SVC =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = svc)
    )
  )

I believe this is due to the parsing of the file not taking this kind of setup into account. When this happens one can only opt for the basic setup. That only allows for a setup to one specific host. Consider a typical pool.xml with basic connection set up.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Mon Mar 06 08:58:12 UTC 2023</comment>
<entry key="db.connectionType">basic</entry>
<entry key="db.hostname">host1</entry>
<entry key="db.port">1521</entry>
<entry key="db.serviceNameSuffix"></entry>
<entry key="db.servicename">svc</entry>
<entry key="db.username">ORDS_PUBLIC_USER</entry>
<entry key="feature.sdw">true</entry>
<entry key="plsql.gateway.mode">proxied</entry>
<entry key="restEnabledSql.active">true</entry>
<entry key="security.requestValidationFunction">ords_util.authorize_plsql_gateway</entry>
</properties>

Line 6 and 7 are the critical ones. They show where to find the listener. Should this database be switched over to host2, this will no longer work. Every attempt to connect will now fail.

To manage this you rename pool.xml to h1 (for host1) or some such. Then you make a copy of it to say h2. Now you can create a link with the name pool.xml that points to h1. Now it works when host1 is where your primary database is running and if it is switched to host 2 you just update the link to point to h2. that is all you need to for ORDS to go to host2 for it’s connections.

A script can be set up that uses the H1 and H2 files to find where they look for their databases and then use that to log in and check where your database is located and automatically switch the link if the database has switched. I do that for a set of CDBs each with their own connection pool so if a switch occurs then ORDS gets an updated link shortly thereafter and there is no manual intervention needed.

You will of course rather use proper TNS for it, so do verify that you have this issue before you implement this workaround.

Should you need to do this and want to automate it, get in touch with me and I’ll share the script with you. That is if I have not gotten around to blog about it, even though it’s not going to be needed much longer (I hope), there are som interesting things that can be done with parsing the pool.xml from a script.