oracle巡检脚本

source /home/oracle/.bash_profile
sqlplus / as sysdba 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
--and PARSING_SCHEMA_NAME = 'YYII_S6MISM'
ORDER BY 4 DESC;

prompt 回滚段检查
select segment_name,status from dba_rollback_segs;

prompt 最高等待的十个事件

SELECT *
FROM ( SELECT event, time_waited
FROM v$system_event
ORDER BY time_waited DESC)
WHERE ROWNUM < 11;

prompt 临时表空间使用情况

SELECT D.TABLESPACE_NAME,
SPACE "Total(M)",
USED_SPACE "USED(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 0) "USED(%)",
NVL (FREE_SPACE, 0) "FREE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / 1048576, 0) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / 1048576, 0) USED_SPACE,
ROUND (SUM (BYTES_FREE) / 1048576, 0) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 4;

spool off
exit
EOF

集群检查
prompt 集群状态
crsctl stat res -t
集群节点信息
olsnodes -n
集群网段检查
oifcfg getif
集群状态检查
crsctl check crs
ocr运行状态检查
ocrcheck
voting disk 检查
crsctl query css votedisk
ocr自动备份信息检查
ocrconfig -showbackup
asm磁盘检查（在asm实例中）
select GROUP_NUMBER,NAME,TOTAL_MB/1024,FREE_MB/1024 from v$asm_diskgroup;

--表空间碎片数量---
select a.tablespace_name,count(1) "碎片量" from
dba_free_space a,dba_tablespaces b
where a.tablespace_name =b.tablespace_name
and b.extent_management = 'DICTIONARY'
group by a.tablespace_name
having count(1) >20
order by 2;

--自由空间碎片索引--
select a.tablespace_name,sqrt(max(a.blocks)/sum(a.blocks)) * (100 / sqrt(sqrt(count(a.blocks)))) FSFI
from dba_free_space a,dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents not in ('TEMPORARY', 'UNDO')
group by a.tablespace_name
order by 2;