Thursday, July 19, 2018

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.

Friday, March 16, 2018

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.