Installing Oracle Application Express 4.2

Oracle APEX is a free browser based tool to develop web applications with SQL and PL/SQL for desktops and mobile devices. It is installed by default in all oracle database editions.

To verify APEX version.
SQL> SELECT schema, version FROM dba_registry WHERE comp_id = 'APEX';

SCHEMA       VERSION
------------------------------ ------------------------------
APEX_040000       4.0.2.00.09

To verify Oracle XML DB Protocol Server Port.
SQL> SELECT dbms_xdb.gethttpport FROM dual;

GETHTTPPORT
-----------
       8080

Disable Oracle XML DB protocol Server.
SQL> EXEC dbms_xdb.sethttpport(0);

Backup existing APEX binaries.
$ mv $ORACLE_HOME/apex $ORACLE_HOME/apex_040000

Unzip APEX software into ORACLE_HOME.
$ unzip apex_4.2.3_en.zip -d $ORACLE_HOME
$ cd $ORACLE_HOME/apex

SQL> CREATE TABLESPACE APEX_040200 DATAFILE '/u01/app/oracle/oradata/apex_423_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

SQL>@apexins.sql APEX_040200 APEX_040200 TEMP /i/

When APEX installs, it creates the below database accounts.
APEX_042000 - Owns APEX schema and metadata
FLOWS_FILES - Owns APEX uploaded files
APEX_PUBLIC_USER - Minimum privileged account used with APEX Listener or Oracle HTTP server with mod_plsql.

Change ADMIN account password.
SQL> @apxchpwd.sql

Configure the Embedded PL/SQL Gateway.
SQL> @apex_epg_config.sql /u01/app/oracle/product/11.2.0/xe
SQL> ALTER USER anonymous ACCOUNT UNLOCK;

If you are upgrading APEX, update the images directory.
SQL> @apxldimg.sql /u01/app/oracle/product/11.2.0/xe

SQL> ALTER USER apex_040200 ACCOUNT UNLOCK;
SQL> ALTER USER apex_public_user ACCOUNT UNLOCK;
SQL> ALTER USER flows_files ACCOUNT UNLOCK;
SQL> ALTER USER xdb ACCOUNT UNLOCK;

Set database parameters for APEX.
SQL> ALTER SYSTEM SET job_queue_processes=20 SCOPE=both;
SQL> ALTER SYSTEM SET shared_servers=5 SCOPE=both;    --> Embedded PL/SQL Gateway uses shared server architecture.

Enable Oracle XML DB  Protocol Server. The Embedded PL/SQL Gateway runs in the Oracle XML DB Protocol Server in the Oracle Database.
SQL> EXEC dbms_xdb.sethttpport(8080);

Enable access for remote users (optional).
SQL> EXEC dbms_xdb.setListenerLocalAccess(l_access => FALSE);

Enable network services in Database 11g by granting connect privileges to any host for APEX_040200 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;

Login as ADMIN to Administrative Services for creating a workspace and APEX users at
http://locahost:8080/apex/apex_admin

Login as ADMIN to INTERNAL workspace at
http://localhost:8080/apex

Drop older version of APEX.
SQL> DROP USER apex_040000 CASCADE;

APEX Listener is a J2EE based alternative for Oracle HTTP Server (OHS) with mod_plsql.
To use APEX Listener, Disable Oracle XML DB Protocol Server.

SQL> EXEC dbms_xdb.sethttpport(0);

Configure RESTful Services.
SQL> @apex_rest_config.sql

When configuring RESTful Services, it creates two additional database accounts.
APEX_LISTENER - To query RESTful Services definitions.
APEX_REST_PUBLIC_USER - To call RESTful Services definitions.

Unzip APEX Listener software into ORACLE_HOME.
$ mkdir $ORACLE_HOME/apexlsnr
$ unzip apex_listener.2.0.5.287.04.27.zip -d $ORACLE_HOME/apexlsnr
$ cd $ORACLE_HOME/apexlsnr

Set the location for configuration files.

$ java -jar apex.war configdir $ORACLE_HOME/apexlsnr

Configure APEX Listener.

$ java -jar apex.war

This Listener instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:/u01/app/oracle/product/11.2.0/xe/apexlsnr
Nov 30, 2013 11:41:34 PM oracle.dbtools.common.config.cmds.ConfigDir execute
INFO: Set config.dir to /u01/app/oracle/product/11.2.0/xe/apexlsnr in: /u01/app/oracle/product/11.2.0/xe/apexlsnr/apex.war
Nov 30, 2013 11:41:38 PM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder
INFO: Using configuration folder: /u01/app/oracle/product/11.2.0/xe/apexlsnr/apex
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:XE
Enter the database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to enter passwords for the RESTful Services database users (APEX_LISTENER,APEX_REST_PUBLIC_USER), 2 to use the same password as used for APEX_PUBLIC_USER or, 3 to skip this step [1]:2
Nov 30, 2013 11:46:55 PM oracle.dbtools.common.config.file.ConfigurationFiles update
INFO: Updated configurations: defaults, apex, apex_al, apex_rt
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1

Enter the APEX static resources location or Enter to skip:/u01/app/oracle/product/11.2.0/xe/apex/images

Enter the HTTP port [8080]:

If you see "503 - Service Unavailable" message, reset the password and unlock the account for APEX_PUBLIC_USER.
SQL> ALTER USER apex_public_user IDENTIFIED BY <above_specified_password> ACCOUNT UNLOCK;

Configure an Administrator User to administer using SQL Developer.

$ java -jar apex.war user adminlistener "Listener Administrator"

Standalone mode doesn't support HTTPS. Disable the default behaviour to use OAuth2 in Non HTTPS Environments.

$ cd $ORACLE_HOME/apexlsnr

Add the following setting to the end of the file default.xml just before the </properties> tag.
<entry key="security.verifySSL">false</entry>

Start APEX Listener in standalone mode.

$ java -jar apex.war

To stop APEX Listener Server in standalone mode, press Ctrl-C.

To configure additional databases.
1. Create a database pool pointing to the database.
    java -jar apex.war setup --database <db_pool>
2. Create a new mapping rule.
    java -jar apex.war map-url --type base-path --workspace-id <workspace> /<path_prefix> <db_pool>
The URL path starts with /apex/<path_prefix> will be mapped to the database.