How to request an (always free!) Oracle APEX Cloud Database

Last year Oracle released its “Oracle Cloud Free Tier” program. This means that you can get your own Oracle cloud database for free – and what’s more, it’s *always* free, so the offer will not expire after a year and you are charged nothing except if you start using one of the paid options. In this blog post I will explain how you can request a free Oracle cloud database and install APEX on top of it. Please note that you need to enter a valid credit card during the sign-in process (a very small amount will be withdrawn and refunded, but nothing will be charged as long as you stay within the capacity limits of the free program).

At the time of writing of this blog post, the following is included in the free cloud offering:

• Databases
  • Autonomous Transaction Processing or Autonomous Data Warehouse. 2 databases total, each with 1 OCPU and 20 GB storage.
• Storage
  • 2 Block Volumes, 100 GB total. 10 GB Object Storage. 10 GB Archive Storage.
• Compute
  • 2 virtual machines with 1/8 OCPU and 1 GB memory each.
• Additional Services
  • Load Balancer: 1 instance, 10 Mbps bandwidth. Monitoring: 500 million ingestion datapoints, 1 billion retrieval datapoints. Notifications: 1 million sent through HTTPS per month, 1,000 sent through email per month. Outbound Data Transfer: 10 TB per month.

New accounts also have access to a 30-day trial period for an expanded set of services.

Step 1: Oracle Cloud Account Signup

• click on “Start for free” on the intro page https://www.oracle.com/cloud/free/
• Enter your Country/Territory and e-mail address, read the Terms of Use and click Next.
• Select “Personal Use” as Account Type and choose a unique Cloud Account Name (this will be used in service URL’s). Now for the Home Region you need to select a region where the “Always Free Eligible” option is available, I selected Germany Central (Frankfurt) because that’s the closest one to my own region (Belgium).

• Fill in your name and address details, and a valid mobile phone number. Click on “Next: Verify Mobile Number“.
• You should now receive a text message with a verification code. Enter the verification code in the Code text field (note that you only have two minutes for this) and click on “Verify Code“.
• In the next screen, enter a strong password and click on “Next: Payment Information“.
• You will get a message before entering the credit card details:
• Enter your credit card details and click on Finish.
• Check the checkbox to agree with the Terms and Conditions, and click on “Complete Sign-Up“. Your account will now be created.
• You will now receiver a confirmation e-mail. Click on “Sign In To Oracle Cloud” in the e-mail, or go to https://www.oracle.com/cloud/free/ and click on “Sign In To Oracle Cloud“.
• For the Cloud Account Name, enter the unique Cloud Account Name that you entered during the registration process (see above). Enter the strong password (see above) when it is requested.
• You should now see the Oracle Cloud home page. Well done! :-)

Step 2: Create a free Oracle ATP Database

ATP stands for “Autonomous Transaction Processing“. It’s Oracle’s self-driving, self-repairing and self-securing Cloud Database, based on Oracle version 18c (at the time of writing this blog post).

Now let’s create our first free ATP database:

• Go to https://www.oracle.com/cloud/free/ and sign in with your Cloud Account Name.
• Click on “Create an ATP database“.
• Fill in the following information:
  • Compartment: leave as is.
  • Display name: a user-friendly name, for example “My first ATP database”.
  • Database name: for example, “MATTHIASDB”.
  • Workload type: Transaction Processing
  • Deployment type: Shared Infrastructure
  • Configure the database: make sure to leave the “Always Free” option to ON, so that only free options can be selected.
  • Choose database version: 18c
  • OCPU Count should be 1, Storage (TB) should be 0.02 (so 20 GB), and Auto scaling OFF.
  • Fill in a password for the ADMIN database account.
  • Choose network access: allow secure access from everywhere
  • License type: License Included
  • Click on “Create Autonomous Database”.
  • You will now come to an overview screen where you will see the message “ATP Provisioning…”. This means that the creation of the database is in progress.
  • After a while (it took only a couple of minutes when I tried) it should turn to green, your free ATP database is now ready!

Step 3: Use SQL Developer Web to connect to your free Oracle ATP Database

SQL Developer Web is the cloud version of the SQL Developer desktop application (which is also free, btw). You can use it to connect to your ATP database and browse database objects or run SQL queries.

