tora with oracle on debian

Update: I’ve posted a new blog entry which contains the prebuilt package I mentioned.

Doing this is a mess. Oracle is non-free (as in freedom, not as in beer) so it can’t be included decently into Debian. It took me a few weeks to do is (not non-stop, of course, but I had to try it a couple of times). It’s a very a frustrating process — at least it was in my experience.

Why you would want this: because Oracle’s SQLplus is ridiculous on GNU/Linux, and SQL Developer (the only decent alternative I’m aware of, besides TOra) uses Java, which makes it “sluggish” on my system to say the least. There’s also Andi Albrecht’s CrunchyFrog which looked *extremely* promising, but Oracle currently isn’t supported in the latest version (because of a rewrite). I hope the developer’s still working on it, because it seemed really cool. The site is down for the moment though. I still have the older version lying around somewhere which did support Oracle, but IIRC I couldn’t get it working. Didn’t spend a lot of time on it though. So: TOra.

The problem: while Debian provides a package for TOra, it doesn’t include support for Oracle (because of the aforementioned licensing issues). You can compile it from scratch, but chances are the compilation won’t work right away. Here, I’ll describe how to modify the Debian package so that it does support TOra. These steps can be applied to standard compilation as well.

The solution:

First of all, there’s also an easy way: you can e-mail (ludovic dot aelbrecht at gmail dot com) me, I’ll send you my tora-with-oracle_1.3.21-3_i386.deb. Or if you have an AMD Athlon XP CPU, I even have an optimized deb file.

And here’s the hard way…

First: add Oracle’s Debian repository to your /etc/apt/sources.list:
#oracle oss repository, contains Oracle XE
deb http://oss.oracle.com/debian unstable main non-free

Next, install these: oracle-xe and oracle-xe-client. See also http://www.togaware.com/linux/survivor/Oracle_Database.html – you need to install libaio. I mention this because it seems to have been a cause of trouble for some people. It didn’t pose a problem on my box though.
To clarify: oracle-xe-client is *only* the client, oracle-xe is *only* the server. For tora, you only need the client, obviously. Unless you have a remote Oracle server at your disposure, you won’t get far without the server though.

apt-get build-dep tora #get the dependencies to build it from source
cd /some/dir/to/put/sources
apt-get source tora #this will drop the source into your current dir
cd tora-<version>/

vim debian/rules #or whatever you favorite editor is
Edit the ./configure line: add “–with-instant-client” to specify you want oracle. Make sure it also reads “–without-kde”, or it won’t work. Don’t remember the exact cause, but I believe you can find it in TOra’s forums or its mailing list archives. Though I’ve seen someone reporting a working TOra with KDE + Oracle, so this might just be an old bug or whatever. Feel free to find out yourself.
debian/rules binary #this command builds it, but it probably won’t work.

(Optionally see these 3 URLs about building it:
http://www.benpinter.net/article.php?story=20051208034638928
http://gudangberas.wordpress.com/2006/08/08/rebuilding-tora-with-oracle-support/

http://ubuntuforums.org/showthread.php?t=189381&page=2)

The ubuntu URL has the clue: we need to specify –with-oracle-includes & -with-oracle-libraries.

Go to Oracle’s instantclient download page for GNU/Linux.
Get these:
*Instant Client Package – SQL*Plus (instantclient-sqlplus-linux32-10.2.0.2-20060331.zip)
*Instant Client Package – Basic (instantclient-basic-linux32-10.2.0.2-20060331.zip)
*Instant Client Package – SDK (instantclient-sdk-linux32-10.2.0.2-20060331.zip)
Unzip them in some dir (all in the same dir). They will overwrite some files – it’s okay, we just want the extra files they provide.
Say you unzipped them in /usr/src/oracle, then now you have a dir called /usr/src/oracle/instantclient_10_2/ with all the files in.

Go back to modifying debian/rules’s ./configure line.
The line should look like this:
./configure --prefix=/usr --with-instant-client --without-rpath --disable-new-check --without-kde --with-oracle-includes=/usr/src/oracle/instantclient_10_2/sdk/include/ --with-oracle-libraries=/usr/src/oracle/instantclient_10_2 --enable-libsuffix=

We’re almost there.

You need an “/etc/oracle/tnsnames.ora”.
Mine looks like this for Oracle XE (express edition), which is the one you get for free (if you have another version, I suppose you know how that file works):

# tnsnames.ora Network Configuration File:
#!!!IMPORTANT: dax is my hostname, change it to yours.

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dax)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

You also need to set $ORACLE_HOME correctly. For compiling, you need to set it to the client’s dir, not the server.
Two options:
(1) Find the directory to set it to. Mine is /usr/lib/oracle/xe/app/oracle/product/10.2.0/client, so I just run: export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client
(2) Run this script: /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/bin/oracle_env.sh (or do a “locate oracle_env.sh” if it’s not there for you, and make sure you select the script from the client dir, not from the server dir).
Last (I promise!), you need LD_LIBRARY set correctly:
export LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/lib/:/usr/src/oracle/instantclient_10_2/:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/:$LD_LIBRARY_PATH
Then run ldconfig. (Yes, I know that that LD path is probably overkill, chances are only the first directory is needed, but after all I’ve been through to get here, I really don’t want to find out which dirs are optional. I frankly couldn’t care less. (Well actually I could – I’m a geek after all. I just don’t want to go through all the crap once more to find out. So if anyone knows, feel free to e-mail me.))

Now you can do a “debian/rules binary” to compile it. This should produce a .deb file.

It’s possible the compilation fails on producing an occi. Check config.log for the error. If it’s about clntsh: you need to symbolically link libclntsh.so to libclntsh.so.xx.x. Do a “locate clntsh”, and create the links everywhere the file appears. See also http://www.redhat.com/archives/rhl-list/2004-December/msg07767.html.

After fixing that, do another “debian/rules binary”. Everything should work now. If you still encounter problems, feel free to contact me.

Install the .deb and start TOra. Select Oracle as the type (obviously), and as the database name pick XE (unless you used something else in tnsnames.ora, I guess). Of course the Oracle daemon should be running by now, so if it isn’t yet, you should run /etc/init.d/oracle-xe to start it. Use “sysv-rc-conf” to make it load at boottime (or to disable it from starting at boottime).

Other stuff you can consult if it doesn’t work out, or for general information (there’s no logic in the order below, I’m just adding everything from my oracle / tora browsing session):

http://www.rittman.net/2004/02/24/has-anyone-got-tora-124-on-linux-working-against-oracle-database-10g/
http://st-curriculum.oracle.com/tutorial/DBXETutorial/index.htm
http://www.puschitz.com/
http://bits-and-bytes.blogspot.com/2005/10/frustrating-toraqt-bug.html
http://sourceforge.net/forum/forum.php?max_rows=25&style=ultimate&offset=25&forum_id=52737
http://sourceforge.net/mailarchive/forum.php?forum_id=2595
http://www.sage.org/pubs/13_oracle/
http://tldp.org/HOWTO/archived/Oracle-8-HOWTO/

http://rpmseek.com/rpm-pl/tora-oracle.html?hl=com&cx=2201:T:0 (Here you can find TOra RPMs with Oracle support – you can try converting them using alien, but I didn’t have much luck at that.)

Advertisements

9 Comments »

  1. […] Why you would want this: because Oracle’s SQLplus is ridiculous on GNU/Linux, and SQL Developer (the only decent alternative I’m aware of, besides TOra) uses Java, which makes it “sluggish” on my system to say the least. There’s also Andi Albrecht’s CrunchyFrog which looked *extremely* promising, but Oracle currently isn’t supported in the latest version (because of a rewrite). I hope the developer’s still working on i … Thanks alot.Link to original article […]

  2. Ivo List said

    Compiled tora myself and noticed a few problems with the above explanation.

    * you don’t need to download oracle instantclient two times, it works with the client & headerfiles and libraries you get from above debian repository
    * it works with –with-kde option, without it copy&paste doesn’t work
    * if you don’t download the server, you’ll need sqlnet.ora besides tnsnames.ora (you can find the contents on google)
    * by default tnsnames.ora is in /etc not /etc/oracle

  3. ludoa said

    Hi Ivo, thanks for your feedback.

    About your first point, where do you specify the includes should ook then? Just /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib ? It doesn’t contain the SDK stuff though… Aren’t they needed?

    Copy pasting what exactly? Results from e.g. select statements etc? Glad to hear that –with-kde does work, now that I have everything setup either way, I guess I’ll have a try at getting it to work. When I was building it, I really didn’t want to spend any more time on it πŸ™‚

    Very weird that you have tnsnames.ora in another dir, as I don’t have it in /etc and it works perfectly.

  4. ludoa said

    An annoyance which comes up after you’ve installed the .deb package, is that ‘apt-get upgrade’ wants to install Debian’s version of TOra. For those who don’t know, you can simply put this package on hold, like so:
    echo tora hold | dpkg --set-selections
    To remove the hold should you want to:
    echo tora install | dpkg --set-selections

  5. ludoa said

    You’re right about –with-kde versus –without-kde. I created –with-kde packages as well now. They’re available from http://rootshell.be/~ludoa/tora-with-oracle.html.

  6. mark said

    The includes from the xe packages are in /usr/lib/oracle/xe/app/oracles/product/10.2.0/server/rdbms/public

    Works ok here

    Mark

  7. ludoa said

    Glad to hear that, Mark.

  8. […] those who haven’t read my previous entry: it contains instructions to build the package yourself, if that’s more your […]

  9. Just wish to say your article is as surprising. The clearness for your put up is simply
    great and that i could suppose you are an expert in this subject.
    Well along with your permission let me to take hold of your feed to
    keep up to date with approaching post. Thanks one million and please keep up the rewarding work.

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: