Creating an Oracle Database with Apex on AWS RDS

Steps to create an Oracle Standard Edition Two database on AWS with APEX as an option. These notes are for Apex version 5.1.2.v1

PRE-REQUISITIES

If you don't already have them, a parameter group (init.ora file) and an option group (options such as Apex) will need to be created. I have chosen to go with the default parameter group for now as the database only requires default parameters.

However, here is an example of the option group creation process which we will need for Apex to be created with the database.

From the Amazon RDS Console, click on Option groups

Click on Create Group

Create option group

Once the option group has been created, highlight it and Click on Add Option

For good measure Add Option

_______________________________________________________________________________________________________________________________________________________________

DATABASE CREATION

* Step 1 Select Engine

* Step 2 Choose use case

* Step 3 Specify DB details

* Step 4 Configure advanced settings

_______________________________________________________________________________________________________________________________________________________________

APEX CREATION

* Unlock the Public User Account

alter user APEX_PUBLIC_USER identified by new_password;
alter user APEX_PUBLIC_USER account unlock;

* On the EC2 instance, set up a listener

Start by creating a non-privileged OS user to own the Apex Listener installation.

useradd -d /home/apexuser apexuser
passwd apexuser;  -- reset the password

* Download and install Apex from http://www.oracle.com/technetwork/developer-tools/apex/downloads/apex-51-archive-3661848.html

cd /home/apexuser/
unzip apex_5.1.2.zip

* Download and Unzip the Apex listener file from Oracle (in the case of Apex version 5 currently it is called <ords.18.1.1.95.1251.zip>) and was downloaded from http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html

mkdir /home/apexuser/ORDS
chmod go+rw /home/apexuser
unzip ords.18.1.1.95.1251.zip

* The master user requires some privileges - may as well get these sorted

CREATE ROLE APEX_ADMINISTRATOR_ROLE NOT IDENTIFIED;

GRANT EXECUTE ON APEX_050100.WWV_FLOW_INSTANCE_ADMIN TO APEX_ADMINISTRATOR_ROLE;
GRANT EXECUTE ON APEX_050100.WWV_FLOW_INSTANCE_REST_ADMIN TO APEX_ADMINISTRATOR_ROLE;
GRANT SELECT ON APEX_050100.WWV_FLOW_PRIVATE_TO_ADMIN TO APEX_ADMINISTRATOR_ROLE;
GRANT APEX_ADMINISTRATOR_ROLE TO SYS WITH ADMIN OPTION;
GRANT APEX_ADMINISTRATOR_ROLE TO <MASTER_USER> WITH ADMIN OPTION;

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'DBMS_REGISTRY',
        p_grantee   => '<MASTER_USER>',
        p_privilege => 'EXECUTE');
end;
/

* Apex Listener needs an ADMIN user plus credentials for Restful Services

@/home/apexuser/apex/apxchpwd.sql

@/home/apexuser/apex/apex_rest_config.sql

* Configure the Actual Listener Services

java -jar ords.war setup

Enter the location to store configuration data:/home/apexuser/configdata -- make sure the directory exists
Enter the name of the database server [nmtestdb.region.rds.amazonaws.com]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name [NMTESTDB_A]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:2
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:1
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:1
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:

Note: Very important to know that RDS appears to suffix the service name with an _A (perhaps related to region). Apex listener cannot connect (ORA-12154) if this does not match. To confirm the service name - log into the database and run SQL>show parameter service_names

* Start up the Listener

java -jar ords.war
nohup java -jar ords.war &  -- as a background process

Note: The first time you start the APEX Listener, you are prompted to provide the location of the APEX Static resources. This images folder is located in the /home/apexuser/apex/images directory in the installation directory for APEX.

* Connecting to Apex from a browser

http://<IP Address of Server with Listener on it>:8080
WORKSPACE_NAME  INTERNAL
USERNAME        ADMIN
PASSWORD        ADMIN_PASSWORD