Overview

This tutorial article and it’s associated video will help guide you through the process of upgrading a previously installed version of Oracle APEX to APEX version 4.0. We are going to be running on a machine that has Oracle Express Edition 10g (XE) and APEX 3.2 installed on it, in a Windows XP Professional environment.

Tutorial Video

High Level steps

1. Create destination directory and extract APEX files. (0:30)
2. Login via SQLPLUS (1:30)
   Optional) Verify HTTP Port and Shutdown. (2:05)
3. Install APEX 4.0. (2:30)
   Optional) Change APEX ADMIN password and restart any processes. (3:30)
4. Update Images (4:00)
5. Verify XML DB HTTP Port and Job Queue Processes. (5:25)
6. Check for Previous Releases and remove if needed. (6:30)
7. Verify Install by logging in. (7:41)

Times in parentheses are approximate times in the video the step is discussed

Download Locations

APEX 4: http://www.oracle.com/technology/products/database/application_express/download.html
(OTN account needed)

There are 2 versions, an English only version and a universal version if you need multilingual support. The multi lingual support version has a final install size of about 1.3 GB, compared to 735 MB for the English only.

APEX 4 Install Guide: http://www.oracle.com/technology/products/database/application_express/html/doc.html

Notes

Base vs Working Path

This sometimes confuses people, including myself when I first tried it. When the files are extracted, whatever directory you selected in your extractor, a subdirectory called ‘apex’ is created, and there the files are extracted to. For example, if you told it to extract to C:\temp, the extractor would create a directory under that. In this example, the WORKING directory would be ‘c:\temp\apex’ and the BASE directory would be ‘c:\temp’. This primarily is used in loading the image files.

Developer vs Runtime Environment

In our setup, we are installing the Developers environment, however it is possible to install APEX in a Runtime setup as well. The primary difference is that in runtime, regardless of user, no developers toolbars will be displayed, and no ability to modify the application runtime parameters is provided. Runtime mode also removes the GUI Administration functions. Runtime mode is a more hardened environment, as only the parts of APEX needed to run applications are installed. It is of course, possible to switch between the two modes if you need to, by use of a pair of sql commands you can run from SQLPLUS. Keep in mind if you do change the mode, it is HIGHLY recommended you change the ADMIN password each time you change modes.

Parameters for install commands.

The installation command for either the runtime or developers mode requires 4 parameters be passed to it. The formal syntax for the installer commands are:

<command> tablespace_apex tablespace_files tablespace_temp images

These parameters correspond to the actual command being used (@apexins for developer, @apxrtins for runtime), the name of the tablespace to be used for the APEX application user, the name of the tablespace to be used for the APEX file user, the name of the temporary tablespace, and the name of the virtual directory for the APEX images. While the first 3 may change to suit your needs, the last one, images, is recommended to be kept as ‘/i/’ in order to allow support for future APEX upgrades.

Checking and setting the HTTP Port

To check if it the HTTP Port is open or not, as well as it’s current setting, use the following command:

 SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

This will return the current HTTP port value. If it is 0 that means the HTTP server is OFF. To turn it back on, or to change the port the HTTP Server will use for connections, use the following:

 EXEC DBMS_XDB.SETHTTPPORT(x);

Where ‘x’ is the port you wish to use. Port 8080 is the default you should use, however, any valid port number is possible. Keep in mind other ports in use if you choose another port value.

Job Queue Processes

In APEX, the parameter job_queue_processes determines the maximum number of jobs that may run concurrently. Transactional support, as well as SQL scripts are run as jobs, and thus, if this parameter is set too low, these jobs cannot run properly. In order to check the current setting, we use the following SQL command:

 SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes';

If you need to alter the setting of the parameter, the command to do that is:

 ALTER SYSTEM SET JOB_QUEUE_PROCESSES = <number>;

It is possible to check the value from inside APEX itself, as well from the installation log. If you are altering it from SQLPLUS, be sure you entered SQLPLUS from the working directory. The other locations to check for this setting are discussed in the formal APEX 4.0 Installation Guide.

Previous Release Checking

The database users that were associated with previous releases of APEX are privileged users, and should be removed when no longer needed as a security precaution. This refers to the internal users, not end users. This is a two step process. First we identify any previously existing users that need removal with the following SQL command:

 SELECT username
    FROM dba_users
      WHERE (username LIKE 'FLOWS_%' OR USERNAME LIKE 'APEX_%')
  AND USERNAME NOT IN (
  SELECT 'FLOWS_FILES'
  FROM DUAL
  UNION
  SELECT 'APEX_PUBLIC_USER' FROM DUAL
  UNION
  SELECT SCHEMA s
  FROM dba_registry
  WHERE comp_id = 'APEX');

This command will return user names that should be considered for removal. In our case, it returns the users ‘APEX_030200’ and ‘FLOWS_020100’ as candidates. In order to remove these users, we use the following command:

 DROP USER <username> CASCADE;

It is important to ensure correct username spelling, as well as including the CASCADE part of the command to ensure proper and complete removal.

Optional Step Notes

Verify HTTP Port and Shutdown

Oracle recommends that any upgrade to your setup be performed without any users logged on or processes running within the database. In our case, since we are running with Oracle XE, using the embedded pl/sql gateway, we can more simply disable the HTTP port, which will automatically disable external access to the system while we upgrade it. In systems running on full database install, consult the APEX documentation and Oracle Database Documentation for your version for guidelines and procedures to shutdown and prevent external access. For us, we simply enter the following sql statement to reset the port to 0, which turns off the HTTP server.

 EXEC DBMS_XDB.SETHTTPPORT(0);

Change APEX ADMIN password and restart any processes.

If you doing a new install, or changing the mode of operation of APEX (IE going from runtime to developers or vice versa) you should change the APEX ADMIN password. During a pure upgrade situation, you do not need to do this step, as the existing password is brought forward. To change the ADMIN password, enter the SQLPLUS from the working directory, and enter the following command at the SQL prompt:

 @apxchpwd

This will prompt you to enter a new password for the ADMIN account. Note that at first login of this account, you will be required to change it, in  accordance with the password complexity rules in place (by default, new password will need to be at least 8 characters long, using upper and lowercase letters, at least 1 number, at least 1 special character, and be at least 3 characters different than the previous password)  Once this is done, if you turned off any services now you need to restart them.