scripts/LJCOPY/db_monitoring/tst02/database_health.sh

#!/bin/sh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=`echo ${1} | tr "[a-z]" "[A-Z]"`
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
TTITLE CENTER ->    'DATABASE :'${ORACLE_SID}
SELECT NAME,OPEN_MODE FROM V\$DATABASE;
TTITLE CENTER ->    'DATABASE : DATAGUARD STATUS'
SELECT SWITCHOVER_STATUS FROM V\$DATABASE;
SELECT thread#, max(SEQUENCE#) FROM V\$ARCHIVED_LOG where APPLIED='YES' group by thread#;
archive log list
TTITLE CENTER ->    'DATABASE : ASM DISKGROUP'
select NAME,(100-FREE_MB/TOTAL_MB*100) from V\$asm_diskgroup;
TTITLE CENTER ->    'DATABASE : RMAN BACKUP STATUS'
col STATUS format a9
col hrs format 999.99
set pages 800
select
SESSION_KEY, INPUT_TYPE, STATUS,
 to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
   to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
    elapsed_seconds/3600                   hrs
  from V\$RMAN_BACKUP_JOB_DETAILS
 where END_TIME > sysdate -1
order by session_key;
TTITLE CENTER ->    'DATABASE : TABLESPACE REPORT'
set linesize 132 tab off trimspool on
set pagesize 105
set pause off
set echo off
set feedb on

column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format  9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00

select a.tablespace_name,
       a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
       a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
       nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
       (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
       sum(bytes) physical_bytes,
       sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
       from dba_data_files
       group by tablespace_name ) a,
     ( select tablespace_name, sum(bytes) tot_used
       from dba_segments
       group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and   a.tablespace_name not like 'UNDO%'
order by 1;
TTITLE CENTER ->    'DATABASE : BLOCKING SESSION REPORT'
set pages 800
select sid,sql_id,status,module,blocking_session from V\$session where blocking_session is not null;
TTITLE CENTER ->    'DATABASE : LONG RUNNING SESSION REPORT'
set pages 800
select sid,TOTALWORK,ELAPSED_SECONDS,SQL_ID,SOFAR,OPNAME from V\$session_longops where time_remaining>0;
TTITLE CENTER ->    'DATABASE : ALERT LOG ORA ERRORS REPORT'
col RECORD_ID for 9999999 head ID
col ORIGINATING_TIMESTAMP for a20 head Date
col MESSAGE_TEXT for a120 head Message
set lines 800
set pages 800

SET VERIFY OFF
SET TERMOUT OFF

column 1 new_value 1
SELECT '' "1" FROM dual WHERE ROWNUM = 0;
--define query = '&1'

SET TERMOUT ON

select 
    record_id,
    to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'),
    message_text 
from 
    x\$dbgalertext 
where 
    lower(MESSAGE_TEXT) like lower(DECODE('ORA-', '', '%', '%ORA-%'))
	and originating_timestamp > sysdate - 1; 

SET HEAD OFF
select 'Filter on alert log message => '||NVL('ORA-','%') from dual;    
SET HEAD ON
undefine 1
EOF
echo "U01 FILESYSTEM USAGE"
df -HlP | grep '/u01' | awk '{ print $5 " " $6 }'