Right Outer Join

5 December 2013

Copy files between s3 buckets

Filed under: AWS, Linux — Tags: , , , , — mdahlman @ 15:06

The problem

I needed to copy files between Amazon AWS S3 buckets. This should be easy. Right?

To be clear, I wanted the equivalent of this:

cp s3://sourceBucket/file_prefix* s3://targetBucket/

The solution (short version)

No, it’s not easy.

Or rather, in the end it turned out to be pretty easy; but it was entirely unintuitive.

s3cmd cp --recursive --exclude=* --include=file_prefix* s3://sourceBucket/ s3://targetBucket/

The explanation (long version)

Get s3cmd

The best command line utility for working with S3 is s3cmd. You can get it from s3tools.org. If you’re on Amazon Linux (or CentOS or RHEL, etc) then this is the easiest way to install it.

# Note the absence of s3tools.repo in your list of repositories like this:
ls /etc/yum.repos.d/
# Put s3tools.repo in your list of repositories like this:
sudo wget http://s3tools.org/repo/RHEL_6/s3tools.repo -O /etc/yum.repos.d/s3tools.repo
# Confirm that you did it correctly:
ls /etc/yum.repos.d/

# Install s3cmd:
sudo yum install s3cmd

# Configure s3cmd:
s3cmd --configure

False start 1

s3cmd has a copy command, “cp”. Try that:

# This should do the trick:
s3cmd s3://sourceBucket/file_prefix* s3://targetBucket/

One file copies successfully… but then it crashes:

File s3://sourceBucket/file_prefix_name1.txt copied to s3://targetBucket/file_prefix_name1.txt

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    An unexpected error has occurred.
  Please report the following lines to:
   s3tools-bugs@lists.sourceforge.net
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Problem: KeyError: 'dest_name'
S3cmd:   1.0.0

Traceback (most recent call last):
  File "/usr/bin/s3cmd", line 2006, in 
    main()
  File "/usr/bin/s3cmd", line 1950, in main
    cmd_func(args)
  File "/usr/bin/s3cmd", line 614, in cmd_cp
    subcmd_cp_mv(args, s3.object_copy, "copy", "File %(src)s copied to %(dst)s")
  File "/usr/bin/s3cmd", line 604, in subcmd_cp_mv
    dst_uri = S3Uri(item['dest_name'])
KeyError: 'dest_name'

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    An unexpected error has occurred.
    Please report the above lines to:
   s3tools-bugs@lists.sourceforge.net
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Argh!! This stackoverflow answer confirms that s3cmd cp cannot handle this. (It is wrong, but for a long time I believed it.)

False start 2

This stackoverflow answer suggests “sync” as the command to use.

It is correct. But sync is not the same as copy, so this has bad side effects if what you really want to achieve is copying files. For example, sync will remove files in the target folder (to keep things in sync, duh). So syncing from source1 and source2 into a single target will cause grief. For copying all files from one location to another it’s great. I wanted to copy files, and I did not want any of the side effects of sync.

Bad alternatives

You can write your own script using boto and python or muck around with awk and getting lists of files to copy one-by-one. In principle these will work, but yuck.

You could download the files from s3 then put them back up into the intended target bucket. This is a terrible solution. It will succeed… but what a waste of time and bandwidth. What makes it so tempting is that s3cmd works exactly like you want it to work with “get” and “put”.

s3cmd put /localDirectory/file_prefix* s3://targetBucket/

If “put” is so easy, why is “cp” so hard?

Enlightenment

I studied the s3cmd options over and over. Eventually I realized “cp” had more flexibility if you look deep enough.

  • –recursive
    In my mind, my requirement is clearly not recursive. I simple want multiple files. But recursive in this context just tells s3cmd cp to handle multiple files. Great.
  • –exclude
    It’s an odd way to think of the problem. Begin by recursively selecting all files. Next, exclude all files. Wait, what?
  • –include
    Now we’re talking. Indicate the file prefix (or suffix or whatever pattern) that you want to include.
  • s3://sourceBucket/  s3://targetBucket/
    This part is intuitive enough. Though technically it seems to violate the documented example from s3cmd help which indicates that a source object must be specified:
    s3cmd cp s3://BUCKET1/OBJECT1 s3://BUCKET2[/OBJECT2]

I posted a brief version of my answer to the most elegant of technical websites. You should vote it up. But that didn’t seem like the best place to elaborate on the answer as I’ve done here.

Postscript

