Planet OCLUG

If you’re planning on running Oracle VM with Amazon EC2, there are some important limitations you should know about. As part of my work getting the Oracle Linux Unbreakable Enterprise Kernel 2 working (yeah that’s a mouthful) I tried using the Oracle-supplied Oracle Linux 6 AMI images that are listed as community AMIs by Amazon: [...]

As announced a few days ago, Oracle’s core database product is now supported on Oracle Linux 6. Coming a full 13 months after Oracle Linux 6′s launch, and 16 months after Red Hat Enterprise Linux 6, it’s a much anticipated announcement. Update 28-Mar-12: the official certification information has come out on My Oracle Support. So [...]

A common scenario in the life of a DBA on a linux server looks something like this: From: Sue-the-sysadamin To:the-dba Date: 2011-10-28 17:54:34 Dear DBA, We will be patching the linux systems this Friday night, October 28th at 21:00. The list of patches is attached. Let us know if there are any conflicts with Oracle. [...]

Among the features announced with the release of version 4.6 of the KDE Software Compilation is KateSQL, a SQL Query plugin for the Kate text editor providing the basic functionality of a SQL client. It leverages the Qt SQL module, allowing you to make a connection to most types of databases. Out of the box [...]

I found I never published this post as it was sitting in my drafts few months now — it was written in 13th February, 2010. I’m publishing it without any changes. I learn therefore I am! I’ve just wrote few bits about learning a new technology and after skimming through my Google Reader, I noticed [...]

I have successfully compiled and installed DBD::Oracle on Windows 2008 Server 64bit operating system today.

I used the latest version of DBD::Oracle 1.24, version 11.2.0.1.0 for 64bit Windows of Oracle’s
Instant Client Package – Basic along with the Instant Client Package – SQL*Plus and finally the Instant Client Package – SDK.

To get it to make and compile correctly I had to download Microsoft’s Visual Studio Ultimate

which should contain all the files you need. It is rather portly at 2+gb so you might want to grab lunch while you are downloading it.

After all the above downloading DBD::Oracle installed right out of the box.

All one has to do is select ‘Start Menu->All Programs->Microsoft Visual Studio 2010->Visual Studio Tools->Visual Studio x64 Win64 Command Prompt (2010)’
which will open a good old ‘dos’ window.

At this point CD to the directory where you downloaded DBD::Oracle

     c:\DBD-Oracle

then set your ‘ORACLE_HOME to the Instant Client directory

     c:\DBD-Oracle set ORACLE_HOME=c:\IC_11

you should also set your NLS like this

     c:\DBD-Oracle set NLS_LANG=.WE8ISO8859P15

Once the above setting are done do a

     c:\DBD-Oracle perl Makefile.PL

and then a

     c:\DBD-Oracle nmake install

Which will produce a whole of warnings (these you can ignore, as they do not seem to effect DBD::Oracle at all) and near the end it should output something like this;

     Generating code
     Finished generating code
     if exist blib\arch\auto\DBD\Oracle\Oracle.dll.manifest mt -nologo -manifest blib\arch\auto\DBD\Oracle\Oracle.dll.manifest -outputresource:blib\arch\auto
\DBD\Oracle\Oracle.dll;2
     if exist blib\arch\auto\DBD\Oracle\Oracle.dll.manifest del blib\arch\auto\DBD\Oracle\Oracle.dll.manifest
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "chmod" -- 755 blib\arch\auto\DBD\Oracle\Oracle.dll
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "cp" -- Oracle.bs blib\arch\auto\DBD\Oracle\Oracle.bs
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "chmod" -- 644 blib\arch\auto\DBD\Oracle\Oracle.bs
     C:\Perl64\bin\perl.exe "-Iblib\arch" "-Iblib\lib" ora_explain.PL ora_explain
Extracted ora_explain from ora_explain.PL with variable substitutions.
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "cp" -- ora_explain blib\script\ora_explain
        pl2bat.bat blib\script\ora_explain

At this point you are all done.

