Installing Oracle Application Express in Oracle Database 12c

Oracle, Oracle Database April 4, 2014 1

Installing Oracle Application Express in Oracle Database 12c

apex_logo Oracle Application Express (Oracle APEX) is Oracle’s primary tool for developing Web applications with SQL and PL/SQL. Using only a web browser, you can develop and deploy professional Web-based applications for desktops and mobile devices. It is a fully supported, no cost option of the Oracle Database, and is installed by default in all editions of the Oracle Database.

This article is for learning purposes only!

Prerequisites:

Running and patched Oracle Database 12c How to install and patch Oracle Database 12c
Deploying and Developing Oracle Application Express with Oracle Database 12c
Latest updates about APEX in Database 12c can be found here (check Known Issues)

We will deploy the latest version of APEX (v.4.2.5 at the time of writing this tutorial) in a patched database 12c.

EDIT: This installation guide also applies to APEX version 5.

Oracle Application Express is unique amongst Oracle Database 12c options in that it can be uninstalled from the root container, CDB$ROOT, unlike other options. This capability allows you to re-install “runtime only” Application Express into the root container. Alternatively, once Application Express is removed from the root container you can manually install different versions of Application Express, Release 4.2.0 or above, into each PDB. In this tutorial we will install APEX into the pluggable database PDB01. Alternative installation options have been explained in this white paper.

1. Uninstalling Common Application Express

Post-installation of Oracle Database 12c Multitenant you can log into the root container and run apxremov_con.sql. This will remove Application Express from the root container, the PDB Seed and all PDBs within the CDB. It is very important to understand that this will also remove all of the Application Express meta-data (application definitions) stored within all of the PDBs. Therefore, Oracle strongly recommends that this script only be run when initially configuring the CDB, and before starting any Application Express development, to ensure applications are not inadvertently deleted.

[neo@zeus ~]$ sudo su - oracle
[sudo] password for neo:
[oracle@zeus ~]$ cd $ORACLE_HOME/apex
[oracle@zeus apex]$ sqlplus / as sysdba

SQL> @apxremov_con.sql
PL/SQL procedure successfully completed.

SQL>

2. Installing Application Express 4.2.5 into pluggable database PDB01.

Download the latest version of APEX and unzip into ORCALE_HOME directory.

#backup common apex 4.2.0
[oracle@zeus ~]$ mv $ORACLE_HOME/apex $ORACLE_HOME/apex_4.2.0

#Unzip new apex version to oracle home
[oracle@zeus ~]$ unzip -d $ORACLE_HOME /u01/app/stage/apex_4.2.5.zip

[oracle@zeus ~]$ cd $ORACLE_HOME/apex

#INSTALL APEX
[oracle@zeus apex]$ sqlplus / as sysdba

SQL> ALTER SESSION SET container = PDB01;

#CREATE APEX Tablespace

SQL> CREATE TABLESPACE APEX 
    DATAFILE 
        '+DATA/ZIONDB/DATAFILE//APEX' SIZE 104857600 AUTOEXTEND ON NEXT 10485760 MAXSIZE 2147483648 

    DEFAULT NOCOMPRESS 
    ONLINE 
    EXTENT MANAGEMENT LOCAL;

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

configure APEX and the Embedded PL/SQL Gateway

SYS> @apxconf
Enter a password for the ADMIN user: *********
Enter a port for the XDB HTTP listener [      8080] 8080
...changing HTTP Port
PL/SQL procedure successfully completed.

SYS> commit;

SYS> ALTER SESSION SET CONTAINER = CDB$ROOT;
SYS> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
SYS> ALTER USER xdb ACCOUNT UNLOCK;

SYS> ALTER SESSION SET CONTAINER = PDB01;
SYS> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
SYS> ALTER USER apex_public_user ACCOUNT UNLOCK;
SYS> ALTER USER flows_files ACCOUNT UNLOCK;

SYS> @apex_epg_config.sql /u01/app/oracle/product/12.1.0/db_1
PL/SQL procedure successfully completed.

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

COMP_NAME                   VERSION       STATUS
--------------------------  -------       -------
Oracle Application Express  4.2.5.00.08   VALID

configure database parameters

SYS>  ALTER SESSION SET CONTAINER = CDB$ROOT;
SYS>  SHOW PARAMETER job_queue_processes

--IF LESS THAN 20 ALTER PARAMETER
SYS>  ALTER system SET job_queue_processes=20 scope=both;

SYS>  SHOW PARAMETER shared_servers
SYS>  ALTER system SET shared_servers=5 scope=both;

SYS>  ALTER SESSION SET CONTAINER = PDB01;

SYS>  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;
/
SYS>  COMMIT;

Login to APEX Admin: