Script Completo de Check

This set of scripts can be used to report on Oracle activity.
oracheck.run
This is a Korn shell script that executes the oracheck.ksh script a
nd e-mail you if a problem is detected. The idea behind this script is to allow
the DBA to repair impending problems before that database crashes. Note that th
e script below redirects the output to a file, checks the number of lines in the
output file, and e-mails an alert message if problems are found.
#! /bin/ksh
# Here we write a blank line to the log file . . .
echo `date` > /oracle/my_sid/scripts/oracheck.log
# Now we run the check, writing errors to the oracheck.log file
/oracle/MY_SID/scripts/oracheck.ksh >> \
/oracle/MY_SID/scripts/oracheck.log
# If errors messages exist (2 or more lines), then go on . . .
if [ `cat /oracle/MY_SID/scripts/oracheck.log|wc -l` -gt 1 ]
then
# Now, be sure that we don't clog the mailbox.
# the following statement checks to look for existing mail,
# and only sends mail when mailbox is empty . . .
if [ ! -s /var/spool/mail/oramy_sid ]
then
cat /oracle/MY_SID/scripts/oracheck.log | mail oramy_sid
fi
fi
oracheck.ksh This is a Korn shell script that does the queries against Oracle an
d the Oracle environment. Note that this script loops through all of the databa
ses on the server (using /etc/oratab) and reports any exceptional conditions for
all databases.
#!/bin/ksh
set PATH=$PATH:/oracle/MY_SID/bin
ORACLE_SID=MY_SID;
export ORACLE_SID;
#ORAENV_ASK=NO
#export ORAENV_ASK;
#/oracle/MY_SID/bin/oraenv
ORACLE_BASE=/oracle/MY_SID; export ORACLE_BASE;
ORACLE_HOME=/oracle/MY_SID; export ORACLE_HOME;
set $pwd=system/scion; export pwd;
ALERT_DIR=/oracle/MY_SID/saptrace/background; export ALERT_DIR;
ALERT_DIR_MOUNTPOINT=/oracle/MY_SID; export ALERT_DIR_MOUNTPOINT;
REDO_LOG_FILESYSTEM=/oracle/MY_SID/saparch; export REDO_LOG_FILESYSTEM;
#**************************************************************
# Check if checknet2 deamon is running . . . .
#**************************************************************
#oracle_up=`ps -ef|grep checknet2|grep -v grep|wc -l`;
#oracle_num=èxpr $oracle_up`
#if [ $oracle_num -lt 1 ]
# then echo "checknet2 deamon is NOT up."
#fi
#***********************************************************************
# Here is where we loop through each SID in /etc/oratab . . .
#***********************************************************************
#for ORACLE_SID in `cat /etc/oratab | grep :N|cut -d":" -f1`
#do
#**************************************************************
# Check if Oracle is up . . . .
#**************************************************************
oracle_up=`ps -ef|grep pmon_$ORACLE_SID|grep -v grep|wc -l`;
oracle_num=èxpr $oracle_up`
if [ $oracle_num -lt 1 ]
then echo "$ORACLE_SID instance is NOT up."
fi
#***********************************************************
# Check alert log for ORA-600
#***********************************************************
tail -400 $ALERT_DIR/alert_$ORACLE_SID.log|grep ORA-00600
#***********************************************************
# Check redo log file-system > 90% full
#***********************************************************
redo_log=`df -k|grep $REDO_LOG_FILESYSTEM|awk '{print $4}'|cut -d"%" -f1`
oracle_num=èxpr $redo_log`
if [ $oracle_num -gt 90 ]
then logger "REV_ORA_030W $ORACLE_SID redo log file system is > 90%. Please "
echo "$ORACLE_SID redo log file system is > 90%."
fi
# Invoke SQL*Plus to generate DB space info.
/oracle/MY_SID/bin/sqlplus -s << UNTIL_DONE
$pwd
REM
SET ECHO OFF;
SET TERM OFF;
SET TIMING OFF;
SET HEAD OFF;
SET FEED OFF;
CREATE TABLE oracheck_fs_temp (tablespace_name,total_bytes,free_bytes,max_chunk)
AS
SELECT tablespace_name, NVL(SUM(bytes), 1), 1, 1
FROM dba_data_files
GROUP BY tablespace_name;
UPDATE oracheck_fs_temp a
SET a.free_bytes = (SELECT NVL(SUM(b.bytes), 1)
FROM dba_free_space b
WHERE b.tablespace_name = a.tablespace_name);
COMMIT;
UPDATE oracheck_fs_temp a
SET a.max_chunk = (SELECT NVL(MAX(b.bytes), 1)
FROM dba_free_space b
WHERE b.tablespace_name = a.tablespace_name);
COMMIT;
REM ****************************************************************
REM Tablespaces more than 95% full.
REM ****************************************************************
SELECT
tablespace_name
||
' is '
||
TO_CHAR(ROUND(100-(free_bytes*100/total_bytes), 2)) ||
'% full.' T
FROM oracheck_fs_temp
WHERE 95 < 100-(free_bytes*100/total_bytes)
ORDER BY tablespace_name;
REM ****************************************************************
REM Tablespaces/objects with insufficient space for NEXT extent.
REM ****************************************************************
SELECT
SUBSTR(a.tablespace_name, 1, 21) tablespace ,
SUBSTR(a.owner
, 1, 16) owner
,
SUBSTR(a.segment_name , 1, 30) object_name,
SUBSTR(a.segment_type , 1, 8 ) what
FROM dba_segments a
WHERE a.segment_type IN ('TABLE', 'INDEX', 'ROLLBACK')
AND NVL(a.next_extent, 1) > (SELECT b.max_chunk
FROM oracheck_fs_temp b
WHERE b.tablespace_name = a.tablespace_name)
ORDER BY 1,2,4,3;
DROP TABLE oracheck_fs_temp;
REM *****************************************************************
REM Objects with more than 600 extents.
REM *****************************************************************
REM Applied NVL function purposely to extents column.
SELECT SUBSTR(owner
, 1, 22) owner
,
SUBSTR(segment_name, 1, 30) object_name,
SUBSTR(segment_type, 1, 8 ) type
,
extents
FROM dba_segments
WHERE 600 < NVL(extents, 1)
AND segment_type IN ('TABLE', 'INDEX', 'ROLLBACK')
ORDER BY 1,3,4 DESC,2;
EXIT;
UNTIL_DONE

Script Completo de Check

Uploaded by

Copyright:

Available Formats

Script Completo de Check

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Script Completo de Check

Uploaded by

Copyright:

Available Formats

This set of scripts can be used to report on Oracle activity.

You might also like