DB Health Check

--############# Start Verify Instance Status #############
set linesize 500
set pagesize 500
column  HOST_NAME format a15
column  STATUS format a8
column  INSTANCE_NAME format a13
column  STIME format a35
column  uptime format a55
select Host_Name, Status,  database_status, Instance_Name
      ,'Started At: ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
      ,'Uptime: ' || floor(sysdate - startup_time) || ' days(s) ' ||
       trunc( 24*((sysdate-startup_time) -
       trunc(sysdate-startup_time))) || ' hour(s) ' ||
       mod(trunc(1440*((sysdate-startup_time) -
       trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
       mod(trunc(86400*((sysdate-startup_time) -
       trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from v$instance
/
--############# END Verify Instance Status #############
--#
--#
--############# START Verify ASM Diskgroup Sizes #############
set linesize 132
set pagesize 100
select name,state,total_mb,free_mb,round(((TOTAL_MB - FREE_MB) / TOTAL_MB)*100) as PCT_USED from v$asm_diskgroup
where TOTAL_MB <> 0
/
--############# END Verify ASM Diskgroup Sizes #############
--#
--#
--############# START Verify FLASHBASK AREA USAGE #############
set lines 100
col name format a20
select    name
,    floor(space_limit / 1024 / 1024) "Size MB"
,    ceil(space_used  / 1024 / 1024) "Used MB"
,round((floor(space_limit / 1024 / 1024)  - ceil(space_used  / 1024 / 1024) )/ floor(space_limit / 1024 / 1024)*100,2)  as PCT_FREE
,round((ceil(space_used / 1024 / 1024) / floor(space_limit  / 1024 / 1024))*100,2)  as PCT_USED
from    v$recovery_file_dest
order by name
/
--############# END Verify FLASHBASK AREA USAGE #############
--#
--#
--############# START ASM Candidate Disks Check #############
set linesize 132
set pagesize 100
col disk for a20
select path DISK,OS_MB "Size in MB",header_status "Disk Type"   from v$asm_disk where header_status IN('CANDIDATE','FORMER') order by os_mb desc
/
--############# END ASM Candidate Disks Check #############
--#
--#
--############# START Verify RMAN BACKUPS #############
set pagesize 200
set linesize 200
COL STATUS FORMAT a25
COL INPUT_BYTES_DISPLAY FORMAT a20
COL OUTPUT_BYTES_DISPLAY FORMAT a20
COL hrs    FORMAT 999.99
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,
       INPUT_BYTES_DISPLAY,
       OUTPUT_BYTES_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
where START_TIME between sysdate -30 and sysdate
ORDER BY SESSION_KEY
/
--############# END Verify RMAN BACKUPS #############
--#
--#
--############# START Tablespace Free Report #############
set pagesize 500
column tablespace_name                format a20        heading 'Tablespace'
column used_pct_of_max                format 999        heading 'Used % of Max'
column actual_free_gb                 format 999        heading 'Actual Free GB'
with
tbs_auto as
     (select distinct tablespace_name, autoextensible
      from dba_data_files
      where autoextensible = 'YES'),
files as
     (select tablespace_name, count (*) tbs_files,
             sum (bytes) total_tbs_bytes
      from dba_data_files
      group by tablespace_name),
fragments as
     (select tablespace_name, count (*) tbs_fragments,
             sum (bytes) total_tbs_free_bytes,
             max (bytes) max_free_chunk_bytes
      from dba_free_space
      group by tablespace_name),
autoextend as
     (select tablespace_name, sum (size_to_grow) total_growth_tbs
      from (select tablespace_name, sum (maxbytes) size_to_grow
            from dba_data_files
            where autoextensible = 'YES'
            group by tablespace_name
            union
            select   tablespace_name, sum (bytes) size_to_grow
            from dba_data_files
            where autoextensible = 'NO'
            group by tablespace_name)
      group by tablespace_name)
select a.tablespace_name,
       round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) used_pct_of_max,
       round(autoextend.total_growth_tbs/1024/1024/1024 - round((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/1024/1024/1024)) actual_free_gb
       from dba_tablespaces a, files, fragments, autoextend, tbs_auto
where a.tablespace_name = files.tablespace_name
  and a.tablespace_name = fragments.tablespace_name
  and a.tablespace_name = autoextend.tablespace_name
  and a.tablespace_name = tbs_auto.tablespace_name(+)
--  and round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) >=$pct_thresh
  order by used_pct_of_max desc
/
--############# END Tablespace Free Report #############
--#
--#
--############# START Review ALERT LOG for ERRORS #############
SELECT TO_CHAR (ORIGINATING_TIMESTAMP) ORIGINATING_TIMESTAMP, MESSAGE_TEXT MESSAGE_TEXT
 FROM X$DBGALERTEXT
 WHERE ORIGINATING_TIMESTAMP > (SYSDATE - 1)
 AND MESSAGE_TEXT LIKE '%ORA-%'
UNION ALL
SELECT TO_CHAR (ORIGINATING_TIMESTAMP) ORIGINATING_TIMESTAMP, MESSAGE_TEXT MESSAGE_TEXT
 FROM X$DBGALERTEXT
 WHERE ORIGINATING_TIMESTAMP > (SYSDATE - 1)
 AND MESSAGE_TEXT LIKE '%Global Enqueue Services Deadlock detected%'
ORDER BY ORIGINATING_TIMESTAMP DESC
/
select ORIGINATING_TIMESTAMP, message_text from X$DBGALERTEXT
where ORIGINATING_TIMESTAMP >= sysdate -1
order by ORIGINATING_TIMESTAMP desc
/
select ORIGINATING_TIMESTAMP, message_text from X$DBGALERTEXT
where ORIGINATING_TIMESTAMP BETWEEN '03-JUL-13 7:00:00.000 AM -04:00' AND '03-JUL-13 10:30:00.000 AM -04:00'
order by ORIGINATING_TIMESTAMP
/
--############# END Review ALERT LOG for ERRORS #############