To Check ORA-Error on Oracle Database using Shell Scripts on Solaris/Unix.

To Check ORA-Error on Oracle Database using Shell Scripts on Solaris/Unix.

Following Scripts will check ORA Error and send alert on mail.

#!/bin/bash

Emails="mention email"

message="******ORA Error@`uname -n` ******"

export message

# an error message is displayed and the script exits with a status of 1

#if [ $1 ]

#then

    ORACLE_SID=ORCL; export ORACLE_SID

##Set the Environment##

. /arch/oracle/devora/ora10g/ORCL.env

cd $ORACLE_HOME/admin/ORCL/bdump

# Copy the current alert log into a temporary file and empty the original

tail -10 alert_$ORACLE_SID.log /arch/oracle/orcl/testscript/logs/alert_$ORACLE_SID.log.temp

# Check the copy in the temporary file for ORA- errors

grep  ORA- /arch/oracle/orcl/testscript/logs/alert_$ORACLE_SID.log.temp >> /arch/oracle/orcl/testscript/logs/ORA_Error.log

grep Shut /arch/oracle/devora/testscript/logs/alert_$ORACLE_SID.log.temp >> /arch/oracle/orcl/testscript/logs/ORA_Error.log

# If found, email the Oracle user with the contents of the alert log

if [ $? = 0 ]

then

    mailx -s  "$message" $Emails < \

    /arch/oracle/orcl/testscript/logs/ORA_Error.log

fi

#remove the temp file.

rm /arch/oracle/orcl/testscript/logs/alert_$ORACLE_SID.log.temp

rm /arch/oracle/orcl/testscript/logs/ORA_Error.log

=====================================================================

This scripts checks for any ORA errors at alert log file and if found any ORA error send an  alert e-mail.

Schedule it with crontab to run every 20 minutes to check error.

Schedule shell script for tablespace report in oracle database

In this article we will explain 'how to generate Tablespace reprot' in oracle database and automate alert on mail using mailx.

To pull out tablespace report in oracle database . We will prepare the shell script as below and schedule it using crontab.

Step 1:-

copy the below scripts in shell scripts as tablespacerpt.sh and grant the permission to execute shell script.

$chmod a+x tablespacerpt.sh


#!/sbin/sh
ORACLE_SID=ORCL; export ORACLE_SID
. /oracle/home/ora10g/ORCL.env

/oracle/home/ora10g/ora10g/bin/sqlplus -S -M "HTML ON TABLE 'BORDER="8"'" user/password @/oracle/home/ora10g/tablespacereport.sql

cat /oracle/home/ora10g/mailheader.log TABLESPACE_REPORT.html | mailx -t;

-----------------------

SET SERVEROUTPUT ON SIZE 1000000;
SET LINESIZE 9999;
set pagesize 400;
COLUMN TABLESPACE HEADING 'TABLESPACE' ENTMAP OFF
COLUMN TOTAL_SIZE(MB) HEADING 'TOTAL_SIZE(MB)'
COLUMN USED_SIZE(MB)  HEADING 'USED_SIZE(MB)'
COLUMN LARGEST_EXTENTS HEADING 'LARGEST_EXTENTS'
COLUMN FREE_SIZE(MB) HEADING 'FREE_SIZE(MB)'
COLUMN USED_USAGE HEADING 'USED_USAGE'
COLUMN FREE_USAGE HEADING 'FREE_USAGE'

SPOOL ABLESPACE_REPORT.html

select substr(A.tablespace_name,1,16) "TABLESPACE",SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024
"TOTAL_SIZE(MB)",(SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)))
"USED_SIZE(MB)",ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE_SIZE(MB)",MAX(A.max_extents)
"LARGEST_EXTENTS",TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%'
"USED_USAGE",
TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE_USAGE"
from dba_tablespaces A,DBA_DATA_FILES B,DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME
order by 6 desc;

SPOOL OFF
exit:

-------------------
Step 2:- In this step we will create the mailheader file to send tablespace report on email.

Copy the below content in file name mailheader.log


From:abc@bc.com
To:abcd@bc.com
Cc:
Subject:Tablespace Report
Content-Type: text/html

after that schedule it using crontab as below.

Edit crontab :- $crontab -e

00 04 * * * /oracle/home/ora10g/tablespacerpt.sh

save it by press Esc + : then wq!.