Rails 3 Setup for Oracle 10g XE

gistfile1.md

Notes on Setting up Oracle 10g XE with Rails

Capturing my notes about setting up an Oracle 10g XE / Rails environment for development work. Tested using the following versions, others may work as well:

1. Ubuntu 11.04
2. Ruby 1.9.2
3. Rails 3
4. Oracle 10 XE

Get Oracle 10g XE

Oracle 10g Express Edition (XE) is a simplified version of Oracle 10g designed to get you up and running with development, without wasting your time installing the full server edition.

1. Go to http://www.oracle.com/technetwork/database/express-edition/downloads/index.html
2. Click on the link to "Oracle Database 10g Express Edition for Linux x86"
3. Choose the "Oracle Database 10g Express Edition (Universal)" Debian package (named something like oracle-xe-universal_10.2.0.1-1.0_i386.deb). NOTE: You may have to register to download the package.

Swap space, do you have enough? Oracle 10g requires 1Gb of swap, check how much you have before doing the install.

$ free
             total       used       free     shared    buffers     cached
Mem:       3095692    3034396      61296          0     757000    1244148
-/+ buffers/cache:    1033248    2062444
Swap:       262136      23416     238720
$ cat /proc/swaps
Filename                          Type      Size        Used       Priority
/host/ubuntu/disks/swap.disk      file      262136      23416      -1

See http://www.debian-administration.org/articles/550 for the easiest way to add extra swap.

Install Oracle XE

Install the Linux Async IO library, called either libaio or libaio1 depending on the Ubuntu version.

$ sudo apt-get install -y libaio

Follow the "Installing Oracle Database XE Server" setup instructions, choosing the defaults. e.g.

$ sudo dpkg -i Downloads/oracle-xe-universal_10.2.0.1-1.0_i386.deb
$ sudo /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express 
Edition.  The following questions will determine whether the database should 
be starting upon system boot, the ports it will use, and the passwords that 
will be used for database accounts.  Press <Enter> to accept the defaults. 
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:8080

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of 
different passwords for each database account.  This can be done after 
initial configuration: password
Confirm the password: password

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"

Set the Oracle Database XE Server Environment Variables

Add the following to $HOME/.bashrc

# Oracle server environment
. /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh

and then source your .bashrc:

$ source ~/.bashrc

If you see an error like this:

/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh: 114: [[: not found

then edit nls_lang.sh

$ sudo vi /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh

and change

#!/bin/sh

to

#!/bin/bash

and try to source .bashrc again

Rails Setup

To connect active record to oracle you'll need the oracle-enhanced driver.

 gem install ruby-oci8
 gem install activerecord-oracle_enhanced-adapter

Put these in your Gemfile and lock down to whatever version works. For reference I'm using ruby-oci8 2.0.4 and oracle_enhanced 1.3.1

Create the development and test databases in Oracle using SQL*Plus

$ sqlplus SYS/password AS SYSDBA

SQL> GRANT CONNECT, RESOURCE TO app_development IDENTIFIED BY app_development_password;
SQL> ALTER USER app_development DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
SQL> GRANT CONNECT, RESOURCE TO app_test IDENTIFIED BY app_test_password;
SQL> ALTER USER app_test DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
SQL> EXIT

Setup database.yml to point to the new tables. eg

development:
    adapter: oracle_enhanced
    database: app_development
    username: app_development
    password: app_development_password

Rails / Oracle Gotchas

1. Oracle Enhanced gem doesn't implement rake db:drop or db:create, you'll need to roll your own solution. rsim/oracle-enhanced#18

2. Oracle 10g XE packages are for 32bit linux, so if you're running 64bit you may have to tell dpkg to --force-architecture or something similar. It has been working for me on 64bit but your mileage may vary.