The situation is this. I am a system administrator working in a world of DBAs. This is not a bad thing, but sometimes tasks crossover and I need to connect to Oracle. In light of this, I have been using the Oracle SQL Developer GUI in KDE for some time now.
While the functionality of the product is quite slick I find it painful to work with. The major source of my pain is the slow interface due to its dependence on Java. I mean seriously, the screen refreshes are abysmal. I found that the more I used it, the more I needed a lighter, faster alternative.
One day a while back, I noticed that the boss was running TOra. He’s more of a DBA than I am, and I tend to trust his opinion on such things, so I asked him about it. He told me it was quite good, and so I decided I should try it out. I installed it on my Kubuntu desktop, fired it up, and much to my chagrin, found that it had no Oracle support. A bit of digging turned up that Oracle support was not included in the Debian package.
I had to do something about it. A bit more research brought me to some useful sites, a couple of which I will reference later, but after going through the process, I found that none of them had all the bits together in one nifty package. This is an attempt to change that.
After bouncing back and forth on some of these steps, fixing environment issues, decoding compiler errors, and generally fiddling, I went back to my notes and terminal histories and figured out what I feel is the best order for the process. This is the streamlined version of maybe two hours of fiddling. I hope it will help someone avoid the pitfalls (there are 8-bit crocodiles below!).
And so I humbly present to you . . .
Installing TOra with Oracle support on Ubuntu 8.04LTS (Hardy Heron)
Based on a 32-bit install of Ubuntu 8.04 LTS using the Oracle 11 clients. AMD 3200+ processor with 1GB of RAM and a bizarre assortment of franken-hardware.
I could have done this on 8.10 but I prefer to stick to the long term support releases. I may try it on my 8.10 laptop at a later date and update the guide.
- I use sudo for everything because logging in to root shells is just bad practice.
- I plug vi whenever possible, mostly to annoy emacs people.
- I did this all in a KDE desktop, which means that some things I say may sound like I use KDE.
- Commands issued are in pre-formatted text without any prompt gunk in front of them, so cut and paste to your hearts content.
- Output is also in pre-formatted text and I use it sparingly where relevant. No one needs to see the list of packages that apt-get wants to autoremove, nor how many updates I should be installing.
Get the packages
Find an acceptable build location in your filesystem,
cd to there and then get the tora source deb package.
sudo apt-get source tora
Get the Oracle client RPMs from the Oracle 11 client download page. All the packages you need are on one page, but you need a log-in to get them. Accounts are free. The files we want are:
Install the prerequisites and development libraries
alien and all the dependencies that this build will need to work. Alien is a cool little tool for converting
.deb files and more.
sudo apt-get install libqt3-mt-dev libqt3-compat-headers libqscintilla-dev build-essential g++ gcc autoconf automake flex zlib1g-dev docbook-xsl debhelper alien libaio1 dpatch
This will install a lot of other dependencies, just go with it.
If you want to build with KDE support then do the following (having never done this without the KDE support, I’m not really sure what the differences are (perhaps someone can enlighten me in a comment).
sudo apt-get install kdebase-dev
This will probably install even more dependencies than above, but hey, you’ve come this far. Now that we have all the prerequisites in place, let’s get Oracle installed.
Change directory to where you downloaded the Oracle RPMs.
cd oracle ls -al total 18912 drwxr-xr-x 2 root root 4096 2008-12-10 00:35 . drwxr-xr-x 4 domito domito 4096 2008-12-10 07:58 .. -rw-r--r-- 1 root root 17958287 2008-12-10 00:28 oracle-instantclient11.1-basiclite-18.104.22.168.0-1.i386.rpm -rw-r--r-- 1 root root 578817 2008-12-10 00:27 oracle-instantclient11.1-devel-22.214.171.124.0-1.i386.rpm -rw-r--r-- 1 root root 782162 2008-12-10 00:27 oracle-instantclient11.1-sqlplus-126.96.36.199.0-1.i386.rpm
Now we are going to convert them to
.deb and install them all in one shot.
sudo alien -i *.rpm
The output will look like this.
dpkg --no-force-overwrite -i oracle-instantclient11.1-basiclite_188.8.131.52.0-2_i386.deb Selecting previously deselected package oracle-instantclient11.1-basiclite. (Reading database ... 172382 files and directories currently installed.) Unpacking oracle-instantclient11.1-basiclite (from oracle-instantclient11.1-basiclite_184.108.40.206.0-2_i386.deb) ... Setting up oracle-instantclient11.1-basiclite (220.127.116.11.0-2) ... Processing triggers for libc6 ... ldconfig deferred processing now taking place dpkg --no-force-overwrite -i oracle-instantclient11.1-devel_18.104.22.168.0-2_i386.deb Selecting previously deselected package oracle-instantclient11.1-devel. (Reading database ... 172399 files and directories currently installed.) Unpacking oracle-instantclient11.1-devel (from oracle-instantclient11.1-devel_22.214.171.124.0-2_i386.deb) ... Setting up oracle-instantclient11.1-devel (126.96.36.199.0-2) ... dpkg --no-force-overwrite -i oracle-instantclient11.1-sqlplus_188.8.131.52.0-2_i386.deb Selecting previously deselected package oracle-instantclient11.1-sqlplus. (Reading database ... 172446 files and directories currently installed.) Unpacking oracle-instantclient11.1-sqlplus (from oracle-instantclient11.1-sqlplus_184.108.40.206.0-2_i386.deb) ... Setting up oracle-instantclient11.1-sqlplus (220.127.116.11.0-2) ...
Now that we have Oracle and the development libraries all in place, we need to get the system to use the libraries and know where Oracle lives.
To permanently add the Oracle library path, called
oracle.conf, and add the Oracle library path. Create a new config file for oracle in
oracle and add the Oracle library path.
sudo echo /usr/lib/oracle/11.1/client/lib > /etc/ld.so.conf.d/oracle.conf
Now rebuild your library cache. No output is good output for ldconfig.
Now set your environment so tora can find Oracle. We’ll also add one that will tell tora where to find the
export ORACLE_HOME=/usr/lib/oracle/11.1/client export LD_LIBRARY_PATH=/usr/lib/oracle/11.1/client/lib export TNS_ADMIN=/usr/lib/oracle/11.1/client
For good measure I would add this to your login scripts so that tora will not bomb when you run it. Depending on your shell you might need to do this to
~/.profile. I use bash, because it rules. And yes, I know there are prettier ways to get this done, but this works.
echo "export ORACLE_HOME=/usr/lib/oracle/11.1/client" >> ~/.bashrc echo "export LD_LIBRARY_PATH=/usr/lib/oracle/11.1/client/lib" >> ~/.bashrc echo "export TNS_ADMIN=/usr/lib/oracle/11.1/client" >> ~/.bashrc
Now we should be ready to build.
A word about the environment
If you are using tora then you are in XWindows. Likely you did all this in a terminal window using xterm or konsole. We created these environment variables in the terminal which means they are local to the terminal window itself. If you try to run TOra from the menu, it will fail. That is why we added them into the
~/.bashrc file. The
.bashrc file will not take effect for your X desktop until you log out and back in again, there’s no way around that. So until you log out/in you can just run TOra from the command line in your terminal window. Bringing up a new xterm should source your
.bashrc file and get the environment variables too.
Building and installing TOra
tar -zxvf tora_1.3.22.orig.tar.gz
cd to the tora source dir:
Change the configure command arguments in
debian/rules using your favorite editor (which surely must be vi). Find line 20, which says:
./configure --prefix=/usr --without-oracle --without-rpath --disable-new-check --with-kde --enable-libsuffix=
i in the following (non vi users delete the line and insert this one).
./configure --prefix=/usr --with-instantclient --with-oracle-includes=/usr/include/oracle/11.1/client --without-rpath --disable-new-check --with-kde --enable-libsuffix=
If you avoided the minute discomfort of installing the KDE development libraries, or just plain do not want KDE support, use this one (I would also suggest this if you run into some bizarre KDE related error when compiling. It’s been known to happen. I think there’s a support group.):
./configure --prefix=/usr --with-instantclient --with-oracle-includes=/usr/include/oracle/11.1/client --without-rpath --disable-new-check --without-kde --enable-libsuffix=
Now it’s time for the main event, we’re going to compile it. How you ask? Thusly! From right where you are in the root of the tora source tree run this stunningly complex command:
This will take a bit of waiting now, but there’s nothing for it. On my out-of-date Athlon XP 3200+, the whole process took 12m27.674s. Just ballparking here.
Once the build is complete you are ready to install tora. Do it like so:
sudo dpkg -i tora_1.3.22-5_i386.deb Selecting previously deselected package tora. (Reading database ... 172036 files and directories currently installed.) Unpacking tora (from tora_1.3.22-5_i386.deb) ... Setting up tora (1.3.22-5) ...
But Mr Science, won’t
apt just clobber my custom package when it updates?
I’m glad you asked that Billy. Get me some coffee and a cigarette and I’ll explain combustion. Ahem.
Normally, yes. in fact when I was testing this I found that apt-get will prefer a repository package to my own even though they were the same version. To fix this we need to add an entry to the apt preferences configuration:
man apt_preferences # :)
/etc/apt/preferences (it will likely be a new file) and add the following lines:
Package: tora Pin: version * Pin-Priority: 50
What this says is that for the package tora, any version it will assign priority 50. Priority 50 means, essentially, that it will not be installed unless it’s not already installed, and you have to ask for it by name. What that means for you is that auto-updates will not destroy all your hard work.
Don’t forget your
tnsnames.ora. We set up the environment to use
TNS_ADMIN=/usr/lib/oracle/11.1/client which means that tora will look for them there. The easiest way I found was to get the production
tnsnames.ora file from the Oracle server itself and place it in
$TNS_ADMIN. Once that is done, start tora and enjoy. Remember to start it from the xterm session that has the environment variable set if you have not yet logged out/in.
This is where I leave off. The running and connecting and such I leave in the hands of you and the TOra documentation.
All in all I was pleased with how the whole process turned out. I have been using TOra and like it so far. I have not yet been able to find stored procedures in it, but that will come. I also had a crash when I brought up the help, but I am not sure if that is a bug and will need to look into it. It compiled cleanly, so I do not blame it on a compilation error.
I’m not great at keeping a regular blogging schedule. I am trying to mend my ways though. That being said, I have one in the works dealing with a statement I read on another blog, that Ubuntu was becoming slower with each release. I captured some performance data before I did a fresh install of 8.10, so I will share the results if they are interesting. I also have a nifty little one about VMware in a hosted ISP environment. Stay tuned.