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:

# 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

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 ]
    cat $BACKUPDIR/apex_export.log|mail -s "APEX export - `hostname -s`"

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/ 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! :-)


Configuration of HugePages for Oracle database on Oracle Linux 6.4

Environment: Oracle database Enterprise Edition, Oracle Linux 6.4 64-bit

HugePages can give a performance boost on Linux systems with large SGA sizes. However, this is not set up by default, and the configuration can sometimes be a bit tricky.

This is how I did it on a system with a 4GB SGA size:

1) Disable Automatic Memory Management (AMM)

AMM is not compatible with HugePages. I disabled it by setting the following memory-related initialization parameters:

ALTER SYSTEM SET memory_max_target=0 SCOPE=SPFILE;




+ restart the instance

2) Calculate the number of HugePages needed

The size of one HugePage can be found as follows:

$ cat /proc/meminfo|grep Hugepagesize
Hugepagesize: 2048 kB

The amount of HugePages that you need can be found with the following script:

# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk {'print $2'}`
# Start from 1 pages to be on the safe side and guarantee 1 free HugePage
# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | awk {'print $5'} | grep "[0-9][0-9]*"`
   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
   if [ $MIN_PG -gt 0 ]; then
      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
# Finish with results
case $KERN in
   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
   '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
# End

$ ./
Recommended setting: vm.nr_hugepages = 2013

3) Set the number of HugePages in /etc/sysctl.conf

Add the following to /etc/sysctl.conf:

vm.nr_hugepages = 2013

4) Set the memory limits for user “oracle”

The memory limits for user “oracle” can be calculated by multiplying the number of HugePages with the Hugepagesize (so, in my case, 2013*2048 = 4122624).

Add this number to /etc/security/limits.conf:

oracle soft memlock 4122624
oracle hard memlock 4122624


By setting the initialization parameter USE_LARGE_PAGES, you can force the instance to only start up when enough HugePages are available.


6) Reboot the system

Now reboot the server. You can check if large pages are being used in 2 ways:

$ cat /proc/meminfo|grep HugePages

AnonHugePages: 237568 kB
HugePages_Total: 2013
HugePages_Free: 1532
HugePages_Rsvd: 1528
HugePages_Surp: 0

Or check the alert.log file for the database instance:

Starting ORACLE instance (normal)
****************** Large Pages Information *****************
Parameter use_large_pages = ONLY

Total Shared Global Region in Large Pages = 4018 MB (100%)

Large Pages used by this instance: 2009 (4018 MB)
Large Pages unused system wide = 4 (8192 KB) (alloc incr 16 MB)
Large Pages configured system wide = 2013 (4026 MB)
Large Page size = 2048 KB

Good luck! :-)



Upgrade APEX 4.1.1 to 4.2.1 on Oracle Linux with GlassFish

Environment: Oracle Linux 6.3, Oracle database EE, APEX, GlassFish

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.


1) download the software

You can download APEX 4.2.1 here. I downloaded the “all languages” zip file. Please note that the documentation is no longer included with the software. You can download the documentation separately here.

2) copy and extract the software

Now copy and extract the 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 Production on Thu Jan 3 11:21:39 2013

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

SQL> connect / as sysdba
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 Production on Thu Jan 3 12:06:21 2013

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

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


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 installation!





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:

-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200 does not have the privilege yet.
'connect') IS NULL THEN
'APEX_040200', TRUE, 'connect');
-- When no ACL has been assigned to '*'.
'ACL that lets power users to connect to everywhere',
'APEX_040200', TRUE, 'connect');

Upgrading Oracle Linux from 6.2 to 6.3

Environment: Oracle Linux 6.2 64-bit, Oracle Linux 6.3 64-bit, Oracle VM VirtualBox 4.1.18

Oracle recently released version 6.3 of their Linux distribution, so I decided to upgrade my 6.2 installation on VirtualBox, which has Oracle 11gR2 and GlassFish running on it.

Some things you need to keep in mind:

– you need root access

– downloading and installing the rpm packages can take a long time

– you need to reboot your server after the upgrade

– for production servers, take a full system backup before starting the upgrade

First, let’s check the version of our current installation:

$ su -
$ cat /etc/oracle-release
Oracle Linux Server release 6.2
$ lsb_release -d
Description:	Oracle Linux Server release 6.2

Next, we need to retrieve the yum repository file. Rename the old one if it already exists.

$ cd /etc/yum.repos.d/
$ mv public-yum-ol6.repo public-yum-ol6.repo.old
$ wget

--2012-08-23 10:43:43--
Connecting to||:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1707 (1.7K) [text/plain]
Saving to: “public-yum-ol6.repo”

100%[===================================================================================>] 1,707       --.-K/s   in 0s

2012-08-23 10:43:48 (42.4 MB/s) - “public-yum-ol6.repo” saved [1707/1707]

Finally, start the upgrade with the command “yum upgrade”. This will generate a list of all rpm packages that will be updated or installed, and also show you how much free disk space you will need for the download. I needed about 420 MB. Press “y” to confirm the upgrade.

$ yum upgrade
Transaction Summary
Install      22 Package(s)
Upgrade     374 Package(s)
Remove        1 Package(s)