• In the list of Autonomous Databases, click on the display name of your ATP database.
• Now click on the Tools tab (next to Autonomous Database Information).
• Now click on “Open SQL Developer Web”.
• Log in with user ADMIN and the password you created earlier (see above). The SQL Developer Web environment should now load into your web browser.
• Now try to run a query, for example put “select * from v$version” in the Worksheet window and click on the green arrow. You should see some output in the Query Result window:
• You can also browse database object using the Navigator window on the left side.

Please note that SQL Developer Web is a very basic tool compared to the desktop version of SQL Developer (which already exists for a very long time). It’s also possible to connect the desktop version of SQL Developer to your ATP Cloud Database, see for example here: https://dgielis.blogspot.com/2019/09/free-oracle-cloud-3-connecting-with-sql.html

Step 4: Access APEX in your free Oracle ATP Database

APEX (Oracle Application Express) is a low-code, web-based development platform that is included with Oracle ATP. The good news is that it’s already pre-installed, so when you have your ATP database ready, no additional steps are needed to install or configure APEX. At the time of writing, the version of APEX that is included with Oracle ATP is APEX 19.2.

• In the list of Autonomous Databases, click on the display name of your ATP database.
• Now click on the Tools tab (next to Autonomous Database Information).
• Click on “Open APEX“.
• Log in with the password of the ADMIN database account (see above).
• Click on “Create Workspace” to create your first APEX Workspace. In this example, I entered MATTHIAS as database user and MY_WORKSPACE as Workspace name.
• Now in the Admin menu (top right of the page), click on “Sign Out“.
• Click on “Return to Sign In Page” and enter the Workspace name, user name and password that you just created.
• Now click on “App Gallery”, “Sample Interactive Grids” and “Install App” to install the Sample Interactive Grids application in your workspace.
• If the installation went fine, click on App Builder and then on the small arrow next to the Sample Interactive Grids application to run it. Log in with the Workspace user that you created earlier (in my case, user MATTHIAS).
• That’s it! You have now successfully installed your first APEX application in your free Oracle ATP Database!
•  The cool thing is that you now use this URL from everywhere, so you just created your first public APEX 19.2 application on Oracle Cloud!

In a future blog post I will explain some more things that you can do with your free Oracle Cloud account.

HTH

Matthias

Oracle REST Data Services (ORDS) 19.1 now supports installation using a regular DBA account

Previous versions of Oracle REST Data Services (ORDS) required access to the “SYS as SYSDBA account” to allow the installation of the schemas ORDS_METADATA and ORDS_PUBLIC_USER. This was problematic on environments where access to the “SYS as SYSDBA” account is restricted or even impossible, for example when using Oracle RDS on Amazon Web Services (where you have to configure/install ORDS on a separate EC2 server).

The recently released ORDS version 19.1.0 has finally changed this requirement: it is now possible to use a “regular” account with DBA privileges to install all the required ORDS schemas:

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.1/aelig/installing-REST-data-services.html#GUID-52E9155F-B36D-4F40-B0C0-AA7311159828

I have not tried it myself, but this is excellent news for those that want to use recent versions of APEX and ORDS on Oracle AWS – RDS with all the features enabled.

Has anyone tried this?

Matthias

 

Shell script to export all APEX applications as SQL scripts

Environment: Oracle 11gR2 EE, Application Express 4.2.2, Oracle Linux 6.4 64-bit, Java JDK 7u21

The APEX software contains a Java class which can be used to script or schedule the export of one or more APEX applications. I will show you in this post how to do this.

First, I unzipped the APEX software to /u01/app/apex. The Java class file is called “APEXExport.class” and can be found in the folder /u01/app/apex/utilities/oracle/apex.

You also need a Java JDK. I installed Java 7 JDK (update 21) under /u01/app/java/java7.

You might need to unlock (and set a password) for the APEX schema owner (in my case, APEX_040200). I’m not sure if it’s possible to export the applications with another user.

This is how my script looks like:

#!/bin/sh
# script to export all ApEx applications as sql scripts

CLASSPATH=$CLASSPATH:/u01/app/oracle/product/11.2.0/db_1/oui/jlib/classes12.jar; export CLASSPATH
BACKUPDIR=/u05/apex_backup

cd /u01/app/apex/utilities

/u01/app/java/java7/bin/java oracle.apex.APEXExport -db localhost:1521:ora11g01 -user apex_040200 -password <password> -instance

mv f*.sql $BACKUPDIR

if [ -f $BACKUPDIR/apex_export.log ]
then
    cat $BACKUPDIR/apex_export.log|mail -s "APEX export - `hostname -s`" matthiash@mydomain.com
fi

You need to include the file classes12.jar in your CLASSPATH or the JDBC connection won’t work.

The -instance setting will export all your applications. To limit the number of applications, you can also use -workspaceid or -applicationid. Check the readme.txt file under the utilities folder for more information.

To schedule the script with cron, I added the following line to my crontab file:

00 22 * * * /home/oracle/scripts/apex_export.sh 1>/u05/apex_backup/apex_export.log 2>&1

This will execute the script each night at 22:00 and mail the log file to the mail address inside the script.

Happy scripting! :-)

Matthias

Getting APEX version number from data dictionary

Environment: Oracle 10gR2, Oracle 11gR2, APEX 4.2.2, APEX 5.1.4, APEX 19.2

If you don’t have access to the development environment, and you need to find the version of APEX installed into your system, there are several ways to do that:

1) Query APEX_RELEASE

APEX_RELEASE is a public view which is present in each APEX schema.

To query the current enabled version:

SELECT VERSION_NO FROM APEX_RELEASE;

4.2.2.00.11

To query older installed versions of APEX:

SELECT VERSION_NO FROM APEX_040100.APEX_RELEASE;

4.1.1.00.23

 

2) Query DBA_REGISTRY

The DBA_REGISTRY keeps track of all components installed into the database.

SELECT VERSION FROM DBA_REGISTRY WHERE COMP_NAME = 'Oracle Application Express';

4.2.2.00.11

 

HTH,

Matthias

HTTP 404/403 executing APEX procedure from URL with Oracle 11g XE

Environment: Oracle Database 11g Express Edition with APEX 4.1, Windows 7 64-bit

I recently helped migrating an APEX application from Oracle 10g EE with GlassFish to a laptop which had Oracle 11g Express Edition running. There were no real issues, except for the fact that procedures called directly from the URL (for example to download documents to the browser) didn’t work. The solution was to first grant EXECUTE rights on these procedures to the user ANONYMOUS, and second to modify the APEX function wwv_flow_epg_include_mod_local.

I will simulate this with an example.

This is the code for my procedure:

CREATE OR REPLACE PROCEDURE MATTHIASH.download_tab_delimited IS
    l_mime_type VARCHAR2(32767) := 'text/plain';
    l_lob_length  NUMBER;
    l_file_name VARCHAR2(32767) := 'tab_delimited.txt';
    l_lob_loc  BLOB;
    l_sql VARCHAR2(32767);
    BEGIN
        l_sql := 'select UTL_RAW.cast_to_raw(''1''||chr(9)||''2''||chr(9)||''3'') from dual';
        execute immediate l_sql into l_lob_loc;
        
        l_lob_length := DBMS_LOB.GETLENGTH(l_lob_loc);
        
        owa_util.mime_header(l_mime_type,FALSE);
        htp.p('Content-length: ' || l_lob_length);
        htp.p('Content-Disposition:  attachment; filename="'||l_file_name|| '"');      
        owa_util.http_header_close;
        wpg_docload.download_file(l_lob_loc);
                
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       htp.p(substr(dbms_utility.format_error_stack()||dbms_utility.format_error_backtrace(), 1 ,4000));
     WHEN OTHERS THEN
       htp.p(substr(dbms_utility.format_error_stack()||dbms_utility.format_error_backtrace(), 1 ,4000));
END;
/

This simple procedure creates a tab delimited text file and presents it to the browser.
I now try to call the procedure by browsing to http://localhost:8080/apex/MATTHIASH.DOWNLOAD_TAB_DELIMITED. The result is a HTTP 404 error (I’m using Google Chrome as web browser):

Not found

The requested URL /apex/MATTHIASH.DOWNLOAD_TAB_DELIMITED was not found on this server
 

To solve this, you need to grant EXECUTE rights to the user ANONYMOUS on this procedure:

grant execute on MATTHIASH.DOWNLOAD_TAB_DELIMITED to ANONYMOUS;

Now try to call this procedure again. You should get a HTTP 403 error:

Forbidden

The requested operation is not allowed

