Installing Oracle 11gXE on Amazon Elastic Cloud

There is officially now no good excuse for not running your own instance of Oracle 11g and getting into APEX 4 development, or just playing with SQL and PL/SQL.

Note: another easy-to-follow guide to this process was published back in April by Alex Gorbachev here: http://www.pythian.com/news/22045/how-to-get-started-with-amazon-ec2-oracle-11g-xe-example/, which may suit your needs – although it is based on an AMI with a beta release of 11gXE pre-installed. Some of the instructions below are virtually identical to his.

1. Gather your software.

You’ll need:

What, no OS, you ask? Nah – the easiest way to get started is to start with an AMI (Amazon Machine Image) that has linux installed.
Install PuTTY and your SCP/SFTP client on your computer, then:

2. Get your Amazon EC2 account.

Go to http://aws.amazon.com/ and click “Sign Up Now”. You’ll need an EC2 account and you’ll need to give them some credit card details.
Navigate to the Amazon Elastic Compute Cloud (EC2) in the AWS Management Console.

3. Launch Instance.

Click the “Launch Instance” button. Go to “Community AMIs“. Change the “Viewing” filter to “64-bit“. In the search field type “oracle“. You should see a long list of AMIs to choose from. What you’re looking for is an EBS-backed AMI running Oracle Enterprise Linux 5. The actual name and AMI ID will vary from region to region, but in US-East you could use “493731438004/Oracle Linux 5.6 x86_64 – OVM”, AMI ID ami-42778a2b (you could enter this ID into the search field to go directly to it), or if you’re using the Singapore region you could use AMI ID ami-bc2f56ee.
Next, you get to choose the size of the instance. The bigger the machine, the more grunt you get, but also slightly more expensive to run. You could choose Micro, but you might find it a bit slow at times. Of course, there’s nothing stopping you from trying one, and if you don’t like what it offers, just trade up!
On the “Advanced Instance Options” page, accept all the defaults and Continue.
On the next page, you can put a name on your instance if you like. This is just a label, and comes in handy when you run many instances.
On the “Create Key Pair” page, you need to select a Key Pair. You can create one if you don’t already have one.
Enter a name for your key pair, then click “Create & Download your Key Pair“. Make sure you keep the file (it’ll be a .PEM file) in a safe place – if you lose it, you’ll lose access to your instance (in which case you’d just have to terminate it and start again).
The next page is the “Configure Firewall” step, where you select a Security Group. You could create a new one if you like – open up TCP ports 22, 8080 and 1521 so you can upload files, access the Apex web interface, and connect to the database (e.g. from SQL Developer).
On the final page, click “Launch”. After maybe a minute or two, your instance will have been started and running, ready for you to connect to it and do stuff on it:

Here, you can find the following useful information:
Status: this will say “Pending”, “Running”, or some other status.
Public DNS: this is the URL you can use to connect to your instance: in my case, it is ec2-72-44-48-28.compute-1.amazonaws.com.

4. Set up your key pair.

Before you can connect to your instance with PuTTY or your SCP client, you’ll need a PPK file. You generate this from the PEM file you got before. In the folder where PuTTY is installed is a little tool called PUTTYGEN.EXE. Run this, select Conversions -> Import Key, select your PEM file, then click Save Private Key. It might warn you about a passphrase – if you want to protect the key in case someone manages to steal it then you could put one in, but personally I don’t. Save it somewhere safe as a PPK file.

5. Upload the Oracle installer to the instance.

Open WinSCP. Click New. Copy the Public DNS into Host (e.g. ec2-72-44-48-28.compute-1.amazonaws.com). Set User name to root. Leave the Password blank. Select your Private Key file (PPK). Save the settings then click Login. If you get a warning about the “server’s host key not in the cache”, just click “Yes” to continue.
If you get timeout errors, check that port 22 has been open (and your changes saved) on your AWS Security Group.
Upload the Oracle installer (oracle-xe-11.2.0-1.0.x86_64.rpm.zip).

6. Login to your instance.

