Installing APEX 4 on Windows Server 2003 64 bit edition with Oracle Database 11g

kevin.landonTechnical Tips6 Comments

Overview

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.

Tutorial Video

[youtube]http://www.youtube.com/watch?v=pLK1r8UY42I[/youtube]

High Level steps

  1. Create destination directory and extract APEX files. (0:20)
  2. Login via SQLPLUS (1:13)
  3. Install APEX 4.0. (1:58)
  4. Change APEX ADMIN password (2:40)
  5. Configure PL/SQL Gateway and unlock Anonymous User. (3:15)
  6. Verify XML DB HTTP Port (4:04)
  7. Enabling Network Services (4:32)
  8. Enable Indexing on online help for 11g R2 (4:32)
  9. Check Job Queue Processes and Shared Servers (6:45)
  10. Check for Previous Releases and remove if needed. (7:00)
  11. Remove Invalid ACL’s if needed (7:27)
  12. Verify Install by logging in. (8:00)

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:

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.

PL/SQL Configuration

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:

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.

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', '');

Admin login

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’

6 Comments on “Installing APEX 4 on Windows Server 2003 64 bit edition with Oracle Database 11g”

  1. Good day,

    I really like this video. All is working well.

    But one question can you make a client PC open a browser and type in an URL and it will straight the login page of the application you have created that is on the server?

    Thanks Dane

  2. Ddar Ladies and Gentlemen,

    thanks for this wonderful guide – I didnt try it not yet , but it looks good.
    (similar to the upgrade from 3.1 to 3.2 partly at least)
    But in spite I have some questions :
    What is the difference between 4.0 and 4-0-1 ? Is there also a upgrade from 4.0 to 4.0.1 ?

    what happens if I use just the OS VISTA home 32 bit (not like you the MS Server 2003 ) on a 64 bit Laptop and on a 32 bit Laptop I have both ?
    Which downlod do I need /can I use for the combination : HW 64 bit-capable but OS 32 Bit ? I need the 32 bit one ?

    Do you have also experience with upgrades under UNIX/LINUX ?

    Do you ever try to download Oracle DB11gR2 32 or 64 bit for VISTA ?
    If so what happened with the first part
    For me the first part ever ended with the remark ‘File could not tbe stored because it couldn’t been read .!! The second part was workg fine , but this alone isn’t very useful Itried it more often with both laptops ,
    It happened as in 4.0 as in 4.0.1 !!! Bug ? it rest 2 files : one normal zip fikle with 0 b and another one called ‘Broken ‘ wih nearl he exact amount of bits abut with .Part-_ between filename and zip what could go rong ?
    Where to get ths pat 1

    thank a lot for answering

    ILD

  3. Getting error trying view video.
    Webpage error details

    User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; MS-RTC LM 8; InfoPath.2; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)
    Timestamp: Thu, 27 Jan 2011 22:13:58 UTC

    Message: ‘pageTracker’ is undefined
    Line: 558
    Char: 5
    Code: 0
    URI: https://www.mandsconsulting.com/installing-apex-4-to-server-2k3-x64-and-oracle-11g-video-training

    Message: Object expected
    Line: 1222
    Char: 1
    Code: 0
    URI: https://www.mandsconsulting.com/installing-apex-4-to-server-2k3-x64-and-oracle-11g-video-training

    1. Hi wcdba13,

      Not sure what are unable to see since you were able to post here. You should be able to simply click the video that you see on this page to play it. Perhaps you are using a browser that is preventing the video from playing.

  4. Thank you for tutorial, just wondering with this installation of Apex 4.0 on 11G, will it impact to oracle enterprise manager that running, please?

    And if yes then how to re-install the oracle enterprise manager again?

    thank you,

    gsarwono

Leave a Reply to ILD Cancel reply

Your email address will not be published. Required fields are marked *