Total download size: 417 M
Is this ok [y/N]:
 vim-minimal.x86_64 2:7.2.411-1.8.el6                           wpa_supplicant.x86_64 1:0.7.3-3.el6
  xmlrpc-c.x86_64 0:1.16.24-1209.1840.el6                        xmlrpc-c-client.x86_64 0:1.16.24-1209.1840.el6
  xorg-x11-drv-ati.x86_64 0:6.14.2-9.el6                         xorg-x11-drv-ati-firmware.noarch 0:6.14.2-9.el6
  xorg-x11-drv-intel.x86_64 0:2.16.0-4.el6                       xorg-x11-drv-mga.x86_64 0:1.4.13-7.el6
  xorg-x11-drv-wacom.x86_64 0:0.13.0-6.el6                       xorg-x11-server-Xorg.x86_64 0:1.10.6-1.el6
  xorg-x11-server-common.x86_64 0:1.10.6-1.el6                   xulrunner.x86_64 0:10.0.6-1.0.1.el6_3
  yum.noarch 0:3.2.29-30.0.1.el6                                 yum-plugin-security.noarch 0:1.1.30-14.el6
  yum-rhn-plugin.noarch 0:0.9.1-40.0.1.el6                       yum-utils.noarch 0:1.1.30-14.el6


When the upgrade completes successfully, reboot your server to activate the new kernel… and hope everything works fine :-)

Note: on VM VirtualBox, you will need to re-install the Guest Additions after the upgrade. Click on Devices -> Install Guest Additions… in the top menu to reinstall them. You will need to reboot your guest one more time to enable them.


Upgrading APEX Listener from 1.1.3 to 1.1.4 on GlassFish

Environment: APEX Listener 1.1.4, GlassFish 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="" xmlns:xsi=""

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
-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 :-)



Oracle Linux 6.3 has been released

Oracle Linux 6.3 has been released! You can view the release notes here, or download the ISO images here.

Have fun :-)


Upgrading the Unbreakable Enterprise Kernel (UEK) on Oracle Linux 6.2

Environment: Oracle Linux 6.2 64-bit with kernel 2.6.32-300.11.1.el6uek.x86_64, Oracle VM VirtualBox 4.1.10.

Oracle just released the RPMs for Oracle Linux 6.3. You can get them here. This includes an update of their Unbreakable Enterprise Kernel (UEK). I will explain here how you can upgrade your kernel to the latest version.

To know which kernel you are currently running, you can use the command “uname -r”:

$ uname -r

We are now going to upgrade our kernel to the latest version by using yum. The first thing you need to do is to get the yum configuration file for your platform. Note: execute all commands with user root.

$ cd /etc/yum.repos.d/
$ wget

--2012-06-28 11:08:31--
Connecting to||:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1707 (1.7K) [text/plain]
Saving to: “public-yum-ol6.repo”

100%[===================================================================================>] 1,707       --.-K/s   in 0s

2012-06-28 11:08:36 (162 MB/s) - “public-yum-ol6.repo” saved [1707/1707]

Next, modify the public-yum-ol6.repo file and set enabled=1 in the section [ol6_UEK_latest]. This is how my file looks like now:

name=Oracle Linux $releasever Latest ($basearch)

name=Oracle Linux $releasever GA installation media copy ($basearch)

name=Oracle Linux $releasever Update 1 installation media copy ($basearch)

name=Oracle Linux $releasever Update 2 installation media copy ($basearch)

name=Oracle Linux $releasever Update 3 installation media copy ($basearch)

name=Latest Unbreakable Enterprise Kernel for Oracle Linux $releasever ($basearch)

name=Unbreakable Enterprise Kernel for Oracle Linux $releasever ($basearch)

To upgrade the kernel, run the command “yum upgrade kernel-uek”. This will ask for a confirmation first before downloading and installing the new kernel:

$ yum upgrade kernel-uek
Loaded plugins: refresh-packagekit, security
ol6_UEK_latest                                                                                        |  951 B     00:00
Setting up Upgrade Process
Resolving Dependencies
--> Running transaction check
---> Package kernel-uek.x86_64 0:2.6.39-200.24.1.el6uek will be installed
--> Processing Dependency: kernel-uek-firmware = 2.6.39-200.24.1.el6uek for package: kernel-uek-2.6.39-200.24.1.el6uek.x86_64
--> Running transaction check
---> Package kernel-uek-firmware.noarch 0:2.6.39-200.24.1.el6uek will be installed
--> Finished Dependency Resolution

Dependencies Resolved

 Package                          Arch                Version                              Repository                   Size
 kernel-uek                       x86_64              2.6.39-200.24.1.el6uek               ol6_UEK_latest               26 M
Installing for dependencies:
 kernel-uek-firmware              noarch              2.6.39-200.24.1.el6uek               ol6_UEK_latest              2.8 M

Transaction Summary
Install       2 Package(s)

Total download size: 28 M
Installed size: 103 M
Is this ok [y/N]:

Downloading Packages:
(1/2): kernel-uek-2.6.39-200.24.1.el6uek.x86_64.rpm                                                   |  26 MB     04:11
(2/2): kernel-uek-firmware-2.6.39-200.24.1.el6uek.noarch.rpm                                          | 2.8 MB     00:27
Total                                                                                        104 kB/s |  28 MB     04:39
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : kernel-uek-firmware-2.6.39-200.24.1.el6uek.noarch                                                         1/2
  Installing : kernel-uek-2.6.39-200.24.1.el6uek.x86_64                                                                  2/2

  kernel-uek.x86_64 0:2.6.39-200.24.1.el6uek
Dependency Installed:
  kernel-uek-firmware.noarch 0:2.6.39-200.24.1.el6uek

After the upgrade, reboot your system. After the reboot run the command “uname -r” again. You will notice that the new kernel is used now:

$ uname -r

Note: if you are trying this on Oracle VM VirtualBox, upgrading the kernel will break the Guest Additions. To fix this, you need to download the “kernel-uek-devel-2.6.39-200.24.1.el6uek.x86_64” package first and then reinstall the Guest Additions.