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.
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.
[Previous]
Disclaimer: don’t blame me if you follow any of the instructions here and get yourself into a right mess – think of me as a helpful but aloof guide who occasionally gets his words mixed up 🙂
I can access Apex from the Linux box, but not from my WinXP machine across the network. This is because the firewall in Linux by default blocks most ports. I’ve got APEX (using EPG) listening on port 8080, and Apache web server (for the static web pages and images) listening on port 8000, so I open up those two ports (System menu -> Administration -> Security Level & Firewall).
The router, by default, assigns IP addresses dynamically via DHCP to each device that connects to it; in order that I don’t have to keep logging into the router to see what today’s IP address is, I need to tell the router to reserve an IP address for the Linux box. To do that I log into the router’s administration page (for mine it’s http://10.1.1.1), and examine the DHCP settings page. There I find that two devices are connected, each with a unique MAC address, and with the IP address currently assigned to them. One is the WinXP machine, the other is the Linux box. I happen to know which one is which, but I suppose if I didn’t know I could have just disconnected one of them and seen which one disappeared from the router. Anyway, I copy the MAC address and tell the router to assign it a static IP address (in my case, 10.1.1.3) (a word of advice: on some routers you have to also change the settings that specify the range of dynamic IP addresses that can be assigned by DHCP, so that they don’t conflict with the static IP addresses; in my case, my router does not work that way).
By the way, if my explanation of this doesn’t help you, just google “static IP address” – you’ll find heaps of guides around – keep reading, and it’ll all make sense…
Now I can access it from WinXP via http://10.1.1.3. For convenience I add a line like the following to C:\windows\system32\drivers\etc\hosts:
10.1.1.3 linuxpc linuxpc
This means I can access it via http://linuxpc, at least from WinXP. The equivalent file on linux, used in exactly the same way, is /etc/hosts [Wikipedia: Hosts file].
What I want now is for friends and family to get to it from the outside. For security reasons there’s a number of things blocking that access which I need to take care of. Also, I want the URL that I give out to be relatively simple; a longish URL with strange numbers and characters can be intimidating and easy to get wrong. Also, at least one of my friends works at a place that seems to block different ports (e.g. 8000). So I want them to get in on port 80, which is the default for HTTP and doesn’t need to be specified in their browser.
At the moment the router ignores mosts requests from the outside world; it only exists to serve the little network connected locally to it and couldn’t care less about my friends and family. I need to make it just a little more friendly. First thing, I need to get a port mapped. In my router admin I navigate to the Port Forwarding (called “Virtual Server” on my router; on your router it might appear in “Network Address Translation (NAT/NAPT/PAT)” or “DMZ host”) section, and select the LAN IP I wish to map the port to (in my case, 10.1.1.3). This router gives me a whole lot of preset ports for various games and applications, but I want to do something different, so I select “User” and click “Add”. Here is where I can create a Rule for mapping ports. I want the router to accept TCP traffic on port 80 (the default for web stuff) but send it to port 8000 on the Linux box. So I give it a name (e.g. something imaginative like “Port80to8000”), select TCP for the Protocol, put in 80 for both Port Start and Port End, and pop in 8000 for the Port Map. I don’t need a range so Port Map End stays blank, and click Apply. Now, I just Add my new rule (Port80to8000) to the list of rules for 10.1.1.3, click Apply, and reboot the router. Now, traffic coming in from the outside on port 80 should be sent to port 8000 on the Linux box.
We’re not out of the woods yet, though, there’s a few more things blocking external access. Firstly, iiNet by default block any incoming traffic on port 80, as well as a few other ports, which is good from a security point of view, but doesn’t help me much, so I go into my account management page and switch this option off.
(Once that’s all done, I can test that external access to the port is working, by logging into the machine I want to test, and plugging 80 into this online tool: CanYouSeeMe.org – Open Port Check Tool.)
Finally, and this is the biggy, to get to my site from the outside, people need a URL; or at least, an IP address they can use (that won’t change from day to day). Now, I don’t have a domain name, nor do I have a static IP address – all I get is a dynamic (i.e. can change without notice) IP address assigned by iiNet. I could upgrade to a business broadband account with a static IP address along with a nice domain name, but I want to do this on the cheap, so I don’t.
Instead, I use DDNS (Dynamic DNS). I learned about this from an excellent article by Nathan Taylor in PC User magazine (May 2007). There’s a few sites out there that do this, the one I use is www.no-ip.com which provides a basic service for free. I sign up, get a domain name of my own (I won’t tell you what it is but it looks like mycomputer.no-ip.info), and plug in the settings into the DDNS page on my router.
The way this works is: whenever someone types in my domain name (e.g. http://mycomputer.no-ip.info) into their web browser, the request goes to no-ip.com. Their computer looks up their database for the current IP address for “mycomputer.no-ip.info”, and then forwards the request on to it. This process is pretty much seamless and without any overhead that I’ve noticed. Whenever iiNet change my IP address, my router sends a message back to no-ip.com with the new IP address and their database is updated so that future requests are passed on correctly.
If your router doesn’t support DDNS, don’t worry – you can get software that will run on a computer on your network, and it will do the same job – whenever the IP address changes, it will notify no-ip.com of the change.
After all that fun, I tried to test it from my WinXP box, but I just couldn’t get it to work. As it turns out, you just can’t access the local network using the external address from within that network – apart from using www.canyouseeme.org to check the port, you have to test your web site from outside your network. So I used my “phone a friend” lifeline and got him to try it out.
You guessed it, it worked first time. Ha! Well,… if I said that I’d be lying. It didn’t work first time, the actual process of working all of the above out was a little bit more bumpy than I’ve made out; but in the end it was all working, and I learned a great deal in the process.
There’s one more thing (oh no, I hear you moan…). You’ll notice I’ve only mapped port 80 to port 8000, which is the Apache web server, and not port 8080, which is APEX. That’s the subject of tomorrow’s post, so stay tuned!
[Next]
[Previous]
Ok, I’ve got all the hardware I need (took a few minutes with a vacuum cleaner to carefully suck out as much of the dust that was caked in there), and hooked it all up. The computer starts up ok, but it needs an OS – the hard disk was wiped clean of all government secrets, as expected.
I downloaded Oracle Enterprise Linux release 5 from OTN [link]. I choose the x86 32 bit version, Release 5 Media Pack. It comes as five ISO images (ignoring the four Source ISOs), which I burned onto five CDs (just happened to have some blanks lying around).
Pop the first CD into the tray and hope nothing’s broken. It works! It’s alive!
Oracle Enterprise Linux welcomes me into its world and I recklessly accept all the defaults. It’s successfully detected all the hardware, and after the last CD has been inserted the transformation from boring ex-government PC into a shiny clean Linux box is complete.
A bit of a brush up on linux commands (Guide to Linux File Command Mastery), and now it’s play time!
[Next]
Seeing how Oracle XE and Linux is free, I want to see for just a minimum of outlay I could get a working database and web server running. I am also keen to give Linux a try; I’ve been a Windows user ever since my dad upgraded his computer from MS-DOS 3.3 to Windows 3.1, and while I’ve always worked for companies that used Unix I’ve never really had to learn much about it.
My goal is to get Linux up and running on the cheapest hardware I could find (free, if possible), and to run Oracle XE, the free version of the Oracle database, which includes Application Express. On the same machine I want to set up a web server to serve static web pages and streaming media.
My plan is as follows:
- Obtain a barebones machine to be the server. Must be free, or dirt cheap.
- Install Oracle Enterprise Linux on it.
- Install Oracle XE 10g R2.
- Upgrade APEX to version 3.0.
- Set up the router for external access.
- Migrate all Apex applications from the Win XP box.
In the next few posts I’ll describe how I went on all six points.
[Next]
