• Tag Archives monitor oracle
  • Monitoring Oracle with Solaris Scripting

    Monitoring Oracle

    I wrote these Solaris Unix scripts for a book project about 10 years ago. They are dated but I have used them myself recently to construct useful monitoring scripting in Linux.

    Display active dedicated connections as shown below. Execute the command below excluding |wc -l to view connected dedicated server processes.

    ps -ef |grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l
    # ps -ef |grep $ORACLE_SID|grep -v grep|grep -v ora_
      oracle 24440 24439  0 16:35:42 ?        0:00 oracle<SID> (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
    

    Examining Processes

    Find all Oracle processes. grep -v grep removes the grep command from the list of processes. Also note that the results of one unix shell command can be passed to the next using the | (pipe)
    command.

    ps -ef | grep ora_ | grep -v grep

    Check the listener and the agent.

    ps -ef | grep [lsnr|dbsnmp] | grep -v grep

    Below is a command to kill all Oracle processes. Only do this in absolute desperation. Solaris may require the manual removal of semaphores after executing a command like this. Always try a shutdown immediate | abort within svrmgrl and lsnrctl stop | dbsnmp_stop first. The ps -ef command in combination with grep will produce a list of processes named as “ora_”. Application of the $ORACLE_SID variable to the grep function will filter and include all oracle processes containing the currently set Oracle database service identifier. The awk processor will pull the second column of the output, ie. the process ID. The xargs function will allow construction and evaluation of the command kill -9 <PID>. The {} curly braces are generally used to supply xargs with the argument passed from the previous pipe command.

    ps -ef | grep ora_ | grep -v grep | grep $ORACLE_SID | awk '{print $2}' | xargs kill -9 {}

    Finding Files

    I find myself looking for files on a Unix box constantly. There are a number ways to do this using three commands I can think of off the top of my head, which, whereis and find. Both which and whereis require that the file be in the path ($PATH variable). The find command can be used to search for file names recursively through sub-directories as shown below. The . option shown below can be an absolute or relative path.

    find . -name "listener.ora" -print

    The -print option will display only file names found. However, if this command is executed from the root directory as the oracle user, and obviously the oracle unix user does not have full access to the entire file system, any non-readable directories will be displayed as non-readable in the output. To remove listings of non-readable directories either redirect the results of the file command to another file. Otherwise use a grep command and/or an xargs command to filter and/or re-execute on the find command STDOUT.

    The oracle User .cshrc File

    Setting Variables

    See the .cshrc file in Installing Oracle8i on Solaris in the Oracle User Startup Configuration Files section.

    Setting Aliases

    Aliases in Unix can allow quick access to otherwise difficult to find directories and can even execute stored commands when applied. Be careful with aliases, some really wild things can be done with them, causing some equally wild results. From an Oracle DBA perspective, it is better to use aliases with respect and perhaps only for quick changes to different directories within the oracle user directory structure. And even then, why would you want to go into directories containing Oracle database datafiles. There is no reason for this. In fact, doing so could cause an error such as deleting a datafile OUCH !!!. Note the inclusion of the alias rm ‘rm -i’. This alias will always prompt prior to deletion.

    See the .cshrc file in Installing Oracle8i on Solaris in the Oracle User Startup Configuration Files section.

    Execution of Scripts

    Executing SQL*PLUS or SVRMGRL within a Script

    The short example below will execute a shutdown immediate on the database accessed by the currently set $ORACLE_SID value. Note that if you wish to execute a script such as below as a crontab job you must declare all variables required within the script or execute the .cshrc (.log or .profile) file at the begginning of the execution of each crontab job. Note, when passing scripts as cron-jobs through crontab, crontab cannot see the PATH variable setting. Thus all variables used in scripts must be explicitly set in those scripts.

    #!/bin/sh
    setenv ORACLE_BASE /<mount-point-1>/oracle
    setenv ORACLE_HOME $ORACLE_BASE/product/8.1.7
    setenv LD_LIBRARY_PATH $ORACLE_HOME/lib
    setenv JAVA_HOME $ORACLE_BASE/jre/1.1.8
    setenv ORACLE_SID <SID>
    
    $ORACLE_HOME/bin/svrmgrl<<!
    connect internal;
    shutdown immediate;
    disconnect;
    exit
    !
    

    Scheduling Jobs with crontab

    The scheduled execution of jobs can be handled using what Unix calls a crontab or cron-job. A crontab file can be stored per user, ie. root would have a different crontab file to that of the oracle user for instance. The [-elr] options will allow editing, listing and removal of the user specified crontab file. Note that by default the crontab editor uses a really nasty editor. Set the EDITOR environment variable to the value vi to allow editing of the crontab file with the vi editor.

    /bin/sh; setenv EDITOR vi; crontab [-elr] <username>

    The crontab entry format is basically <execution-time> <process>. The execution time is entered as minute, hour, day, month of year and day of week as shown below. The * (asterisk) is used as a wildcard to indicate repetition.

    minute (0-59)
    hour (0-23)
    day of the month (1-31)
    month of the year (1-12)
    day of the week (0-6 with 0=Sunday).
    

    In the example crontab script below two backups, one export and a trace file removal process are executed. The two backups are executed on Monday to Saturday and Sunday at 03h30. The Sunday backup is a more full-backup than the other backup. The export exports the contents of the Oracle USER schema every day. In a large database exporting anything but small things (not data) would be very impractical. In a production environment typically individual tables can be exproted on a daily basis. However, exporting of specific tables and/or Oracle schemas are probably more applicable to development and testing environments. Developers can quite easily drop tables and other database objects in error. Export is a very quick and easy way of restoration for small database objects, particulary not ridiculously large tables. Also note the inclusion of explicit path names. Also note that the inclusion of passwords into crontab executed shell scripts is a possible security risk. However, if a hacker is in that far then the hacker is in already in anyway. Also Oracle password files tend to give complete access to the database if one knows of the existence of the internal user. As far as I know Oracle is trying to phase out the use of the internal, system and sys users. This security issue may be resolved in the future.

    30 3 * * 1-6 /<mount-point-1>/oracle/product/8.1.7/sbin/backup.sh <password> <SID> <SID>
    30 3 * * 0 /<mount-point-1>/oracle/product/8.1.7/sbin/backup.sh <password> <SID> <SID> All
    30 4 * * 0 /<mount-point-1>/oracle/product/8.1.7/sbin/export.sh <password> <SID> <SID> user
    30 4 * * 0-6 /<mount-point-1>/oracle/product/8.1.7/sbin/utils/delTraceAudit.sh <SID>
    

    Two files in the etc/cron.d directory are the cron.allow and the cron.deny files. If either or both of the files do not exist then their are no restrictions. Obviously inclusion of a username into either allows or denies cronr-jobs for a user specifically declared in /etc/cron.d/cron.allow and /etc/cron.d/cron.deny respectively.

    Running Processes in Background

    The nohup command can be used to run processes or scripts in background. What this means is that a process can be executed and the calling shell can be aborted. A crontab job will execute in background. Note that 2>&1 will redirect standard error messages to the standard output device (1 = standard output device and 2 = standard error device). The final & character in the command causes the process to run in background, freeing up the shell for further use.

    nohup ./$ORACLE_HOME/sbin/backup.sh temp <SID> <SID> > $ORACLE_HOME/logs/backup.log 2>&1 &

    The command nohup ./<shell script> & will send the output to a file called nohup.out.

    You may want to watch the execution of the nohup executed process. Do this by viewing the log file as shown below. Note that the tail -f command will display each line as it is appended to a file such as a log file.

    more $ORACLE_HOME/logs/backup.log
    cat $ORACLE_HOME/logs/backup.log
    tail [-20 | -f] $ORACLE_HOME/logs/backup.log

    You could even email the results to yourself at an email address. This email address could even be a pager or a cellphone. Pagers and cellphones can be used very effectively to pass problematic or potentially problematic results of a script’s execution requiring action by the DBA. Automated database monitoring and O/S level monitoring can be very effective by utilising email as show below.

    nohup $ORACLE_HOME/sbin/backup.sh temp <SID> <SID> > $ORACLE_HOME/logs/backup.log & | mailx -s "backup.log" errors@hostname.com

    Disk and File Management

    Disk Space

    df -k will show mount points and space used.

    # df -k
    Filesystem            kbytes    used   avail capacity  Mounted on
    /proc                      0       0       0     0%    /proc
    /dev/dsk/c0t0d0s0    7670973 6310535 1283729    84%    /
    fd                         0       0       0     0%    /dev/fd
    swap                 1300760     400 1300360     1%    /tmp
    

    The command below could constitute a basis for a disk space running-low warning script.

    # df -k | awk '{print $1 " " $5}'
    Filesystem capacity
    /proc 0%
    /dev/dsk/c0t0d0s0 84%
    fd 0%
    swap 1%
    

    The script below can be used to automate checking of disk space.

    #!/bin/ksh

    export PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME

    export host=`hostname`
    export email=$1
    export USAGE=”$0: Incorrect arguments, Usage: $0 <”email1 email2 … emailn”>”

    if [ -z “$email” ]; then
    echo “$USAGE”
    mailx -s “$USAGE” emergencyEMail@whatever.com < /dev/null
    exit 1
    elif [ `whoami` != ‘root’ ]; then
    echo “$0 aborted – user `whoami` is incorrect, must be user root”
    mailx -s “$0 aborted – user `whoami` is incorrect, must be user root” $email < /dev/null
    exit 1
    fi

    if [ -z “$PATH” ] || [ -z “$host” ]; then
    echo “$0 aborted – variable not defined”
    mailx -s “$0 aborted – variable not defined” $email < /dev/null
    exit 1
    fi

    echo “Executing $0 on $host”

    panic=95
    scream=99

    df -k | awk ‘{
    if (($1 != “Filesystem”) && ($1 != “fd”) && ($1 != “/proc”))
    {
    if ($5 > scream) { print “SCREAM !!! – Disk space on”,host,$1,”@”,$5 }
    else if ($5 > panic) { print “Panic – Disk space on”,host,$1,”@”,$5 }
    }
    }’ scream=$scream panic=$panic host=$host > /usr/local/sbin/logs/diskspace.log

    if [ -s /usr/local/sbin/logs/diskspace.log ]; then

    sub=”Script $0 on $host detected disk space limits exceeded !!!”
    echo $sub
    mailx -s “$sub” $email < /usr/local/sbin/logs/diskspace.log
    exit 1
    fi

    exit 0

    Find and Display Files

    ls -lat – list recently touched files.
    ls -lac – list recently changed files.
    du -s * | sort -n – du -s shows file size only, piping to sort orders the results in ascending order.

    # du -s $ORACLE_HOME | sort -n
    1801978 /<mount-point-1>/oracle/product/8.1.7
    

    du -sk $ORACLE_HOME – changes number displayed from units of 512 bytes to 1024 bytes.

    # du -sk $ORACLE_HOME
    900989  /<mount-point-1>/oracle/product/8.1.7
    

    find . -print | xargs grep -i oracle – finds lines in files, in the current directory recursively, which have the word oracle in them.

    find . -mtime -1 -print – files created or altered today.

  • find . -size +1048576c -print – files greater than 1Mb.

    find . -mtime +7 -exec rm {} ; – remove files in the current directory created more than 7 days ago.

    Backups Using the tar Utility

    Compress <file> with tar cvf <file> | gzip <file> and decompress with gzip -d <file>.tar.gz | xargs tar xvf {}.

    Delete Old Trace and Audit Files

    #!/bin/ksh
    
    export ORACLE_BASE=/<mount-point-1>/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/8.1.7
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/openwin/lib
    export PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME
    
    export host=`hostname`
    export email=$1
    export USAGE="$0: Incorrect arguments, Usage: $0 <"email1 email2 ... emailn">"
    
    if [ -z "$email" ]; then
    echo "$USAGE"
    mailx -s "$USAGE" emergencyEMail@whatever.com < /dev/null
    exit 1
    elif [ `whoami` != 'oracle' ]; then
    echo "$0 aborted - user `whoami` is incorrect, must be user root"
    mailx -s "$0 aborted - user `whoami` is incorrect, must be user root" $email < /dev/null
    exit 1
    fi
    
    if [ -z "$PATH" ] || [ -z "$host" ] || [ -z "$ORACLE_BASE" ] || [ -z "$ORACLE_HOME" ]; then
    echo "$0 aborted - variable not defined"
    mailx -s "$0 aborted - variable not defined" $email < /dev/null
    exit 1
    fi
    
    echo "Executing $0 on $host"
    
    echo Cleaning trace and transaction audit files for $1 upto 14 days ago
    unalias rm
    find $ORACLE_BASE/admin/$1/bdump/*.trc -mtime +14 | xargs rm -f
    find $ORACLE_BASE/admin/$1/udump/*.trc -mtime +14 | xargs rm -f
    find $ORACLE_BASE/admin/$1/cdump/*.* -mtime +14 | xargs rm -f
    find $ORACLE_HOME/rdbms/audit/*.aud -mtime +14 | xargs rm -f
    alias rm 'rm -i'
    

    File Permissions and Ownership

    Change file permissions with the chmod command and change file ownership with the chown command.

    Making Sure the Database is Running

    #!/bin/ksh
    
    export ORACLE_BASE=/<mount-point-1>/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/8.1.7
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/openwin/lib
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    export ORACLE_SBIN=$ORACLE_HOME/sbin
    export PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME
    
    export host=`hostname`
    export email=$1
    export password=$2
    export ORACLE_SID=$3
    export tnsname=$4
    USAGE="$0: Incorrect arguments, Usage: $0 <email> <password> <sid> <tnsname>"
    
    if [ -z "$email" ]; then
    echo "$USAGE"
    mailx -s "$USAGE" emergencyEMail@whatever.com < /dev/null
    exit 1
    elif [ `whoami` != 'oracle' ]; then
    echo "$0 aborted - user `whoami` is incorrect, must be user root"
    mailx -s "$0 aborted - user `whoami` is incorrect, must be user root" $email < /dev/null
    exit 1
    fi
    
    if [ -z "$PATH" ] || [ -z "$host" ] || [ -z "$ORACLE_BASE" ] || [ -z "$ORACLE_HOME" ]
    || [ -z "$password" ] || [ -z "$ORACLE_SID" ] || [ -z "$tnsname" ]; then
    echo "$0 aborted - variable not defined"
    mailx -s "$0 aborted - variable not defined" $email < /dev/null
    exit 1
    fi
    
    echo "Executing $0 on $host"
    
    #check processes
    pmon="`ps -eaf | grep -i pmon | grep -i ora_ | grep -v grep | wc -l`"
    smon="`ps -eaf | grep -i smon | grep -i ora_ | grep -v grep | wc -l`"
    dbwr="`ps -eaf | grep -i dbw | grep -i ora_ | grep -v grep | wc -l`"
    lgwr="`ps -eaf | grep -i lgwr | grep -i ora_ | grep -v grep | wc -l`"
    ckpt="`ps -eaf | grep -i ckpt | grep -i ora_ | grep -v grep | wc -l`"
    reco="`ps -eaf | grep -i reco | grep -i ora_ | grep -v grep | wc -l`"
    arch="`ps -eaf | grep -i arc | grep -i ora_ | grep -v grep | wc -l`"
    processes=`echo "$pmon+$smon+$dbwr+$lgwr+$ckpt+$reco+$arch" | bc`
    
    if [ $processes -eq 0 ]; then
    echo Aborting - Database process ERROR
    echo pmon=$pmon,smon=$smon,dbwr=$dbwr,lgwr=$lgwr,ckpt=$ckpt,reco=$reco,arch=$arch
    mailx -s "Aborting - Database process ERROR" $email > /dev/null
    exit 1
    fi
    
    #check listener
    listener="`ps -eaf |grep lsnr |grep -v grep | wc -l`"
    if [ $listener -eq 1 ]; then
    lsnr_SIDs="`lsnrctl status | grep $2 | grep -v grep | wc -l`"
    if [ $lsnr_SIDs -eq 0 ]; then
    echo Aborting - Listener configuration does not match SID parameter
    mailx -s "Aborting - Listener configuration does not match SID parameter" $email > /dev/null
    exit 1
    fi
    else
    i=0
    while [ $listener -eq 0 ]; do
    if [ $i -gt 1 ]; then
    echo Aborting - Listener is DOWN - failed to restart
    echo Check $TNS_ADMIN/listener.ora configuration
    mailx -s "Aborting - Listener is DOWN - failed to restart" $email > /dev/null
    exit 1
    fi
    lsnrctl start
    listener="`ps -eaf |grep lsnr |grep -v grep | wc -l`"
    i=`echo "$i+1" | bc`
    done
    fi
    
    #check database state
    
    $ORACLE_HOME/bin/sqlplus $3<<!
    connect internal;
    set termout off echo off feed off trimspool on head off pages 0;
    spool $ORACLE_SBIN/logs/databaseAlive.log;
    select name from v$database;
    select open_mode from v$database;
    spool off;
    disconnect;
    exit;
    !
    
    dbState="`cat $ORACLE_SBIN/logs/databaseAlive.log |grep READ |grep -v grep | wc -l`"
    if [ $dbState -eq 0 ]; then
    echo Aborting - database for SID $2 not open
    mailx -s "Aborting - database for SID $2 not open" $email > /dev/null
    exit 1
    fi
    

    ORA-600 Errors in the Alert Log

    The script below will detect and email ORA-00600 errors in the Oracle database alter log file.

    #!/bin/ksh
    
    export ORACLE_BASE=/<mount-point-1>/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/8.1.7
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    export ORACLE_SBIN=$ORACLE_HOME/sbin
    export PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME
    
    export host=`hostname`
    export email=$1
    
    USAGE="$0: Incorrect arguments, Usage: $0 <email>"
    
    if [ -z "$email" ]; then
    echo "$USAGE"
    mailx -s "$USAGE" emergencyEMail@whatever.com < /dev/null
    exit 1
    elif [ `whoami` != 'oracle' ]; then
    echo "$0 aborted - user `whoami` is incorrect, must be user root"
    mailx -s "$0 aborted - user `whoami` is incorrect, must be user root" $email < /dev/null
    exit 1
    fi
    
    if [ -z "$PATH" ] || [ -z "$host" ] || [ -z "$ORACLE_BASE" ] || [ -z "$ORACLE_HOME" ]
    || [ -z "$password" ] || [ -z "$ORACLE_SID" ] || [ -z "$tnsname" ]; then
    echo "$0 aborted - variable not defined"
    mailx -s "$0 aborted - variable not defined" $email < /dev/null
    exit 1
    fi
    
    echo "Executing $0 on $host"
    
    err="`tail -50 $ORACLE_ALERT/alert_$ORACLE_SID.log | grep ORA-00600 | grep -v grep | wc -l`"
    
    if [ $err -gt 0 ]; then
    
    tail -50 $ORACLE_ALERT/alert_$ORACLE_SID.log | grep ORA-00600 | grep -v grep > $ORACLE_SBIN/logs/ora600.log
    sub="Script $0 on $host detected ORA-00600 for SID $ORACLE_SID"
    echo $sub
    mailx -s "$sub" $email < $ORACLE_SBIN/logs/ora600.log
    exit 1
    
    fi
    
    exit 0
    

    Tablespace Size Checking

    This script is intended for a database with non-AutoExtensible tablespace databases. The highlighted sections apply to a standby database only.

    #!/bin/ksh
    
    ORACLE_BASE=/<mount-point-1>/oracle
    ORACLE_HOME=$ORACLE_BASE/product/8.1.7
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/openwin/lib
    JAVA_HOME=$ORACLE_BASE/jre/1.1.8
    TNS_ADMIN=/<mount-point-1>/oracle/product/8.1.7/network/admin
    ORACLE_SID=<SID>
    ORACLE_DBF1=/<moint-point-1>/oracle/oradata/$ORACLE_SID
    ORACLE_DBF2=/<moint-point-2>/oracle/oradata/$ORACLE_SID
    ORACLE_BACKUPS=/<moint-point-2>/backups
    ORACLE_SBIN=$ORACLE_HOME/sbin
    ORACLE_ALERT=$ORACLE_BASE/admin/$ORACLE_SID/bdump
    PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME
    PATH=${PATH}:$ORACLE_HOME/bin:$ORACLE_HOME/sbin
    
    host=`hostname`
    
    if [ -z "$ORACLE_BASE" ] || [ -z "$ORACLE_HOME" ] || [ -z "$LD_LIBRARY_PATH" ] || [ -z "$JAVA_HOME" ]
    || [ -z "$TNS_ADMIN" ] || [ -z "$ORACLE_SID" ] || [ -z "$ORACLE_DBF1" ] || [ -z "$ORACLE_DBF2" ] || [ -z "$ORACLE_BACKUPS" ]
    || [ -z "$ORACLE_SBIN" ] || [ -z "$ORACLE_ALERT" ] || [ -z "$PATH" ] || [ -z "$host" ]; then
    echo "$0 aborted - variable not defined"
    mailx -s "$0 aborted - variable not defined" $email < /dev/null
    exit 1
    fi
    
    USAGE="$0: Incorrect arguments, Usage: $0 <"email1 email2 ... emailn">"
    
    if [ `whoami` != 'oracle' ]; then
    echo "$0 aborted - user `whoami` is incorrect, must be user oracle"
    mailx -s "$0 aborted - user `whoami` is incorrect, must be user oracle" $email < /dev/null
    exit 1
    elif [ -z "$1" ]; then
    echo "$USAGE"
    mailx -s "$USAGE" name@xyz.com < /dev/null
    exit 1
    fi
    
    email=$1
    
    echo "Executing $0 on $host"
    
    panic=95
    scream=99
    
    $ORACLE_HOME/bin/svrmgrl<<!
    connect internal;
    alter database open read only;
    exit
    !
    
    $ORACLE_HOME/bin/sqlplus system/<password><<!
    set term off echo off feedback off show off trim off trims off verify off linesize 132;
    spool $ORACLE_SBIN/logs/tablespace.log;
    SELECT 'Tablespace '||df.tablespace_name "TBS"
    ,round((sum(nvl(fs.bytes,0))/ (df.bytes)) * 100) "%Free"
    ,round(((df.bytes - sum(nvl(fs.bytes,0))) / (df.bytes) ) * 100) "%Used"
    ,round(sum(nvl(fs.bytes/1024/1024,0))) "Mb Free"
    ,round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0))) "Mb Used"
    ,df.autoextensible "AutoExtensible"
    FROM dba_free_space fs,
    dba_data_files df
    WHERE fs.file_id(+) = df.file_id
    GROUP BY df.tablespace_name, df.file_id, df.bytes, df.autoextensible
    ORDER BY df.file_id;
    SELECT round(sum(round(sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Free"
    ,round(sum(round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Used"
    ,sum(round(sum(nvl(fs.bytes/1024/1024,0)))) "Mb Free"
    ,sum(round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) "Mb Used"
    ,sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) "Size"
    FROM dba_free_space fs,
    dba_data_files df
    WHERE fs.file_id(+) = df.file_id
    GROUP BY df.tablespace_name, df.file_id, df.bytes, df.autoextensible
    ORDER BY df.file_id;
    spool off;
    exit
    !
    
    $ORACLE_HOME/bin/svrmgrl<<!
    connect internal;
    shutdown immediate;
    startup nomount;
    alter database mount standby database;
    exit
    !
    
    cat $ORACLE_SBIN/logs/tablespace.log | grep Tablespace | grep -v grep | grep -v SQL> | awk '{
    if (($7 == "NO") && (int($4) > int(scream)))
    {
    print "SCREAM !!! - Non-AutoExtensible Tablespace",$2,"space in database",sid,"on",host,"@",$4"%"
    }
    else if (($7 == "NO") && (int($4) > int(panic)))
    {
    print "Panic - Non-AutoExtensible Tablespace",$2,"space in database",sid,"on",host,"@",$4"%"
    }
    }' scream=$scream panic=$panic host=$host sid=$ORACLE_SID > $ORACLE_SBIN/logs/dbfspace.log
    
    #else if (int($4) > int(scream)) { print "SCREAM !!! - Tablespace",$2,"space in database",sid,"on",host,"@",$4"%" }
    #else if (int($4) > int(panic)) { print "Panic - Tablespace",$2,"space in database",sid,"on",host,"@",$4"%" }
    
    if [ -s $ORACLE_SBIN/logs/dbfspace.log ]; then
    sub="Script $0 on $host in database $ORACLE_SID detected tablespace limits exceeded !!!"
    echo $sub
    mailx -s "$sub" $email < $ORACLE_SBIN/logs/dbfspace.log
    exit 1
    fi
    
    exit 0
    

    Related Postings:
    Unix for Oracle
    Monitoring Oracle Standby with Solaris Scripting