Installing APEX 5 in Oracle for use with Oracle Rest Data Services (ORDS)
Oracle databases come with APEX pre-installed. But the version installed may not be the version that you want. This article details how to install APEX 5 in an Oracle database.
Relevant Topics: Oracle DBA, Oracle APEX, Oracle Database Development, Oracle REST Data Services
Check the version of APEX currently in the database as follows:
SQL> select version from dba_registry where comp_id='APEX';
VERSION
------------------------------
3.2.1.00.10
SQL>
In this case, APEX version 3.2 is installed. We will first uninstall version 3.2 before installing version 5.
If this is the version of APEX installed on database installation, go to the APEX directory of the $ORACLE_HOME.
[oracle@Forge apex]$ cd $ORACLE_HOME/apex
Log into Oracle and run the script @apxremov.sql from the $ORACLE_HOME/apex directory. If this is not the default version of APEX that you wish to remove, please run the @apxremov.sql script in the corresponding apex directory.
[oracle@Forge apex]$ cd $ORACLE_HOME/apex
[oracle@Forge apex]$ ls
apex_epg_config_core.sql apxdevrm.sql apxrelod.sql catapx.sql owa
apex_epg_config.sql apxdvins.sql apxremov.sql core patches
apexins.sql apxe101.sql apxrtins.sql coreins.sql utilities
apexvalidate.sql apxe102.sql apxsqler.sql devins.sql
apxchpwd.sql apxe111.sql apxxemig.sql endins.sql
apxconf.sql apxldimg.sql apxxepwd.sql images
apxdbmig.sql apxpatch.sql builder load_trans.sql
[oracle@Forge apex]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 25 14:37:11 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> @apxremov.sql
...Removing Application Express
old 1: alter session set current_schema = &APPUN
new 1: alter session set current_schema = APEX_030200
Session altered.
PL/SQL procedure successfully completed.
no rows selected
old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
old 1: drop user &APPUN cascade
new 1: drop user APEX_030200 cascade
User dropped.
old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then
PL/SQL procedure successfully completed.
old 5: if '&UPGRADE' = '1' then
new 5: if '1' = '1' then
PL/SQL procedure successfully completed.
old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then
PL/SQL procedure successfully completed.
...Application Express Removed
SQL>
You can use the query from before to confirm that APEX has been removed.
SQL> select version from dba_registry where comp_id='APEX';
no rows selected
SQL>
Now, we can install APEX 5 into the database. First create new tablespaces:
-- Tablespace for the APEX application user
CREATE TABLESPACE APEX_DATA DATAFILE
'+DATA_DG1/prddb1/datafile/apex_data01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 2048M MAXSIZE 10240M
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
Tablespace created.
-- Tablespace for the APEX files user
CREATE TABLESPACE APEX_FILES_DATA DATAFILE
'+DATA_DG1/prddb1/datafile/apex_files_data01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 2048M MAXSIZE 10240M
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
Tablespace created.
Download and unzip APEX 5. Change to the apex directory and run the script apexins.sql as follows:
@apexins.sql apex_app_tspace apex_files_tspace temp virtual_images_directory
[oracle@Forge apex]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 25 17:11:33 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> @apexins.sql APEX_DATA APEX_FILES_DATA TEMP /i/
You will get the following message upon successful installation.
PL/SQL procedure successfully completed.
Since we are going to use Oracle Rest Data Service (ORDS), we must also configure ORDS. Run the script apex_rest_config.sql to configure ORDS.
[oracle@Forge apex]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 25 17:32:19 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> @apex_rest_config.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Enter a password for the APEX_LISTENER user []
Enter a password for the APEX_REST_PUBLIC_USER user []
...create APEX_LISTENER user
PL/SQL procedure successfully completed.
...create APEX_REST_PUBLIC_USER user
PL/SQL procedure successfully completed.
.
.
.
PL/SQL procedure successfully completed.
SQL>
Now, we need to create an APEX instance administrator account by running the script apxchpwd.sql.
[oracle@Forge apex]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 25 17:40:33 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> @apxchpwd.sql
========================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
=========================================================
Enter the administrator's username [ADMIN]
User "ADMIN" does not yet exist and will be created.
Enter ADMIN's email [ADMIN] xxxxxxxx
Enter ADMIN's password []
Created instance administrator ADMIN.
SQL>
The APEX_PUBLIC_USER account is locked with a random password by default. We must unlock it and change the password.
[oracle@Forge apex]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 25 17:53:14 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
User altered.
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY xxxxxxx;
User altered.
SQL>
Note that the APEX_PUBLIC_USER password will expire in 180 days by default. Once it expires, your APEX instance will not work until the password is changed. This can be prevented by changing APEX_PUBLIC_USER to a profile with a longer or unlimited lifetime (parameter PASSWORD_LIFE_TIME).
At this point, the installation is complete on the database side.