Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1614073
  • 博文数量: 409
  • 博客积分: 6240
  • 博客等级: 准将
  • 技术积分: 4908
  • 用 户 组: 普通用户
  • 注册时间: 2011-06-01 00:04
文章分类

全部博文(409)

文章存档

2021年(1)

2019年(1)

2017年(1)

2016年(13)

2015年(22)

2013年(4)

2012年(240)

2011年(127)

分类:

2011-12-04 23:17:33

原文地址:数据库巡检脚本 作者:baroquesoul

for i in $(ps -ef | grep ora_pmon_ | grep -v grep | awk -F '{print $1}');
do
su - $i -c /tmp/check-ora.sh;
whoami;
done

for i in $(ps -ef | grep ora_pmon_ | grep -v grep | awk -F '_' '{print $3}');
do
ORALOG=/tmp/Oracle_Check_$(hostname)_$i_$(date +%Y-%m-%d).log
echo $(date +%Y-%m-%d) >$ORALOG
ps -ef | grep ora_ |grep $i | grep -v grep >> $ORALOG
lsnrctl status >>$ORALOG
tnsping $i >>$ORALOG
tail -1000  /oracle/admin/$i/bdump/alert_$i.log | grep ^ORA- >> $ORALOG
export ORACLE_SID=$i;
sqlplus "/as sysdba" @/tmp/ora_check.sql >$ORALOG;
done


/tmp/ora_check.sql

set pagesize 1000
set linesize 300
-----------------instance status----------------------------------
select status from v$instance;
-----------------archive log list----------------------------------
archive log list;
col member for a40
select a.group#,a.type,a.member,b.SEQUENCE#,b.BYTES,b.ARCHIVED,b.STATUS from v$logfile a,v$log b where a.group#=b.group#;
-----------------Data file----------------------------------
col name for a50
select a.name,a.status,b.tablespace_name,b.autoextensible from v$datafile a,dba_data_files b where a.name=b.file_name;
-----------------Control file----------------------------------
select * from v$controlfile;
-----------------Tablespace used----------------------------------
SELECT tablespace_name,sum_m,sum_free_m, to_char(100*(sum_m-sum_free_m)/sum_m,'99.9') ||'%' AS pct_used
FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),
( SELECT tablespace_name AS fs_ts_name,sum(bytes/1024/1024) AS sum_free_m
FROM dba_free_space GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name ;
-----------------pool----------------------------------
show parameter pool;
-----------------spfile----------------------------------
show parameter spfile;
-----------------Invalid Object----------------------------------
SELECT owner, object_name, object_type FROM dba_objects where status = 'invalid';
-----------------Disabled table----------------------------------
SELECT owner, constraint_name, table_name, constraint_type, status FROM dba_constraints  WHERE status = 'DISABLED' AND constraint_type = 'P';
-----------------Disabled Trigger----------------------------------
SELECT owner, trigger_name, table_name, status FROM dba_triggers      WHERE status = 'DISABLED';
-----------------PGA Sorts----------------------------------
select a.value "Disk Sorts", b.value "Memory Sorts",  round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2) "Pct Memory Sorts" from v$sysstat a, v$sysstat b where a.name = 'sorts (disk)' and b.name = 'sorts (memory)';
-----------------Exit-----------------------------------------
quit

阅读(675) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~