Amazon offers a command line interface (CLI) tool to do the same thing. AWS Command Line Interface. I swear that I looked extensively and repeatedly for exactly this saying, “I just can’t believe that Amazon wouldn’t have this by now.” Well, they do. I have no idea why I could not find it, but I’m mentioning it here for my own future reference and for anyone else who is using s3cmd as an alternative to the Amazon utility that they couldn’t find.

I have no idea if the Amazon CLI is [ better | worse | different ] than s3cmd in any interesting way regarding S3. (It’s certainly different in the respect that it interacts with many other AWS services besides S3.) If I ever need to compare them, then I’ll write it up.

 

20 November 2013

Citi doesn’t get it

Filed under: JasperReports — Tags: , , , — mdahlman @ 22:12

I received an email today with this quote:

Once you register a purchase online with Citi Price Rewind, we will search our database of online merchants for a lower price for 30 days after the purchase date. If we find a price that is at least $25 less than what you paid, you can be eligible for a refund of the difference, up to $250 per item.

This is an email from someone who deeply “doesn’t get it”. Allow me to elaborate.

“Once you register a purchase…” Citi already knows all of my Citi purchases. All of them. They bill me for them, so they have to know them. But they still make me register a purchase. This is a waste of time. It’s silly.

“… search our database of online merchants …” If I buy a $300 mixer at Target, they know. If a million other people buy the same mixer, they know. But they don’t consider these purchases. They only look at online purchases. This is intentionally incomplete.

“… a price that is at least $25 less than what you paid …” They’ll keep it to themselves if I could have gotten the same item for $23 cheaper somewhere else? This is petty and mean. If I could have saved a nickel somewhere else, then they should tell me.

“… you can be eligible for a refund …” Sweet! I’ll get a refund! Oh wait… I’ll be eligible for a refund. What?

Desired situation:
I use my Citi card for purchases. Citicard looks out for me; if they find the item cheaper then they refund me the difference.

Actual situation:
I use my Citi card for purchases. If I think to manually go to “Price Rewind” then…

  • Read 1500 words explaining the fine print of what’s covered.
  • Then provide the details about it:
    How much did it cost?
    When did you buy it?
    Where did you buy it?
    This should surely be a skit on SNL. They want me to tell them the cost of something I just bought using their card? I should tell them the date and location of the transaction that they already know? This is cynical and stupid.
  • Having selected an item and consulted a lawyer … I then …
    wait 30 days.
    Then I receive my refund.
  • Oops, no. I don’t receive a refund. I receive an email indicating that I’m eligible for a refund. I’m then invited to upload a scanned copy of my receipt. I assume you all file all of your receipts  for all purchases by date for future reference and uploading to cynical credit card offers. I do. I photocopy all of them and cross file them by date, merchant, product line, color, and average specific gravity of the products. Who doesn’t?

I appreciate the warm regards from Jud Linville. But his email inspires me to use other credit cards instead of my Citi card.

If they want to inspire me to use their credit card, then they should do something for me. It should require no effort from me. They should call within an hour of my purchase to say, “Item ABC is available for $X cheaper at store XYZ which is within 5 miles of your purchase.” They wouldn’t have to refund me a cent. But they would let me know that I could return my item and buy it cheaper somewhere else. That would be putting big data to a practical use which helps me instead of giving me useless, legalistic, nearly-impossible-to-use delayed benefits.

16 September 2013

Listen on port 80

Filed under: Linux — Tags: , , , , , , — mdahlman @ 11:44

Problem

I have an application server running on port 8080. I want it to listen on port 80. In my case it was Tomcat, but this applies to any application server.

I know this problem is somewhat common problem. I get lots of Google hits on it. But I have found that the answers are surprisingly non-great. They often assume a set of knowledge that doesn’t match with my personal knowledge. They [probably] tell me everything I need to know, but they tell me a lot more as well. This is not better; it’s hard to find what’s really important. This iptables answer on serverfault.com was really quite good. But it offers a little too much detail without offering firm enough guidance about what the best and simplest solution is. I want just one perfect answer if I can find it.

Answer

sudo iptables -t nat -A PREROUTING -p tcp --dport 80 -j REDIRECT --to-port 8080

It’s that easy. Now your app server can continue to run on port 8080. If port 8080 is open to the outside world then you’re free to connect directly to it… but you can also connect on the traditional port 80.

But… you’ll lose the change if your machine reboots. So there’s one more step. An Amazon Linux I used the following. It should be fine on CentOS and RHEL etc.

