Menu Bar

Tuesday, January 17, 2012

Oracle and SQL Server: Compare and Contrast (Database vs Instance)


"Sacrificing minions: Is there any problem it can't solve?" -Xykon, Order of the Stick webcomic
As it states in my profile, I work in both Oracle and SQL Server on a daily basis. When I was first introduced to SQL Server back in 2008, I had a great deal of trouble wrapping my mind around some of the concepts, since some of them were so strange and counter intuitive to what I was used to.

So, as an exercise in both self-review and in an effort to put something like this 'out there', I thought I might start a series of postings comparing and contrasting Oracle to SQL Server.

This could be looked at as an "Oracle for SQL Server DBA's", or alternatively, "SQL Server for Oracle DBA's", depending upon who's reading it and I'll try to cover both sides of the story.

To get the ball rolling, there are two concepts that form the core of a relational database management system. The terms are used often and just as often are probably used incorrectly. Database and Instance.

What is a database and what is an instance ?

Not surprisingly, the definitions vary depending on what system you're working with

Let's start with Oracle. The Oracle Documentation (Oracle Database Concepts - Oracle 11g Release 2 - E25789-01) defines an instance as "An instance is a set of memory structures that manage database files. The instance consists of a shared memory area, called the system global area (SGA), and a set of background processes. An instance can exist independently of database files."

So, from an Oracle perspective, the instance is the memory processes that manage the files that make up the database.

The database is defined, from the same document, as "A database is a set of files, located on disk, that store data. These files can exist independently of a database instance.".

The two are closely related, and the term "Oracle database" is often used to refer to both under the same umbrella, but in the purest sense, they are separate and distinct.

There are three different types of files that make up a database.

  • Data Files - these contain, funnily enough, your data, as well as the corresponding indexes and any other database objects (procedures and functions, triggers etc).
  • Control Files - these contain the metadata about the physical structure of the database - names and locations of data files and the name of the database, among other things.
  • Redo Log Files - these are a set of two or more files that record transactional changes made to the data in the database. As they fill, they are written out to archive log files and rotate back through the other files in the redo log group.

