Select Page

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

Download

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.zip 
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

. /etc/profile

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>;
ALTER PROFILE DEFAULT 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;
EXIT;


Next Install Apache Tomcat 7 Oracle Restful Data Services

yum install tomcat7
--yum remove java-1.7.0-openjdk
--yum remove java
rpm -i /tmp/jdk-8u92-linux-x64.rpm
mkdir /u01/app/oracle/product/ords
cd /u01/app/oracle/product/ords
unzip /tmp/ords.3.0.5.124.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 [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
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 [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 [1]:
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 [1]: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

JAVA_OPTS="-Duser.timezone=UTC"

Change the following line from

JAVA_HOME="/usr/lib/jvm/jre"

to

JAVA_HOME="/usr/java/jdk1.8.0_92/"

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.

Apex_5_login

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!

%d bloggers like this: