This tutorial article and it’s associated video will help guide you through the process of installing APEX version 4.0 in a Windows Server 2003 64 bit OS environment with Oracle Database 11g R2 installed.
High Level steps
- Create destination directory and extract APEX files. (0:20)
- Login via SQLPLUS (1:13)
- Install APEX 4.0. (1:58)
- Change APEX ADMIN password (2:40)
- Configure PL/SQL Gateway and unlock Anonymous User. (3:15)
- Verify XML DB HTTP Port (4:04)
- Enabling Network Services (4:32)
- Enable Indexing on online help for 11g R2 (4:32)
- Check Job Queue Processes and Shared Servers (6:45)
- Check for Previous Releases and remove if needed. (7:00)
- Remove Invalid ACL’s if needed (7:27)
- Verify Install by logging in. (8:00)
Times in parentheses are approximate times in the video the step is discussed
(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
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:
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.
In 11g for the Embedded PL/SQL gateway option the configuration script is all we need to run. For users of previous versions of APEX, or those who used Oracle XE, we do not need to run a separate image updater. The functions of that command are integrated into the PL/SQL gateway configuration script.
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:
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.
Enabling Network Services.
Oracle Database 11g by default has it’s ability to interact with network services disabled. We therefore must enable this ability and grant connect privileges to any host that the APEX_040000 user will need. Without these services enabled many things in APEX, including PDF and report printing, Web Services, Online help searching, and some mail issues will fail to work properly. Below is the code required to allow any host the APEX_040000 user needs connect privileges.
DECLARE ACL_PATH VARCHAR2(4000); ACL_ID RAW(16); BEGIN -- Look for the ACL currently assigned to '*' and give APEX_040000 -- the "connect" privilege if APEX_040000 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; -- Before checking the privilege, ensure that the ACL is valid -- (for example, does not contain stale references to dropped users). -- If it does, the following exception will be raised: -- -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000' -- ORA-06512: at "XDB.DBMS_XDBZ", line ... -- SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID FROM XDB.XDB$ACL A, PATH_VIEW P WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND EQUALS_PATH(P.RES, ACL_PATH) = 1; DBMS_XDBZ.ValidateACL(ACL_ID); IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000','connect') IS NULL THEN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 'APEX_040000', 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_040000', TRUE, 'connect'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*'); END; / COMMIT;
There are other variations of this code that allow for some restriction of ability, and they are listed in the APEX 4 installation documentation. If after running this code, you receive an ‘ORA-4416: Invalid ACL’ error, we have a few more things to do. First, we run the following code to identify the invalid ACL:
REM SHOW the dangling REFERENCES TO dropped users IN the ACL that IS assigned REM TO '*'. SELECT ACL, PRINCIPAL FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL AND NACL.ACLID = ACE.ACLID AND NOT EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);
And then, to fix the invalid ACL we run the following
DECLARE ACL_ID RAW(16); CNT NUMBER; BEGIN -- Look for the object ID of the ACL currently assigned to '*' SELECT ACLID INTO ACL_ID FROM DBA_NETWORK_ACLS WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL; -- If just some users referenced in the ACL are invalid, remove just those -- users in the ACL. Otherwise, drop the ACL completely. SELECT COUNT(PRINCIPAL) INTO CNT FROM XDS_ACE WHERE ACLID = ACL_ID AND EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL); IF (CNT > 0) THEN FOR R IN (SELECT PRINCIPAL FROM XDS_ACE WHERE ACLID = ACL_ID AND NOT EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL)) LOOP UPDATE XDB.XDB$ACL SET OBJECT_VALUE = DELETEXML(OBJECT_VALUE, '/ACL/ACE[PRINCIPAL="'||R.PRINCIPAL||'"]') WHERE OBJECT_ID = ACL_ID; END LOOP; ELSE DELETE FROM XDB.XDB$ACL WHERE OBJECT_ID = ACL_ID; END IF; END; / REM commit the changes. COMMIT;
Once these 2 sets of code have been run, you then re-run the first code block from this section to grant the required privileges to the APEX_040000 user.
Indexing of Online Help in Database 11g R2
The ability to search the APEX online help is accomplished through a a combination of Oracle Text and a URL datastore. It is possible however that the needed permissions for this functionality have not been provided to the APEX_040000 user. This is usually evident if a user receives a ORA-29855, ORA-20000, or DRG-10758 error. In order to determine if a change is needed, we have a few steps. First, we run the following code to determine if an existing role has the needed abilities.
SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';
This will return either a NULL value (no results) or the name of a role. If NO VALUE IS RETURNED, create a new database role with the following:
CREATE ROLE APEX_URL_DATASTORE_ROLE;
Next, we grant the role to the APEX_040000 user with the following. Note, if the first code returned a value, use that in the , otherwise, use the role we just created above.
GRANT TO APEX_040000;
And lastly, if the initial code returned nothing, and you had to create the role, we enter the following code to grant permission to the role as needed:
EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');
ONLY execute this last statement if you had to create the role.
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 =
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.
Shared Servers Parameter
The embedded PL/SQL gateway uses parts of the shared server architecture of Oracle Database. In order to ensure acceptable performance, we need to make sure the SHARED_SERVERS parameter is set to a reasonable level. Oracle recommends a setting of 5 for small groups of concurrent users.
To find your current setting, you can go into the Database’s console (Enterprise manager) and select the ‘Initialization Parameters’ option under the ‘Database Configuration’ section on the ‘Server’ tab. Simply search for ‘Shared_servers’ to pull up the setting. If you need to change the setting, the command is:
ALTER SYSTEM SET SHARED_SERVERS = SCOPE=BOTH;
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 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.
Removing potentially Invalid ACL’s in Database 11g
After removing any previous users in an 11g environment, it is possible that an ACL error may occur. To help prevent that, you can use the following code, inserting the username(s) that may have appeared in the check for previous versions.
EXEC DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('power_users.xml', '');
A point I forgot to mention int the video, is actually the address for the APEX admin page. In our setup, using the embedded PL/SQL gateway, the admin page is addressed by adding a ‘/apex_admin’ to the base webpage address. For example, our access to the the APEX system is through the link ‘http://win2k3-x64.com:8080/apex’ so our APEX admin page would be ‘http://win2k3-x64.com/apex/apex_admin’