Migrating Oracle Databases to PostgreSQL using Xen on Debian GNU/Linux

This article describes my experiences in migrating a database from Oracle to PostgreSQL under Xen on Linux. Feedback welcome at mailto:raju(at)kandalaya(dot)org.

Author and copyright

Copyright 2008 by Raj Mathur. This document may be reproduced under the terms of the Creative Commons CC-BY-SA License.

Background

A client has multiple applications currently running under Winduhs using Tomcat and Oracle 10g. She wishes to move these to a more stable, cheaper, more efficient platform, and I was asked for recommendations. My suggestion was Linux, Tomcat and PosgtreSQL; the client gave me a sample application and database to be migrated. I managed to migrate the database to PostgreSQL in a Xen virtual machine. This article documents my learning experience from the database migration.

What We Will Do

In the rest of this document we will do the following:

• Set up a Xen virtual machine for running Oracle under Debian.
• Install Oracle in the VM
• Install Oracle-PostgreSQL migration tools in the VM
• Import the existing database into Oracle.
• Export the Oracle DB into PostgreSQL scripts.

Caveats

I don't know Oracle at all, or Xen too well. While what follows has been tested personally by me on my own computer, there are no guarantees that it will work the same way (or even not cause problems) on any other machine. Use at your own risk!

In short, if you follow the instructions in this write-up and your computer blows up, World War III starts, your dog gets run over by a Blue Line bus or you get diagnosed with chronic, incurable halitosis, I am not responsible.

Versions

We use the following versions of software for this HOWTO. While this document is pretty generic, we do use specific package versions at places. It should be fairly straightforward to modify the commands for other versions of software though.

• Debian GNU/Linux Lenny (testing)
• Linux Kernel 2.6.26
• Oracle Express Edition 10g 10.2.0.1
• Postgresql 8.3.5
• Xen 3.2

Prerequisites

Before we start you must have the full dump of the existing Oracle database from the user. This generally comes in a file named EXPDAT.DMP (case may vary).

Also get a list of database users with privileges from the user, since you will need to create some of these before you import the database. Specifically, you need a list of all the users who own portions of the schema, since the EXPDAT.DMP file won't import into Oracle until these users are defined.

Make sure that you have adequate free disk space on your system. I used an 8GB primary partition and a 1GB swap partition, which was enough for the database being migrated; if your database is much larger you would need to scale your disk requirements accordingly.

OK, time to actually do something...

Setting up Xen

Xen (http://xen.org/) is a virtualisation architecture for Linux and other platforms. I decided to use Xen since I didn't want to clutter up my primary installation with all the packages (and dependencies) that I would need for this specific task.

However, Xen on Debian is a bit quirky and non-intuitive to set up and get running. The following sections describe in detail the process I followed to finally get it running.

Getting a Xen-Enabled Kernel

The default Debian kernel does not have Xen enabled. In order to be able to use Xen you must boot with a special kernel. This kernel behaves (for the most part) like a normal Linux kernel, so you could keep this the default kernel on your system if you plan to use Xen a lot.

On Debian Lenny (Testing), the Xen-enabled kernel comes in the package:

• linux-image-2.6.26-1-xen-686

You can use your favourite package tool (I prefer aptitude) to install it. Boot after the install and you should now be able to create and run Xen virtual machines.

Installing the Xen-enabled kernel retains your original (non-Xen) kernel as an option in GRUB, should you wish to return to a vanilla Linux kernel later. I would have made Xen the default kernel on my system, except that it doesn't interact well with Compiz on my Intel Graphics chipset (yes, Yes, YES!!! I'm a GUI eye-candy weenie!). Some lesser-used drivers are also missing in the Xen kernel modules packages.

Technically the linux-image-2.6.26-1-xen-686 package provides the Xen Administrative Domain (Domain 0 or Dom0) functionality.

Installing the Xen Support Tools

To actually build and run a Xen guest you need the following packages:

• xen-linux-system-2.6.26-1-xen-686
• xen-utils-3.2-1
• xenstore-utils
• xen-tools

The first package (xen-linux-system-2.6.26-1-xen-686) is the guest kernel that will get booted within the virtual machines that you create.

The last package (xen-tools) is a set of wrapper scripts that make creation of a Xen virtual host easy and simple. We will use the scripts in this package later for making our guest (virtual) servers.

Again, aptitude will fetch and install these packages and all dependencies for you.

Preparing for a Xen Guest

No, we haven't created a virtual machine yet! One last thing before you do so: setting up networking. And this one's a Pain In The... uh... Ankle!

Remember, each guest that you set up would be a virtual machine running under your primary system. So how do you communicate with it? Through the IP address, of course! However, that means that each guest machine must have its own IP, which must be different from the IP address of the host system. In order to achieve this, you need to make a bridge on the host system.

I won't go into the details of bridging under Linux: they're explained well in many documents a quick web search would locate. Suffice it to say that you need to convert the primary IP of your machine to a bridge. For instance, if your primary IP is 192.168.1.100, you would currently have a stanza similar to the following in /etc/network/interfaces:

# The primary network interface
auto eth0
iface eth0 inet static
        address 192.168.1.100
        netmask 255.255.255.0
        broadcast 192.168.1.255
        gateway 192.168.1.1

To make this into a bridge, disable the eth0 interface, and add a br0 (BRidge 0) interface with the same properties to the file:

# For Xen
auto br0
iface br0 inet static
        address 192.168.1.100
        netmask 255.255.255.0
        broadcast 192.168.1.255
        gateway 192.168.1.1
        bridge_ports eth0
        # optional
        bridge_maxwait 0
# The primary network interface
# auto eth0 (will not come up automatically now)
iface eth0 inet static
        address 192.168.1.100
        netmask 255.255.255.0
        broadcast 192.168.1.255
        gateway 192.168.1.1

One

/etc/init.d/networking restart

and your new interface should be up (reboot if the restart doesn't fix it).

With this in place we can now assign IP addresses in the 192.168.1.X range to Xen guests on our system, and packets to those addresses will automagically be forwarded to the guest virtual machines.

Creating a Xen Guest (Virtual Machine)

I used the xen-create-image command from the xen-tools package to ease guest machine creation. The virtual machine I created had the following attributes:

• Directory for images: /data/domains
• Memory: 512MB
• IP address: 192.168.1.101
• Primary (root) partition size: 8GB
• Swap partition size: 1.2GB
• Debian Distribution: Lenny (testing)
• Instance Name: Oracle

The command for creating a virtual machine with the above attributes is:

xen-create-image --hostname=Oracle --cache=yes --dir=/data \
    --memory=512Mb \
    --size=8Gb --swap=1200Mb --dist=lenny --force --verbose \
    --ip=192.168.1.101 --netmask=255.255.255.0 \
    --broadcast=192.168.1.255 --gateway=192.168.1.1 \
    --role=udev

While man xen-create-image describes all these options, the non-intuitive ones are:

• --cache: Caches the packages downloaded for creating the guest in your primary package cache, so you don't need to download them again when you create another virtual machine.
• --force: Overwrites an existing virtual machine with the same name if it exists. Use this option with caution!
• --role: Forces installation of the udev dynamic device system in the newly-created guest. I was unable to login to the guest system without this role specified.

xen-create-image will take some time to run, depending on how many packages it needs to download and install. You can follow the detailed progress of the command by running the following command in a different console:

tail -f /var/log/xen-tools/Oracle.log

Once xen-create-image has finished you will be able to boot your new guest machine, but don't do it yet!

Changing the Root Password of the New Guest

xen-create-image creates a root user with an empty password in the guest system. This doesn't permit SSH logins to the guest, and for some reason I have not been able to get the login prompt in the console of the virtual machine, so we need to set a root password before we can start using our newly-created guest machine.

The approach I took was to use the virtual machine's own facilities to set the password using chroot. In a nutshell:

Mount the disk image of the guest:

mkdir /tmp/mount
mount -oloop /data/domains/Oracle/disk.img /tmp/mount

Switch to using the disk image as root:

chroot /tmp/mount

Now you will see a fresh root prompt. You are now using the guest root filesystem as your root. Run the password command to set the password and exit from the chroot:

passwd root
exit

Finally, unmount the guest filesystem:

umount /tmp/mount

Booting and Connecting To the Guest Machine

OK, we're finally ready to boot our fresh virtual machine. To startup, give the following command:

xm create -c Oracle

This will create and boot up the Oracle guest and connect you to its console. You should see the normal Linux boot-up messages now.

Ideally the boot process should take you to the login prompt, but for me it always stops after the Starting Cron line. I stopped bothering about fixing that since the machine is up, and I can connect to it using SSH in any case.

By the way, you can disconnect from the guest console by typing Control-] (^]). If you do not want to connect to the console of the guest at startup, omit the -c option from the xm create command above.

Now that the guest machine is booted, you can connect to it through SSH:

ssh root@192.168.1.101

Use the same password you assigned in the previous step to login to the guest and play around to your heart's content. If all has gone well, you should be able to install any new packages you need, reconfigure the system for your specific needs, etc.

To shut down the virtual machine, run xm list to get the IDs of running virtual machines on your system. If the ID of your guest is 1, use xm shutdown 1 to do a safe shutdown of the guest.

In the next section we look at installing packages required for migration in the guest system.

Package Installation

We need the these broad classes of packages to effect the migration in the guest machine:

• Oracle Server
• Oracle support libraries
• PostgreSQL
• ora2pg

The following sections describe installing each of these in detail. Before you start, be sure to login to the guest machine with:

ssh root@192.168.1.101

and run all the commands there.

Installing Prerequisites

We need the following packages before we can install all required Oracle packages:

• bc
• libaio1
• alien

In the guest machine, as root run:

aptitude install bc libaio1 alien

Retrieve and Install Oracle Server

The Debian package for Oracle 10g Express Edition is available at:

• http://download.oracle.com/otn/linux/oracle10g/xe/10201/oracle-xe-universal_10.2.0.1-1.0_i386.deb

You may need to register at oracle.com to access this. If the url above doesn't work, try getting to the downloads page from:

• http://www.oracle.com/technology/software/products/database/index.html

and clicking on the Oracle Database 10g Express Edition for Linux x86 link. The file is pretty big (some 220MB), so be patient while it downloads. To install the Oracle 10g XE server. cd to the directory where you've downloaded the oracle-xe package and:

dpkg -i oracle-xe-universal_10.2.0.1-1.0_i386.deb

Now configure and start the Oracle Server (the default values for the parameters should be OK, choose manager as the password for the SYS and SYSTEM accounts):

/etc/init.d/oracle-xe configure

Retrieve and Install the Oracle Support Packages

In order to run ora2pg we need the Oracle Instant Client package. Installing this is a bit tricky, since we have to convert the package from rpm to deb, then edit the deb to fix some of the meta-data.

The package can be downloaded from:

• http://download.oracle.com/otn/linux/instantclient/oracle-instantclient-basic-10.2.0.3-1.i386.rpm

After downloading it, alien it into a deb:

alien oracle-instantclient-basic-10.2.0.3-1.i386.rpm

Now we need to fix the package so that it provides the oracle-instantclient-basic requirement of the Perl Oracle module. Bit tricky, but the following commands will make this work:

mkdir oracle-instantclient-basic-10.2.0.3
cd oracle-instantclient-basic-10.2.0.3
dpkg-deb -x ../oracle-instantclient-basic_10.2.0.3-2_i386.deb .
dpkg-deb -e ../oracle-instantclient-basic_10.2.0.3-2_i386.deb DEBIAN
perl -p -i -e 's/(Priority: extra)/\1\nProvides: oracle-instantclient-basic/' DEBIAN/control
cd ..
dpkg-deb -b oracle-instantclient-basic-10.2.0.3
dpkg -i oracle-instantclient-basic-10.2.0.3.deb

Install PostgreSQL

Installing PostgreSQL is a single aptitude command:

aptitude install postgresql-8.3

Install ora2pg

