oracle 自动化巡检脚本(V1)

#!/bin/bash
file_name=F:\check.txt
sqlplus system/manager as sysdba <<eof
@F:\oracle_check.sql
eof
echo '######################################检查后台进程######################################'>>$file_name
ps -ef|grep -v grep|grep OINMS|wc -l>>$file_name
ps -ef|grep -v grep|grep -E "ora_dbw0|ora_lgwr|ora_smon|ora_pmon|ora_ckpt|ora_reco|ora_arc0">>$file_name
echo '######################################检查监听进程######################################'>>$file_name
ps -ef|grep -v grep|grep lsn>>$file_name
lsnrctl status>>$file_name
echo '######################################查看报错日志记录######################################'>>$file_name
tail -100 /oracle/admin/OINMS/bdump/alert_OINMS.log|grep -iE "ora-|err|fail"|sort -u >>$file_name
echo '######################################检查核心转储目录######################################'>>$file_name
ls /oracle/admin/OINMS/udump

 

以下为oracle_check.sql文件的内容：

spool F:\check.txt
set linesize 320
set pagesize 2000
prompt #############################检查实例状态#############################
col instance_name for a20
col host_name for a20
col startup_time for a20
col status for a10
col database_status for a20
select instance_name,host_name,startup_time,status,database_status from v$instance;
prompt #############################检查数据库状态#############################
select name,log_mode,open_mode from v$database;
prompt #############################检查控制文件#############################
col name for a60
col is_recovery_dest_file for a20
col status for a10
select name,is_recovery_dest_file,status from v$controlfile;
prompt #############################检查在线日志#############################
col group# for 99999
col status for a10
col type for a10
col member for a35
col is_recovery_dest_file for a20
select * from v$logfile;
prompt #############################检查表空间是否存在离线#############################
select tablespace_name,status from dba_tablespaces where status<>'ONLINE';
prompt #############################检查数据文件是否存在离线#############################
select name,status from v$datafile where status<>'ONLINE';
prompt #############################检查回滚段是否存在离线#############################
select segment_name,status from dba_rollback_segs where status<>'ONLINE';
prompt #############################检查无效对象#############################
col owner for a20
col object_name for a40
col object_type for a20
select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';
prompt #############################检查表空间使用情况#############################
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;
prompt #############################检查初始化文件中的相关参数值#############################
select resource_name,max_utilization,initial_allocation,limit_value from v$resource_limit;
prompt #############################检查扩展异常的对象#############################
select Segment_Name, Segment_Type, TableSpace_Name,
(Extents/Max_extents)*100 Percent
From sys.DBA_Segments
Where Max_Extents != 0 and (Extents/Max_extents)*100>=95
order By Percent;
prompt #############################检查system表空间的内容#############################
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM';
prompt #############################检查会话连接情况#############################
col sid for 9999999
col serial# for 999999999
col username for a10
col program for a30
col machine for a30
col status for a10
select sid,serial#,username,program,machine,status from v$session;
prompt #############################检查等待事件#############################
col event for a60
select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';
prompt #############################Disk Read最高的SQL语句的获取#############################
col SQL_TEXT for a100
SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM<=5; 
prompt #############################查找前十条性能差的sql#############################
col SQL_TEXT for a60
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10;
prompt #############################等待时间最多的5个系统等待事件#############################
col EVENT for a40
col wait_class for a20
SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
prompt #############################检查运行很久的sql#############################
COLUMN USERNAME FORMAT A12
COLUMN OPNAME FORMAT A16
COLUMN PROGRESS FORMAT A8
col sql_text for a60
SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,
TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS ,
V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;

prompt #############################检查碎片程度最高的表#############################
col TABLE_NAME for a30
SELECT segment_name table_name,COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);
prompt #############################检查死锁#############################
col sid for 999999
col username for a10
col schemaname for a10
col osuser for a16
col machine for a16
col terminal for a20
col owner for a10
col object_name for a30
col object_type for a10
select sid,serial#,username,SCHEMANAME,osuser,MACHINE,
terminal,PROGRAM,owner,object_name,object_type,o.object_id
from dba_objects o,v$locked_object l,v$session s
where o.object_id=l.object_id and s.sid=l.session_id;
prompt #############################检查失效索引#############################
col index_name for a50
col owner for a30
select index_name,owner,table_name from dba_indexes where status !='VALID';
prompt #############################检查不起作用的约束#############################
SELECT owner, constraint_name, table_name, constraint_type, status FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P';
prompt #############################检查无效的触发器#############################
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';
spool off
exit