It’s always nice to dip your toes in the familiar with Oralce Apex 5 being out for over a year now I thought I’d validate an old blog I wrote about running Apex in the Cloud.
Oracle do provide cloud options if you just want to try it out but like any tech geek I always like to have my own if I can and it’s not too much effort.
Streamlined install steps are as follows:
Start an EC2
- Amazon Linux AMI
- t2.small for the instance type
- 20GB storage
- Set up a security group with ports 8080 and 22 open
- Apex 5 (5.0.3 was used in my test)
- Oracle XE Database 11g R2 for Linux to your local machine
- Oracle REST Data Services (ORDS) (3.0.5 was used in my test)
- Java 8 (x64 version 8u92 was used in my test)
Copy the downloaded files to /tmp
Connect to your new server and run the following commands
sudo su yum update dd if=/dev/zero of=/swapfile bs=1536 count=1572864 chmod 0600 /swapfile /sbin/mkswap /swapfile /sbin/swapon /swapfile /sbin/swapon -s cd /tmp unzip oracle-xe-11.2.0-1.0.x86_64.rpm -Uvh Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm /etc/init.d/oracle-xe configure <****Accept all default prompts in this script but enter a password when prompted***> ln -s /u01/app/oracle/product/11.2.0/xe/bin/sqlplus /usr/bin/sqlplus
Edit the file /etc/profile and insert the following lines at the bottom
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe export ORACLE_HOME export ORACLE_SID=XE export NLS_LANG=AMERICAN.AL32UTF8 export PATH=$PATH:$ORACLE_HOME/bin Save the file then run thw following
Save the file then run the following command
To test that the DB is working run the following
sqlplus sys as sysdba exit
Next, upgrade Apex to version 5. Note this is for a development environment and as part of this process I turn off password expiry and is not recommended for a production environment.
cd /tmp/ unzip apex_5.0.3_en.zip cd /tmp/apex
sqlplus /nolog CONNECT SYS as SYSDBA @apexins.sql SYSAUX SYSAUX TEMP /i/
sqlplus /nolog CONNECT SYS as SYSDBA
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY <new_password>;
LIMIT PASSWORD_LIFE_TIME UNLIMITED;EXEC DBMS_XDB.SETHTTPPORT(0); @apex_rest_config.sql <***enter passwords when prompted***>
DECLARE ACL_PATH VARCHAR2(4000); BEGIN -- Look for the ACL currently assigned to '*' and give APEX_050000 -- the "connect" privilege if APEX_050000 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_050000', 'connect') IS NULL THEN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 'APEX_050000', 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_050000', TRUE, 'connect'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*'); END; / COMMIT;
Next Install Apache Tomcat 7 Oracle Restful Data Services
yum install tomcat7 --yum remove java-1.7.0-openjdk
--yum remove javarpm -i /tmp/jdk-8u92-linux-x64.rpm mkdir /u01/app/oracle/product/ords cd /u01/app/oracle/product/ords unzip /tmp/ords.188.8.131.52.10.54.zip java -jar -Duser.timezone=UTC ords.war Enter the location to store configuration data: /u01/app/oracle/product/ Enter the name of the database server [localhost]: Enter the database listen port : Enter 1 to specify the database service name, or 2 to specify the database SID : Enter the database service name: xe Enter the database password for ORDS_PUBLIC_USER: [new password here] Enter the username with SYSDBA privileges to verify the installation [SYS]: Enter the database password for SYS: If using Oracle Application Express or migrating from mod_plsql then you must enter 1 : Enter the database password for APEX_PUBLIC_USER: Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step : Enter the database password for APEX_LISTENER: Enter the database password for APEX_REST_PUBLIC_USER: Enter 1 if you wish to start in standalone mode or 2 to exit :2
chown tomcat /u01/app/oracle/product/ords/defaults.xml cp ords.war /usr/share/tomcat7/webapps mv /tmp/apex/images /usr/share/tomcat7/webapps/i
Edit the /usr/share/tomcat7/conf/tomcat7.conf
add the following line to the file
Change the following line from
Finally, run the following command.
service tomcat7 start
You’re done, you can now connect to Apex from your web browser by going to http://<IP of server>:8080/ords.
Your initial credentials will be
- Workspace: internal
- User: admin
- Password: Same as your initial SYS database password
If you have any problems check the logs here /usr/share/tomcat7/logs to see if there is anything obvious.
This process has a few java related tweaks that I would have preferred not do, but there seem to be conflicts with the OpenJDK that AWS Linux likes to use by default. I think I will try another flavour of Linux when time permits to see if I have the same problems.
All the code, all the fun – Ben
Ben writes blogs about the technical side of BI the code, all the code and not much other than the code.
You can read all of Ben’s blog here.
Don’t forget, we can train your team in the art of agile business intelligence at any time!