Again, installing ora2pg is trivial. The following command will retrieve and install ora2pg and all its dependencies in a single shot:

aptitude install ora2pg

Importing the Data into the New Oracle Installation

Importing the dump data into Oracle is a two-phase process:

• Set up required users
• Import the data

Set Up Users

You did get a list of users required for importing the database earlier, didn't you? OK, to add those users we need to use a browser to connect to the Oracle Application Express (apex) on the guest machine. However, we haven't installed a web browser in the guest, so we use ssh to forward requests to the guest.

From any computer with a browser and ssh installed, run the command

ssh -L 9999:localhost:8080 root@192.168.1.101

Once you've logged in, point your browser at

• http://localhost:9999/apex

and you will see the Oracle administrative screen. Use this to create required Oracle users. (You can also do a lot of other stuff through this interface, the details of which we don't need to go into right now.)

Import the Data

For importing the data into Oracle, first copy the dump EXPDAT.DMP from wherever you have stored it to the guest machine:

scp EXPDAT.DMP root@192.168.1.101:.

Now login as root to the guest machine, move the dump to the oracle user's home directory, become the oracle user and setup the environment:

ssh root@192.168.1.101
mv EXPDAT.DMP ~oracle/
su - oracle
export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_SID=XE
export PATH=$PATH:$ORACLE_HOME/bin

Finally, run the imp command to import the dump into the current database:

imp system/manager FILE=EXPDAT.DMP FULL=Y

If all goes well you should see a ``Import terminated successfully without warnings.'' message at the end. Congratulations, you now have your Oracle database up and running!

Exporting the Data for PostgreSQL

We're finally ready to export the Oracle database. A little bit more configuration and we can export the database into PostgreSQL scripts and import them into PostgreSQL.

The tool we will use for the export is ora2pg, which we installed in an earlier step. ora2pg needs a configuration file set up before it can run, so first copy the sample configuration file over to the current directory as the oracle user:

su - oracle
export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_SID=XE
export PATH=$PATH:$ORACLE_HOME/bin

You may want to put the 3 export statements above into the oracle user's .bash_profile so that the variables are set automatically each time you su.

cp /usr/share/doc/ora2pg/examples/ora2pg.conf.gz .
gunzip ora2pg.conf.gz

Edit ora2pg.conf as per the table below:

Unfortunately ora2pg doesn't permit dumping everything from the database in one shot, so we have to export different components of the database in multiple commands. The various components that may be exported are:

• TABLE
• PACKAGE
• COPY (or DATA, but COPY is faster)
• VIEW
• GRANT
• SEQUENCE
• TRIGGER
• FUNCTION
• PROCEDURE
• TABLESPACE

Save ora2pg.conf and run the following script to export each of these components into a file named <component>.out in the current directory:

for i in TABLE PACKAGE COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE ;\
do \
  perl -p -i -e "s/^TYPE\s+.*/TYPE $i/; s/^OUTPUT\s+.*/OUTPUT $i.sql/" ora2pg.conf; \
  ora2pg ora2pg.conf; \
done

Now you would have the following files in your current directory, which you can run as PostgreSQL scripts:

• TABLE.sql
• PACKAGE.sql
• COPY.sql
• VIEW.sql
• GRANT.sql
• SEQUENCE.sql
• TRIGGER.sql
• FUNCTION.sql
• PROCEDURE.sql
• TABLESPACE.sql

That's it -- we now have PostgreSQL scripts for our complete Oracle database including schema, data, functions and procedures (remember to edit these into plPgSQL!), views, etc.

Conclusion

Due to various quirks in the required packages, setting up a virtual machine for migrating Oracle to PostgreSQL under Linux is not a trivial task. However, I found that with a bit of research on the 'net, innumerable cups of tea (and bottles of soda) and some jumping to obvious conclusions you can manage it quite easily. When I started out on this task I was a total newbie to Xen and Oracle; hopefully I know a bit more about them now and despite my lack of knowledge I didn't face any insurmountable issues in the configuration and migration.

Change History

2008-12-26: Initial publication.