The solution to this is to allow the Embedded PL/SQL Gateway (EPG) to access this procedure. You do this by modifying the APEX function wwv_flow_epg_include_mod_local (which in my case resides in the APEX_040100 schema):

CREATE OR REPLACE function APEX_040100.wwv_flow_epg_include_mod_local(
    procedure_name in varchar2)
return boolean
is
begin
    -- remove this statement when you modify this function
    --
    -- Administrator note: the procedure_name input parameter may be in the format:
    --
    --    procedure
    --    schema.procedure
    --    package.procedure
    --    schema.package.procedure
    --
    -- If the expected input parameter is a procedure name only, the IN list code shown below
    -- can be modified to itemize the expected procedure names. Otherwise you must parse the
    -- procedure_name parameter and replace the simple code below with code that will evaluate
    -- all of the cases listed above.
    --
    if upper(procedure_name) in (
          'MATTHIASH.DOWNLOAD_TAB_DELIMITED') then
        return TRUE;
    else
        return FALSE;
    end if;
end wwv_flow_epg_include_mod_local;
/

After this you should be able to call your procedure from the web browser!

HTH,
Matthias

Upgrade APEX 4.1.1 to 4.2.1 on Oracle Linux with GlassFish

Environment: Oracle Linux 6.3, Oracle database 11.2.0.3.0 EE, APEX 4.1.1.00.23, GlassFish 3.1.2.2

This blog post explains how you can upgrade your Application Express installation from version 4.1.1 to version 4.2.1, with the APEX Listener deployed under GlassFish.

Steps:

1) download the software

Update: APEX 4.2.1 is no longer available from the Oracle website. Please use a more recent version of APEX (5.1, 18.x, 19.x). You can download the software here.

2) copy and extract the software

Now copy and extract the apex_4.2.1.zip file to your server. I use “unzip” to unzip the zip file. This will create a folder named “apex”. I usually keep this folder on my server in a permanent location, for example /u01/app/apex.

3) check the tablespace requirements

You need about 275MB of free space in the APEX tablespace (mine is called “APEX”) and 100MB of free space in the SYSTEM tablespace.

4) stop the APEX Listener

Before you start with the upgrade, you need to stop the APEX Listener to make sure no one is using the software during the upgrade.

To stop the APEX Listener under GlassFish, you can do it as follows:

– connect to the GlassFish administration console (by default this is port 4848 using SSL).

– log in with the “admin” user.

– click on Standalone Instances => Applications.

– select the “apex” application and select “Disable” from the “More Actions” drop down list.

The APEX Listener should now be stopped on your GlassFish instance.

5) start the upgrade

To upgrade the APEX software, you need to run the “apexins.sql” sql script from within the “apex” folder that you extracted above. You need to connect as SYS as SYSDBA with the oracle software owner (mine is called “oracle”). Make sure the “apex” folder is writeable!

apexins.sql needs 4 parameters: the name of the tablespace for the APEX user (in my case: APEX), the name of the tablespace for the FLOWS_FILES user (in my case: APEX), the name of the TEMP tablespace (in my case: TEMP) and the name of the virtual image directory (which is “/i/”).

$ su - oracle
$ cd /u01/app/apex
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 3 11:21:39 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> @apexins.sql APEX APEX TEMP /i/

The upgrade will now start. This will create a new schema called “APEX_040200”. The upgrade can take some time, depending on the speed of your server. On my server (with fairly recent hardware), it took about 17 minutes. After the upgrade, check the installation log file (in the same folder where you exectuted the apexins.sql file) to make sure no errors were encountered during the process.

6) verify the installation

I always verify my APEX installation after a upgrade by querying dba_registry:

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 3 12:06:21 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> select status from dba_registry where comp_id='APEX';

STATUS
-------------------------------------------------------
VALID

7) copy the images

Next, copy the new and updated images from your installation folder to the GlassFish docroot (you might take a backup folder of the original docroot/i folder just in case):

