Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jul 25, 2006

Tips and Tricks for Oracle in a UNIX World

By Steve Callan

The title says it all, except these are some of my personal favorites.

Use a standard setup in shell scripts

Why re-invent the wheel? Any script that relies on access to SQL*Plus or SQL*LDR (on the same machine) can be easily initialized. Take the following code snippet and put it into its own file:

#! /usr/bin/ksh
#<----------------------------------------------------------->
# Check Oracle environment
#<----------------------------------------------------------->
if [ -z "${ORACLE_HOME}" ]
then
    echo ""
    echo "ORACLE_HOME is not set."
    echo "ORACLE_HOME is needed for SQL*Plus and SQL*LDR."
    echo ""
    echo "Exiting $0"
    echo ""
    exit 1
fi
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
    echo ""
    echo "Can not find sqlplus."
    echo ""
    echo "Exiting $0"
    echo ""
    exit 1
fi

Let's say you named the file as shell.txt. Save shell.txt in the user's $HOME directory. If I am logged in as oracle and my home directory is /opt/oracle, then $HOME refers to /opt/oracle. When starting a new script, you have environment checking already added in, you also have the new script file created in the current working directory. The example below checks for ORACLE_HOME and the sqlplus executable. Other executables can be included/checked, but chances are, that if sqlplus exists under ORACLE_HOME, so does sqlldr.

linux.box.net > cat ~/shell.txt > my_new_script.ksh
linux.box.net > more my_new_script.ksh
#! /usr/bin/ksh
#<----------------------------------------------------------->
# Check Oracle environment
#<----------------------------------------------------------->
if [ -z "${ORACLE_HOME}" ]
then
    echo ""
    echo "ORACLE_HOME is not set."
    echo "ORACLE_HOME is needed for SQL*Plus and SQL*LDR."
    echo ""
    echo "Exiting $0"
    echo ""
    exit 1
fi
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
    echo ""
    echo "Can not find sqlplus."
    echo ""
    echo "Exiting $0"
    echo ""
    exit 1
fi

While you are at it, especially if the new script is going to interview the user, add in a "yes or no" block. The script can then test SOME_VARIABLE's value and branch appropriately.

#<----------------------------------------------------------->
# Variable assignment
#<----------------------------------------------------------->
  YN_ANSWER=""
  echo "Are you doing <fill in the blank> (y/n) [Y]: "
  read YN_ANSWER
  while [ "$YN_ANSWER" != "Y" -o "$YN_ANSWER" != "N" ]
  do
  case "$YN_ANSWER"
  in
      y | Y | yes | YES | Yes | "" )  YN_ANSWER="Y"
                                      break;;
      n | N | no  | NO  | No )        YN_ANSWER="N"
                                      break;;
      * ) echo ""
          echo "Please enter y or n (Ctrl-c to quit): "
          read YN_ANSWER;;
  esac;
  done
  if [ "${YN_ANSWER}" = "Y" ]
  then
    SOME_VARIABLE=Y
  else
    SOME_VARIABLE=Y
  fi

Get output from a query returned into a shell script variable

The utility of this is endless as most anything from a query in SQL*Plus can be returned into a shell variable. Although there are other ways of adding a timestamp as part of a file's name, this example uses the date returned from a query and places it into a file's name (e.g., a spool file).

TS=`sqlplus -s username/password@SID <<EOF
    set heading off feedback off verify off
    select to_char(sysdate, 'YYYYMMDD_SSSSS') from dual;
    exit
EOF
`
TS=`echo $TS | tr "[a-z]" "[A-Z]"`
echo "The timestamp is $TS"

As a test, put this into my_new_script.ksh and run it.

linux.box.net> my_new_script.ksh
The timestamp is 20060720_18200

If you are familiar or comfortable using date formatting/masking in UNIX, this approach does everything UNIX does and more because of the numerous ways in which dates can be formatted. You can also include a random number (concatenated with the date query) if the 86,400 seconds in a day in combination with the date is not enough to make a file name unique.

Two things to take note of include the back tick single quotation mark and the use of the tr command. The "`" character is hard to see, especially if your editor allows color mode, so don't forget to include it (or check for it during troubleshooting). The other item is the "tr" (translate) command. This helps to stop carriage/line return when referencing the variable. If your output looks like what is shown below, the tr command's conditioning of the variable's value will make the variable more "normal."

The timestamp is
20060720_18200

Use mail as a file transfer utility

Let's say you just spooled the output of a query into a file. If you work in a PC desktop/UNIX server environment, as is very common, how do you get the spool file to your desktop (or other users, for that matter)? Are you one of those who copies the file to your home directory, starts up PuTTY/WinSCP3/Tectia/etc., copies the file onto your PC, then attached it to an email? If the file is small enough (relatively speaking), send it via mail using:

mail –s "Your subject line" your_name@your_domain.com < file_to_send

The exact name of "mail" can vary across platforms/vendors, but either mail or mailx typically does the trick. Of course, your environment has to be configured to allow mail to be sent from UNIX-land to PC-world.

Armed with this functionality, you can create an alert log checker and have it send mail when it discovers problems or alertable conditions in the end of the alert log. Add a cron job to check every five minutes (as an example). Use the tail command to output the bottom however many lines you want from the alert log into a temp file, grep for ORA-xxxxx or whatever else is of interest, and if you get a hit, send the results or temp file via email.

This method isn't bulletproof in that a lot can happen in five minutes and the scan (using

tail -#_of_lines) may miss the event you were hoping to be alerted on. Not to knock the improvements in OEM/Database Control/whatever, but a cron on UNIX is quite a bit more trustworthy than relying on an Oracle agent. Keep in mind that this isn't an either-or choice. The cron can backup the agent, and vice versa.

Run a script right now

Related to cron jobs is the "at" command. On Linux, running a script is as simple as:

at –f alert_log_checker.ksh now

This is much easier (and safer) to perform when compared to waiting for the scheduled cron job to start, editing the crontab and introducing errors/typos, and preventing output from scrolling in your window. The "now" part can include noon, midnight, 0600, and a variety of other named time formats.

Pick one shell and become proficient in it (for beginners)

Bash, Korn, Bourne, C, and the list goes on and on. Each shell has it own nuances, so instead of having your scripts spread over several shells, pick one and stick with it. The payoff is consistency. You can almost always switch between shells, so if your personal preference is the C-shell (hard to beat the tab key for file name completion), but all "real" work is done the Korn shell, learn how to switch between them.

In Closing

There are scores of books (and bibles) on tips, tricks, techniques, utilities, insider secrets, internals, power tools, complete (but not really, depending on the publisher) reference, essentials, nutshells, and well, you must get the idea by now. They all complement one another because if there were one definitive guide (add that one to the list as well), the market wouldn't have that many books to choose from. You have the freedom to pick and choose the ones you like, so happy hunting. Perhaps some of the ones in this article will make your life and job easier.

» See All Articles by Columnist Steve Callan



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM