In our user group OUGN we used to collect abstracts for our annual conference with a simple Google Form.
Advantage:
Disadvantages
- No possibility for users to reconnect and correct what they had already submitted
- Lots of manual work
- Difficult to work with in the process later (selection, agenda, late submissions, special guests)
- Not really cool or cloudy
Google Forms is really just a shared spreadsheet with a form interface wrapped around it. And like any other spread sheet it is easy to work with in the beginning, but as the number of people involved increases and data grows it soon gets cumbersome to work with.
This year we moved it to Oracle Application Express (APEX). We should have done that long time ago, especially since we are an Oracle User Group...
I figured out that the Express Edition (XE) would be sufficient, even 300 abstracts with speaker info won't take all of the 11GB of space available. Next issue was to figure out where to host it. I looked around and decided that a micro-instance at Amazon Web Services (AWS) should work. It is actually located in Ireland; Hi,
Brendan!
The rest of this post is about experiences I made with running APEX in XE on AWS.
OS One of the default Red Hat Linux 6.5 images from AWS works well. You can then download the rpm-file for XE from
OTN. The rpm-file is quite complete, no need to install a lot of extra packages or install the
oracle-rdbms-server-11gR2-preinstall package.
Swap space During installation of XE the installer may complain about not enough or missing swap space. The default installation that I used did not come with much swap space, and since you don't want your database server to start swapping you shouldn't need any either. But, I like to get rid of warning messages so I created temporary swap space like this to stop the complaints from runInstaller:
dd if=/dev/zero of=/tmp/swap bs=1024 count=1300000
mkswap /tmp/swap
swapon /tmp/swap
Upgrade APEX in db The version of APEX that comes with XE 11g is old, but it is easy to upgrade. Download a new version from
OTN. Start with
3.5.2 in the Installation Guide
Installing full version of APEX Though strictly not necessary I find it easier to have the full version of APEX installed, not only the run-time version. Running the script apexins.sql took 3.5 hours on this micro-instance, so plan ahead for some other work to do during installation.
URL not found If you start getting messages like
The requested URL /apex/f was not found on this server then check the alert log, you may find ORA-04031 errors there, see next bullet.
Shared pool size too small The XE instance is configured during installation with a small shared pool. Try to increase it in small steps (to avoid swapping), our instance is now running with 250MB shared pool. We'll see how it works out as people connects to it. You may well reduce the java pool to a minimum too; set it to 0 and Oracle will allocate 1 granule for it, which is 4MB on this instance. As always, make a pfile copy of the spfile before you do any changes to the spfile that requires a restart of the instance. Certain combinations of memory parameters are not supported and I had to revert the changes a couple of times to get the instance up.
Deploying new versions in APEX I developed the application on a local database and for each round of fixes I exported the application to a file and then uploaded it to the AWS instance with build status
Run Application Only. With a good separation of data and application it was easy to deploy new versions as feedback from the user group board members came in. I also learned that in order to deploy a new version you have to change the build status to
Run and build application before you can delete or replace it. That is obvious when you've done it once...
Disable access to workspace login With the full installation installed the database will by default allow you to login to the APEX workspace, e.g. c4p.ougn.no/apex. This is typically not something you want in this case, but can be turned off easily with the following executed as SYS or other privileged user:
alter session set current_schema=APEX_040200;
BEGIN
APEX_INSTANCE_ADMIN.SET_PARAMETER('DISABLE_WORKSPACE_LOGIN','Y');
commit;
END;
/
When you need to redeploy you turn it on again replacing Y with N.
Using standard http port 80 You need to change the ownership of the listener to root and start it as root:
tnslsnr LISTENER -user 0 -group 0
You will to need to run something similar to this as SYSTEM:
exec DBMS_XDB.SETLISTENERENDPOINT(DBMS_XDB.XDB_ENDPOINT_HTTP, '10.212.42.42', 80, DBMS_XDB.XDB_PROTOCOL_TCP);
The IP address is the internal IP address for the network interface. Check with
ifconfig eth0 to see what address is being used.
Not using SSL / HTTPS Of course we could have bought an SSL certificate to protect the usernames and passwords from being sent in clear text. But there is really not much sensitive material here, if you disagree strongly I would like to hear your opinion. Most of the work was actually spent on login and account administration. I can say that the passwords are MD5 hashed before being stored in the database.
Start early with a good data model This has nothing to do with APEX, XE or AWS. It is just that you need to be reminded that everything goes smoother when you start modelling the whole thing in SQL Developer Data Modeller (SDDM) before you start developing code and APEX-application. Keep future features in mind a little without actually including them in the current data model, meaning imagine how you can expand it later for other usage like a membership administration tool. Me being not too much experienced with modelling think that a good understanding of entities and the relationship between them brings you a long way in the right direction. When Heli has finished
her book we all will become experts.
This is my first conference as a board member, and going to a presentation for every slot is not possible. I did go to Doug Burns presentation on SQL Plan Management in 11g though I had actually seen it at OOW 2011, probably because I like Doug's presentation style. This preso does not go into very much detail on SPM, but takes us on a travel through the subject and leave it to us to actually get some experience on it. It does look interesting, and the only thing that keeps me from getting much experience on it is that the current customer is lagging behind on the upgrades to 11g; the few we have on 11g are small and have not presented any problems with plan instability.