数据库巡检脚本

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