sudo service iptables save

On Ubuntu I found it easiest to persist the change like this:

sudo apt-get install iptables-persistent

Alternative Answers

There are, of course, an infinite number of alternatives. I’m more interested in having one easy-to-understand solution than having lots of alternatives. But sometime it’s useful to consider the alternatives explicitly… even if it’s only to mock and ridicule them afterwards.

Run your app server on port 80. I declare this to be a bad solution. But hey, maybe you’ve got a valid use case for this. We tracked down how to do it in the past. I found it to be difficult (grabbing those ports below 1024 is intended to be tough), and I found it to have bad side effects (some things broke on upgrades). The side effects were surely our own fault… but the ‘iptables’ solution above is much less prone to side effects. And running your application server as root in order to access port 80 opens security issues as well.

Run a web server on port 80 in front of the application server and route requests to the application server as appropriate. This is a fine solution. In fact, it’s vastly better in a bunch of ways. I have used it myself several times. It’s just overkill for many needs. Administering httpd isn’t so difficult… but it’s harder than not administering httpd.

Edit the file /etc/sysconfig/iptables manually. Yuck. Sure… you could… but why? The command ‘iptables’ exists to make your life easier. Let it.

6 August 2013

Oracle SQL*Loader on Amazon Linux

Filed under: Linux, Oracle — Tags: , , , , , , — mdahlman @ 14:22

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.

11g is listed under 12c

11g is listed under 12c

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 (11.2.0.1.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:

wget http://www.oracle.com/correct/download/link.zip

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.

  1. Login. Accept the license agreement. (This is done while browsing from your local machine. (This step is genuinely easy! Hooray!))
  2. 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.
  3. 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.)
  4. 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.

Install x11:

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 ec2-user@ec2-123-456-246-579.us-west-1.compute.amazonaws.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:

./client/runInstaller
Oracle Client Installer

Oracle Client Installer

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.

Bonus Appendix

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"

Error Appendix

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.

10 July 2013

Oracle on Ubuntu

Filed under: Oracle, Semarchy — Tags: , , — mdahlman @ 11:52

Background

I mostly use Mac OS X, but I needed to install Oracle to work with Semarchy MDM. I created a VM with Ubuntu 12.04 LTS to run Oracle. But installing Oracle XE was significantly harder than I had expected, so I’m documenting what I went through for both my own future reference and to help others.

Extra background details

You can skip this if you don’t care why I chose the different pieces that I chose.

  • Oracle. Semarchy requires Oracle. This is just a demo environment, so I want Oracle Express Edition (XE). I want the current version of Oracle (11g R2 as I write this). I would like Oracle to run on Mac. It did, but it doesn’t:
    “Oracle Database 10g Release 2 [... is] fully certified on Mac OS X.” -Oracle Technology Network
    Oracle 11g Client Tools are available for Mac, but there’s no server install.” -Myself (since it’s hard to find articles or press releases announcing lack of support for something)
    Downloads for Mac OS are conspicuously absent on the Oracle XE download page. But Linux is supported: “Oracle Database Express Edition 11g Release 2 for Linux x64″.
  • Ubuntu. It’s among the most popular distributions (perhaps the most popular). I considered Ubuntu 13.04, but I settled on Ubuntu 12.04 LTS because it’s a long-term support release. I don’t have any need to be on the cutting edge with this project. I want a GUI to have flexibility to install other tools, so I chose Ubuntu Desktop over Ubuntu Server. These instructions should work equally well on Ubuntu Server. I chose 64-bit because… well, this is 2013 and 32-bit just seems wrong. Actually, Oracle XE isn’t available for 32-bit, so that wasn’t really an option. CentOS could be a reasonable choice. But in my experience they’re much more server focused. It’s great on AWS, but I don’t know how it is on the desktop. More folks in my company use Ubuntu than other releases, so I’ll benefit from their experience.
    Oracle XE does not work in Windows x64, so Windows feels like a choice destined to cause extra problems in the future.
  • VMWare Fusion. Other choices like VirtualBox would surely be fine. But I already knew Fusion, and I already had a license for it.
    Update: I exported an .ova from Fusion and imported into VirtualBox. It worked just like it’s supposed to.

Ubuntu Installation

