Thursday, September 23, 2010

For my own sanity

by Richard Vowles

Oracle XE and EC2/EBS instances was not a fun time. Oracle being at fault here.

So I needed an XE instance and rather than downloading and installing a VM specifically for this particular test, I figured an easier way would just be to choose an Ubuntu VM AMI image and set it up there.

So some things to remember:

  • Make sure you use a 32bit i386 AMI (or it won't install)
  • When it starts up, you won't have enough swap to install XE, so add more
  • Make sure bc is installed (sudo apt-get install bc)
  • Follow the instructions for installing Oracle XE here and here

So the last bit which is really the difficult thing to find is that the listeners will kill you. When Oracle XE installs, it hard codes the machine name somewhere. So if you shut down and start up again, you get it failing the listener.

 

So you need to go into $ORACLE_HOME/network/admin and edit tnsnames.ora AND listener.ora and change the hostname from ip-whatever to localhost. But that isn't enough - Oracle itself will still screw up as it registers with the original name as installed each time - so you won't be able to connect to the listener, even though diagnostics imply it worked. So you need to look into the log (which is in $ORACLE_HOME/network/log) and you'll see something like this:

22-SEP-2010 20:05:55 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ip-10-203-55-158)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0

That HOST=ip-10-203-55-158 - thats the domain name originally registered against your machine. Take that name and edit /etc/hosts (sudo vi /etc/hosts) and add it into localhost's config:

 

127.0.0.1 localhost ip-10-203-55-158

Then restart your Oracle XE

sudo /etc/init.d/oracle-xe restart

By the way, I'm using a script to tunnel to the box (so Oracle is never exposed to the internet)

 

#!/bin/sh
ssh -i KEYNAME.pem -L 8080:localhost:8080 -L 1521:localhost:1521   ubuntu@$1

 

This shows up as
21-SEP-2010 10:05:20 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=47502)) * establish * XE * 12505
TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
failure when trying to use jdbc to connect using the typical url: jdbc:oracle:thin:@localhost:1521:XE when the IP address keeps changing.
(I added all that in for anyone using a search to find this page)