db health check .sh

$ cat health_check.sh

! echo ***INSTANCE STATUS***! echo ***INSTANCE STATUS***
set lines 300
col HOST_NAME for a25
select INSTANCE_NAME,HOST_NAME,STATUS,ACTIVE_STATE,LOGINS from gv$instance;


! echo ***LIST OF INACTIVE SESSIONS***
select inst_id, username,count(1) from gv$session where status='INACTIVE' group by inst_id, username order by 1;



! echo ***LOAD BALANCING***
select count(*),inst_id from gv$process group by inst_id;



! echo ****PROCESS USAGE****
set lines 300
col RESOURCE_NAME for a10
col INITIAL_ALLOCATION for a15
select * from gv$resource_limit where resource_name='processes';


! echo ****SESSION USAGE****
select * from gv$resource_limit where resource_name='sessions';



! echo ***BLOCKING SESSIONS***
select  'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking
from    gv$lock l1, gv$lock l2
where   l1.block =1 and l2.request > 0
and     l1.id1=l2.id1
and     l1.id2=l2.id2
/



! echo ***TABLE LOCKS***
set lines 100 pages 999
col username    format a20
col sess_id     format a10
col object      format a25
col mode_held   format a10
select  oracle_username || ' (' || s.osuser || ')' username
,       s.sid || ',' || s.serial# sess_id
,       owner || '.' || object_name object
,       object_type
,       decode( l.block
        ,       0, 'Not Blocking'
        ,       1, 'Blocking'
        ,       2, 'Global') status
,       decode(v.locked_mode
        ,       0, 'None'
        ,       1, 'Null'
        ,       2, 'Row-S (SS)'
        ,       3, 'Row-X (SX)'
        ,       4, 'Share'
        ,       5, 'S/Row-X (SSX)'
        ,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
from    gv$locked_object v
,       dba_objects d
,       gv$lock l
,       gv$session s
where   v.object_id = d.object_id
and     v.object_id = l.id1
and     v.session_id = s.sid
order by oracle_username
,       session_id
/



! echo ***TABLESPACE USAGE***
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
,       decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
,       decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from    (select tablespace_name, sum(bytes)/1024/1024 used_mb
        from    dba_data_files group by tablespace_name union all
        select  tablespace_name || '  **TEMP**'
        ,       sum(bytes)/1024/1024 used_mb
        from    dba_temp_files group by tablespace_name) tsu
,       (select tablespace_name, sum(bytes)/1024/1024 free_mb
        from    dba_free_space group by tablespace_name) tsf
where   tsu.tablespace_name = tsf.tablespace_name (+)
order   by 4
/



! echo ***ARCHIVE USAGE***

show parameter recovery;

select NAME,TOTAL_MB,FREE_MB,round((FREE_MB*100)/(TOTAL_MB)) PERCENT_FREE from V$ASM_DISKGROUP
/