Armed with downloads and instructions, I installed Ubuntu. Then I discovered some eccentricities of Oracle that forced me modify my Ubuntu configuration. I ended up breaking things, so it because easier to simply re-install Ubuntu. Then I had to do it again. In the end I fully documented it:

  • The best way to install Ubuntu 12.04 LTS and configure to VMware Fusion to be ready to install Oracle.
  • All or most of the article applies to all or most versions of Ubuntu… but I only tested Ubuntu 12.04.

Oracle Installation – Zoinks

This was harder than I expected. The pain of installing and configuring Oracle on Ubuntu is what motivated this article.

First, I want to give credit to this outrageously helpful article about installing Oracle on Ubuntu. I have no idea who “Dude” is, but he’s clearly The Dude. I could not possibly installed Oracle on Ubuntu without this guide. Much of my article is based directly on that one.

But… that article is really hard to read. This is partly due to formatting. Presumably the forum changed some things after the initial post, so it wasn’t Dude’s fault. But it’s really hard to read now. It also lacks some updates for Ubuntu 12.04. Dude added the relevant details in this follow-up post. But it would be much better for Ubuntu 12.04 users to have that fix incorporated into the original code.

Also, the article contains too much detail in some places. It contains multiple ways of solving some problems. As a reference, that can be useful. But in this case I prefer just a single canonical Ubuntu solution. (Get it? Canonical. That’s a little Ubuntu joke.) Most of all, it documents how to fix your Ubuntu setup when you need to make changes to accommodate Oracle. But I was in a position to anticipate these issues and simply install Ubuntu with appropriate configuration settings. So the “Oracle Install” part of my document can focus on installing Oracle. I moved the “Configuring Ubuntu” sections into a separate Installing Ubuntu article.

So with a well-configured Ubuntu 12.04 machine as your starting point, let’s install Oracle 11g XE.

Launch Terminal

Oracle 11g Express Edition requires additional software that is not installed by default:

sudo apt-get install alien libaio1 unixodbc

If you followed my Ubuntu install you have plenty of swap space. Whether you installed like that or not, you should confirm that you actually have plenty of swap space.

cat /proc/meminfo | grep -i swap
SwapCached: 0 kB
SwapTotal: 3879932 kB
SwapFree: 3879932 kB

Yep. 3879932 kB is nearly 4 GB. Oracle XE requires 2 GB.
If you do not have at least 2 GB swap space, then fix it before proceeding. The awesome post I mentioned above is a good source to help with that.

Modify Kernel Parameters

Oracle 11gR2 Express Edition requires the following kernel parameters.

Log in as root:

sudo su -

Cut & paste the following directly into a command shell (not a text editor):

cat > /etc/sysctl.d/60-oracle.conf <<-EOF
# Oracle 11g XE kernel parameters
fs.file-max=6815744
net.ipv4.ip_local_port_range=9000 65500
kernel.sem=250 32000 100 128
# kernel.shmmax=429496729
kernel.shmmax=107374183
EOF

Log out from being root:

exit

Load and verify the new kernel parameters:

sudo service procps start
sudo sysctl -q fs.file-max
sudo sysctl -q kernel.shmmax
sudo sysctl -q net.ipv4.ip_local_port_range
sudo sysctl -q kernel.sem

The SHMMAX kernel parameter defines the upper memory limit of a process. It is a safeguard to stop a bad process from using all memory and causing RAM starvation. The Linux default is 32 MB. The official Oracle XE installation documentation suggests a value of 4 GB – 1 byte (429496729 bytes). Since Oracle 11g XE has a 1 GB memory limit, a smaller footprint will be a better safeguard for the complete system. Setting the SHMMAX parameter to 107374183 will be sufficient.

Oracle Home Directory

This should already be well configured. Confirm that this is really true:

df -h /u01

This df command should have a result similar to this:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2       3.7G   72M  3.5G   3% /u01

This is important because if /u01 isn’t there, then the Oracle Installer will have big problems. If you don’t have it, then start over and re-install Ubuntu with a better configuration or else fix it. Re-installing Ubuntu is easier. But if that’s not an option for you then the fully documented, if quite complex, process to fix the existing Ubuntu instance is available.

ORA-00845: MEMORY_TARGET

Oracle 11gR2 XE under Ubuntu 12.04 will result in “ORA-00845: MEMORY_TARGET not support on this system” either at Oracle database startup or during the initial installation. Ubuntu 12.04 uses a newer version of the “systemd” system and session manager and has migrated away from /dev/shm and other common directories in favor of /run.

Here’s how to avoid the problem.
Login as root:

