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;