Also of note is that each instance (set of memory processes) supports one and only one database (at the basic level. Real Application Clusters - RAC - can change that, where you have multiple instances accessing a single set of files, but that is outside of the scope of what I'm talking about here).

The figure below is a representation of what makes up and Oracle instance and database (also from the same Oracle document). It also shows the assorted processes and everything else associated with the database, which is not what we're talking about here. For now, just look at the groupings indicated as Instance (memory) and Database (files).

It is important to note that you can create as many instances as you like from a single set of binaries installed on a server.

SQL Server considers an instance to the be the installation of binaries on a server. Your first install on a server is usually set up to be the Default Instance on the server (but it doesn't have to be). If you install the software a second or third time, these become separate named instances. Each instance can the a Named instance if desired.

Each installed instance has its own set of memory processes when started.

They key difference between the two is that Oracle can spawn multiple sets of Oracle instances (memory processes) from each Oracle software install. SQL Server has a one to one relationship between memory processes and SQL Server software installation.

Buck Woody (blog|twitter) wrote an article talking about SQL Server instances in much more detail than I plan on talking about here, if you're interested in the finer points.

What about SQL Server databases ? A SQL Server database is defined as one or more data files and a log file. In SQL Server, one instance can support multiple databases.

Another difference to be noted here, is that while Oracle rotates Online redo logs through a group in a loop and writes out old changes to archive log files, the SQL Server transaction log will continue to grow and grow and grow unless the database is running in Simple Recovery Mode or a full backup is taken, at which time the log is reset and writing starts again from the beginning of the file.

Upon installation, SQL Server contains several system databases that contain objects shared by all other databases in the instance. From Books Online, the SQL Server system databases are:

  • master - Contains all the instance wide metadata of the instance - logins, endpoints, linked servers, system configuration settings.
  • model - Used as a template for creating all databases in the instance.
  • msdb - Used by SQL Server agent for scheduling jobs. Also used by database mail
  • tempdb - Used for temporary user objects for all databases. Also is the row version store for some isolation levels.
  • Resource Database - a read only database that contains all of the system objects in SQL Server.

I did find an interesting article on SQL Server Central that covered some of what I've written about here and has a summary at the end mapping the system databases of SQL Server to appropriate Oracle entities.

A similarity between both systems is that it is possible to run multiple applications out of a single database, where they could be separated by different schemas, but I haven't seen that done a whole lot. Separating applications into different databases does seem to make maintenance tasks somewhat easier.

Next Time: Some of the Architectural differences between Oracle and SQL Server.

Monday, January 16, 2012

CASE in point, redux


"My friends, you bow to no one" - Aragorn, to the hobbits, Return of the King
In a recent post made for my reference, I came posted the syntax for the SQL Server CASE statement, because I always forget some of the finer points of less used syntax.

Well, it just so happens that I also found myself recently needing the syntax for a CASE statement used inside an update.

In keeping with the other posting, this seems a mighty fine place to record such a thing


update mytable
set col1 =
  (CASE
     WHEN
        (condition)
     THEN
        (value)
     ELSE
        (other value)
     END
  )
where column = value

Another quickie, mainly for my enjoyment at some stage down the road!

Tuesday, January 10, 2012

T-SQL Tuesday #14, er #26: Second Chances


"Whoo-hoo-hoo, look who knows so much. It just so happens that your friend here is only MOSTLY dead. There's a big difference between mostly dead and all dead. Mostly dead is slightly alive." - Miracle Max, The Princess Bride
T-SQL TuesdayA new year is upon us and that inevitably means that there must be a second Tuesday of the month again. Which means it's T-SQL Tuesday again.

David Howard (blog|twitter) is acting as the Master of Ceremonies for the start of the 2012 volume of T-SQL Tuesday and his topic is "Second Chances", much like Westley got in the scene from "The Princess Bride" mentioned at the top. The theme this month is to revisit any of the previous 25 topics and write a little about one of them - without recycling your own or someone else's previous musings.

Before I begin, let me tell you a little story. Once upon a date/time, a young DBA was faced with a Puzzling Situation. He had been having a rough time with this Relationship with his I/O subsystem. For all the Reporting he'd been doing trying to work out what was wrong, he hadn't had much luck. He felt like such a BLOB. He didn't really care though, because it was Summertime, and after picking up his kids from Getting Schooled, he was off for some good old fashioned Beach Time. He had all of his vacation plans Indexed and organized and even had the Misconception that his DBA Skills wouldn't be needed while he was away. However, the Business Wants what the Business thinks it wants and apart from these Resolutions, he was soon paged by his Automated Systems, and had to get on the phone and Aggregate and APPLY his knowledge to help fix a problem with a misbehaving CTE. Thankfully there weren't any Disasters while was away because of the Best Practices he employed - making sure that there was not any Crap Code in the system really helped. His monitoring data was nicely Presented and Joined with some clever Procedures and Functions he was able to Trick his way to a good vacation.

OK, it's a convoluted and long winded story, but hidden in the links above you'll find all 25 of the previous topics embedded for your reference :) Most of them link directly to the roundup / summary but there's one or two where I just couldn't find the darn thing.

As you might have guessed from the topic, I was thinking I might revisit #14 (Resolutions). Being that it's the start of a new year, visiting the list of "Technical Resolutions" is probably not a bad way to get the show on the road. So, what are my technical resolutions for 2012?

  • Attend PASS Summit 2012 - My first ever PASS Summit was 2011 and I came away with such a mental overload that I've probably forgotten half of what I learned. Which leads nicely into ...
  • Review the PASS Summit DVD's - I have all eight of them on my desk (they arrived last Tuesday as I was writing the draft of this post). I need to pay particular attention to the sessions I was in and go over all of it again - and then go over the sessions I did NOT attend.
  • Try to be more active on this blog, making at least one post a week.
  • Upgrade my Oracle credentials. (It's T-SQL post, but this is definitely on my list of things to do this year). Currently sitting certified for 9i, should really try to upgrade to at least 10g, if not 11g.
  • I really need to try to get to the local user group meetings this year. I made plans to go about 4 times in 2011, and every time work got in the way. I need to try to tell myself that unless it's a real Priority One issue, that it can wait 3 hours while I drive over and attend a 2 hour user group meeting! (already blown this one for January, thought it was *next* week, turns out it was *yesterday*... Eleven out of Twelve, maybe?)
  • Learn some Powershell. I've heard so many people evangelizing this tool, that I really need to suck it up and at least learn the basics!
  • Read some, if not all of the tech books I've bought. I have four sitting on my desk, with ebook versions of three of them as well. Time to put my Kindle Fire that Santa brought me to use and get them read! (This doesn't include the shelf full of other books that I've bought and pick through as I need the relevant parts of them)

What remains to be seen is, this time next year, how many of these have check marks next to them and how many have big red crosses.

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.

Friday, January 6, 2012

CASE in point


"They have a cave troll!" - Boromir, The Fellowship of the Ring
I recently needed to write a query where based upon the value of a lookup field, I needed to find the description of a code.

I can never remember the exact syntax of a case statement, so I thought I would throw a post up here with what I did so that I could refer to it later.


select account_code,
       case when account_ind = '1' then  
                             (select cost_center_description 
                              from cost_centers 
                              where cost_center_code = account_code)
            when account_ind = '2' then  
                             (select exp_element_description 
                              from exp_elements 
                              where exp_element_code = account_code)
            when account_ind = '3' then  
                             (select gl_description 
                              from gl_codes
                              where gl_code = account_code)
            else 'Unknown'
       end as account_desc,
open_balance,
end_balance
from account_codes

Nothing ground breaking in this one, but I know that I'll refer to it regularly because nitty gritty things like syntax is something that I often need to look up.

Tuesday, January 3, 2012

It's a New Year, time for a change in theme and a First for me


"I got no rudder. Wind blows northerly, I go north. That's who I am." - Captain Malcolm Reynolds, Serenity
OK, the black background and white text (particularly with the colors of the hot links) was getting too much for these tired old eyes.

Time for a change!

Might try this blue and white thing and see how it holds up.

RMOUG 2012 Also, I was quite happy to find out that I've had an abstract accepted for the Rocky Mountain Oracle Users Group Training Days at the Colorado Convention Center in Denver, CO happening February 15 and 16, 2012.

My paper is titled "Hailing Frequencies Open - An Introduction to Oracle Heterogeneous Services". This will be my first time presenting at a technical conference and while very nervous about the whole thing, I'm quite excited at the same time.

The abstract of the presentation reads:

Abstract: In business today, applications run on multiple platforms, across multiple database systems. You may have your timekeeping application running on Windows with Microsoft SQL Server, yet your payroll system runs on Oracle on Solaris. Perhaps you have some other system using MySQL on Linux. If you need to have your Oracle system talk to both of them, how do you do it? Making Oracle talk to Oracle is easy. How do we make Oracle talk to these alien systems? The answer is Oracle Heterogeneous Services. Learn how to configure Oracle and your other database systems to talk together. Hailing Frequencies Open!

Apart from the obvious Star Trek connotations in the title, I'll try to keep the Trek references to a minimum during the presentation, but there's some definite Trek related slides in the planning to help get a point across.

Definitely looking forward to this one!