Tuesday, November 13, 2018

Multitenant ORDS with Local APEX in PDBs

Oracle REST Data Services (ORDS) has a great feature that allows you to address PDBs that have APEX in them, just by using the name of the PDB in the URL, right after the ORDS context root. So if I have two PDBs, named apex181 and apex182, I can address them with URLs similar to the following:




The names of my PDBs imply that I have two different versions of APEX installed in the CDB. This is only possible if APEX is installed locally in each PDB and not in CDB$ROOT. To allow for ORDS to be multitenant while APEX is local to the PDBs, you must pre-create APEX_PUBLIC_USER, APEX_LISTENER, and APEX_REST_PUBLIC_USER database users as common users in the CDB. You can do this easily with the $ORACLE_HOME/rdbms/admin/catcon.pl perl utility script for running Oracle supplied scripts.

Create APEX Users Common

$ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -b create_apex_pub -- --x'grant create session to apex_public_user identified by oracle'

$ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -b create_apex_list -- --x'grant create session to apex_listener identified by oracle'

$ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -b create_apex_rest_pub -- --x'grant create session to apex_rest_public_user identified by oracle'

Obviously you will want to come up with a better password than the ones in the example above. Also note that I am using a funny syntax to create the users, using the grant statement. This allows me to create the user and do the grant in one statement.

Install ORDS Multitenant

Follow the advanced installation instructions for installing ORDS, making sure you specify the service name of the CDB.

java -jar ords.war install advanced

Make All PDBs Addressable 

To make all PDBs, including newly created PDBs addressable through a URL, you add a parameter to the defaults.xml file which can be found in the ords directory in the location you specified during installation. The value of the parameter should match the database parameter DB_DOMAIN.

SQL> show parameter DB_DOMAIN

------------------------------------ ----------- ------------------------------
db_domain      string

In my case, there is no DB_DOMAIN value, so in defaults.xml, I made the following entry:

Otherwise, enter the value you see for parameter DB_DOMAIN and make sure you precede it with a dot, or period (.). 

Install APEX Locally in each PDB

The last step is to install APEX locally in the PDB. After you install APEX, you can access it through ORDS, by appending the name of the PDB name, after the ords context root in the URL.