"Of my friend, I can only say this: of all the souls I have encountered in my travels, his was the most... human." - Captain James T. Kirk, Star Trek II: The Wrath of Khan.
Back in my early Oracle days in 2000-2001, I had been given a chance by a manager who I had worked with for a long time to prove myself as an Oracle DBA. I had expressed an interest in getting into the field, had self taught myself quite a bit and was learning a whole lot more as I went along.
I hadn't been at it more than about 6 months when a new face was hired into the company. A jovial fellow by the name of Stan Yellott. Stan was a big happy fellow, with a bushy white beard.
In the 2 years I worked with him before I left that company's employ, Stan taught me a great deal. He had probably forgotten more about Oracle that I will ever know.
We were working for the Managed Services division of the company at the time, hosting servers for any number of different customers. The environment was set up so that from inside our network, the only way to get to the customer machines was via a single Unix host that was the gateway to all the other Unix servers where the databases were hosted.
We would often login to that gateway machine, and connect to the Oracle databases on the other machines via TNS - which saved us having to login to those remote machines and also somewhat eased having to quickly change from machine to machine to run queries.
Unfortunately, Stan passed away in November, 2006 after a long battle with illness and I was so happy to have a chance to talk to him a few days before he passed.
In keeping with my New Year's resolution (see tomorrow's post!) to be a little more active on this blog, I was thinking about what I could write about.
One of the tools that Stan developed (that I still carry in my toolkit to this day) is a perl script that parses tnsnames.ora and allows you to set your environment so that you can easily query databases on remote machines.
The utility was called "setora".
You could pass a parameter "-l" to it and it would parse your tnsnames.ora file and give you a list of every alias in there, along with the database name and host of the alias.
To set your environment, you would simply do:
setora alias
If your database was called sales, it would be:
setora sales
If you had a couple of databases called sales on two different machines, you could also specify the host and go:
setora sales@server1
So, whilst I don't claim to understand all that this perl script does, I thought I'd share it here, in the hope that someone else can benefit from Stan's wisdom.
This setup is based in a Unix environment and I'm copying the setup that Stan had configured. If someone wants to edit it, to suit themselves, go for it.
The first thing we need to do is to setup a directory called DBA under /home/oracle (or whereever your default Oracle login directory is).
Then under that, we'll have two more directories called fcn and bin.
In /home/oracle, edit .profile (or the login script specific to your shell, we used kshell) and add a line in there somewhere:
. /home/oracle/DBA/bin/profile.dba
Don't forget the leading period and a space. "Dotting" the script ensures that the variables set within are set in the current shell's context and not spawned into a subshell (which is the Unix default).
Next, let's create a file called /home/oracle/DBA/bin/profile.dba
# Setup various environment variables
set +o nounset
export NODENAME=`uname -n`
export ORACLE_BASE=/oracle
export HOME=/home/oracle
export ORACLE_HOME=${ORACLE_BASE}/product/10.2
export PATH=${PATH}:${HOME}/DBA/bin
# Load DBA functions
export FPATH=${FPATH}:${ORACOMMON}/DBA/fcn
. /home/oracle/DBA/fcn/setora
Next step, is to setup a file called /home/oracle/DBA/fcn/setora
This file loads into the shell a function called setora which it the driver of our bus.
function setora
{
OptList=":hl"
PassArgs=""
Status=0
Usage="Usage: setora [-h] [-l] [sid | sid@host | alias]"
while getopts ${OptList} OptArg
do
case ${OptArg} in
h) PassArgs="${PassArgs}-h " ;;
l) PassArgs="${PassArgs}-l " ;;
\?) print "Invalid Option -${OPTARG}"
print "${Usage}"
Status=255 ;;
esac
done
if [[ ${Status} -eq 0 ]]
then
setora.pl "${PassArgs}${1:-}"
Status=$?
CmndFile=/tmp/setora.set.${LOGNAME}
if [[ ${Status} -eq 0 && -r ${CmndFile} ]]
then
. ${CmndFile}
rm ${CmndFile}
fi
fi
return ${Status}
}
This function calls setora.pl (which will write a command file setting some environment variables) and then execute that file within the current shell context.
Finally, the file that does all the work (that I don't begin to completely understand, because perl isn't my thing), that lives in /home/oracle/DBA/setora.pl:
#!/usr/bin/perl
#
#------------------------------------------------------------------------------
#
main:
{
$Status = &Initialization ;
unless ( $Status ) { $Status = &GetCmndArgs ;}
unless ( $Status ) { $Status = &ParseTnsnamesFile ;}
if ( $RunOptions{'Help'} )
{
unless ( $Status ) { $Status = &ShowCmndHelp ;}
unless ( $Status ) { $Status = &ListTnsData ;}
}
elsif ( $RunOptions{'List'} )
{
unless ( $Status ) { $Status = &ListTnsData ;}
}
else
{
unless ( $Status ) { $Status = &FindTargetDb ;}
unless ( $Status ) { $Status = &CreateCmndFile ;}
}
if ( $Status ) { &PrintErrorMessage ;}
exit ( $Status ) ;
}
#
#------------------------------------------------------------------------------
#
sub Initialization
{
my $LclReturnStatus = 0 ;
# Standard Variables
$Null = "" ;
# Translate current environment variables
$DatPath = $ENV{'DATPATH'} ;
$Home = $ENV{'HOME'} ;
$LdLibraryPath = $ENV{'LD_LIBRARY_PATH'} ;
$LibPath = $ENV{'LIBPATH'} ;
$LogName = $ENV{'LOGNAME'} ;
$LogPath = $ENV{'LOGPATH'} ;
$ManPath = $ENV{'MANPATH'} ;
$NodeName = $ENV{'NODENAME'} ;
$OracleBase = $ENV{'ORACLE_BASE'} ;
$OracleHome = $ENV{'ORACLE_HOME'} ;
$OraCommon = $ENV{'ORACOMMON'} ;
$OutPath = $ENV{'OUTPATH'} ;
$Path = $ENV{'PATH'} ;
$PID = $$ ;
$SqlPath = $ENV{'SQLPATH'} ;
$TmpPath = $ENV{'TMPPATH'} ;
$TnsAdmin = $ENV{'TNS_ADMIN'} ;
$TwoTask = $ENV{'TWO_TASK'} ;
# Include user library area
unshift ( @INC , "$Home/lib" ) ;
unshift ( @INC , "$OracleHome/lib" ) ;
unshift ( @INC , "$OraCommon/DBA/lib" ) ;
# Standard Packages
use Getopt::Std ;
# Return Codes & Messages
$ReturnStatus{'Success'} = 0 ;
$ReturnCode{'Success'} = 'Success' ;
$ReturnText{'Success'} = $Null ;
$ReturnStatus{'NullSIDArg'} = 1 ;
$ReturnCode{'NullSIDArg'} = 'CmdArgMissing' ;
$ReturnText{'NullSIDArg'} = 'SID must be specified' ;
$ReturnStatus{'NoNamesFile'} = 2 ;
$ReturnCode{'NoNamesFile'} = 'FileOpenErr' ;
$ReturnText{'NoNamesFile'} = $Null ;
$ReturnStatus{'MultSIDFound'} = 3 ;
$ReturnCode{'MultSIDFound'} = 'CmdArgInvalid' ;
$ReturnText{'MultSIDFound'} = 'Multiple SIDs found - Must specify host' ;
$ReturnStatus{'SIDNotFound'} = 4 ;
$ReturnCode{'SIDNotFound'} = 'SIDInvalid' ;
$ReturnText{'SIDNotFound'} = $Null ;
$ReturnStatus{'CmndFileErr'} = 5 ;
$ReturnCode{'CmndFileErr'} = 'FileCreateErr' ;
$ReturnText{'CmndFileErr'} = $Null ;
# TNS Data
$TnsCount = -1 ;
$TnsEntry = -1 ;
@TnsAlias = () ;
@TnsHome = () ;
@TnsHost = () ;
@TnsSid = () ;
# Return successful completion status
return $LclReturnStatus ;
}
#
#------------------------------------------------------------------------------
#
sub GetCmndArgs
{
my $LclReturnStatus = $ReturnStatus{'Success'} ;
my $LclOptionList = "hl" ;
# Define Processing Options
getopt( $LclOptionList ) ;
if ( $opt_h ) { $RunOptions{'Help'} = 1 ;}
if ( $opt_l ) { $RunOptions{'List'} = 1 ;}
# Define Command Arguments
$ArgValue{'DbSpec'} = $ARGV[0] ;
if ( $ARGV[0] eq $Null
&& ! $RunOptions{'List'}
&& ! $RunOptions{'Help'} )
{
$LclReturnStatus = $ReturnStatus{'NullSIDArg'} ;
}
return $LclReturnStatus ;
}
#
#------------------------------------------------------------------------------
#
sub ParseTnsnamesFile
{
my $LclReturnStatus = $ReturnStatus{'Success'} ;
my $LclFileSpec = $Null ;
my $LclLineChar = $Null ;
my $LclParenCnt = 0 ;
my $LclParenLeft = 0 ;
my $LclParenRight = 0 ;
my $LclTnsLine = $Null ;
if ( $TnsAdmin eq $Null )
{
$LclFileSpec = join ( "/" , $OracleHome ,
"network" , "admin" , "tnsnames.ora" ) ;
}
else
{
$LclFileSpec = join ( "/" , $TnsAdmin , "tnsnames.ora" ) ;
}
if ( open ( NAMESFILE , $LclFileSpec ) )
{
while ( )
{
chop ( $LclFileLine = $_ ) ;
$LclLineChar = substr ( $LclFileLine , 0 , 1 ) ;
if ( $LclLineChar ne "#" )
{
if (( $LclParenLeft == $LclParenRight ) &&
(( $LclParenLeft != 0 ) || ( $LclParenRight != 0 )))
{
&ParseTnsLine ( $LclTnsLine ) ;
$LclParenLeft = 0 ;
$LclParenRight = 0 ;
$LclTnsLine = $Null ;
}
$LclParenCnt = tr/(/(/ ;
$LclParenLeft += $LclParenCnt ;
$LclParenCnt = tr/)/)/ ;
$LclParenRight += $LclParenCnt ;
$LclTnsLine = join ( $Null , $LclTnsLine , $LclFileLine ) ;
}
}
close ( NAMESFILE ) ;
}
else
{
$LclReturnStatus = $ReturnStatus{'NoNamesFile'} ;
$ReturnText{'NoNamesFile'} = $LclFileSpec ;
}
return $LclReturnStatus ;
}
#
#------------------------------------------------------------------------------
#
sub ParseTnsLine
{
my $LclReturnStatus = $ReturnStatus{'Success'} ;
my $LclTnsLine = $_[0] ;
my $LclLineChar = $Null ;
my $LclLineLength = 0 ;
my $LclLineOffset = 0 ;
my $LclParenLeft = 0 ;
my $LclParenRight = 0 ;
my $LclTnsAlias = $Null ;
my $LclTnsHome = $Null ;
my $LclTnsHost = $Null ;
my $LclTnsSid = $Null ;
my $LclTnsSpec = $Null ;
my $LclTnsSegment = $Null ;
my $LclTnsParm = $Null ;
my $LclTnsValue = $Null ;
( $LclTnsAlias , $LclTnsSpec )= split ( "=" , $LclTnsLine , 2 ) ;
$LclTnsAlias =~ tr/ //d ;
$LclLineLength = length ( $LclTnsSpec ) ;
for ( $LclLineOffset = 0 ; $LclLineOffset < $LclLineLength ;
$LclLineOffset++ )
{
$LclLineChar = substr ( $LclTnsSpec , $LclLineOffset , 1 ) ;
if ( $LclLineChar eq "(" )
{
$LclParenLeft = $LclLineOffset + 1 ;
}
elsif ( $LclLineChar eq ")" )
{
if ( $LclParenLeft != 0 )
{
$LclParenRight = $LclLineOffset ;
$LclTnsSegment = substr ( $LclTnsSpec , $LclParenLeft ,
$LclParenRight - $LclParenLeft ) ;
$LclTnsSegment =~ tr/ //d ;
( $LclTnsParm , $LclTnsValue ) = split ( "=" , $LclTnsSegment ) ;
$LclTnsParm = uc ( $LclTnsParm ) ;
if ( $LclTnsParm eq "HOST" ) { $LclTnsHost = $LclTnsValue ;}
elsif ( $LclTnsParm eq "ORACLE_HOME" ) { $LclTnsHome = $LclTnsValue ;}
elsif ( $LclTnsParm eq "SID" ) { $LclTnsSid = $LclTnsValue ;}
}
$LclParenLeft = 0 ;
$LclParenRight = 0 ;
}
}
$TnsCount++ ;
$TnsAlias[$TnsCount] = $LclTnsAlias ;
$TnsHome[$TnsCount] = $LclTnsHome ;
$TnsHost[$TnsCount] = $LclTnsHost ;
$TnsSid[$TnsCount] = $LclTnsSid ;
return $LclReturnStatus ;
}
#
#------------------------------------------------------------------------------
#
sub ShowCmndHelp
{
my $LclReturnStatus = $ReturnStatus{'Success'} ;
my $LclDash = "-" x 80 ;
my $LclHeaderFormat = "%-27.27s\n" ;
my $LclLineFormat = "%-8.8s %-12.12s %-40.40s\n" ;
printf $LclHeaderFormat , "List of Databases available" ;
printf $LclHeaderFormat , $LclDash ;
printf $LclLineFormat , " SID" , " Host" , " Alias" ;
printf $LclLineFormat , $LclDash , $LclDash , $LclDash ;
return $LclReturnStatus ;
}
#
#------------------------------------------------------------------------------
#
sub ListTnsData
{
my $LclReturnStatus = $ReturnStatus{'Success'} ;
my $LclCount = 0 ;
my $LclLineFormat = "%-8.8s %-12.12s %-40.40s\n" ;
for ( $LclCount = 0 ; $LclCount <= $TnsCount ; $LclCount++ )
{
printf $LclLineFormat , $TnsSid[$LclCount] , $TnsHost[$LclCount] ,
$TnsAlias[$LclCount] ;
}
return $LclReturnStatus
}
#
#------------------------------------------------------------------------------
#
sub FindTargetDb
{
my $LclReturnStatus = $ReturnStatus{'Success'} ;
my $LclCount = 0 ;
my $LclTestAlias = $Null ;
my $LclTestHostSid = $Null ;
my $LclTestSid = $Null ;
for ( $LclCount = 0 ; $LclCount <= $TnsCount ; $LclCount++ )
{
$LclTestAlias = $TnsAlias [ $LclCount ] ;
$LclTestHostSid = join ( "@" , $TnsSid [ $LclCount ] ,
$TnsHost [ $LclCount ] ) ;
$LclTestSid = $TnsSid [ $LclCount ] ;
if ( $ArgValue{'DbSpec'} eq $LclTestSid )
{
if ( $TnsEntry != -1 )
{
$LclReturnStatus = $ReturnStatus{'MultSIDFound'} ;
last ;
}
else
{
$TnsEntry = $LclCount ;
}
}
elsif ( $ArgValue{'DbSpec'} eq $LclTestHostSid )
{
$TnsEntry = $LclCount ;
}
elsif ( $ArgValue{'DbSpec'} eq $LclTestAlias )
{
$TnsEntry = $LclCount ;
}
}
if ( $TnsEntry == -1) { $LclReturnStatus = $ReturnStatus{'SIDNotFound'} ;}
return $LclReturnStatus ;
}
#
#------------------------------------------------------------------------------
#
sub CreateCmndFile
{
my $LclReturnStatus = $ReturnStatus{'Success'} ;
my $LclFileSpec = "/tmp/setora.set.$LogName" ;
my $LclAlias = $TnsAlias [ $TnsEntry ] ;
my $LclHome = $TnsHome [ $TnsEntry ] ;
my $LclHost = $TnsHost [ $TnsEntry ] ;
my $LclSid = $TnsSid [ $TnsEntry ] ;
my $LclTarget = $Null ;
my $LclValue = $Null ;
$DatPath = &ResetPath ( $DatPath , "DBA/dat" ) ;
$LdLibraryPath = &ResetPath ( $LdLibraryPath , "lib" ) ;
$LibPath = &ResetPath ( $LibPath , "DBA/lib" ) ;
$ManPath = &ResetPath ( $ManPath , "DBA/man" ) ;
$Path = &ResetPath ( $Path , "bin" ) ;
$Path = &ResetPath ( $Path , "DBA/bin" ) ;
$SqlPath = &ResetPath ( $SqlPath , "DBA/sql" ) ;
if ( open ( CMNDFILE , ">$LclFileSpec" ) )
{
print CMNDFILE "export DATPATH=$DatPath\n" ;
print CMNDFILE "export LIBPATH=$LibPath\n" ;
print CMNDFILE "export LD_LIBRARY_PATH=$LdLibraryPath\n" ;
print CMNDFILE "export MANPATH=$ManPath\n" ;
print CMNDFILE "export ORACLE_BASE=$OracleBase\n" ;
print CMNDFILE "export ORACLE_HOME=$LclHome\n" ;
print CMNDFILE "export ORACLE_SID=$LclSid\n" ;
print CMNDFILE "export ORAADMIN=$OracleBase/admin/$LclHost/$LclSid\n" ;
print CMNDFILE "export ORAHOST=$LclHost\n" ;
print CMNDFILE "export PATH=$Path\n" ;
print CMNDFILE "export SQLPATH=$SqlPath\n" ;
print CMNDFILE "export TNS_ALIAS=$LclAlias\n" ;
if ( $LclHost eq $NodeName )
{
print CMNDFILE "export ORAENV_ASK=NO\n" ;
print CMNDFILE "export TWO_TASK=\n" ;
print CMNDFILE "export ORAREMADMIN=\n" ;
print CMNDFILE ". oraenv\n" ;
print CMNDFILE "export ORAENV_ASK=\n" ;
}
else
{
print CMNDFILE "export TWO_TASK=$LclAlias\n" ;
print CMNDFILE "export ORAREMADMIN=$OracleBase/admin/$LclSid\n" ;
}
print CMNDFILE "PS1=\"$NodeName:$LogName:$LclSid\@$LclHost >> \"\n" ;
print CMNDFILE "\n" ;
close ( CMNDFILE ) ;
}
else
{
$LclReturnStatus = $ReturnStatus{'CmndFileErr'} ;
$ReturnText{'CmndFileErr'} = $LclFileSpec ;
}
return $LclReturnStatus ;
}
#
#------------------------------------------------------------------------------
#
sub ResetPath
{
my $LclReturnStatus = $ReturnStatus{'Success'} ;
my $LclOrigPath = $_[0] ;
my $LclSubDir = $_[1] ;
my $LclElement = $Null ;
my $LclHome = $TnsHome [ $TnsEntry ] ;
my $LclNewPath = $Null ;
my $LclNewValue = "$LclHome/$LclSubDir" ;
my $LclTarget = "$OracleHome/$LclSubDir" ;
if ( ( $LclOrigPath eq $Null )
|| ( $LclOrigPath eq $LclTarget ) )
{
$LclNewPath = $LclNewValue ;
}
else
{
$LclNewPath = $Null ;
foreach $LclElement ( split ( ":" , $LclOrigPath ) )
{
if ( $LclElement eq $LclTarget )
{
$LclElement = $LclNewValue ;
$LclNewValue = $Null ;
}
if ( $LclElement ne $Null )
{
if ( $LclNewPath eq $Null )
{
$LclNewPath = $LclElement ;
}
else
{
$LclNewPath = join ( ":" , $LclNewPath , $LclElement ) ;
}
}
}
if ( $LclNewValue ne $Null )
{
$LclNewPath = "$LclNewPath:$LclNewValue" ;
}
}
return $LclNewPath ;
}
#
#------------------------------------------------------------------------------
#
sub PrintErrorMessage
{
my $LclArrayKey = $Null ;
foreach $LclArrayKey (sort keys(%ReturnStatus))
{
if ( $ReturnStatus{$LclArrayKey} == $Status )
{
$LclErrKey = $ReturnCode{$LclArrayKey} ;
print "$ReturnCode{$LclArrayKey} $ReturnText{$LclArrayKey} \n" ;
}
}
}
#
#------------------------------------------------------------------------------
#
Make sure all three new files have their owner's executable bit set.
Logout and login again so that the new .profile can be processed and loaded into memory.
For usage help:
setora -h
For a list of known TNS aliases:
setora -l
To set an environment
setora testdb
Try it out and see what you think.
This code was not written by me but by my late friend Stan as mentioned above. Stan had a love of sharing knowledge. I hope that he won't mind me posting this for him. I use it every day and love it.
This post is dedicated to the memory of Stanley L. Yellott. You were a giant among men, my friend, and I still miss your joviality and friendship.
