Oracle Database Health Check Script
Run this script has sysdba user or any user having DBA Role
Change the spool file patch to correct directory. In this example it is set to
/export/home/oraprod/
$ cat Health_Check_Script.sql
set pagesize 1100
SET MARKUP HTML ON SPOOL ON
spool /export/home/oraprod/health_check_new.html
set feedback off
set linesize 200;
set pages 50
set lines 1000
set pages 70
set heading on
PROMPT================================================================
prompt
PROMPT
PROMPT Daily_Monitoring Checkup Report
prompt =================================================================================================================================================
PROMPT REPORT DATE
select to_char(sysdate,'DD-MON-YYYY:HH:MI:SS') "Report Run Date" from dual;
prompt DATABASE NAME
PROMPT ==============
select instance_name,host_name,version,status from gv$instance ,v$database a;
PROMPT DATABASE INFO
PROMPT =============
select NAME,
CREATED,
LOG_MODE,
CHECKPOINT_CHANGE#,
ARCHIVE_CHANGE#
from v$database;
select status from v$instance;
prompt TABLESPACE INFO
prompt ===================
set linesize 100
set pagesize 100
select a.tablespace_name,
round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
round(SUM(decode(b.maxextend, null,A.BYTES/(1024*1024*1024),b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
(SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024)) USED_GB,round((SUM(decode(b.maxextend,null,A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024)))-(SUM(a.bytes)/(1024*1024*1024)- round(c.Free/1024/1024/1024))),2)FREE_GB,
round(100*(SUM(a.bytes)/(1024*1024*1024)- round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend,null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024)))))USED_PCT
from dba_data_files a,sys.filext$ b,
(SELECT
d.tablespace_name ,sum(nvl(c.bytes,0)) Free
FROM dba_tablespaces d,DBA_FREE_SPACE c
WHERE d.tablespace_name = c.tablespace_name(+)
group by d.tablespace_name) c
WHERE
a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
ORDER BY tablespace_name;
PROMPT SESSION INFORMATION
PROMPT ========================
select status,count(status) Count from v$session group by status;
PRoMPT HIT RATIOS
PROMPT =============
PROMPT SORT STATISTICS THIS SHOULD BE MORE THAN 95 %
SELECT (1-d.VALUE/m.value)*100 "SORT RATIO " FROM V$SYSsTAT d,v$sysstat m
WHERE d.name ='sorts (disk)' and m.name='sorts (memory)';
PROMPT DICTIONARY HIT RATIO
PROMPT Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora
PROMPT ======================
select (1-(sum(getmisses)/sum(gets)))*100 "OVER ALL DICT HIT RATIO" from v$rowcache;
PROMPT THE OVER ALL HITRATIO OF THE LIBRARY CACHE
select sum(gethitratio)/count(*) *100 " LIBRARY CACHE HIT RATIO " from v$librarycache;
PROMPT BUFFER HIT RATIO
PROMPT =================
SELECT (1-PHY.VALUE/(cur.value+con.value))*100 " BUFFER CACHE HIT RATIO " from v$sysstat phy,v$sysstat con,v$sysstat cur
where cur.name='db block gets'
and con.name='consistent gets'
and phy.name='physical reads';
PROMPT INDEX LOOK UP RATIO
PROMPT =====================
SELECT (1-l.VALUE/(l.value+s.value))*100 "INDEX LOOK UP RATIO " FROM V$SYSSTAT l ,v$sysstat s
WHERE s.name ='table scans (short tables)' and l.name= 'table scans (long tables)';
PROMPT DATABASE SIZE
PROMPT ================
PROMPT TOTAL SIZE OF A DATABASE
select sum(bytes)/1024/1024/1024 "Physical Database Size" ,' GB '
from dba_data_files ;
PROMPT ACTUAL SIZE OF DATABASE
select sum(bytes)/1024/1024/1024 "Actual Database Size", ' GB '
from dba_segments ;
PROMPT INVALID OBJECTS
PROMPT =====================
Select count(*) "INVALID OBJECTS",OWNER,object_type from all_objects where status='INVALID' group by owner,object_type order by 2;
PROMPT REDO LOGS AND ARCHIVE STATUS
PROMPT ==================================
COLUMN member_name HEADING 'Member_Name';
COL MEMBER FOR A40;
SELECT vlf.member "member_name",
vl.group# "Group",
vl.status "Status",
vl.archived "Archived",
vl.bytes / 1024 "Size (K)",
vl.sequence# "Sequence"
FROM v$logfile vlf,
v$log vl
WHERE vlf.group# = vl.group#
ORDER BY 1, vl.group#, vlf.member;
PROMPT Session I/O By User
PROMPT ==================================
select nvl(ses.USERNAME,'ORACLE PROC') username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME;
prompt SEGMENTS HAVING LESS THAN 45 FREE EXTENTS
PROMPT ========================================
SELECT owner, segment_name, segment_type, extents, max_extents, next_extent,
initial_extent
FROM dba_segments WHERE max_extents- extents < 45;
PROMPT LOCK INFORMATION
PROMPT ===================
select OS_USER_NAME os_user,
PROCESS os_pid,
ORACLE_USERNAME oracle_user,
l.SID oracle_id,
decode(TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', type) lock_type,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', lmode) lock_held,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', request) lock_requested,
decode(BLOCK,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global', block) status,
OWNER,
OBJECT_NAME
from v$locked_object lo,
dba_objects do,
v$lock l
where lo.OBJECT_ID = do.OBJECT_ID
AND l.SID = lo.SESSION_ID;
PROMPT HIGH RESOURCE CONSUMING SQL
PROMPT ===========================
select sql_text,username,
disk_reads_per_exec,buffer_gets,
disk_reads,parse_calls,
sorts,executions,
rows_processed,
hit_ratio,first_load_time,
sharable_mem,persistent_mem,
runtime_mem,cpu_time,
elapsed_time,address,hash_value
from
(select sql_text ,b.username ,
round((a.disk_reads/decode(a.executions,0,1,
a.executions)),2)
disk_reads_per_exec,
a.disk_reads ,
a.buffer_gets ,
a.parse_calls ,
a.sorts ,
a.executions ,
a.rows_processed ,
100 - round(100 * a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
a.first_load_time ,
sharable_mem ,
persistent_mem ,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value
from
sys.v_$sqlarea a,
sys.all_users b
where
a.parsing_user_id=b.user_id and
b.username not in ('sys','system')
order by 3 desc)
where rownum < 21;
PROMPT RMAN BACKUP Status
PROMPT ====================
col STATUS format a9
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
from V$RMAN_BACKUP_JOB_DETAILS
where start_time > sysdate-1
order by session_key;
PROMPT TEMP TABLESPACE Status
PROMPT ====================
set feedback off echo off
set linesize 200
set pagesize 200
set colsep |
SET TRIM
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total-SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
prompt ******************************************************************************************************
prompt number of tables analyzed in the last 5 days .. we have problem
prompt The below count indicates that tables which had their stats gathered
prompt ******************************************************************************************************
select count(*) from dba_tables where last_analyzed > sysdate-5;
prompt ******************************************************************************************************
prompt ******************************************************************************************************
prompt number of INDEXES analyzed in the last 5 days .. we have problem
prompt The below count indicates that index which had their stats gathered
prompt ******************************************************************************************************
select count(*) from dba_indexes where last_analyzed > sysdate-5;
prompt ******************************************************************************************************
prompt db users who are expired or locked in last 7 days
prompt *************************************************************************************************************
select username , ACCOUNT_STATUS from dba_users where ACCOUNT_STATUS NOT LIKE 'OPEN' and EXPIRY_DATE > sysdate -7;
prompt UNUSABLE INDEXES ON THE DATABASE
prompt *************************************************************************************************************
select index_name,table_owner from DBA_INDEXES where status='UNUSABLE';
prompt CORRUPTED BLOCKS ON THE DATABASE:
prompt *************************************************************************************************************
select count(*) from V$DATABASE_BLOCK_CORRUPTION;
PROMPT Active Incidents:
PROMPT ———————-
set linesize 170
col RECENT_PROBLEMS for a45
select PROBLEM_KEY RECENT_PROBLEMS_1_WEEK_BACK,to_char(FIRSTINC_TIME,'DD-MON-YY HH24:mi:ss') FIRST_OCCURENCE,to_char(LASTINC_TIME,'DD-MON-YY HH24:mi:ss')
LAST_OCCURENCE FROM V$DIAG_PROBLEM WHERE LASTINC_TIME > SYSDATE -1;
PROMPT ORIGINAL HEALTH CHECK
prompt *************************************************************************************************************
select dbid,name,instance_name,host_name,version,platform_name,created,log_mode,open_mode,protection_mode,protection_level
,database_role,switchover_status,force_logging,flashback_on from gv\$database, gv\$instance;
prompt *************************************************************************************************************
select host_name
,instance_name
,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,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 gv\$instance
prompt *************************************************************************************************************
select round(sum(used.bytes) / 1024 / 1024 /1024 ) || ' GB' "Total Database Size" ,round(free.p / 1024 / 1024/1024) || ' GB' "Free space" from (select bytes from v\$datafile
union all
select bytes from v\$tempfile
union all
select bytes from v\$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p ;
prompt *************************************************************************************************************
SELECT lf.INST_ID,l.thread#,
lf.group#,
lf.member,
TRUNC(l.bytes/1024/1024) AS size_mb,
l.status,
l.archived,
lf.type,
lf.is_recovery_dest_file AS rdf,
l.sequence#,
l.first_change#,
l.next_change#
FROM gv\$logfile lf
JOIN gv\$log l ON l.group# = lf.group#
ORDER BY l.thread#,lf.group#, lf.member, lf.INST_ID;
prompt *************************************************************************************************************
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
WITH hours AS (
SELECT TRUNC(SYSDATE) - 1 + ((level-1)/24) AS hours
FROM dual
CONNECT BY level < = 24
)
SELECT h.hours AS date_hour,
ROUND(SUM(blocks * block_size)/1024/1024/1024,2) size_gb
FROM hours h
LEFT OUTER JOIN v\$archived_log al ON h.hours = TRUNC(al.first_time, 'HH24')
GROUP BY h.hours
ORDER BY h.hours;
prompt *************************************************************************************************************
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
order by session_key;
prompt *************************************************************************************************************
WITH sessions AS
(SELECT /*+ materialize*/ username,sid,sql_id, SERIAL#
FROM GV\$session),
locks AS
(SELECT /*+ materialize */ *
FROM GV\$lock)
SELECT s1.sql_id, l2.type,s1.username blocking_user , s1.sid blocking_sid, s1.SERIAL# blocking_SERIAL,
s2.sql_id, s2.username blocked_user, s2.sid blocked_sid, s2.SERIAL# blocked_SERIAL, sq.sql_text
FROM locks l1
JOIN locks l2 USING (id1, id2)
JOIN sessions s1 ON (s1.sid = l1.sid)
JOIN sessions s2 ON (s2.sid = l2.sid)
LEFT OUTER JOIN v\$sql sq
ON (sq.sql_id = s2.sql_id)
WHERE l1.BLOCK = 1 AND l2.request > 0;
prompt *************************************************************************************************************
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V\$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V\$BACKUP_SET_DETAILS d
join V\$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV\$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-7
order by j.start_time;
prompt *************************************************************************************************************
SELECT name, free_mb/1024, total_mb/1024, free_mb/total_mb*100 as percentage
FROM v\$asm_diskgroup;
prompt *************************************************************************************************************
spool off
exit
