APEX within the Oracle RDS environment

We I have written a post regarding getting APEX and Oracle XE working, but now Amazon support APEX within their RDS offering, great I hear you cry. Oracle in a multi A-Z environment backed up and ultra reliable. So how hard could it be to get the thing working.

 

Be Warned the APEX version is 4.1.1 so make sure you download the correct APEX version from Oracle, this was correct at the time of writing

This configuration is based on Oracle in a VPC, but if you are not using a VPC then there should be no issue, it is just the security groups you will need to change.

The Amazon instructions for APEX are hidden away and took a bit of finding Oracle Application Express

As always I will give you the areas that they don’t tell you about

Start by creating a database using the AWS management console. (The version number for the Oracle engine must be 11.2.0.2.v4 or newer).

I would get this all working before you attempt to get anything to do with APEX working.

When you create an Oracle or MySQL instance in a VPC it creates a security group, this is really cool as this is where you can control what server has access to what. It also allows you to add the IP addresses of the server in you LAN that are allowed to connect to the instance. I would recommend full access to start with until you have proved it is working, before you close the security down. Then just go the other way, remove all of the security rules and replace and test.

So now you have a database working you need to create a new option group.

Go to the RDS part of the console and find the Option Group area.

Create a new group and give it an appropriate name (apex-oracle)

Once the group has created you need to Add more options, click in the group and add the following

  • XMLDB
  • APEX
  • APEX_DEV

You can apply them immediately.

Now you need to apply the option group to the RDS instance, go back to the DBInstances view and pick the relevant RDS instance that you want to add APEX to. Right click on the selected instance and choose Modify.

You need to pick the new Option Group and don’t forget to Apply Immediately

This will add the APEX bits n pieces into the environment.

From the instructions

After the option group is successfully applied, you will need to change the password for the APEX_PUBLIC_USER database account and unlock it. You can do this using the Oracle SQL*Plus command line utility: Connect to your DB instance as the master user and issue the following commands:

alter user APEX_PUBLIC_USER identified by newpass;

alter user APEX_PUBLIC_USER account unlock;

Replace newpass with a password of your choice.

I love these lines in any instructions “you just do instructions” so how to you get SQLPlus installed to get this working.

SQLplus is needed for the APEX server anyway, so it will not be lost. I would not install this on a server in the public area to start with, get it working in an environment that can see the database, i.e. one of the private subnets.

For the instruction of how to do this bit of the set-up head over to my other technical blog survivalguides and read the aptly named Installing SQL*plus instant client on Linux

You can now run the APEX sql commands above

Now to install the Java JRE environment, again over to the sister blog Installing Java JRE on an Amazon Linux AMI

Last thing on the list before we can get to the APEX install is the installation of the  Oracle Net Services, to enable the APEX listener to connect to your Amazon RDS instance.

You kind have already done this, when you created the tnsnames.ora you just need to ensure that when server starts the services know where the tnsnames.ora file is, you need therefore to add the TNS_ADMIN lines to the shell script that will be called at startup.

cd /etc/profile.d

vi oracle_env.sh

Add the the following lines (this assumes that you followed the instructions Installing SQL*plus instant client on Linux)

TNS_ADMIN=/u01/app/oracle/product/11.2.0/general/network/admin

export TNS_ADMIN

OK now for the APEX installation

Create an apex user to run the listener

useradd -d /home/apexuser apexuser

Now assign a password to apexuser:

passwd apexuser

You need to download the APEX and APEX Listener install files from

http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.html

As per usual I store all the bits and pieces in the opt/software directory, no idea if this is standard practice but I always know where the stuff is.

mkdir -p /opt/software/apex

mkdir -p /opt/software/apex/apex_listener

cd /opt/software/apex

unzip apex_4.2_en.zip

mv apex /opt/

cd /opt/software/apex/apex_listener

unzip apex_listener.1.1.4.195.00.12.zip

rm -f apex_listener.1.1.4.195.00.12.zip

cd /opt/software/apex

