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.

MERGE to table with Virtual Columns + Error Log Table = ORA-03113
Are You Ready? Upcoming Conference in Perth

Comments

  1. Thank you very much, well presentation and very much effective.
    i am benefited from this sharing.

  2. Dear Jeff,

    Thanks for sharing this article. I am from Taiwan. How do you think about the open bug 11738319:

    http://docs.oracle.com/cd/E17781_01/doc.112/e21743/toc.htm#CIAEIIAJ

    Johan

    • Hi Johan,

      I haven’t experienced the bug you referred to, so I have no opinion on it at this time.

      Anything I can help you with?

      Cheers,
      Jeff

  3. Hi Jeff,

    I follow the same steps to create my EC2. But I can’t login with ec2-user. The server response ““Server refused our key”. Do you know why? Thank you very much.

    Johan from Taiwan

    • I haven’t come across that problem. Did you use the PPK file (not the PEM) file? I don’t think ec2-user is created on this instance. Try logging in as root instead.

  4. Dear Jeff,

    I have a question about Oracle XE License Agreement. If I use Oracle XE to develop my member system and shopping cart for commercial purpose. Do I violate the (a) rule:

    “We grant you a nonexclusive, nontransferable limited license to use the programs for: (a) purposes of developing, prototyping and running your applications for your own internal data processing operations;…”

    http://docs.oracle.com/cd/E17781_01/license.112/e18068/toc.htm

    Thank you very much.

    Johan

    • Hi Johan,

      Great question! I’m not a lawyer so cannot give you a definitive answer, unfortunately.

      The full terms from the link you refer to include:

      “We grant you a nonexclusive, nontransferable limited license to use the programs for:
      (a) purposes of developing, prototyping and running your applications for your own internal data processing operations;
      (b) you may also distribute the programs with your applications;
      (c) you may use the programs to provide third party demonstrations and training; and
      (d) you may copy and distribute the programs to your licensees provided that each such licensee agrees to the terms of this Agreement.”

      While these terms don’t appear to explicitly exclude commercial purposes (e.g. they may be used for internal data processing operations, i.e. within your company), they don’t seem to include their use for building external commercial applications. However, the opinion on Oracle 10g XE (which has a very similar license agreement) is that commercial use is fine – see http://forums.oracle.com/forums/thread.jspa?threadID=700100. This is probably in the context of using Oracle XE internally within a company as a database, while exposing a separate interface to the outside world.

      But, I’m not a lawyer so the above is not advice, merely opinion.

      Jeff

  5. Excellent tutorial! I have been down this road myself more than once. I have run into one issue that used to drive me absolutely nutters until a moment of inspiration a week ago led to a solution.

    Every time you restart your EC2 instance, you get a new host name. Every time you get a new host name, your Oracle Listener fails because it’s configured by host name. So I was updating the listener.ora file after every restart (reboots are not a problem because the host name doesn’t change). Since I am mostly broke, I couldn’t leave the instance running 24/7.

    So I copied the listener.ora file into a file called listener.template. Then, in the template file, I changed the host name to ‘myName’. I wrote a shell script that uses sed to find and replace ‘myName’ with the current host name. Finally, the script restarts Oracle 11 XE and the listener.

    Once I got the script working, it was then a matter of configuring it to run at run level 3 on every start up, which, depending upon your Linux, can be really easy or rather not easy. I use SuSE 11, so I had to learn how to create a ‘service’ script, save that script to /etc/init.d, create a link to it in /etc/init.d/rc3.d, then execute a chkconfig -add .

    Here is the script:

    #
    # changeHostname
    # ————–
    #
    # 22 March 2012
    #
    # bash script to change the hostname in the LISTENER.ORA file
    # this needs to be done every time you launch your AWS cloud machines
    # that are not permanent (a.k.a expensive).
    #
    #
    LISTENER=’/u01/app/oracle/product/11.2.0/xe/network/admin/’
    NEWNAME=`/bin/hostname`

    sed “s/myName/$NEWNAME/” ${LISTENER}listener.template > ${LISTENER}listener.ora

    /etc/init.d/oracle-xe restart

    You need to change the LISTENER variable to the location of your listener.ora file.

    I have the other scripts too if interested.

Leave a Reply

Your email address will not be published / Required fields are marked *