$ cp -rf /u01/app/apex/images/* /u01/app/glassfish/glassfish/nodes/localhost-domain1/node01/docroot/i/

8) start the APEX Listener

Finally, start the APEX Listener again (see under step 4) and check if all your applications work fine :-) You should now have a nicely upgraded APEX 4.2.1.00.08 installation!

HTH,

Matthias

 

Update:

If you are accessing network services from withing APEX, like LDAP authentication, print servers and such, you will need to grant the new APEX_040200 user network access or it won’t work. This is documented in the installation guide and is only needed for Oracle 11g. You can grant network access by running the following PL/SQL script with user SYS as SYSDBA:

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040200', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

 

APEX 4.2.1 and APEX Listener 2.0 released!

First of all: a happy New Year to all my blog readers! I wish you an exciting 2013 with lots of interesting projects, no budget restraints and no unexpected downtime ;-)

When I returned from the Christmas holidays, I noticed that both Application Express 4.2.1 and the APEX Listener 2.0 have been released!

APEX 4.2.1 is actually the first patch set release for APEX 4.2. You can download the full release here (for new installations or upgrades) or, if you have a support contract, you can download the patch only (with number 14732511) here (for upgrades from version 4.2). There are a lot of bugs that have been fixed with this patch set (check for a full list here),  and also some new features have been introduced, for example the inclusion of AnyChart version 6 (which has even more HTML5 support). I’m happy with this patch set release, since this means it’s time to upgrade for us. We had some issues in the past after upgrading to a minor APEX release, so I learned to have patience and wait for the first patch set ;-)

Application Express Listener 2.0 was available as a Early Adaptor version for a while, but now the first production release is available. There are a lot of new features, like the ability to define multiple database connections, and more support for RESTful services. There are now also 2 ways to configure it: using SQL Developer (you need at least version 3.2.2) and using a new command-line interface. The web interface however has been removed, which I find a bit sad because I liked it :( We are using the APEX listener deployed to GlassFish, and I’m not sure if anything has changed in that matter. This is something I need to look into. I also wonder how you would upgrade from a previous version of the APEX Listener to this one. I suppose this involves some redeployment, and so also some (planned) downtime.

Enjoy,

Matthias

Upgrading APEX Listener from 1.1.3 to 1.1.4 on GlassFish 3.1.2.2

Environment: APEX Listener 1.1.4, GlassFish 3.1.2.2 OSE Web Profile, Oracle Linux 6.2 64-bit

In this post I will explain how you can upgrade the APEX Listener from version 1.1.3 to version 1.1.4 deployed under GlassFish.

Note: unless you are running a GlassFish cluster, there is no way to upgrade the APEX Listener without interruptions. However, if you plan things well, you can minimize downtime to only a few minutes.

Step 1: download and unzip the new software

Version 1.1.4 of the APEX Listener can be downloaded here. There are no separate 32-bit/64-bit versions or different versions for Windows/Linux. Put the zip file somewhere on the file system of your server and unzip it. Inside the zip file you will find the documentation files and the apex.war file, which contains the source code.

Step 2: modify the web.xml file inside apex.war

Before deploying the new APEX Listener, we will first modify the location of the config file inside the apex.war file. By default, the config file is stored in /tmp, but since this is a temporary file system, you might lose this file after a server reboot and APEX will stop working.

To modify the location of the config file, we need to extract the apex.war file, modify the web.xml file and finally recreate the apex.war file.

First, extract the apex.war file into a new folder by using the Java “jar” utility:

oracle@ol6ora11g02 /u01/app/apex_listener/1.1.4/modified>/u01/app/java/java6/bin/jar xf ../apex.war
oracle@ol6ora11g02 /u01/app/apex_listener/1.1.4/modified>ls -la
total 32
drwxr-xr-x 8 oracle oinstall 4096 Aug 20 14:45 .
drwxr-xr-x 5 oracle oinstall 4096 Aug 20 14:44 ..
drwxr-xr-x 2 oracle oinstall 4096 Jun 15 22:04 api
drwxr-xr-x 2 oracle oinstall 4096 Jul 13 00:14 ____embedded
drwxr-xr-x 2 oracle oinstall 4096 Jul 13 00:19 META-INF
drwxr-xr-x 2 oracle oinstall 4096 Jun 15 22:02 resourceTemplates
drwxr-xr-x 5 oracle oinstall 4096 Jun 15 22:03 scripts
drwxr-xr-x 3 oracle oinstall 4096 Jul 13 00:19 WEB-INF

Next, use a text editor to uncomment and modify the “config.dir” param-value inside the WEB-INF/web.xml file. This is how the first part of my web.xml file looks like:

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4"
        xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
        <display-name>ApexMod</display-name>
        <context-param>
                <param-name>config.dir</param-name>
                <param-value>/home/oracle/apex_listener/config</param-value>
        </context-param>
        <context-param>
                <param-name>version</param-name>
                <param-value>1.1.4.195.00.12</param-value>
        </context-param>

Note that my APEX Listener config file is stored under /home/oracle/apex_listener/config/apex.

Now let’s recreate the apex.war file again using the “jar” utility:

oracle@ol6ora11g02 /u01/app/apex_listener/1.1.4/modified>/u01/app/java/java6/bin/jar cf ../apex.war .
oracle@ol6ora11g02 /u01/app/apex_listener/1.1.4/modified>cd ..
oracle@ol6ora11g02 /u01/app/apex_listener/1.1.4>ls -la
total 20140
drwxr-xr-x 5 oracle oinstall     4096 Aug 20 14:44 .
drwxr-xr-x 6 oracle oinstall     4096 Aug 17 11:22 ..
-rwxr-x--- 1 oracle oinstall 10719937 Aug 17 11:26 apex_listener.1.1.4.195.00.12.zip
-rw-r--r-- 1 oracle oinstall  9839841 Aug 20 15:09 apex.war
drwxr-xr-x 5 oracle oinstall     4096 Jul 13 00:20 docs
-rw-r--r-- 1 oracle oinstall     2906 Jul 13 00:20 index.htm
drwxr-xr-x 4 oracle oinstall     4096 Jul 13 00:20 javadoc
-rw-r--r-- 1 oracle oinstall    24890 Jul 13 00:20 license.html
drwxr-xr-x 8 oracle oinstall     4096 Aug 20 14:45 modified
-rw-r--r-- 1 oracle oinstall     3242 Jul 13 00:20 readme.html
oracle@ol6ora11g02 /u01/app/apex_listener/1.1.4>rm -rf modified

Step 3: redeploy the apex.war file under GlassFish

Start up the GlassFish admin console (by default it runs under port 4848), click on “Applications” and then click on “Redeploy” next to the “apex” application (or whatever you called your APEX Listener deployment).

 

In the next screen, browse to the new “apex.war” file and click on “OK”. This will remove the old APEX Listener and deploy the new version. If everything went fine, click on “Launch” to check if APEX is working fine again. In case of errors, you should check the server.log file for the GlassFish instance where you deployed the APEX Listener.

That’s it! Good luck :-)

 

Matthias

Removal of APEX Listener cache folder causes hanging of APEX

Environment: APEX 4.1, GlassFish 3.1.1/3.1.2, APEX Listener 1.1.3, Oracle Linux 6.2 64-bit

In the past couple of months, we had several lock ups of our APEX environments. This all started after we moved to GlassFish with the APEX Listener. The symptoms were always the same: all database connections in the connection pool were in use and hanging, and we had to restart the GlassFish instance to release them.

Today, I was finally able to reproduce this problem at will and I found a solution!

The problem is caused by the removal of the file system folder that the APEX Listener uses to cache files.

We had the file caching enabled to increase the performance of a APEX application which downloads images from the database through a procedure.

This is how our caching is configured:

As you see, the caching folder is set to /tmp/apex/cache. I believe this is the default configuration.

Now, for some reasons, Linux sometimes removes all folders from the /tmp folder. And this not only happens after a server reboot! There must be some process scheduled to do this, but I haven’t found yet which one. When we had a recent hang of APEX I noticed some broken images in the applications. I checked the file system and behold, the file cache folder was gone! A restart of the GlassFish instance (and so also the APEX Listener) recreated the caching folder.

Maybe this issue has been fixed in the newer version 1.1.4 of the APEX Listener, I didn’t try it out yet…

In the mean time, I found a permanent solution for the problem by setting the Cache File Location to a folder outside the /tmp file system, for example /home/oracle/apex/cache.

 

Matthias

GlassFish Server release 3.1.2.2 fixes APEX file download bug

A new microrelease version of GlassFish Server has been released: version 3.1.2.2. According to the release notes, the following issues were fixed:

• NetBeans bug 211962
• JAX-WS issue JAX_WS-1059
• GlassFish Server issue GLASSFISH-18446

I didn’t find the time to try out this new release yet, but according to the bug issue list, it also contains a fix for GlassFish issue 18444, something that I blogged about in one of my previous posts. Since this bug breaks the file downloads in APEX, it’s a critical issue, so it’s strange they didn’t mention this in the release notes.

 

Matthias