Well almost.

It is important that you test your code before you install but you will have to set a few things up first to get it to fully test correctly.

You will need a TNSNAMES.ORA file that points to a valid DB in the Instant Client Directory

Next you will need to set the ORACLE_USER_ID to a valid user

     c:\DBD-Oracle set ORACLE_USER_ID=system/system@XE

You will have to set up TNS_ADMIN to point to the Instant Client Directory

     c:\DBD-Oracle set TNS_ADMIN=c:\IC_11

Most importantly you will have to add the Instant Client directory to your path like this

     c:\DBD-Oracle path = c:\IC_11;%path%

If you do not do this step you will run into the dreaded

Can’t load ‘C:/Perl/lib/auto/DBD/Oracle/Oracle.dll’ for module DBD::Oracle: load_file:%1 is not a valid Win32 application at C:/Perl/lib/DynaLoader.pm line 202.

Error later on after the compile when you try to use DBD::Oracle.

What is actually going on is that Perl cannot find oci.dll (or one of the other .dlls it needs to run) the

C:/Perl/lib/auto/DBD/Oracle/Oracle.dll’ and the DynaLoader error

is just a false trail as perl is very limited in what it Windows errors it can report on. For more complet info on this sort of error check out this page;

Oracle Troubleshooter HOWTO

by Alexander Foken. It is rather dated but the facts of why perl did not find a dll are still valid.

now you can do this

     c:\DBD-Oracle nmake test

and all the tests should run and it will report.

Finally simply do a

     c:\DBD-Oracle nmake install

and you are all set.

That is about it.

At this point you might want to add the Instant Client directory permanently to your path so you will not run into the Dynaloader error again.

As well you do not need to keep Visual Studio around to use DBD::Oracle so you can uninstall that as well.

I’m tired reading all over the internet — Oracle taking back OpenSolaris, Open Solaris May Die
?, Solaris Is Dead, Save Open Solaris, Oracle taking back OpenSolaris.

I’m so sick of it!

I see that some don’t even know the difference between OpenSolaris and commercial Oracle Solaris (former Sun Solaris 10)!

Wake up people! Oracle did make commercial Solaris 10… eh… commercial, that is. They (well, Sun but Oracle paid big $$ for it) have invested lots into Solaris IP and they have full rights to actually charge money for it and they probably should. Struggling Sun made commercial Solaris free to use in desperation to maintain their rapidly shrinking market share. Oracle doesn’t need that – they are not desperate. You’ve made the right decision Oracle – keep Solaris commercial and use these funds to continue developing this great operating system (or whatever makes business sense).

Having said all this, what does it have to do with OpenSolaris? Nothing!

OpenSolaris was and is free. I have just quickly skimmed through the licensing (Binary License and CDDL) and there are no caveats that I can see like 90 days limitation or whatsoever. All the OpenSolaris goodies are still available to everyone for free.

Whining starts that Oracle will not contribute to OpenSolaris anymore. Come on people! Couldn’t you just appreciate what’s been done already and what a great product OpenSolaris is? If you forgot what open-source is about, it’s about community contributions and not about a single vendor giving away it’s IP so that everyone around can scream how great open-source movement is what great products it produces. If one vendor pulls out and community can’t sustain product development, then the product cannot live its normal open-source life.

Get over it! Want a high quality software with great support without any fuss? Pay $$. Want a high quality free open-source software? Make it happen!

Today I tested OracleVM (OVM) templates on their own distribution of Oracle Enterprise Linux (OEL) 5 with seeded VNC Viewer Free Edition 4.1.2 for X. All went fine, but the VNC connection to virtual machine was not painless as I expected.

[root@oram ~]# rpm -qa "vnc|xen"
kernel-xen-2.6.18-128.el5
xen-3.0.3-80.el5
kmod-gfs-xen-0.1.31-3.el5
kmod-cmirror-xen-0.1.21-10.el5
vnc-4.1.2-14.el5
kmod-gnbd-xen-0.1.5-2.0.1.el5
vnc-server-4.1.2-14.el5

On a brand new OEL5 system with virtualization support, I have created a new virtual machine with a fresh OEL4 (plain OS), and set appropriate memory for the virtual domain using the Xen management user interface commands. I also checked if the VNC port was allocated with the command virsh dumpxml:

[root@oram ~]# xm create /OVS/running_pool/OVM_EL4U6_X86_PVM_4GB/vm.cfg
Using config file "/OVS/running_pool/OVM_EL4U6_X86_PVM_4GB/vm.cfg".
Started domain OVM_EL4U6_X86_PVM_4GB
[root@oram ~]# xm mem-set 1 1024
[root@oram ~]# xm list
Name                     ID Mem(MiB) VCPUs State   Time(s)
Domain-0                 0      997     2 r-----   284.5
OVM_EL4U6_X86_PVM_4GB    1     1023     1 -b----    73.6
[root@oram ~]# virsh dumpxml OVM_EL4U6_X86_PVM_4GB |grep port
<graphics type='vnc' port='5900' listen='0.0.0.0'/>
[root@oram ~]# netstat -l |grep 5900
tcp        0      0 *:5900               *:*                  LISTEN

Now I expected that VNC Viewer would simply connect to the console of VM, but it displayed only the very first frame and then died after a few moments with a Connection reset by peer (104) error:

[root@oram ~]# vncviewer localhost:0
VNC Viewer Free Edition 4.1.2 for X - built Jan 21 2009 14:35:26
Copyright (C) 2002-2005 RealVNC Ltd.
See http://www.realvnc.com for information on VNC.
Fri Aug 14 15:59:24 2009
CConn:       connected to host localhost port 5900
CConnection: Server supports RFB protocol version 3.8
CConnection: Using RFB protocol version 3.8
TXImage:     Using default colormap and visual, PseudoColor, depth 8.
Fri Aug 14 15:59:25 2009
CConn:       Using pixel format depth 6 (8bpp) rgb222
CConn:       Using ZRLE encoding
Fri Aug 14 15:59:29 2009
CConn:       Throughput 20156 kbit/s - changing to hextile encoding
CConn:       Throughput 20156 kbit/s - changing to full colour
CConn:       Using pixel format depth 8 (8bpp) colour-map
CConn:       Using hextile encoding
main:        read: Connection reset by peer (104)

As I was able to see the VM console for a while,  my first guess was that it would be related to the virtual system itself, so  I decided to create another virtual domain to see if the problem is replicable. And yes, it was! (Well, in addition to the 104 error I got another one: Broken pipe (32), which was very likely related to domain auto-restarts, and easily traceable by xm list command showing the current VM domain ID changes.)

And the solution for Connection reset by peer (104)? I noticed that VNC Viewer’s default setting is auto-encoding selection, which means first use ZRLE and then switch to hextile if possible—that creates a problem here. When I set any encoding (fixed to either ZRLE or hextile), it started to work well:

[root@oram ~]# vncviewer localhost:0 PreferredEncoding=Hextile
VNC Viewer Free Edition 4.1.2 for X - built Jan 21 2009 14:35:26
Copyright (C) 2002-2005 RealVNC Ltd.
See http://www.realvnc.com for information on VNC.
Fri Aug 14 18:10:39 2009
CConn:       connected to host localhost port 5900
CConnection: Server supports RFB protocol version 3.8
CConnection: Using RFB protocol version 3.8
TXImage:     Using default colormap and visual, PseudoColor, depth 8.
Fri Aug 14 18:10:40 2009
CConn:       Using pixel format depth 6 (8bpp) rgb222
CConn:       Using hextile encoding

Do you have any related or similar issues to share?

My old friend and collaborator Theo Schlossnagle at OmniTI posted his slides from his Scalable Internet Architectures talk at VelocityConf 2009.

The slides are brilliant even without seeing Theo talk and I highly recommend the time it takes to flip through them, for anyone who is interested in systems performance. If anyone took an mp3 of this talk I’m dying to hear it, please let me know.

For those of you unfamiliar with OmniTI, Theo is the CEO of this rather remarkable company specializing in Internet-scale architecture consulting. They generalize on Internet-scale architecture, not on one specific dimension the way Pythian specializes on the database tier. This allows them to see Internet-scale workloads from a unique systemic, multidisciplinary point of view; from the user experience all the way up the stack, through the load balancer (or not), the front-end cache, the application server, the database server, the operating system, the storage, and so on. This approach lets them build Internet architectures and solve scalability problems in a unique and powerful, wholistic way.

Pythian first collaborated with OmniTI in 2001, and they deserve all of their success and profile that they’ve built since then. Trivia: both Pythian and OmniTI were founded in September 1997 and both companies continue to be majority-owned and controlled by founders (in Pythian’s case, yours truly).

Here’s the slide deck. Let me know your thoughts.

In about 15 minutes, Giuseppe Maxia will begin a webinar in which the main focus is a presentation on “How to have a good presentation”. Talk about meta!

Giuseppe posted how to join the free webinar.

The slides can be found at http://datacharmer.org/downloads/2009_03_Presentation.pdf.

Welcome to the 128^th edition of Log Buffer, the weekly review of database blogs.

Let’s begin with some PostgreSQL blogs. Jignesh Shah shares his recipe for making a PostgreSQL 8.3 appliance based on OpenSolaris using VirtualBox. While we’re on appliances, Dave Page shows off PostgreSQL management on the iPhone with an application he himself wrote. Stealth DBA for the bus-rise home.

On Database Soup, Josh Berkus has been finding useless indexes. He begins, “I’d say, in general, that you can’t have really well-chosen indexes without the help of a performance expert. However, anyone can improve their use of indexes in PostgreSQL fairly easily using a few system tools … and a little improvement is all that a lot of users need.” And it’s what Josh offers.

Sometimes a DBA is asked to make something real purty-like, contrary to his or her nature though that may be. On the Postgres OnLine Journal, Leo Hsu and Regina Obe offer some help with the first of a series on Fusion charts and PostgreSQL. (”Fusion Charts . . . is a flash-based charting product that makes beautiful flash charts.”)

And now—hey what’s MySQL maven Baron Schwartz doing with a Postgres post on xaprb? He’s asking, what are your favorite PostgreSQL performance resources?

Maybe he’s considering crossing the floor out of weariness with all the contention in the MySQL world? Can’t say I blame him. Lately, the conversation in the MySQL ’sphere has been dominated by non-technical talk of the pluses and minuses of 5.1, of forking, community vs. enterprise, and so on. This week was no exception.

The week began with Jay Pipes’s advice to MySQL: “Drop the current roadmap . . . Forget Windows for now . . . Clean up the abysmal messiness of the code base . . .” It’s strong stuff and worth a read.

Lukas Kahwe Smith followed with his advice to the database division at Sun, purveyor or patron now of MySQL, Drizzle, and PostgreSQL.

Jeremy Zawodny surveyed this new MySQL landscape, full as it now is of patches, forks, and Drizzle, and liked what he saw.

Speaking of which, the MySQL Performance Blog announced the Percona XtraDB Storage Engine: a drop-in replacement for standard InnoDB.

Ronald Bradford got some unexpected results while looking into the size of memory tables. Can you help Ronald out?

On High Availability MySQL, Mark Callaghan showed us how to make MySQL faster in one hour. Nice stuff. And real purty charts, too.

Let’s see what happened in SQL Server now. Kalen Delany opined that there is no such thing as a SQL Server, and she’s not the only one with an opinion on this (one would think) straight-forward matter.

Lichi Shea asserted that there is a limit to set-based solutions: “After all, some procedural solutions are not so bad!  . . .  Now, it’s time for me to dodge the set-based solution crowd.”

Lots of thoughtful comment on that one, and a blog response from Ward Pond, who says that Linchi Shea makes an interesting point about hints, vis-a-vis the set-based and procedural paradigms.

The Data Management Journal looked into extracting numbers with SQL Server: “We all have perfectly normalized tables, with perfectly scrubbed data, right? I wish! Sometimes we are stuck with dirty data in legacy applications. What’s worse is that we are sometimes expected to do interesting things with dirty data. In this blog, I will show you how to extract a number from a varchar column that contains letters and numbers.”

TJay Belt published his reflection, Cloud Computing and me. Like many DBAs, TJay has some thoughts on how the advent of “The Cloud” is going to affect databases and database administration.

Moving into things Oracle, Chen Shapira was thinking about a musical analogy for the DBA. Their not “rockstar programmers” or “jazz programmers”, says Chen. But I won’t give away her conclusion—click on.

Chet Justice, the Oracle Nerd, was pursuing ontology too in the second part of his Application Developers vs. Database Developers. (I wonder if it’s generally true that apps developers have such terrible manners.)

Gary Myers responded, “Oraclenerd has opened that can of worms about OO, ORMs and Databases,” in his item, The Certainty Bottleneck (and ORMs).

On An Expert’s Guide to Database Solutions, James Koopman suggested, maybe it’s time to extend the DBA’s realm of influence, using tools like Spotlight on Oracle.

Or perhaps with other tools, such as TOra? Here on the Pythian Blog, Brad Hudson posted his howto, Installing TOra with Oracle support on Ubuntu 8.04LTS (Hardy Heron).

One last important bit of news from this week—Thomas LaRock, AKA SQLBatman, is America’s Most Exciting DBA.

Until next time, Happy Holidays to all our readers!

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)

Environment

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.

Conventions

• 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:

• oracle-instantclient11.1-basiclite-11.1.0.7.0-1.i386.rpm
• oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
• oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm

Install the prerequisites and development libraries

Install alien and all the dependencies that this build will need to work. Alien is a cool little tool for converting .rpm to .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-11.1.0.7.0-1.i386.rpm
-rw-r--r-- 1 root   root     578817 2008-12-10 00:27 oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
-rw-r--r-- 1 root   root     782162 2008-12-10 00:27 oracle-instantclient11.1-sqlplus-11.1.0.7.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_11.1.0.7.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_11.1.0.7.0-2_i386.deb) ...
Setting up oracle-instantclient11.1-basiclite (11.1.0.7.0-2) ...

Processing triggers for libc6 ...
ldconfig deferred processing now taking place
        dpkg --no-force-overwrite -i oracle-instantclient11.1-devel_11.1.0.7.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_11.1.0.7.0-2_i386.deb) ...
Setting up oracle-instantclient11.1-devel (11.1.0.7.0-2) ...
        dpkg --no-force-overwrite -i oracle-instantclient11.1-sqlplus_11.1.0.7.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_11.1.0.7.0-2_i386.deb) ...
Setting up oracle-instantclient11.1-sqlplus (11.1.0.7.0-2) ...

Environment setup

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 /etc/ld.so.conf.d called 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.

sudo ldconfig

Now set your environment so tora can find Oracle. We’ll also add one that will tell tora where to find the tnsnames.ora file.

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

Unpack tora:

tar -zxvf tora_1.3.22.orig.tar.gz

cd to the tora source dir:

cd tora-1.3.22/

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=

And shift-D, 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:

debian/rules binary

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  # :)

Edit /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.

End Game

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.

Coming up

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.

References

The TOra homepage

Installing TOra with Oracle Support on Ubuntu Community Forums

APT HOWTO

I’m pleased to announce that there will be the formal launch of Pythian Europe at the premises of the Canadian Embassy in Prague on Wednesday the 6th of August from 17:00 to 18:30. This historic event will be announced by Mrs. Sameena Qureshi, Trade Counsellor, Embassy of Canada; and Paul Vallée, President and Founder, The Pythian Group. Present will be various members from the press (IT and Business), as well as representatives from Oracle and Sun Microsystems, the Canadian Chamber of Commerce in Prague, and many more. We will prepare some unusual and very tasty snacks and refreshments.

