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