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:

http://localhost:8080/ords/apex181/

and...

http://localhost:8080/ords/apex182/

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

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

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.

Removing Prior Versions of APEX in a PDB

Dimitri Gielis discovered a possible gap in our Application Express Installation Guide about removing prior versions of APEX, when APEX is installed locally in a PDB. If you follow our recommendation to remove the prior version of APEX after a successful upgrade to APEX 18.1, you might run into something similar to the following:

SQL> alter session set container=APEX18;

Session altered.

SQL> SELECT username

  FROM dba_users 

 WHERE (  username LIKE 'FLOWS\_______' ESCAPE '\'

       OR username LIKE 'APEX\_______' ESCAPE '\' )

   AND username NOT IN ( SELECT schema

                           FROM dba_registry

                          WHERE comp_id = 'APEX' );  2    3    4    5    6    7  

USERNAME

--------------------------------------------------------------------------------------------------------------------------------

APEX_050100

SQL> DROP USER APEX_050100 CASCADE;

DROP USER APEX_050100 CASCADE

*

ERROR at line 1:

ORA-28014: cannot drop administrative users

You would encounter this error if you followed section "4.14.3 Removing Schemas from Prior Installations" in the Installation Guide. The section you actually need to follow is "4.14.4 Removing Schemas from Prior Installations in a CDB." Now, you may argue that since APEX is installed locally, you are not installed in a CDB. But you actually are. You just don't have APEX installed in CDB$ROOT, but your database architecture is still a CDB. You need to use catcon.pl to drop the Oracle Maintained APEX_050100 schema like the following example:

$ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex050100 -- --x'drop user APEX_050100 cascade'

The command above though will run in every container, or PDB, in the CDB. In some containers, APEX_050100 will not exist, and you will see an error in the catcon logs. Also, you may only want to drop the APEX_050100 schema from a particular PDB. There is the gap in the documentation, and what we need to add to the next version. In my case, my PDB was called APEX18, so to drop APEX_050100 only in the APEX18 PDB, the command would be:

$ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex050100 -c 'APEX18' -- --x'drop user APEX_050100 cascade'

We will add this example as well as try and make the section title and lead in more clear in the next version of the Installation Guide.

APEX 18.1 New Feature: Logging Web Service Requests

APEX has turned 18 (ish) and EA-2 is now available. In this post, I will introduce a brand new (very small) feature of APEX 18.1.

APEX has had some support for consuming web services even before it was a shipped product (even before it was called HTMLDB). With this release of APEX, Carsten Czarski has made consuming RESTful style web services and the new ORDS REST Enabled SQL feature a breeze. You can read all about this new support in a series of excellent blog posts by Carsten here.

Also introduced in this release is a log of all web service requests made through APEX. Whether they are made through the greatly improved RESTful Web Sources and Remote SQL, the legacy web service REST and SOAP web references, or either the APEX_WEB_SERVICE or APEX_EXEC PL/SQL API, the request is logged. This log can be useful for workspace administrators and developers to get a glimpse of all web service requests being done by the workspace. The view APEX_WEBSERVICE_LOG contains the log information.

Figure 1 - APEX_WEBSERVICE_LOG

As a developer or administrator in a workspace, you can query this view to find the URL, method, request content length, HTTP status code, response content length, the time in seconds between request and response, the date of the request, and the user making the request. To find a description of all the columns in the APEX_WEBSERVICE_LOG view, you can go to the Data Dictionary report in SQL Workshop Utilities.

Figure 2 - Data Dictionary report for APEX_WEBSERVICE_LOG

The logging of all web service requests is even more useful for administrators of the entire APEX instance. If you are running a hosting business, this log can be used for forensics in case any of your tenants used your service as a launch platform for attacks on other systems. There is a handy interactive report on the log available in Instance Administration - Monitor Activity - Web Service Activity Log.

Figure 3 - Web Service Activity Log in Internal Administration

By default, there is a log switch every 14 days, so you will have about 28 days of log. If you need to retain information longer than that (or not as long), you can change the default at Internal Administration - Manage Instance - Manage Log Interval.

Figure 4 - Manage Log Interval in Internal Administration

And finally, also handy for hosting or cloud providers, you can set the maximum number of web service requests that each workspace can do in a twenty four hour period. You set the maximum in Internal Administration - Manage Instance - Security - Workspace Isolation.

Figure 5 - Maximum Web Service Requests in Workspace Isolation

You can also override the instance setting at the workspace level. In internal Administration, go to Existing Workspaces, find the workspace, and click on the workspace name to edit the properties. Set Maximum Web Service Requests under Workspace Isolation.

Figure 6 - Override ride Maximum Web Service Requests for a Workspace

And there you have it, your 18.1 new feature "nugget," logging web service requests. I hope you find it useful.