Health Checkup Script

spool dbcheck.log

prom          =============================================================
prom          **************     OS Related Informations  *****************
prom          =============================================================

!echo "prom" > os_rep.sql
!echo "prom System Date : `date`" >> os_rep.sql
!echo "prom" >> os_rep.sql
!echo "prom OS/platform : `uname -a`" >> os_rep.sql
!echo "prom" >> os_rep.sql
!echo "prom Oracle_owner: `id`" >> os_rep.sql
!echo "prom" >> os_rep.sql
!echo "prom Oracle_home : $ORACLE_HOME" >> os_rep.sql
!echo "prom" >> os_rep.sql

@os_rep.sql
prom
prom          =============================================================
prom          **************     Database Information     *****************
prom          =============================================================
set linesize 80
break on today
column today noprint new_value xdate
select substr(to_char(sysdate,'fmMonth DD,YYYY,HH:MI:SS P.M.'),1,35) today from dual;
column db noprint  new_value dbname

ttitle "1. DATABASE: "dbname" :.Database name Info .(AS OF:"xdate")" skip 2
select name as  db from v$database;
ttitle off
column name format a15
column created format a15
column log_mode format a15
select name, created, log_mode from v$database;

ttitle "2. :========= Database Block Size Info ==================:" skip 2
prom
column value format a10
column name format a15
select name,value from v$parameter where name='db_block_size';
prom
prom

ttitle "3. :========== Oracle Version Info ======================:" skip 2
select * from v$version;
prom
prom

ttitle "4. :========== Oracle Installed Options Info ============:" skip 2
set pagesize 80
set feedback off
select * from v$option;
prom
clear columns
ttitle "5.  :=========== Archive Location Info ==================:" skip 2
set linesize 80
column destination format a25
select destination as "Archive log Location" from v$archive_dest;
prom

ttitle "6. :=========== Instance Name Info ==============:" skip 2
set heading off
select 'Database Name:      "'||name||'"' from v$database;
ttitle off
select 'Instance Name:      "'||instance_name||'"' from v$instance;
select 'Host     Name:      "'||host_name||'"' from v$instance;
select 'Oracle Version:     "'||version||'"' from v$instance;
set heading on
prom
prom

ttitle "7. :========== Control files Info ================:" skip 2
column name format a60
select * from v$controlfile;
prom
prom

ttitle "8. :========== Redolog Files Info ================:" skip 2
column member format a60
select group#,member from v$logfile;
prom

ttitle "9. :========== Redolog Files Size Info ============:" skip 2
select group#, sequence#, members, bytes , archived, status from v$log;
prom



ttitle "10. :============= Tablespace With Datafiles name Info ===================:" skip 2
set pagesize 33
set linesize 132
column file_name format a40
column tablespace_name format a15
column M_Bytes format 99999.99
select tablespace_name,file_name,file_id,(bytes/1024/1024) M_Bytes,status,autoextensible
from sys.dba_data_files
order by tablespace_name;

ttitle "11. :============== Tablespace Usage Information ==================:" skip 2
set linesize 100
col Total format 99999.99 heading "Total space(MB)"
col Used  format 99999.99 heading "Used space(MB)"
col Free  format 99999.99 heading "Free space(MB)"
break  on  report
compute sum of  Total space(MB) on  report
compute sum of  Used space(MB)  on report
compute sum of  Free space(MB)  on report
select a.tablespace_name, round(a.bytes/1024/1024,2)  Total,
round( nvl( b.bytes,0)/1024/1024,2)  Used,
round(nvl(c.bytes, 0)/1024/1024,2)  Free ,
round(nvl(b.bytes,0)*100/nvl(a.bytes,0),2) "% Used"
from  sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
where a.tablespace_name=b.tablespace_name(+)
and b.tablespace_name=c.tablespace_name(+);
clear breaks
clear columns
set verify on


ttitle "12. :================ TABLESPACE FRAGMENTATION REPORT =================:" skip 2
      select  substr(ts.name, 1,10) TableSpace,
             to_char(f.file#,990) "file #",
             tf.blocks blocks,
             sum(f.length) free,
             to_char(count(*),9990) frags,
             max(f.length) bigst,
             to_char(min(f.length),999990) smllst,
             round(avg(f.length)) avg,
             to_char(sum(decode(sign(f.length-5), -1, f.length,0)),99990) dead
     from    sys.fet$ f, sys.file$ tf, sys.ts$ ts
     where   ts.ts# = f.ts#
     and     ts.ts# = tf.ts#
     group by ts.name, f.file#, tf.blocks;
   
prom
prom
   select tablespace_name,
      percent_blocks_coalesced
      from   sys.dba_free_space_coalesced
      order by percent_blocks_coalesced;
prom
prom


ttitle "13. :=========== Temporary Tablespace Defaults Report ================:" skip 2
        clear breaks
        clear computes
        clear columns
        column init/next format a20
        column min/max format a10
        column pct format 999 heading "Pct|Inc"
        column tablespace_name format a24 heading "Tablespace"
        select  tablespace_name,
                initial_extent||'/'||next_extent "Init/Next",
                min_extents||'/'||max_extents "Min/Max",
                pct_increase pct_inc,
                status
        from    sys.dba_tablespaces
        where   tablespace_name like 'TEMP%';
prom


ttitle "14. :=========== Tablespace Storage Report ==========================:" skip 2
 set linesize 90
 set pagesize 33
 column TABLESPACE_NAME format A15
  select TABLESPACE_NAME,INITIAL_EXTENT,
 NEXT_EXTENT,MIN_EXTENTS "MIN_ext",MAX_EXTENTS,
 PCT_INCREASE "PCT_inc",STATUS
 from sys.dba_tablespaces order by 1;
prom


ttitle  "15. :==================== DataFile's Disk Activity ========================:" skip 2
REM  Datafile I/O distribution, across all datafiles
set linesize 80
set pagesize 100
column File_Name format A18 wrap word
select DF.Name File_Name,
       FS.Phyblkrd Blocks_Read,
       FS.Phyblkwrt Blocks_Written,
       FS.Phyblkrd+FS.Phyblkwrt Total_IOs,
        status Status,
        (bytes/1024/1024) M_Bytes
  from V$FILESTAT FS, V$DATAFILE DF
 where DF.File#=FS.File#
 order by FS.Phyblkrd+FS.Phyblkwrt desc;
prom
prom

ttitle "16. :=================== User Informations ===========================:" skip 2
set line 80
set pagesize 100
clear columns
prom
  column "Username" format a15
  column "Account Status" format a15
  column "Default Tablespace" format a20
  column "Temporary Tablespace" format a20
  select distinct d.username "Username",d.account_status "Account Status",
 d.default_tablespace "Default Tablespace",
 d.temporary_tablespace "Temporary Tablespace"
 from dba_users d, v$pwfile_users p
 where p.username(+)=d.username order by initcap(d.username);
prom
prom


ttitle "17. :======== Total No. of Objects TABLES,Indexes..etc.(User Vice) =======:" skip 2
clear breaks
clear computes
break on "Schema" skip 1
compute sum of "No.of objects" on "Schema"
select owner "Schema",object_type "Object Type",count(*) "No.of objects" from sys.dba_objects
where owner not in ('SYS','SYSTEM') group by owner,object_type
order by owner,object_type;
prom


ttitle "18. :================== Rollback Information ========================:" skip 2
prom
prom
select COUNT(V$ROLLSTAT.USN)  Num_Rollbacks,
       V$WAITSTAT.Count       Rollback_Header_Waits
  from V$WAITSTAT, V$ROLLSTAT
 where V$ROLLSTAT.Status = 'ONLINE'
   and V$WAITSTAT.Class = 'undo header'
 group by V$WAITSTAT.Count;


ttitle "19. :================== Rollback Information ========================:" skip 2
set pagesize 100
set linesize 90
select  substr(dba_segments.OWNER,1,8) "Owner",
        substr(dba_segments.TABLESPACE_NAME,1,10) "Ts_Name",
        substr(dba_segments.SEGMENT_NAME,1,10) "Roll_Name",
        substr(dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Ext",
        substr(dba_rollback_segs.NEXT_EXTENT,1,10) "Next Ext",
        substr(dba_segments.MIN_EXTENTS,1,5) "MinEx",
        substr(dba_segments.MAX_EXTENTS,1,5) "MaxEx",
        substr(dba_segments.PCT_INCREASE,1,5) "%Incr",
        substr(dba_rollback_segs.STATUS,1,10) "Status"
from dba_segments, dba_rollback_segs
where dba_segments.segment_name = dba_rollback_segs.segment_name and
      dba_segments.segment_type = 'ROLLBACK';
prom
prom

ttitle "20. :===================== Rollback Status===========================:" skip 2
set linesize 80
select substr(V$rollname.NAME,1,20) "Rollback_Name",
        substr(v$rollstat.WAITS,1,6) "WAITS",
        substr(v$rollstat.WRAPS,1,6) "WRAPS",
        v$rollstat.SHRINKS,
        substr(v$rollstat.EXTENDS,1,6) "EXTEND",
        v$rollstat.AVESHRINK,
        v$rollstat.OPTSIZE
from v$rollname, v$rollstat
where v$rollname.USN = v$rollstat.USN
order by v$rollname.USN;
prom
prom




ttitle "21. :====================== DEFRAGMENTATION NEEDS ==================:" skip 2
set linesize 80
select  substr(de.owner,1,8) "Owner",
        substr(de.segment_type,1,8) "Seg Type",
        substr(de.segment_name,1,20) "Table Name (Segment)",
        substr(de.tablespace_name,1,20) "Tablespace Name",
        count(*) "Frag NEED"
       from sys.dba_extents de, v$datafile df
where de.owner <> 'SYS'
and de.file_id = df.file#
and de.segment_type = 'TABLE'
group by de.owner, de.segment_name, de.segment_type, de.tablespace_name,
df.name
having count(*) > 1
order by count(*) desc;
ttitle off

ttitle "22. :================ Total Size Of The Database Info ===================:" skip 2
prom
column "Total Size of Database in MB" format 999999.99
select sum(bytes/1024/1024) "Total Size of Database in MB" from sys.sm$ts_avail;
prom


ttitle "23. :=============== Total Used Size Of The Database Info ===============:" skip 2
prom
column "Total Used Size of Database in MB" format 999999.99
select sum(bytes/1024/1024) "Total Size of Database in MB" from sys.sm$ts_used;
prom



ttitle "24. :================ Total Free Size Of The Database Info ==============:" skip 2
prom
prom
column "Total Free Size Database in MB"  format 999999.99
select sum(bytes/1024/1024) "Total Free Size Database in MB" from sys.sm$ts_free;
prom
prom
ttitle off

rem ===============================================================================
rem    SGA Information
rem ================================================================================

prompt **********************************************************
prompt *****            SGA  Information                    *****
prompt **********************************************************
prom

ttitle "25. Database: "dbname":.Info About SGA Size..(As of:"xdate")" skip 2
column name format a40
clear columns
clear breaks
clear computes
column value format 9,999,999,999
break on report
compute sum of value on report
select name,value from v$sga;

prom
rem----------------------------------------------------------------
rem  Buffer Cache Hit Ratio
rem---------------------------------------------------------------
prom

ttitle  "26. :==================== Buffer Cache Hit Ratio =====================:" skip 2
column "Physical Reads" format 99,999,999,999
column "Consistent Gets" format 99,999,999,999
column "DB Block Gets" format 99,999,999,999
column "Hit Ratio" format 999.99
clear breaks
set linesize 80
select
   SUM(DECODE(Name, 'consistent gets',Value,0)) "ConstGets",
   SUM(DECODE(Name, 'db block gets',Value,0)) "DbBlkGets",
   SUM(DECODE(Name, 'physical reads',Value,0)) "PhyReads",
   ROUND(((SUM(DECODE(Name, 'consistent gets', Value, 0))+
    SUM(DECODE(Name, 'db block gets', Value, 0)) -
    SUM(DECODE(Name, 'physical reads', Value, 0)) )/
   (SUM(DECODE(Name, 'consistent gets',Value,0))+
    SUM(DECODE(Name, 'db block gets', Value, 0)))) *100,2)|| '%' "Hit Ratio(90%))"
   from V$SYSSTAT;
prom
prom


clear columns
prom
rem -------------------------------------------------------------
rem             Shared Pool Size - Gets and Misses
rem -------------------------------------------------------------

ttitle "27. :================== FREE MEMORY IN SHARED POOL REPORT ===============:" skip 2

column pool format A15
column name format A15
      select pool,name,
              (bytes/1024/1024) "Free Memory in MB"
      from   v$sgastat
      where  name = 'free memory';


ttitle "28. :============= Shared Pool Size (Execution Misses) ==================:" skip 2

column "Executions" format 999,999,990
column "Cache Misses Executing" format 999,999,990
column "Data Dictionary Gets" format 999,999,999
column "Get Misses" format 999,999,999
select sum(pins) "Executions",
       sum(reloads) "Cache Misses Executing",
     (sum(reloads)/sum(pins)*100) "%Ratio (STAY UNDER 1%)"
from v$librarycache;
prom
prom

ttitle "36. :============ Shared Pool Size (Shared SQL Area Hit Ratio) ==========:" skip 2
column Miss_Ratio format 999.99
column Hit_Ratio format 999.99
select
   SUM(Pins) Execs,
   SUM(Reloads) Cache_Misses,
   DECODE(SUM(Pins),0,0,(SUM(Reloads)/SUM(Pins))*100)
      Miss_Ratio,
   DECODE(SUM(Pins),0,0,((SUM(Pins)-SUM(Reloads))/SUM(Pins))*100)
      "HIT_RATIO (STAY ABOVE 99%)"
from V$LIBRARYCACHE;
prom
prom

ttitle "29. :=============== Shared Pool Size (Dictionary Gets) =================:" skip 2
select sum(gets) "Data Dictionary Gets",
       sum(getmisses) "Get Misses",
       100*(sum(getmisses)/sum(gets)) "%Ratio (STAY UNDER 15%)"
from v$rowcache;

ttitle off
prom
prom

rem -------------------------------------------------------------
rem             Log Buffer
rem -------------------------------------------------------------

ttitle "30. Database: "dbname" :.. Log Buffer..(As of: "xdate")" skip 2
column name format A30
select  substr(name,1,35) name,substr(value,1,15) "VALUE (Near 0?)"
 from v$sysstat
where name = 'redo log space requests';
ttitle off
prom
prom

ttitle "31. :=================== Log Buffer Size =============================:" skip 2
column log_buffer_size format A20
select  A.Value  Log_Buffer_Size,
      B.Value  Log_Buffer_Space_Waits
  from V$PARAMETER A, V$SYSSTAT B
 where A.Name = 'log_buffer'
   and B.Name = 'redo log space requests';
prom
prom

ttitle off




ttitle "32. :================ Shared SQL Area User Statistics ========:" skip 2
set linesize 80
clear columns
clear breaks
set pagesize 60 heading off termout off echo off verify off
REM
col val1 new_val lib noprint
select 100*(1-(SUM(Reloads)/SUM(Pins))) val1
  from V$LIBRARYCACHE;

ttitle off
col val2 new_val dict noprint
select 100*(1-(SUM(Getmisses)/SUM(Gets))) val2
  from V$ROWCACHE;

ttitle off
col val3 new_val phys_reads noprint
select Value val3
  from V$SYSSTAT
 where Name = 'physical reads';

ttitle off
col val4 new_val log1_reads noprint
select Value val4
  from V$SYSSTAT
 where Name = 'db block gets';

ttitle off
col val5 new_val log2_reads noprint
select Value val5
  from V$SYSSTAT
 where Name = 'consistent gets';

ttitle off
col val6 new_val chr noprint
select 100*(1-(&phys_reads / (&log1_reads + &log2_reads))) val6
  from DUAL;

ttitle off
col val7 new_val avg_users_cursor noprint
col val8 new_val avg_stmts_exe    noprint
select SUM(Users_Opening)/COUNT(*) val7,
       SUM(Executions)/COUNT(*)    val8
  from V$SQLAREA;
ttitle off
set termout on
set heading off
ttitle -
  center  'SGA Cache Hit Ratios' skip 2

select  'Data Block Buffer Hit Ratio : '||&chr db_hit_ratio,
        '  Shared SQL Pool                        ',
        '  Dictionary Hit Ratio      : '||&dict dict_hit,
        '  Shared SQL Buffers (Library Cache)                        ',
        '    Cache Hit Ratio         : '||&lib lib_hit,
        '    Avg. Users/Stmt         : '||
               &avg_users_cursor||'         ',
        '    Avg. Executes/Stmt      : '||
               &avg_stmts_exe||'            '
  from DUAL;
prom
prom
prom


set heading on
set linesize 80
column name format A25

ttitle "Database: "dbname" :*****....END OF REPORTS....******(As of: "xdate")" skip 2
set heading off
select '----------------------------------------------------------------------'  from dual;
ttitle off
spool off
!df -k >> dbcheck.log
exit