Sunday, October 3, 2010

Red Hat 4.x to 5.x fuser "Enhancement"

I came across an interesting situation related to an upgrade of Red Hat.  Due to planned upgrades of Oracle 10g to 11g, we are first performing upgrades of RH from 4.x to 5.x.  Normally an OS upgrade would involve sanity checks that nothing breaks, but you'd expect your basic Unix commands to behave the same.

Yet after running a few DBA maintenance scripts I found a problem with one script that was using "fuser".  Our script checks a list of files to see if any are currently open and if so, it processes them differently.  The problem was that under RH 5.x (5.3, specifically), where "fuser" piped to "cut":

for active_file in `fuser $dir/$sub/* 2>&1 | cut -d\: -f1`

In the snipet of code above under RH 5.3, it appeared that both files and the PID from "fuser" were getting returned, as if "cut" didn't work at all.  After closer investigation it turned out that "fuser" was the guilty party.  I ran a simple test on a database server with Oracle running, knowing that "libclntsh*" would be open. 

Using the command:

fuser $ORACLE_HOME/lib/libclntsh* | cut -d\: -f1

… under RH 4 you'll get:


… while under RH 5 you'll get:

  7309  7309

I did a crazy thing and checked the "man" pages for "fuser" to see if I was missing something and sure enough, I was: a change in RH 5!  See the following line listed right before the OPTIONS section:

       fuser outputs only the PIDs to stdout, everything else is sent to stderr.

That explains the difference in behavior between releases.  Fortunately the workaround is easy: redirect stderr to stdout.

Fortunately I caught this, but I wonder what other changes are lurking out there?

Sunday, March 21, 2010

SQL*Plus & Command History with Cygwin + rlwrap

Although nearly all apps today are use some sort of point-click, windowing front end, there are plenty of us who still use the command-line when available.  It's not that we're set in our ways, it's that for many repetative tasks, the command-line provides us with enough shortcuts that we're just way more efficient with a few key strokes.

This brings me to Oracle's SQL*Plus, or more specifically, running "sqlplus" from the command-line.  "sqlplus" has long provided the ability to edit the previous command in it's buffer, either pulling it up into an editor or using a few commands to change it in place (such as "CHANGE//").  But obviously this is limited.

In comes "rlwrap".  This application is also nothing new, but did you know it's available from within Cygwin?  This issue came up during Tanel Poder's recent Training Day at Hotsos Symposium 2010.  He gave some great examples of "rlwrap"'s power using it from Linux, then laid the gauntlet down by saying it should work under Cygwin, but he hasn't tried it and we should let him know if we can get it working.  That was enough for me!

I've used Cygwin for some time as a way to start an XWin server, in support of other apps that require it (such as Oracle's installer).  I reviewed the my installation source ( and ran the installation again to see if "rlwrap" is available.  Sure enough, if you change the View to be by Category, then you'll file "rlwrap" under Utils:

In the screenshot above you can see "rlwrap" is already installed, but this was taken from my current install, so you get the idea.

The installation process dumps "rlwrap" under c:\cygwin\bin by default.  Now comes the power of the tool!
From your PC, open a Cygwin XWin server to work off of, then an xterm from this XWin server session.  From the xterm session you can fire off your "rlwrap" commands.  You can run them from the XWin server session, but I prefer to instead use that as a central session, firing off separate xterm sessions per connection I plan on making.  In my $HOME directory I created a setup script which defines a series of aliases, one per color-coded xterm screen:

alias black="xterm -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg black -fg gainsboro &"
alias brown="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg brown -fg gainsboro &"
alias blue="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg darkblue -fg gainsboro &"
alias darkred="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg darkred -fg gainsboro &"
alias green="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg green4 -fg gainsboro &"
alias purple="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg purple4 -fg gainsboro &"
alias teal="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg darkcyan -fg gainsboro &"
alias white="xterm -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg white -fg black &"

This way typing "blue", for example, quickly gives me a new blue background, white foreground xterm.
Back to "rlwrap" power.  A simple example of the command is as follows:

rlwrap -r sqlplus dherri@ORCL1

Within this sqlplus session I now have full command-line history using the arrow keys.  Plus with the "-r" argument, "rlwrap" will "remember", so to speak, all text that passes on the screen.  What this means is through the TAB key you can now quickly access output from previous queries.  For example, suppose you wanted to query an AWR view but couldn't remember the name.  So you enter:

SELECT view_name
  FROM dba_views
 WHERE view_name LIKE 'DBA_HIST%'

Now the output list of view names is in rlwrap's buffer, which means the view names are available for command completion.  If you enter:


… and hit TAB twice, you'll get the following displayed:


This is just a small sampling of what "rlwrap" can provide in terms of added efficiency within sqlplus command-line or for nearly any app for that matter (RMAN quickly comes to mind).  The main point I'm raising here is Windows users need not be excluded from the benefits of command-line completion using "rlwrap".

For more detail on "rlwrap" and excellent examples of applying its use, see Tanel Poder's blog @ or his new tech site @