Fast Small Oracle Remote Analyzer
From OraWiki
fsora - Fast Small Oracle Remote Analyzer
Oracle and SAP are trademarks hold by Oracle and SAP Corporation.
Contents |
Author
| (c) 1992-2007 by Gerald Roehrbein | ||
|---|---|---|
| Author | ||
| Name | Gerald Roehrbein | |
| http | www.oraforecast.com | |
| Gerald.Roehrbein@OraForecast.com | ||
| Release | 0.99 | |
| Created | 2007.11.02 | |
| Last update | 2009.04.15 | |
| Changes | New Content | |
| Estimated duration of installation | 5 minutes | |
| License | GPL 2.0 | |
Additional Watchdog_license. The license of this stuff is identical to Watchdog's license.
Purpose
A small package with more than 200 functions for ad hoc Oracle database performance and hang analysis and a script wrapper to be able to write your own extensions for this great and very useful utility.
This tool is very fast to install. It fit's to a small floppy disk and the Oracle database admin will be able to do fast system checks remote via a phone line offering a very small bandwidth.
Installation
Simple:
Extract tarball into your scripts directory in Oracle DBA Unix account. Remark: This software is also running in Windows environments running CygWin.
Add environment variable FSORA to users environment. Should point to shell script fsora. Add environment variable FSORA to PATH.
Advanced:
If you want to do remote analysis than you have to have a running Oracle client able to execute SQL*Plus. Than you have to install the software into a user account able to run SQL*Plus or in a Windows environment into a CygWin shell running under a user account able to run SQL*Plus.
Than add your connection data to the fsora source code using an editor and set the environment variable REMOTE_DB to the given URI of the database you want to analyze as used in tnsnames.ora.
Usage
- Just call fsora without param. This will show all functions fsora offers
- Just call fsora <function name> to execute a function
There is a help facility available. If you call fsora help <function name> than you will get online instructions for described function.
List of functions
Functions grouped by topic. You will find almost everything you need to analyze a slow or hanging Oracle database. I will link to each function name a page at this Wiki with their documentation.
Click here to see a list of all available functions
Recommendations
There are NO security features or questions in fsora implemented. It's recommended to read the code before you use it. KILL scripts and REORG analysis scripts will do their job without a safety question!
Example(s)
The next image shows an uptime report generated by fsora.
Security related issues
Since you are able to do remote analysis using the environment variable REMOTE_DB and accounts stored in fsora shell script you should limit access to fsora in this case to your personal account!
Just use chmod 700 fsora in this case.
How to make this feature as secure as possible
Install fsora direct at the database server you want analyze!
Source code of this extension
#!/usr/bin/bash
START=`date +%s`
echo "Starttime "`date`
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
#
# If using CygWin or Linux just add a link from awk to nawk in awk directory!
# Maybe it is required to change the shebang line pointing to /usr/bin/bash as needed by your OS
# This script is tested using Linux, Windows CygWin, SUN Solaris and AIX with bash shell!
#
wkd=`dirname $0`
if [ "$REMOTE_DB" == "" ]
then
username='/ as sysdba'
fi
if [ "$REMOTE_DB" == "DEMO" ]
then
username='demo/demo@'$REMOTE_DB' as sysdba'
fi
cd $wkd
function runSql ()
{
cmd=`echo $1 | nawk ' { print toupper($1) }'`
echo "Executing $cmd"
echo "Please wait."
echo "exit" | sqlplus -S "$username" << EOF
set arraysize 4096
column USERNAME format a20
column PROCESS format a30
column LOGON format a17
set timing of
@@$cmd.SQL $2 $3 $4
exit
EOF
}
function listJobs ()
{
i=1
for n in ./*.SQL
do
str="$i $n"
echo $str | nawk ' { printf "%04i",$1 ;print ". "substr($2,3,length($2)-6) } '
i=$[ $i + 1 ]
done
echo "Starte Job mit fsora [-n <NUMMER>] | [<NAME>] + <OPTIONALE PARAMETER z.B SID>."
echo "Hilfe zum Job erh�lt man mit fsora help <NAME>."
}
function runJob()
{
i=1
for n in ./*.SQL
do
if [ $i == $1 ]
then
str=`echo $n | nawk ' { print substr($1,3,length($1)-6) } ' `
runSql $str $2 $3 $4 $5
fi
i=$[ $i + 1 ]
done
}
function showHelp()
{
cmd=`echo $1 | nawk ' { print toupper($1) }'`
less HELP/$cmd.HLP
}
# runSql "BUFFER_HIT_RATIO"
case $1 in
"--?")
listJobs
;;
"-h")
listJobs
;;
"HELP")
showHelp $2
;;
"Help")
listJobs
;;
"help")
showHelp $2
;;
"-help")
listJobs
;;
"-n")
runJob $2 $3 $4 $5
;;
*)
if [ "$1" == "" ]
then
listJobs
else
cmd=`echo $1 | nawk -F, ' { print upper $1 } ' `
runSql $cmd $2 $3 $4 $5
fi
;;
esac
echo ""
echo "REMOTE_DB="$REMOTE_DB
echo "Oracle SQLPlus Script Wrapper V 0.91 beta"
echo "(c) Gerald Roehrbein 1992-2007"
echo "Endtime "`date`
END=`date +%s`
ELAPSEDTIME=`expr $END - $START`
echo Runtime $ELAPSEDTIME seconds
Download of FSORA
You can get a tarball containing fsora and hundreds of scripts from our site. There is actual no english documentation available and just a preview of a performance optimization book in German language, I'm actual writing on, as a PDF and some help files for a few functions. So if you use fsora than please be sure to use only those functions you really understand. For me fsora is the best and useful Oracle analysis tool I've ever used but it is sometimes hard to understand.
Click here to go to FSORA download directory
Documentation
German language online help and book
Preview of Performance book (totally draft)
Online documentation
English language online help and book
Sample sessions
Planned enhancements
It's just simple to write enhancements for fsora. Just add a .SQL file with upper case name to the fsora working directory and if you want to add an online help than just add an upper case .HLP to the HELP subdirectory using same name as function.
Discussions
- Some functions are redundant. I'm sorry but this tool was written by a DBA under heavy pressure. I will try to change this.
- One idea of the Watchdog project is to add the functions of fsora and to allow to generate a history and execute some of the functions of fsora periodically and to add the regression analysis module I've already documented at CPM and Performance Sizing and Forecast for Oracle Databases (Remark: I will allow online acces to the regression analysis and statistics tool in a few days).
Maybe there are some guys which understand the great benefit of having such a performance monitoring and analyzing tool interested into working on such a piece of software in a small team. My dream is to make this really running. I could but I'm still living in a country which punish their companies and people with strange taxes and masses of paperwork so I have no chance to grow.
If you are from outside Germany than consider to hire me. I would like to work and live in Canada, Australia or NZ.
History
- 1992.05.07 Start of project
- 2009.03.09 Added set timing off to be Oracle 11 compatible
- 2009.04.15 Added some format options required in LINUX/AIX environments
- 2009.06.09 Added elapsed time
Known bugs
- Reported Description of problem Fixed Release
- yyyy.mm.dd error xy at line yyyy.mm.dd n.nn</pre>