sudo su -

Cut & paste the following directly into a command shell (not a text editor):

cat > /etc/init.d/oracle-shm <<-EOF
#! /bin/sh
# /etc/init.d/oracle-shm
#
#
case "\$1" in
  start)
    echo "Starting script /etc/init.d/oracle-shm"
    # Run only once at system startup
    if [ -e /dev/shm/.oracle-shm ]; then
      echo "/dev/shm is already mounted, nothing to do"
    else
      rm -f /dev/shm
      mkdir /dev/shm
      # Good for Ubuntu 11. Bad for 12. Instead use this:
      # mount -B /run/shm /dev/shm
      mount --move /run/shm /dev/shm
      mount -B /dev/shm /run/shm
      touch /dev/shm/.oracle-shm
    fi
    ;;
  stop)
    echo "Stopping script /etc/init.d/oracle-shm"
    echo "Nothing to do"
    ;;
  *)
    echo "Usage: /etc/init.d/oracle-shm {start|stop}"
    exit 1
    ;;
esac
#
### BEGIN INIT INFO
# Provides:          oracle-shm
# Required-Start:    $remote_fs $syslog
# Required-Stop:     $remote_fs $syslog
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6 
# Short-Description: Bind /run/shm to /dev/shm at system startup.
# Description:       Fix to allow Oracle 11g use AMM.
### END INIT INFO
EOF

Log out from being root:

exit

Install the oracle-shm init script that you just created:

sudo chmod 755 /etc/init.d/oracle-shm
sudo update-rc.d oracle-shm defaults 01 99

You will see a result like this:

 Adding system startup for /etc/init.d/oracle-shm ...
   /etc/rc0.d/K99oracle-shm -> ../init.d/oracle-shm
   /etc/rc1.d/K99oracle-shm -> ../init.d/oracle-shm
   /etc/rc6.d/K99oracle-shm -> ../init.d/oracle-shm
   /etc/rc2.d/S01oracle-shm -> ../init.d/oracle-shm
   /etc/rc3.d/S01oracle-shm -> ../init.d/oracle-shm
   /etc/rc4.d/S01oracle-shm -> ../init.d/oracle-shm
   /etc/rc5.d/S01oracle-shm -> ../init.d/oracle-shm

Reboot

Once the machine restarts, verify that all went well:

sudo cat /etc/mtab | grep shm

Expected (desired) result:

none /run/shm tmpfs rw,nosuid,nodev 0 0
/run/shm /dev/shm none rw,bind 0 0

If you run the command before rebooting you would see the first line with tmpfs but not the second line.

Verify the available shared memory:

sudo df -h /run/shm

The upper limit of shared memory under Linux is set to 50% of the installed RAM by default. So a good result on a machine with 2 GB of RAM allocated is the following:

Filesystem      Size  Used Avail Use% Mounted on
none            999M  152K  998M   1% /dev/shm

Machine configuration

There are a few configuration changes needed to accommodate what the Oracle installer expects to find.

The following needs to be set for compatibility:

sudo ln -s /usr/bin/awk /bin/awk

Ubuntu uses different tools to manage services and system startup scripts. The “chkconfig” tool required by the Oracle installer is not available in Ubuntu. The following will create a file to simulate the “chkconfig” tool.

Log in as root:

sudo su -

Cut & paste the following directly into a command shell (not a text editor):

cat > /sbin/chkconfig <<-EOF
#!/bin/bash
# Oracle 11gR2 XE installer chkconfig hack for Debian based Linux (by dude)
# Only run once.
echo "Simulating /sbin/chkconfig..."
if [[ ! \`tail -n1 /etc/init.d/oracle-xe | grep INIT\` ]]; then
cat >> /etc/init.d/oracle-xe <<-EOM
#
### BEGIN INIT INFO
# Provides:              OracleXE
# Required-Start:        \\\$remote_fs \\\$syslog
# Required-Stop:         \\\$remote_fs \\\$syslog
# Default-Start:         2 3 4 5
# Default-Stop:          0 1 6
# Short-Description:     Oracle 11g Express Edition
### END INIT INFO
EOM
fi
update-rc.d oracle-xe defaults 80 01
EOF

Log out from being root:

exit

Set execute  privileges for the script that you just created:

sudo chmod 755 /sbin/chkconfig

The preparation steps are complete! It’s finally time to begin the actual installation of Oracle 11g XE.

Install Oracle

Begin by unzipping the installer zip file. Most folks will do this by right-clicking and choosing “Extract Here“. But here’s the command line version for the sake of completeness (and for folks on Ubuntu Server).

cd ~/Downloads
unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip

The Debian Linux based package management of Ubuntu is not compatible with the Red Hat package manager this installer is delivered in. The Oracle installer needs to be converted using the following commands:

cd ~/Downloads/Disk1
sudo alien --to-deb --scripts oracle-xe-11.2.0-1.0.x86_64.rpm

This alien command took 3 minutes to run on my instance. You can delete the zip and the original installer to save space:

rm ~/Downloads/oracle-xe-11.2.0-1.0.x86_64.rpm.zip
rm ~/Downloads/Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm

Install Oracle 11gR2 XE:

cd ~/Downloads/Disk1
sudo dpkg --install ./oracle-xe_11.2.0-2_amd64.deb

This dpkg command took 30 seconds to run on my instance. If you look through the output you’ll see the line, “You must run ‘/etc/init.d/oracle-xe configure’ as the root user to configure the database.” Do that next:

sudo /etc/init.d/oracle-xe configure
  • HTTP port: During the interactive configuration I set the port for Oracle Application Express to 8181 instead of the default value of 8080. I plan to install Tomcat, and I prefer to use 8080 for Tomcat instead.
  • Database listener: Keep the default value of 1521
  • Password for SYS and SYSTEM: MANAGER (It’s traditional. It’s totally insecure, but it’s simple. For my demo machine it’s perfect.)
  • Start on boot: Yes

This configure procedure took 2 minutes to run on my instance.

Set a password for the Oracle account:

sudo passwd oracle

In order to use sqlplus and other tools, the Oracle account requires specific environment variables.

Log in as oracle:

su - oracle

Copy the default account skeleton files and add the Oracle env script to .profile:

cp /etc/skel/.bash_logout ./
cp /etc/skel/.bashrc ./
cp /etc/skel/.profile ./
echo "" >>./.profile
echo '. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh' >>./.profile

Log out from being oracle. (This is important because you need to log out and log back in before the sqlplus command below will work.)

exit

Enable remote logins to the XE GUI. Log in as oracle:

su - oracle

Login as SYSDBA then execute the relevant stored procedure:

sqlplus / as sysdba
SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
SQL> exit
[this exits from SQL*Plus]
exit
[this logs out from being oracle]

Lots more information is available here: Oracle® Database Express Edition Getting Started Guide

According to the Oracle documentation, the password for the INTERNAL and ADMIN Oracle Application Express user accounts is initially the same as the SYS and SYSTEM administrative user accounts. Well, I tried several times without success. ["I" refers to "Dude" in this sentence. I copied, reused, rearranged, and reformatted much of Dude's work. But I'm not attempting to claim his work as my own. ("I" refers to "Matt" in these last two sentences.)] Reset the Apex Admin password:
Log in as oracle:

su - oracle

Log in as SYSDBA:

sqlplus / as sysdba

At the SQL prompt, execute apxxepwd.sql using the following command. You will be prompted to change the password:

SQL> @?/apex/apxxepwd.sql
SQL> exit

Now you can log in to the Apex Admin from a remote machine with this url:

http://ubuntu64oracle11gxe:8181/apex/apex_admin

Of course you can always log in locally like this:

http://localhost:8181/apex/apex_admin

It will prompt you to reset the password. It uses the most restrictive password rules that I have ever encountered. My first attempt at a password for ADMIN was ‘ADMIN’. It failed no fewer than six complexity rules. I eventually settled on this as the simplest acceptable password I could find:

abc!=XYZ1

For me, one of the most annoying things about sqlplus is that by default I cannot simply hit the up arrow to get back to the last command. Fortunately, the problem is easily solved.

First get the readline wrapper utility:

sudo apt-get install rlwrap

Then create an alias to use rlwrap with SQL*Plus:

su - oracle
cat >> ~/.bashrc <<-EOF
alias sqlplus="rlwrap sqlplus"
EOF

Log out from being oracle. (This is important because you need to log out and log back in before the new alias will take effect and sqlplus will have that wonderful up-arrow-for-retrieving-history functionality.)

exit

Your Oracle instance should now be installed and configured and ready for you to use. This post made it possible for me to write my article. I hope I have not introduced too many errors. Please let me know if you found the article helpful.

It’s time for me to do some Master Data Management.

Older Posts »

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 36 other followers