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
Author and copyright
Copyright 2008 by Raj Mathur. This document may be reproduced under
the terms of the Creative Commons CC-BY-SA License.
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
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.
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.
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
Before we start you must have the full dump of the existing Oracle
database from the user. This generally comes in a file named
(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
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
) 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
On Debian Lenny (Testing), the Xen-enabled kernel comes in the
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
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:
The first package (
) is the guest
kernel that will get booted within the virtual machines that you
The last package (
) 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
# The primary network interface
iface eth0 inet static
To make this into a bridge, disable the
interface, and add a
(BRidge 0) interface with the same properties to the file:
# For Xen
iface br0 inet static
# The primary network interface
# auto eth0 (will not come up automatically now)
iface eth0 inet static
and your new interface should be up (reboot if the restart doesn't fix
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
command from the
ease guest machine creation. The virtual machine I created had the
- Directory for images:
- 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
xen-create-image --hostname=Oracle --cache=yes --dir=/data \
--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 \
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.
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
tail -f /var/log/xen-tools/Oracle.log
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
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:
mount -oloop /data/domains/Oracle/disk.img /tmp/mount
Switch to using the disk image as root:
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:
Finally, unmount the guest filesystem:
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
). If you do not want to connect to the console of
the guest at startup, omit the
option from the
Now that the guest machine is booted, you can connect to it through
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
to get the IDs of
running virtual machines on your system. If the ID of your guest is
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.
We need the these broad classes of packages to effect the migration in
the guest machine:
- Oracle Server
- Oracle support libraries
The following sections describe installing each of these in detail.
Before you start, be sure to login to the guest machine with:
and run all the commands there.
We need the following packages before we can install all required
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:
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:
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.
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
as the password for the SYS
and SYSTEM accounts):
Retrieve and Install the Oracle Support Packages
In order to run
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:
After downloading it,
it into a deb:
Now we need to fix the package so that it provides the
requirement of the Perl Oracle module.
Bit tricky, but the following commands will make this work:
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
dpkg-deb -b oracle-instantclient-basic-10.2.0.3
dpkg -i oracle-instantclient-basic-10.2.0.3.deb
Installing PostgreSQL is a single aptitude command:
aptitude install postgresql-8.3
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
to forward requests to the guest.
From any computer with a browser and ssh installed, run the command
ssh -L 9999:localhost:8080 email@example.com
Once you've logged in, point your browser at
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
Import the Data
For importing the data into Oracle, first copy the dump
from wherever you have stored it to the guest machine:
scp EXPDAT.DMP firstname.lastname@example.org:.
Now login as root to the guest machine, move the dump to the oracle
user's home directory, become the
user and setup the
mv EXPDAT.DMP ~oracle/
su - oracle
Finally, run the
command to import the dump into the current
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
, which we installed in
an earlier step.
needs a configuration file set up before it
can run, so first copy the sample configuration file over to the
current directory as the
su - oracle
You may want to put the 3
statements above into the
so that the variables are set automatically
each time you su.
cp /usr/share/doc/ora2pg/examples/ora2pg.conf.gz .
as per the table below:
| Original Line || Change To |
| ORACLE_HOME /usr/local/oracle/oracle816 || ORACLE_HOME /usr/lib/oracle/xe/app/oracle/product/10.2.0/server |
| ORACLE_DSN dbi:Oracle:host=thedb.mydom.fr;sid=TEST || ORACLE_DSN dbi:Oracle:host=127.0.0.1;sid=XE |
| # EXPORT_SCHEMA 1 || EXPORT_SCHEMA 1 |
| # SCHEMA APPS || SCHEMA <name of database schema owner that you created earlier in APEX> |
| TYPE TABLE || (see under) |
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
- COPY (or DATA, but COPY is faster)
and run the following script to export each of
these components into a file named
for i in TABLE PACKAGE COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE ;\
perl -p -i -e "s/^TYPE\s+.*/TYPE $i/; s/^OUTPUT\s+.*/OUTPUT $i.sql/" ora2pg.conf; \
ora2pg ora2pg.conf; \
Now you would have the following files in your current directory,
which you can run as PostgreSQL scripts:
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.
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
2008-12-26: Initial publication.