Oracle REST Data Services (ORDS) offers a seamless installation experience in the root container. By making a simple configuration adjustment, all pluggable databases can effortlessly be addressed, empowering users with enhanced functionality and convenience.
Installing ORDS in the root container
At one of my customers, Oracle Application Express (APEX) has been successfully deployed with Oracle REST Data Services (ORDS) running on Tomcat. However, ORDS was primarily utilized as a proxy for APEX in the database, with limited exploration of its broader capabilities. Typically, ORDS was installed within the pluggable database (PDB) as a natural choice.
In a recent project, I aimed to leverage ORDS extensively and take advantage of one of its bundled REST APIs known as the Pluggable Database Lifecycle Management API. To employ this API effectively, it was necessary to install ORDS in the root container, enabling the creation and deletion of pluggable databases.
However, one crucial caveat surfaced during the process: ORDS doesn’t permit installation in the root container (CDB$ROOT) if it already exists in one or more PDBs. In such cases, uninstallation from all other containers is required before proceeding.
Important note: Uninstalling ORDS will remove any configuration for ORDS-enabled schemas and objects. It is imperative to create a comprehensive list of these schemas and objects (such as tables, views, functions, procedures, or packages) to ensure their re-enablement after installation. While interactively managing a small number of objects can be done via SQL Developer, it’s advisable to create scripts and store them in your repository for future reference.
Based on my experience, the latest version of ORDS can be used to uninstall older versions. Simply execute the following command from the directory where you’ve extracted the latest version :
bin/ords uninstall
It will ask for the connection details for the PDB, and you have to execute this for each PDB where ORDS is installed.
Now is time to install ORDS into CDB$ROOT. The following example does a silent install:
echo -e "YourSYSpassword\nORDS_PUBLIC_USER_password42" | \
bin/ords --config $HOME/ordsconfig install --admin-user SYS \
--proxy-user --db-hostname your_host --db-port 1521 \
--db-servicename db_service --feature-sdw true --password-stdin
To ensure access to all PDBs, a crucial configuration step involves setting the db.serviceNameSuffix parameter to the same value as the db_domain database parameter. For detailed instructions, please refer to Section 7.3 of the installation guide
.
ords --config $HOME/ordsconfig config set "db.serviceNameSuffix" ".example.com"
Once configured, a PDB named BARDB, for instance, can be accessed through the URL /ords/bardb.
Now you may have many application servers connecting either to the CDB$ROOT or to any of the PDBs. For instance if you install APEX in one PDB and want to deploy ORDS somewhere to route requests to it you will install ORDS as you did before. The only difference is that the password for user ORDS_PUBLIC_USER is the same for all PDBs since it is now a common user.
Postscript
I got some assistance from ChatGPT with parts of this text, mostly to see if it could improve the language. But it always comes up with words like leverage, empower, and imperative, not quite my style. I’ll stick with spelling check for future posts.
I don’t recommend installing ORDS in the root container (CDB$ROOT) unless you are going to use the Pluggable Database Lifecycle Management API that comes with it. At this moment I am not aware of any other benefits. The downside with this installation is that you add a potential dependency between the containers. Testing so far gives me the impression that ORDS is flexible when it comes to versions; I have used lower versions of ORDS connected to an upgraded ORDS in the database.