A standard Oracle 11.2.0.3 database installation comes bundled with Application Express (APEX) 3.2.1 by default.  I’m going to upgrade to the latest version of APEX (currently 4.2.2) and then configure the Embedded PL/SQL Gateway (EPG), which uses the Oracle
XML DB HTTP components within the database itself, so I don’t need to run a separate HTTP server.

First off, download apex_4.2.2_en.zip from the following location and copy the zip file to your
database server:

http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

Disable HTTP access and backup the existing binaries 

Connect as SYS and disable the Oracle XML DB HTTP server by temporarily setting the HTTP port to zero (if it’s already zero, then it’s not enabled):

sqlplus / as sysdba

SELECT dbms_xdb.gethttpport FROM dual;

EXEC dbms_xdb.sethttpport(0);

Backup and move the existing APEX binaries:

mv $ORACLE_HOME/apex $ORACLE_HOME/apex.3.2.1

Unzip the APEX 4.2.2 software and change directories ready for the install:

unzip /u01/app/oracle/software/apex_4.2.2_en.zip -d $ORACLE_HOME

cd $ORACLE_HOME/apex

Install APEX 4.2.2

Connect as SYS again, and create a new APEX tablespace (this is optional, but I prefer to keep things separate from the default SYSAUX tablespace):

sqlplus / as sysdba

CREATE TABLESPACE APEX DATAFILE '/u02/oradata/snap11g/apex_01.dbf'

SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1000M LOGGING

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

Check which version is currently installed:

COL comp_name FOR A30

SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

COMP_NAME                      VERSION                        STATUS

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

Oracle Application Express     3.2.1.00.12                    VALID

Start the installation of 4.2.2:

@apexins APEX APEX TEMP /i/

Usage: @apexins <apex_tbs> <apex_files_tbs> <temp_tbs> <images>

apex_tbs – name of the tablespace for the APEX user.

apex_files_tbs – name of the tablespace for APEX files user.

temp_tbs – name of the temporary tablespace.

images – virtual directory for APEX images.  Define the virtual image directory as /i/ for future updates.

Once the installation has finished, reconnect and change the ADMIN account password:

sqlplus / as sysdba

@apxchpwd

NOTE: The password must contain at least one punctuation character: (!”#$%&()“*+,-/:;?_).

Check the registry again:

COL comp_name FOR A30

SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

COMP_NAME                      VERSION                        STATUS

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

Oracle Application Express     4.2.2.00.11                    VALID

Run the Embedded PL/SQL Gateway configuration (EPG)

@apex_epg_config.sql /u01/app/oracle/product/11.2.0.3

Update the APEX images with those from the new release:

@apxldimg.sql /u01/app/oracle/product/11.2.0.3

NOTE: This step isn’t necessary if you ran the apex_epg_config.sql script above, as it will have already done this for you.  If you didn’t run the EPG script above, because you’ve upgraded from an install where EPG was already configured, then
you do need to run this.

Make sure that the following accounts are unlocked:

ALTER USER anonymous ACCOUNT UNLOCK;

ALTER USER xdb ACCOUNT UNLOCK;

ALTER USER apex_public_user ACCOUNT UNLOCK;

ALTER USER flows_files ACCOUNT UNLOCK;

Configure database parameters for APEX

Check that the JOB_QUEUE_PROCESSES parameter is set to at least 20:

SHOW PARAMETER job_queue_processes

ALTER system SET job_queue_processes=20 scope=both;

For a small group of concurrent users, Oracle recommends a value of 5 for SHARED_SERVERS:

SHOW PARAMETER shared_servers

ALTER system SET shared_servers=5 scope=both;

Enable network services (ACL) and XML DB HTTP server

Re enable the Oracle XML DB HTTP Server port (8082):

EXEC dbms_xdb.sethttpport(8082);

Enable remote HTTP connections (optional):

EXEC dbms_xdb.setListenerLocalAccess(l_access => FALSE);

If l_access is set to TRUE, setListenerLocalAccess allows access to the XML DB HTTP server on the localhost only.

If l_access is set to FALSE, setListenerLocalAccess allows access to the XML DB HTTP server on both the localhost and non-localhost interfaces i.e. remote connections.

By default, the ability to interact with network services is disabled in Oracle Database 11g. Therefore, you must use the DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_040200 database user:

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;

Finally, login and check everything is working…

Administration Services login page (used for managing the APEX instance): http://linux03.vbox:8082/apex/apex_admin

NOTE: You’ll be prompted to change the ADMIN password the first time you logon.

Username: ADMIN

Password: *****

Workspace login page: http://linux03.vbox:8082/apex

Workspace: INTERNAL

Username: ADMIN

Password: *****

Once you’re logged in, you’ll see something like this…

Removing previous versions of APEX

If later on you decide to clean-up and remove older versions of APEX, you can run the following SQL to identify such schemas:

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

FROM dba_registry

WHERE comp_id = 'APEX');

…and then drop the schema(s), with the cascade option:

DROP USER APEX_030200 CASCADE;