#!/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;