We would love for readers of this blog to join us, so please consider this your special, personal invitation from me. Please come if you’re in Prague on Wednesday. If you plan to attend, please contact Dan at elbl@pythian.com.

I am traveling to Europe next week to brief major prospects in Germany (Daimler, MAN) as well as to attend to administrative matters at Pythian Europe in Prague and would love to meet any readers of this blog during this trip!

I’m especially interested in meeting:

• DBAs, Applications Administrators and Systems Administrators,
• Potential customers (IT Directors, DBA Managers, Supply Managers for IT), and
• Potential partners (IT product of service companies that could partner with Pythian to delight our mutual customers)

Here is my itinerary:

• Sunday, August: Frankfurt,
• Monday, August 4: Stuttgart,
• Tuesday, August 5: Munich, and
• Wednesday, August 6 through Saturday, August 9: Prague, Czech Republic.

Please reach out to me using vallee@pythian.com if you would like to meet!

I try to do a decent job of advocating for caring about good backups and business continuity strategies in my 7 Deadly Habits article.

But this one beats them all:

Just too funny and great not to share. Found via this reddit article, where there is a lively discussion underway.

In the era of consolidation, storage has not been left out. Different systems are made to share the same storage boxes, fiber-channel switches and networks. Inside a typical storage box, we have front-end and back-end controllers, cache, physical spindles shared amongst different applications, databases, backup destinations, and so on.

The impact of backup on normal database activity . . . batch processing in one database impacting transactional processing — these are two real life examples of the consequences of storage consolidation known to almost every DBA. Of course, it’s easy to suggest separating databases to different physical disks, but what about SAN box controllers and shared cache? And don’t forget about the cost factor and ubiquitous consolidation that forces storage administrators to pack as much data as possible into a single SAN or NAS storage device.

Some of our customers use hosting services — they outsource hardware hosting just like they outsource DBA work to Pythian. In such scenarios, hosting service providers usually have storage hardware shared amongst different customers to provide higher utilization and on-demand storage capacity at a lower cost.

(more…)

I gave this talk at the UKOUG, and I have received a few requests to post the slides online. Instead of just posting the PowerPoint I took some time to give the presentation again (internally here at Pythian) and this time we recorded the session and we’re posting it here in a variety of formats. This is a bit of a departure from the typical Pythian Goodies, in that it is scripted, and there is a lot of content here in the whitepaper, but there hasn’t been a Goodie in a while so why not!

I’d love to hear from you, so please feel free to ask any follow-up questions to this post in the comments.

Abstract

Do I have enough memory? Why is my free memory so low? Am I swapping to disk? Can I increase my SGA (db cache) size? Can I add another instance to this server? Are my system resources used optimally? These are all questions that often haunt DBAs. This presentation is The Answer. It covers in detail the different types of memory, how to monitor memory, and how to optimally use it with Oracle. Multiple examples in the presentation demonstrate how certain actions on the database side cause different memory areas to be allocated and used on the OS side. Key underlying differences in operating systems approaches to managing memory will be highlighted, with special attention given to Linux, Solaris, and Windows. Using Linux as an example throughout, this presentation explains how to effectively use tools such as “top”, “vmstat” and “/proc/meminfo” to look into into a system’s allocation and use of memory.

Below you should see a flash video with me giving the session.

Download this presentation!
Powerpoint
IPod video (right-click and Save As . . .)
MP3 audio only

And below you will find the complete contents of the whitepaper. This is intended to be a good overall reference resource for how memory works in Oracle, using Linux as an example.

(more…)

It recently came up that it would be helpful if we had a cheat sheet to find out the machine names for any given UNIX. I knew these off the top of my head but it would be great if people added more as comments.

HP/HP-UX: /bin/uname -i
IBM/AIX: /bin/uname -m
SGI/IRIX: /sbin/sysinfo -s
Sun/Solaris: /usr/ucb/hostid