mv apex_listener /opt/

cd /opt

chown -R apexuser:apexuser apex
chown -R apexuser:apexuser apex_listener

cd /opt/apex_listener

java -Dapex.home=/opt/apex -Dapex.images=/opt/apex/images -Dapex.erase -jar /opt/apex_listener/apex.war

INFO: Starting: /opt/apex_listener/apex.war
See: ‘java -jar apex.war –help’ for full range of configuration options
INFO: Extracting to: /opt/apex
INFO: Using classpath: file:/opt/apex/apex/____embedded/start.jar:file:/opt/apex/apex/WEB-INF/lib/apex.jar:file:/opt/apex/apex/WEB-INF/lib/poi-3.6-20091214.jar:file:/opt/apex/apex/WEB-INF/lib/ojdbc6.jar:file:/opt/apex/apex/WEB-INF/lib/xmlparserv2-11.2.0.jar:file:/opt/apex/apex/WEB-INF/lib/ucp.jar:file:/opt/apex/apex/WEB-INF/lib/xdb-11.2.0.jar:file:/opt/apex/apex/WEB-INF/lib/ojmisc.jar:file:/opt/apex/apex/WEB-INF/lib/commons-fileupload-1.2.1.jar:file:/opt/apex/apex/WEB-INF/lib/je-4.0.103.jar:
INFO: Starting Embedded Web Container in: /opt/apex
Enter a username for the APEX Listener Administrator [adminlistener]:
Enter a password for adminlistener: ENTER SECURE PASSWORD
Confirm password for adminlistener: ENTER SECURE PASSWORD 
Enter a username for the APEX Listener Manager [managerlistener]:
Enter a password for managerlistener: ENTER SECURE PASSWORD
Confirm password for managerlistener:ENTER SECURE PASSWORD
Oct 18, 2012 1:40:25 PM ____bootstrap.Deployer deploy
INFO: Will deploy application path=/opt/apex/apex/WEB-INF/web.xml
Oct 18, 2012 1:40:27 PM ____bootstrap.Deployer deploy
INFO: deployed application path=/opt/apex/apex/WEB-INF/web.xml
Using config file: /opt/apex/apex-config.xml
APEX Listener version : 1.1.4.195.00.12
APEX Listener server info: Grizzly/1.9.18-o
Oct 18, 2012 1:40:27 PM com.sun.grizzly.Controller logVersion
INFO: Starting Grizzly Framework 1.9.18-o – Thu Oct 18 13:40:27 UTC 2012
INFO: Please complete configuration at: http://localhost:8080/apex/listenerConfigure

Obviously I am connecting via a remote server so

http://x.x.x.x:8080/apex/listenerConfigure

 

You will be presented with the Oracle Listener configuration screen

 

 

Note due to the size of the field, basic can not be used and TNS is needed, use the alias that you created in the tnsnames.ora file earlier on, if you have followed the above example yours will be called general.

 

Now you need to do some sqlplus stuff, so again if you have followed the instructions above you need to set the environmental variables

cd /opt/oracle

. set_env

If you have not done it this way, make sure you have all the relevant setting to call make sqlplus work

cd /opt/apex

sqlplus username@general

Replace master with your master user name. Enter a new admin password when the apxchpwd.sql script prompts you.

grant APEX_ADMINISTRATOR_ROLE to master;

@apxchpwd.sql

I got the following error when I ran this:

Enter a value below for the password for the Application Express ADMIN user.

Enter a password for the ADMIN user []
ERROR:
ORA-01435: user does not exist

…changing password for ADMIN
wwv_flow_security.g_security_group_id := 10;
*
ERROR at line 5:
ORA-06550: line 5, column 5:
PLS-00201: identifier ‘WWV_FLOW_SECURITY.G_SECURITY_GROUP_ID’ must be declared
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored
ORA-06550: line 6, column 5:
PLS-00201: identifier ‘WWV_FLOW_SECURITY.G_USER’ must be declared
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored
ORA-06550: line 7, column 5:
PLS-00201: identifier ‘WWV_FLOW_SECURITY.G_IMPORT_IN_PROGRESS’ must be declared
ORA-06550: line 7, column 5:
PL/SQL: Statement ignored
ORA-06550: line 15, column 31:
PLS-00201: identifier ‘WWV_FLOW_SECURITY.STRONG_PASSWORD_VALIDATION’ must be
declared
ORA-06550: line 15, column 5:
PL/SQL: Statement ignored
ORA-06550: line 41, column 23:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 40, column 16:
PL/SQL: SQL Statement ignored
ORA-06550: line 46, column 32:
PLS-00364: loop index variable ‘C1′ use is invalid
ORA-06550: line 45, column 9:
PL/SQL: Statement ignored
ORA-06550: line 53, column 5:
PLS-00201: identifier ‘WWV_FLOW_SECURITY.G_IMPORT_IN_PROGRESS’ must be declared
ORA-06550: line 53, column 5:
PL/SQL: Statement ignored

 This was because I used the wrong version of the apex code, make sure you use 4.1.1. You have been warned :)

You should now be able to connect to the server and finish the install.

Just for reference the urls for the listener administration are

http://FQDN:8080/apex/listenerConfigure

http://FQDN:8080/apex/listenerAdmin

http://FQDN:8080/apex/listenerStatus

The URL for the main configuration will be something like this

http://FQDN:8080/apex/f?p=4550:1:2640709286573801

What you probably will get it a Blank White screen,(or for Google purposes Apex blank screen),  this has something to do with path statements and where images are stored. Believe it or not it is to do with and directory that is called “i”. Go figure. It then dawned on me we need to install a Glassfish server anyway in order to run this.  Luckily that clever bloke at Survivalguides has already done the hard work, so plagiarism here we come, can you plagiarise your own work? (answers on a postcard)

But what you have proved is that you have a connection, that’s a good thing.

We can though get this bit working to prove it can work for real, with this configuration.

cd /opt/apex

ln -s /opt/apex/images/ i

Now get the listener started

java -Dapex.home=/opt/apex -Dapex.images=/opt/apex/images -Dapex.erase -jar /opt/apex_listener/apex.war

You should now have a APEX workspace to log onto.

 

Like I said to make this a bit more robust we really need the Glassfish installation or the Oracle HTTP server. Well I am feeling overly generous today and will try both. Glassfish is easy as I have already done this. So we will try the HTTP server to see what that is like.

 

Oracle HTTP Server

As per usual this will be a combination of the Oracle documentation and what they leave out :)

To download the server head off to

http://www.oracle.com/technetwork/java/webtier/downloads/index2-303202.html

The help and installation documents are here

http://www.oracle.com/technetwork/middleware/ias/index-091236.html

 

So lets get started

mkdir -p /opt/software/oracle_http

cd /opt/software/oracle_http

Please come back for more on this area as I have run out of time, but if you need your environment up and running try the Glassfish installation :)

 

Glassfish installation

You can find this at Installing a Glassfish Server for APEX access in Amazon AWS

 

 

 

About these ads

,

  1. #1 by monagmail on May 5, 2014 - 9:42 am

    Thank you sooo much ….. that was really helpful :)

  2. #2 by Steve Pizzuti on May 5, 2013 - 5:42 pm

    Spot on with this write-up, I seriously believe that this web site needs a great deal more attention.
    I’ll probably be back again to see more, thanks for the information!

  3. #3 by anderson7-1.newsvine.com on April 16, 2013 - 10:28 pm

    Hi, after reading this remarkable article i am also delighted to share my know-how
    here with mates.

  4. #4 by SutoCom on October 24, 2012 - 3:51 pm

  1. Installing Oracle 11g XE and Apex 4.11 in Amazon AWS « The Survival Guides's Blog
  2. Configuring the APEX workspace « The Survival Guides's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Survival Guides's Blog

How to Survive IT and Holidays

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: