Oracle on OS/X – Part 2

19 Feb 2009

In the first part of this article, I explained how to get VirtualBox running on an Intel Mac with Ubuntu server installed and running as the operating system. However, the point of all of this is to get an Oracle XE server running on the Mac so in this second installment, I’ll explain the remaining Oracle specific steps needed to get Oracle up and running in the Ubuntu VM.

You’ll need the Debian installation package (rather than the RPM) from the XE download page (having agreed to the license agreement etc etc). Download this on your Mac and copy it to your “shared” folder that the Ubuntu VM is sharing with the Mac.

Linux Setup

The XE installation process is radically simpler than the Universal Installer one for the Enterprise Edition and it does a lot of the Linux setup required silently for you (it will set kernel parameters, create users and so on). However, one thing it doesn’t do is to set up swap space correctly.

The amount of swap that the installer demands depends on the amount of memory you’ve got, but if its 512MB or more, you need at least 1024MB of swap. Ubuntu silently sets up swap space for you during installation. I’m not sure how it works out what it needs, but my VM ended up with a 401,440K swap partition which is a lot less than Oracle demands.

You can add swap in a number of ways, but I just added 1024MB of swap in a file on the filesystem:

  • Create a file to hold the swap space: sudo dd if=/dev/zero of=/swap.1024mb bs=1M count=1024
  • Format the file as a swap file: sudo mkswap /swap.1024mb
  • Activate the swap space: sudo swapon /swap.1024mb
  • Add a line to /etc/fstab to get the OS to mount this every time it starts. This should be of the form:
    /swap.1024mb none swap sw 0 0

I also found that you need to install:

  • the bc command line calculator for the Oracle installer, which can be installed with the command: apt-get install bc
  • the libaoi1 package, again by running a command like: apt-get install libaio1.

You should now be able to install Oracle XE into the VM.

Installing Oracle XE

To install the XE database, just use the dpkg command to install the Oracle Debian bundle that you’ve downloaded and run its configuration script:
sudo dpkg -i /shared/oracle-xe_10.2.0.1-1.0_i386.deb
sudo /etc/init.d/oracle-xe configure

Answer the prompts about ports, system passwords and starting on boot (I just accepted the defaults and entered a simple password for the system users).

(Wasn’t that easier than the Universal Installer!)

You’ll now find that Oracle XE is installed in an $ORACLE_HOME of /usr/lib/oracle/xe/app/oracle/product/10.2.0/server.

In order to test the new server, you can initialise the environment using the $ORACLE_HOME/bin/oracle_env.sh script. However, before you do this, you’ll need to fix the nls_lang.sh script that oracle_env.sh calls! When I ran this on my Ubuntu VM I found that it encountered errors relating to the [[ operator on lines 108 and 110, so to fix this change “[[” to “[” and “]]” to “]” on these two lines.

To check out your server:

   $ . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
   $ sqlplus "sys/password as sysdba"
   SQL> select * from v$instance ;
    INSTANCE_NUMBER INSTANCE_NAME
    --------------- ----------------
    HOST_NAME
    ----------------------------------------------------------------
    VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
    ----------------- --------- ------------ --- ---------- ------- ---------------
    LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO EDITION
    ---------- --- ----------------- ------------------ --------- --- -------
                  1 XE
    oracle1
    10.2.0.1.0        18-FEB-09 OPEN         NO           1 STOPPED
    ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO  XE

You now have a running Oracle instance, so you can start using it immediately from Ubuntu. However you only have the basic console in the VM window so it would be much easier to use it from the Mac, so how do we connect the two?

Getting Connected to Oracle

There are a number of networking options available to a VirtualBox VM:

  • “NAT” as you’d expect means that the VM shares a physical network interface (and address) with the host Mac and has a non routable IP address itself (e.g. 10.0.2.15);
  • “Host Interface” is where the VM has dedicated use of one of the host’s network interfaces and so gets a real network interface and address (but your host has to have two connected interfaces – WiFi and wired for example); and
  • “Internal Network” which is VM to VM networking.

I used NAT networking because it just seemed a lot simpler than the alternatives! The only snag with NAT is that you can’t connect to the VM directly as a network host. To work around this, VirtualBox provides port forwarding from the Mac host to the VM. As a general solution this is quite fiddly to set up, but for our case we only need a couple of ports forwarded:

  • SSH (22) – allow a terminal connection from the Mac;
  • HTTP (8080) – allow connection to the Oracle XE web administration interface;
  • TNS (1521) – allow an SQL*Plus or SQL Developer connection from the Mac.

To redirect a port needs three commands, for example in the SSH case, use the commands:
VBoxManage setextradata VMNAME VBoxInternal/Devices/pcnet/0/LUN#0/Config/ssh/Protocol TCP
VBoxManage setextradata VMNAME VBoxInternal/Devices/pcnet/0/LUN#0/Config/ssh/HostPort 2222
VBoxManage setextradata VMNAME VBoxInternal/Devices/pcnet/0/LUN#0/Config/ssh/GuestPort 22

Where “VMNAME” is the name of your VM, “ssh” is an arbitrary name for this group of settings and the port numbers are the guest port you’re directing to and the port you want to use on the host Mac as its proxy. You need a group of these three commands for each port you’re redirecting. I redirected port 22 on the VM via 2222 on the Mac, port 8080 on the VM via 9080 on the Mac and 1521 on the VM via 1521 on the Mac.

One last thing you need to do to allow you to get at your Oracle instance is to enable remote access to the web administration interface, as the default configuration only allows connections from the local machine. You do this by running an SQL command as the system user on the VM:

    $ sqlplus system/password
    SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
    PL/SQL procedure successfully completed
    SQL> quit;

Using Oracle

You can now access Oracle via (at least) three routes:

  • SQL Developer: a Java based SQL GUI freely available from Oracle here. Just download it and connect to hostname “localhost“, port “1521” (or whatever port you redirected through) and SID “XE” (or whatever SID you used for your database).
  • Instant Client SQL*Plus: a version of SQL*Plus that doesn’t require all of the client installation to run, available from Oracle here. Download the “Basic” and “SQL*Plus” packages and unzip them into a convenient directory, and add this directory to your $PATH and $DYLD_LIBRARY_PATH. Then you can use SQL*Plus as follows:
    sqlplus user/password@//localhost:1521/xe
    (You can also use a local TNS names file – see section 1.3 of the Instant Client documentation for details).
  • XE Database Home Page Web Interface: having redirected the VM 8080 port to another port on the Mac (e.g. 9080) then just direct a web browser to the URL http://locahost:port/apex in order to access the XE web interface. It’s pretty easy to use (being much simpler than OEM) and documentation is available here.

So that’s it. It’s been a bit fiddly in places, but not really a difficult process and we now have a VM running on our Mac that automatically starts an Oracle database instance when it boots that we can access via GUI, command line and web based interfaces running on your Mac. What’s not to love?

published under Oracle, Technology