SQL*Loader on Amazon Linux
I’m using Oracle on Amazon RDS. I want to load some data into it from an EC2 instance. SQL*Loader (sqlldr) is a reasonable way to load data into Oracle. Amazon agrees. But for someone who’s a little rusty with Oracle installation procedures, it’s a bit harder to get the SQL*Loader client installed than I had hoped.
Find the Oracle Client
I didn’t think this section would need to exist. But it was harder to find than one might expect.
First, don’t be fooled into thinking Oracle Database Instant Client will be instantly useful. Or even eventually useful. It doesn’t have SQL*Loader.
Second, don’t be fooled into thinking Oracle’s download page for Oracle 12c includes downloads for Oracle 12c. Well… it does… but it also includes the downloads for Oracle 11g. Go figure.
Third, don’t be fooled into thinking the lack of links to anything labeled “client” is a problem. Just follow the link “See All” to get to the client downloads. Of course. It’s even explained in the improbably punctuated note below the links, “- See All, page contains unzip instructions plus Database Client, Gateways, Grid Infrastructure, more”.
The “See All” link corresponding to “Oracle Database 11g Release 2 Client (18.104.22.168.0) for Linux x86-64” got me to the correct spot:
Get the Oracle Client
With the link in hand it’s trivially easy to download the installer. Not so fast. It’s possible to download the installer to my laptop and then upload it to my EC2 instance. But that’s slow, and it’s a terrible waste of bandwidth. I want to download it directly onto the EC2 instance.
The problem is that the download page requires me to accept the license terms before the download link will work, but the EC2 instance has no GUI in which to easily do this.
A naive attempt like this will fail:
The issue addressed in a blog on My Oracle Support. I’m optimistic that it ought to work as indicated. But the solution was old, seemingly brittle (failed based on locale), and strangely unofficial feeling. I don’t need to automate the process, so it’s much easier to understand with a quick manual process.
- Login. Accept the license agreement. (This is done while browsing from your local machine. (This step is genuinely easy! Hooray!))
- Get the relevant cookie. This was harder than I expected. Chrome and Firefox store their cookies in a SQLite database. Various browser extensions and database clients allow you to get at them. But I found the Chrome extension Cookie.txt export to be the simplest way to get the info. Just click the button that the extension creates and copy the complete contents of the popup.
- Save the cookie information. On the Amazon EC2 instance create a new file called cookies.txt. Paste in the text copied in the previous step. (Details are left as an exercise for the reader. Use vi or cat or whatever. If you get stuck here feel free to post a comment.)
- Run wget using the new cookie file:
wget -x --load-cookies cookies.txt -O linux.x64_11gR2_client.zip http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_client.zip
Run the Oracle Client Installation
This final step sounds trivial… but once again I realized I needed a few sub-steps. I’m using Amazon Linux which is decidedly un-GUI. I had forgotten that the Oracle Client doesn’t have a simple interactive text version. It’s all-or-nothing silent install or GUI install.
sudo yum install xorg-x11-xauth exit
Then log back in. But… don’t forget to use the -X option. I’m on Mac, so this part works easily. On Windows you can do the equivalent with PuTTY, but you’ll need to look up the details.
ssh -X -i mykey.pem email@example.com
Test that x11 will work as intended:
sudo yum install xclock xclock
If xclock pops up, then the Oracle Client installation should be good as well:
And finally, don’t forget to choose the Administrator installation type. After all, the whole point was to get SQL*Loader, and that’s the only option where it’s included.
Once you have SQLPlus installed, you’ll want rlwrap installed. It will allow you to hit the up arrow to get your command history. SQLPlus is miserable without it. The Amazon Linux repositories do not have rlwrap. But EPEL does. So here’s how to install it with a single line:
sudo yum -y install rlwrap --enablerepo=epel
Here’s a good way to transparently launch SQLPlus with rlwrap giving you access to your command history.
#Add these lines to .bashrc for both ec2-user and oracle: alias sqlplus="rlwrap sqlplus"
The first time I tried to run the install I got this error:
ubuntu@ip-10-48-138-63:~/wget_test/download.oracle.com/otn/linux/oracle11g/R2/client$ ./runInstaller Starting Oracle Universal Installer... ... >>> Ignoring required pre-requisite failures. Continuing... Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-08-06_07-26-42PM. Please wait ...ubuntu@ip-10-48-138-63:~/wget_test/download.oracle.com/otn/linux/oracle11g/R2/client$ Exception in thread "main" java.lang.NoClassDefFoundError at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Class.java:164)
This is clearly a Java problem. It clearly has nothing to do with X11. Except… well… it does. Installing xterm or x11 (installing xorg-x11-xauth as indicated above) solved it for me.