#!/bin/ksh set PATH=$PATH:/oracle/SA9/bin ORACLE_SID=SA9; export ORACLE_SID; #ORAENV_ASK=NO #export ORAENV_ASK; #/oracle/SA9/bin/oraenv ORACLE_BASE=/oracle/SA9; export ORACLE_BASE; ORACLE_HOME=/oracle/SA9; export ORACLE_HOME; set $pwd=system/secret_password; export pwd; ALERT_DIR=/oracle/SA9/saptrace/background; export ALERT_DIR; ALERT_DIR_MOUNTPOINT=/oracle/SA9; export ALERT_DIR_MOUNTPOINT; REDO_LOG_FILESYSTEM=/oracle/SA9/saparch; export REDO_LOG_FILESYSTEM; #*********************************************************************** # 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=`expr $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=`expr $redo_log` if [ $oracle_num -gt 90 ] then logger "REV_ORA_030W $ORACLE_SID redo log file system is > 90%. Please c ontact Systems ASAP" echo "$ORACLE_SID redo log file system is > 90%." fi # Invoke SQL*Plus to generate DB space info. /oracle/SA9/bin/sqlplus -s << UNTIL_DONE $pwd 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;