Setting Up Oracle 10g XE With Rails 3 Jul 25th, 2011 | Comments Just linking to a gist I created about setting up Ubuntu with Oracle 10g XE and Rails 3. ### 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](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](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](http://download.oracle.com/docs/cd/B25329_01/doc/install.102/b25144/toc.htm#CIHHJEHF), 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](https://github.com/rsim/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. [https://github.com/rsim/oracle-enhanced/issues/18](https://github.com/rsim/oracle-enhanced/issues/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. The Oracle gem works for most of the common cases but it’s quite clear that in the Rails world it isn’t a first class citizen.