YouTube Playlist – Oracle Database and APEX on AWS EC2

AWS (Amazon Web Services) supports some versions of APEX on its RDS service but if you really want to be able to have the full power and ability of the latest versions you need to learn how to install the database on an EC2 instance ja(as I show in this post – DEPLOYING ORACLE DATABASE 12C ON AWS EC2 INSTANCE) and install and configure APEX and ORDS on a separate EC2 instance.

In the accompanying YouTube video I will show the steps to delete APEX from the container 12c Database and then install on a pluggable database.  Lastly I’ll show how to configure and run the ORDS listener on a separate EC2 instance

First let me list some super helpful resources –

Posts from my new hero Tim Hall and other random stuff I encountered

Multitenant : Uninstall APEX from the CDB in Oracle Database 12c Release 1 (12.1)

Oracle Application Express (APEX) 5.0 Installation

Connection error after upgrade to 4.1

Register DB Service to Listener

REST Data Services Installation and Configuration Guide

Application Express Installation Guide

Workflow for Installing APEX and ORDS

This YouTube video follows the workflow I’ve created below –

1. Set up Ec2 instance using OL7.2-x86_64-HVM-2015-12-10 Amazon Machine Image (AMI)
2. Login via MobaXterm as ec2-user and change password
   • sudo passwd ec2-user
3. Install packages needed for instance
   • sudo yum install wget zip unzip -y
   • sudo yum install perl-libwww-perl.noarch -y
   • sudo yum install java-1.8.0-openjdk.x86_64 -y
   • sudo yum install updates
4. Create apex user and make it so user can connect
   • sudo useradd -d /home/apexuser apexuser
     sudo passwd apexuser
     cd ~
     mkdir .ssh
     chmod 700 .ssh
     touch .ssh/authorized_keys
     chmod 600 .ssh/authorized_keys
     GET http://169.254.169.254/latest/meta-data/public-keys/0/openssh-key>.ssh/authorized_keys
     log out and login as apexuser
5. Make directories for apexlistener, oracle client, and ORDS
   • mkdir /home/apexuser/apexlistener
   • mkdir /home/apexuser/orclclient
   • mkdir /home/apexuser/ORDS
6. Upload files and unzip in respective directories
   • unzip apex_5.0.3_en.zip -d /home/apexuser/apexlistener
   • upload oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm into /home/apexuser/orclclient
   • upload oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm into /home/apexuser/orclclient
   • unzip ords.3.0.3.351.13.24.zip -d /home/apexuser/ORDS
7. Install oracle client
   • su ec2-user
   • cd /home/apexuser/orclclient
   • rpm -ivh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
   • rpm -ivh oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
8. Update bash profile of apex user for client
   • su apexuser
   • cd ~
   • vi ~/.bash_profile
     • export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH
     • export PATH=/usr/lib/oracle/12.1/client64/bin:$PATH
   • source ~/.bash_profile
   • sqlplus SYS/PASSWORD@HOSTNAME:1521/orcl as sysdba
9. Go to your database server & uninstall apex from container database
   • !!!!!!!Make sure you uninstall or unplug any pluggable databases before you do this!!!!!!
   • /u01/app/oracle/product/12.1.0/dbhome_1/apex
   • sqlplus / sys as sysdba
   • @apxremov_con.sql
10. Create pluggable database & add USERS tablespace
    • Go to SQL Developer for these tasks
    • Create pluggable database and open read / write
    • Connect to pluggable database
    • Create Users tablespace on pluggable database
      • CREATE TABLESPACE “USERS” DATAFILE
        ‘/u01/app/oracle/oradata/orcl/pdbseed/users01.dbf’ SIZE 5242880
        AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
        LOGGING ONLINE PERMANENT BLOCKSIZE 8192
        EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
        NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
11. Install apex on your container database, change apex admin user and create rest data services user
    • ALTER SESSION SET CONTAINER =PDBORCL (your pluggable database name)
    • @apexins.sql SYSAUX SYSAUX TEMP /i/
    • @apxchpwd.sql
    • @apex_rest_config.sql
    • ALTER USER APEX_PUBLIC_USER IDENTIFIED BY Password1 ACCOUNT UNLOCK;
12. Go to your Apex server and Run ORDS set up
    • cd /home/apexuser/ORDS
    • java -jar ords.war
    • /home/apexuser/ORDS/config/ (configuration directory)
    • /home/apexuser/apex/images (static resources directory)
    • add tcp rule for 8080 (or port you used if you haven’t already when you created server)
    • stop server using ctrl c and start with nohup
      • nohup java -jar ords.war &
    • if you need to stop server issue top command and find java process running under apexuser and kill process number
      • top -u apexuser
      • kill [process]
13. At this point your APEX UI should be available at http://HOSTNAME:8080/ords