Menu Bar

Monday, January 9, 2012

SETORA: A Utility to set up a Unix Session for Oracle in a multi DB Environment


"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.

1 comment:

  1. Oracle's integrated Customer Relationship Management (CRM) solution is a set of applications that give you information-driven sales, service, and marketing. Mark Hurd

    ReplyDelete