Open PuTTY. Copy the Public DNS into Host Name, and select your Private Key file (PPK) (this option is tucked away under Connection/SSH/Auth). You can save the session if you want. When you Open the connection, it will prompt for the login. Login as root.

7. Install Oracle.

You can follow Oracle 11g XE installation instructions to install and configure Oracle, which honestly are really easy and straightforward:
http://download.oracle.com/docs/cd/E17781_01/install.112/e18802/toc.htm

If you just want to know what to type, here is basically all you need (type the commands after the “#” – don’t type the “#” itself though):

# unzip /root/oracle-xe-11.2.0-1.0.x86_64.rpm.zip
# rpm -ivh /root/Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm
# /etc/init.d/oracle-xe configure

This script will prompt you for the APEX port and the database port – just press Enter to accept the defaults (8080 and 1521, respectively). It will then ask for the password for SYS and SYSTEM. Finally, it will ask you if you want it to run on startup. Accept the default (yes).

8. Disable the firewall.

# chkconfig iptables off
# service iptables stop

This disables the internal firewall running in your linux instance.

9. Log into APEX.

Open your browser and point it at your instance. Take the Public DNS and add :8080/apex/apex_admin, e.g.:

http://ec2-72-44-48-28.compute-1.amazonaws.com:8080/apex/apex_admin

The login name is admin, and the password is (for now) the same password you entered for the SYS and SYSTEM accounts earlier.

It will prompt you to change the password. It has to be a fairly secure – you’ll need at least a letter, number and symbol, and you can’t include the login name. After changing your password, you’ll need to log in again but using the new password.

You now have a fully-fledged Oracle Application Express environment, version 4.0.

10. Create a Workspace.

The last thing you need to do before you can start creating applications and stuff is to create a workspace. Click “Create Workspace” and answer the prompts to create the workspace along with its own schema and admin user account.

Log out of APEX Administration, and log into the newly created workspace – and you will now be able to start creating applications, tables, and what-have-you. You can also use APEX to write code (such as procedures and packages), but I suggest you use Oracle SQL Developer for this kind of thing because it’s a bit more powerful.

From now on, to get back into your APEX development environment, you can use this url:

http://ec2-72-44-48-28.compute-1.amazonaws.com:8080/apex

Have fun!
Oh, by the way: when you’ve had your fun, and you want to stop your instance (which at about 34c/h may cost around $60 per week if you leave it running 24×7), don’t forget to terminate it from AWS Management Console. Before you do, however, you can take a snapshot of it in its current state so you can restore from it later. (Before this, you might want to shut down the Oracle database within the instance first – although I’ve found it quite easy to recover an instance which was snapshotted while the database was up and running). To do that, right-click on the instance in AWS Management Console, and select “Create Image (EBS AMI)”. It may take some time for the image to be created. When the image creation is complete, you can then Terminate the instance. The AMI will take up a bit of storage that will cost a dollar or two per month; and you can now spin up as many copies of your machine whenever you want.

Update:

Lew posted an excellent introduction to PuTTY and SSH for AWS Newbies that I heartily recommend. It was posted in 2009 but still applies.


Converting instance-store to EBS-backed on Amazon

For a long time my disaster recovery procedure for my Amazon EC2-based web site was:

  1. Find an Oracle AMI that has 10g XE with APEX pre-installed, and start up an instance with it.
  2. Create a volume from a backup snapshot and attach it to the new instance.
  3. Log into the instance, shut down apache and oracle, then delete all the oracle data files, apache config files, and a few other bits and pieces.
  4. Create symbolic links for the deleted bits and pieces (including the oracle data files) that point to the attached volume.
  5. Start up oracle and apache and test.
  6. Switch the elastic IP over to the new instance.

This procedure has been tested and retested multiple times, and came in useful once when I almost lost the site (actually, it just went unresponsive due to some general problems at Amazon, but at the time I thought it’d had gone down).

Last week I logged in to the AWS Management Console to do a routine backup-and-restore test, but discovered a problem: it couldn’t find the Oracle 10g XE AMI. Searches on the image ID and various keywords, across all the Amazon regions, returned no results. Searches on “oracle” brought back a number of options but none of them close to what I required. I enquired with Amazon and they responded that the AMIs are supplied by Oracle and had been removed. I discovered this meant that not only could I not start up an instance using one of these images, I also couldn’t point to my running instance and “start up another like this” – because this requires access to the original AMI that was used to start it.

The machine image which I was trying to find is (at least, as of today 5 Jul 2011) still referred to here: http://aws.amazon.com/amis/Oracle/1720 “Oracle Database 10g Release 2 Express Edition – 32 Bit” so I raised a question on the oracle forum (http://forums.oracle.com/forums/thread.jspa?messageID=9707298&#9707298) and sent an email to Bill Hodak at Oracle who was named in the description of the AMI. He replied he would see if he could find out what had happened to it.

At this point I was hoping that my running instance wouldn’t go down, because I didn’t know if I’d be able to restore from backup. My backup consisted solely of snapshots of just the data – the rest of the OS was supposed to be provided by the AMI.

Meanwhile, asam replied to my oracle forum thread, suggesting I create my own AMI. A bit of googling yielded this result, which proved very helpful: http://webkist.wordpress.com/2010/03/16/creating-an-amazon-ec2-ebs-ami-from-a-running-instance/ “Creating an Amazon EC2 EBS AMI from a running instance”. I followed the instructions, slightly modified as follows:

  1. Use AWS Management Console to create a new volume
  2. Attach the volume to my running instance and mount it:
    # mkdir /u03
    # mount -t ext3 /dev/sdf /u03
  3. Move everything from the old volume so that it all sits under / again instead of via symbolic links
  4. Synchronize the filesystem to the new volume:
    # rsync -a --delete --progress -x / /u03
  5. When rsync has completed, fix up the devices:
    # MAKEDEV -d /u03/dev -x console
    # MAKEDEV -d /u03/dev -x zero
    # MAKEDEV -d /u03/dev -x null
  6. Unmount the volume:
    # umount /u03
  7. Get the EC2 X.509 cert and private key from the “Security Credentials” area under “Account” in AWS Management Console.
  8. Download the Amazon EC2 API tools:
    http://aws.amazon.com/developertools/351
  9. I needed java to run the API tools, so download the rpm:
    jre-6u26-linux-i586.rpm – e.g. from http://www.oracle.com/technetwork/java/javase/downloads/jre-6u26-download-400751.html
  10. Upload the EC2 X.509 cert and private key, the Amazon EC2 API tools, and the java rpm to the instance. Unzip and install the API tools and the java rpm.
  11. Set up all the required environment variables (replace xxxwith the appropriate bits from the relevant file names):
    # export EC2_CERT=/root/cert-xxx.pem
    # export EC2_PRIVATE_KEY=/root/pk-xxx.pem
    # export EC2_HOME=(path-to-ec2-stuff)
    # export JAVA_HOME=(path-to-java-stuff)
    # export PATH=$PATH:$EC2_HOME/bin
  12. Set up a symbolic link so that the EC2 tools can find java:
    # ln -s (path-to-java-stuff) /usr/bin/java
  13. Back in the AWS Management Console, create a snapshot of the volume.
  14. In the instance, run this command (this is the only command you can’t do in the management console, which is what all that rigmarole about installing the API tools was all about):
    # ec2-register --snapshot snap-xxx   --description "my ami description" --name "my ami name"
      --ramdisk ari-yyy --kernel aki-zzz   --region ap-southeast-1

    You can get the snapshot, ramdisk and kernel identifiers from the AWS Management Console. (my instance was running in Singapore, so my region is ap-southeast-1)

  15. Back in AWS Management Console, I see my new AMI has been created. All I have to do now is select it, click Launch Instance, and a copy of my site is up and running.

I startup the database and see if it’s working. Unfortunately it isn’t – a bit more investigation revealed that the Oracle listener was not responding to requests. lsnrctl status reveals that it is still using the old internal IP address from the original instance – but this is a new instance with a different internal IP address.

To fix this, I edit listener.ora to correct the IP:

# cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin
# chmod +w listener.ora
# vi listener.ora

The IP address is listed as the “Private IP Address” on the instance in AWS Management Console.

# lsnrctl start

After that, it’s all working – and very soon I will have a much simpler (and hopefully somewhat less reliant on the kindness of big corporations) disaster recovery process. I just need to work out the simplest way to restore the data from backup to the new instance. I’ll probably just create a new volume from a backup snapshot, attach it to the instance, and copy all the data across.

UPDATE: With an EBS-backed volume, I can now create a new AMI from the running instance whenever I want – it takes a complete snapshot of the instance, from which I can then create new instances. So my disaster recovery procedure is much simpler than it was before 🙂


APEX Tip: How to fix the Date Picker icon position

I was playing around with the layout of my APEX form, while at the same time upgrading the date items from the Classic date items to the new Date Picker items, and was rewarded with this:

The simplest way to fix this is to edit the Item, specifically the HTML Table Cell Attributes – set to NOWRAP:

Viola, problem solved:

June 2016: Update for APEX 5 Universal Theme

I was alerted by  that the above solution does not work with the Universal Theme in APEX 5.

datepickerwrap

I had a go at trying to workaround it. I tried adding “white-space:nowrap” to various levels of divs around the item, which stopped it from wrapping the icon, but it kept hiding the icon behind the label div for the next item.

So through the magic of twitter we got a CSS solution proposed by :

.t-Form-inputContainer { white-space: nowrap; }
.ui-datepicker-trigger { right: 25px; }

datepickernowrap

This takes care of the wrapping, and fixes the problem with the icon being hidden. The only downside is that when the screen is very slim, this css causes a gap to be shown on the right-hand side of the icon; this is not too big a deal but I suspect with the right media query syntax that could be fixed as well.

datepickerslim


APEX role in Perth

Looks like someone’s looking for an APEX developer here in Perth.

Unfortunately for me, I don’t have any experience in “Apex 3.5” – I hadn’t even heard of that version…

http://www.seek.com.au/job/19123893?cid=jobmail


APEX Browser Requirement

Whereas APEX 3.2.1 (and earlier, I think) required one of:

  • Microsoft Internet Explorer 6.0 or later version
  • Firefox 1.0 or later

APEX 4.0 requires:

  • Microsoft Internet Explorer 7.0 or later version
  • Mozilla Firefox 3.5 or later version
  • Google Chrome 4.0 or later version
  • Apple Safari 4.0 or later version

While they’ve dropped support for IE 6, it does mention that you can still develop applications that will work perfectly well on the older version. Also, it’s good to see Oracle officially supporting more browsers, including Chrome. I’ve been using Chrome to develop APEX applications for quite a while now, and the only problem I’ve encountered is that the procedure editor wouldn’t work. I’ve tried it in APEX 4.0 using EPG and it seems to work fine – so that’s good. I doubt APEX will ever really replace SQL Developer but it’s already useful for many development and maintenance tasks.



Customising a Navigation List

I like the “Vertical Images List” in APEX, which allows me to create a navigation bar of icons to give users quick access to various pages in my site. It’s easy to customise each item – you can select any image, add attributes for the image if necessary, and each item in the list has a URL which can point to another page in the application, or to an arbitrary URL.

My problem, however, was that some of the URLs in my list took the user to another site, or opened a PDF, and these would open in the same window. I wanted these particular items to open a new window, but the navigation item properties don’t allow this.

To solve this, I modified the Vertical Images List template, and used one of the User Defined Attributes to add “target=_blank” to the items that I wanted. While I was in there, I made a few tweaks to customise the template further to my liking.

A. Modify the Vertical Images List template.

  1. Go to Shared Components and open the Templates (under User Interface).
  2. Scroll down to Vertical Images List (in the Lists category) and open it for editing.
  3. Modify the Template Definition (WARNING: the code for different Apex templates may differ slightly; you’ll have to use a bit of nouse to customise it to your requirements) – you can add bits like #A01#, #A02#, etc – in my case I’ve used the following convention:

#A01# = extra text to appear below the icon & link;
#A02# = tooltip text for the hyperlink;
#A03# = extra attributes for the link (HTML <A> tag).
I’ve done this in both the “List Template Current” and “List Template Noncurrent” sections.

Customising the Vertical Image List template

For example, for “List Template Noncurrent, I’ve modified the template code as follows:

<tr><td><a href=”#LINK#” TITLE=”#A02#” #A03#>
<img src=”#IMAGE_PREFIX##IMAGE#” #IMAGE_ATTR# />#TEXT#</a>
<BR>#A01#</td></tr>

B. Set User Defined Attributes.

  1. Open the Navigation List for editing (Shared Components -> Navigation -> Lists).
  2. Open the list item for editing that you wish to customise (or create a new one).
  3. In User Defined Attributes, attribute 1, add any text you wish to show beneath the link (but not highlighted as part of the ink)
  4. For attribute 2, add the title you wish to show up as a tooltip.
  5. For attribute 3, add the html attribute “target=_blank” if you wish this navigation entry to open a new window when invoked.

This is how it looks in a sample application:

If the user clicks on “Address Book”, the “target=_blank” attribute instructs the browser to open in a new window (or tab, in some cases).


Auto-print a page in APEX

My web site accepts applications for a sports team, and the last step is the applicant needs to print out a form to be signed. To make things as simple as possible, I want this form to send itself to their printer as soon as they open it. Now, I’m not a javascript expert; but instead of googling for the code, I stole it by doing a View Source on Google Mail’s print feature.

To get any APEX page to print when it is opened, all you need to do is add two bits to the page definition:

HTML Header

function Print(){document.body.offsetHeight;window.print()}

Page HTML Body Attribute

onload="Print()"

Isn’t javascript easy? I’m not sure what the “document.body.offsetHeight” is all about but I suspect it’s something to do with waiting for the whole page to load and render before the print starts.


Why EAV is bad, mostly; and why it is used so often

Tom Kyte offers some astute observations regarding EAV data models in general.

Interesting to see the justification for entity-attribute-value data models from their use in the design of Oracle Application Express. I’d say I’d have to agree – the site I’m at currently is chocas with name/value pairs, which works fine when querying individual bits of data for the UI, but works miserably for reporting and batch jobs.


Googlebot boosts APEX performance

Every day it seems, Googlebot scans my site. You wouldn’t think that would affect the performance of the site, or if at all, would make it worse, right?

A few days ago I used APEX’s Page View Analysis, Weighted Page Performance (under Home > Administration > Monitor Activity) to see which pages I should focus on to improve performance. The top page was my home page, which was taking 0.9 seconds to load on average. There were other pages that were taking longer to load, but the home page was the most common entry point so it had a higher “weighting”.

So I loaded up the page while logged in as a developer and clicked the “Debug” button at the bottom of the page. It showed that all the regions on the page were rendering in about 0.01 seconds each (sometimes a bit more), except for one region – a region that pulled a small bit of XML from another website (“Verse of the Day” via BibleGateway.com) and displayed it – it was consistently taking 0.5 – 0.8 seconds to load.

The “Verse of the Day”, obviously, only changes once a day, but for some reason, in spite of the Cache settings on the region, it was still going back to the source every page view (side question: have you noticed that in Debug mode, it seems no regions are found in the cache? – perhaps this is by design?).

Oh well – I was thinking that I’d like to store the text that is being shown in that region anyway, so I created a simple table to store the text, then rewrote the PL/SQL to get the text from the table instead; and if not found, only then does it go to BibleGateway.com to get the VotD.

After the first pageview loads the verse into the cache, subsequent pageviews are taking less than 0.1 seconds total, so that’s solved – except for the very first visitor to the site of the day, who will have to wait a whole 0.9 seconds.

However, as I discovered this morning, looking at the logs, it appears Googlebot is the one who is suffering – sometime after midnight most nights it scans the site, seeds the cache for me, then everyone who visits that day gets the benefit of the cache 🙂 🙂 🙂