Oracle 数据库日常巡检脚本，通用版， 主要是资源使用情况，可增加导出awr等相关报告，后续优化，如有题问题，可联系我， 具体脚本如下: ``` #!/bin/bash #===================================================== # summary: Daily check script,eg:tablespace resource usage info # note:root_exec.linux:/bin/bash unix:/usr/bin/ksh # support: Oracle10g/11g linux/aix/unix # default logfile: /opt/xunjian/logs # 2020/03/27, modify listener_time and display format # 2020/05/08, modify if statement bug,find listenerlog_11g # 2021/10/14,modify Output format and environment variable # # # --By Firsouler # --mail:longzhimeng99@sina.com #===================================================== #if grid exist,use grid_profile,if grid not exist ,use oracle_home sname=`uname` id grid >/dev/null 2>&1 ZT=`echo $?` # if rac CL=`ps -ef | grep crsd.bin |grep -v grep | wc -l` # if asm CS=`ps -ef | grep cssd.bin |grep -v grep | wc -l` if [[ $sname = 'Linux' && $ZT -eq 0 && $CS -eq 1 ]]; then . /home/grid/.bash_profile >/dev/null 2>&1 elif [[ $sname = 'AIX' && $ZT -eq 0 && $CS -eq 1 ]]; then . /home/grid/.profile >/dev/null 2>&1 elif [[ $sname = 'Linux' && $ZT -ne 0 ]]; then . /home/oracle/.bash_profile >/dev/null 2>&1 elif [[ $sname = 'AIX' && $ZT -ne 0 ]]; then . /home/oracle/.profile >/dev/null 2>&1 fi #manual set environment variable #export ORACLE_BASE=/u01/app/grid #export ORACLE_HOME=/u01/app/11.2.0/grid #export PATH=$ORACLE_HOME/bin:$PATH SH_HOME=/opt/xunjian if [ ! -d $SH_HOME ] then mkdir -p $SH_HOME/logs else mkdir -p $SH_HOME/logs fi LOG_FILE=$SH_HOME/logs/db_check_`date +'%Y%m%d%H%M%S'`.log echo -e " =========================================================================== ** Start execution...... " #DB check echo "***************************************************************" >>$LOG_FILE echo " Check_time `date +'%Y/%m/%d'` " >> $LOG_FILE echo " 1.db:tablespace,db_size,archivelog_info,alertlog " >>$LOG_FILE echo " 2.os_info:filesystem_size,systemlog,cpu,memory " >>$LOG_FILE echo " 3.listener_log_info " >>$LOG_FILE echo " 4.cluster_info " >>$LOG_FILE echo " 5.os_resource_info " >>$LOG_FILE echo "***************************************************************" >>$LOG_FILE echo "" >>$LOG_FILE echo "" >>$LOG_FILE echo "-- 1.db check" echo "*************1.db check*****************" >> $LOG_FILE chmod 777 $LOG_FILE su - oracle <<EOF sqlplus -S / as sysdba > /tmp/spoolfile_temp1989.log set linesize 2000 set pagesize 30000 set long 999999999 set longchunksize 999999 SET NEWPAGE NONE SET HEADING ON SET SPACE 1 SET TRIMOUT ON SET TERMOUT OFF SET TERM OFF SET ECHO OFF set FEEDBACK OFF set trimspool on set serveroutput on set colsep ' | ' --spool '/tmp/spoolfile_temp1989.log' --DB VERSION prompt ##1.1.version prompt select * from v\$version; --DB sum size Gb prompt prompt ##1.2.Db_size prompt select trunc(sum(bytes)/1024/1024/1024,2) as "DB_SIZE(GB)" from dba_segments; --transactions Per Day prompt prompt ##1.3.Transaction_per_day prompt select instance_number, metric_unit, to_char(trunc(begin_time),'yyyy-mm-dd') time, avg(average)*60*60*24 "Transactions Per Day" from DBA_HIST_SYSMETRIC_SUMMARY where metric_unit = 'Transactions Per Second' group by instance_number, metric_unit, trunc(begin_time) order by instance_number; --tablespace use size for autoextend DB prompt prompt ##1.4.Tablespace_use_info_includeAutoextend prompt col tablespace_name for a25 select f.tablespace_name tablespace_name, round((d.sumbytes/1024/1024/1024),2) total_noextend, round(((d.sumbytes+d.extend_bytes)/1024/1024/1024),2) total_extend, round((f.sumbytes+d.Extend_bytes)/1024/1024/1024,2) free_extend, round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_G, round((d.sumbytes-f.sumbytes)*100/(d.sumbytes+d.extend_bytes),2) used_percent_extend from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f, (select tablespace_name,sum(aa.bytes) sumbytes,sum(aa.extend_bytes) extend_bytes from (select nvl(case when autoextensible ='YES' then (case when (maxbytes-bytes)>=0 then (maxbytes-bytes) end) end,0) Extend_bytes,tablespace_name,bytes from dba_data_files) aa group by tablespace_name) d where f.tablespace_name= d.tablespace_name order by used_percent_extend desc; --top 30 segment size prompt prompt ##1.5.Segment_size_top30 prompt col owner format a15 col Segment_Name format a40 col segment_type format a15 col tablespace_name format a25 col object_name for a30 with temp_select as (select * from(select owner, Segment_Name,segment_type,tablespace_name,trunc(Sum(bytes)/1024/1024/1024,2) as GB From dba_segments group by owner,Segment_Name,segment_type,tablespace_name order by GB desc) where rownum < 31) select * from (select t.*,l.table_name as object_name from temp_select t,dba_lobs l where t.segment_name=l.segment_name union select t.*,t.segment_name as object_name from temp_select t where t.segment_type not in ('LOBSEGMENT')) order by GB desc; --schema size prompt prompt ##1.6.User_size prompt col owner for a20 select owner, count(*),trunc(sum(bytes)/1024/1024/1024,2) as GB from dba_segments group by owner order by GB desc; --archivelog message prompt prompt ##1.7.Archivelog_count_info prompt set numw 4 col Total for 9999 col Day for a3 col "ID" for 9 SELECT THREAD# "ID", to_char(first_time,'mm-dd') "Date", to_char(first_time, 'Dy') "Day", count(1) "Total", SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0", SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1", SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2", SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3", SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4", SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5", SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6", SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7", SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8", SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9", SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10", SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11", SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12", SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13", SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14", SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15", SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16", SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17", SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18", SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19", SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20", SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21", SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22", SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23" FROM V\$log_history where to_date(first_time)>to_date(sysdate-15) group by THREAD#,to_char(first_time,'mm-dd'), to_char(first_time, 'Dy') Order by 1,2; set numw 20 --archivelog total/day=(rmam:execute "list archivelog all" Before deleting) prompt prompt ##1.8.Archivelog_size_perDay prompt select to_char(trunc(completion_time),'yyyy-mm-dd') as "Date",count(*) as "Count",trunc(((sum(blocks*block_size))/1024/1024),2) as "MB" from v\$archived_log where STANDBY_DEST ='NO' and to_date(first_time)>to_date(sysdate-15) group by trunc(completion_time) order by trunc(completion_time) desc; --not delete archivelog sum size prompt prompt ##1.9.Archivelog_size_NotDelete prompt select THREAD# "ID",status,trunc(((sum(blocks*block_size))/1024/1024),2) as "MB" from v\$archived_log where STANDBY_DEST ='NO' and deleted='NO' group by THREAD#,status; --asm diskgroup prompt prompt ##1.10.Asm_diskgroup_info prompt col name format a15 set lines 200 with temp_asm as (select name,trunc(free_mb/1024,2) free_gb,trunc(total_mb/1024,2) total_gb,trunc(REQUIRED_MIRROR_FREE_MB/1024,2) rm_free_gb,trunc(USABLE_FILE_MB/1024,2) uf_gb,type,state from v\$asm_diskgroup) select * from ( select t.*,trunc((total_gb-free_gb)/total_gb*100,2) as used_percent from temp_asm t where type='EXTERN' union all select t.*,trunc(((total_gb-free_gb)/2)/((total_gb-rm_free_gb)/2)*100,2) as used_percent from temp_asm t where type='NORMAL' union all select t.*,trunc(((total_gb-free_gb)/3)/((total_gb-rm_free_gb)/3)*100,2) as used_percent from temp_asm t where type='HIGH') order by used_percent desc; --asmdisk prompt prompt ##1.11.Asm_disk_info prompt col path format a20; col group_name format a10 col name format a20 select a.group_number,b.name as group_name,a.name,a.path,a.state,trunc(a.total_mb/1024,2) total_gb from v\$asm_disk a,v\$asm_diskgroup b where a.group_number=b.group_number order by 2,3; --rman message prompt prompt ##1.12.Rman_backup_info prompt col INPUT_BYTES_PER_SEC_DISPLAY format a15 col OUTPUT_BYTES_PER_SEC_DISPLAY format a15 col TIME_TAKEN_DISPLAY format a17 col status format a25 COL hours FORMAT 999.999 COL out_size FORMAT a10 col type format a10 select session_key,AUTOBACKUP_DONE,OUTPUT_DEVICE_TYPE type, INPUT_TYPE,status,ELAPSED_SECONDS/3600 hours,TO_CHAR(START_TIME,'yyyy-mm-dd hh24:mi') start_time, OUTPUT_BYTES_DISPLAY out_size,OUTPUT_BYTES_PER_SEC_DISPLAY,INPUT_BYTES_PER_SEC_DISPLAY from v\$RMAN_BACKUP_JOB_DETAILS where end_time>sysdate-40 order by start_time ; prompt prompt prompt ##1.20 alert_log:tail rownum<=300 not support win platform: prompt --spool off column instancename new_value instname column alertfile new_value alertfiledr select instance_name as instancename from v\$instance; col alertfile for a60 select value as alertfile from v\$parameter where name='background_dump_dest'; !tail -300 \&&alertfiledr/alert_&&instname..log >> /tmp/spoolfile_temp1989.log exit EOF cat /tmp/spoolfile_temp1989.log >> $LOG_FILE rm -f /tmp/spoolfile_temp1989.log echo "" >> $LOG_FILE echo "" >> $LOG_FILE #OS check echo "-- 2.os_info check" echo "*************2.os_info check****************" >> $LOG_FILE #typeset -u VARIABLE #VARIABLE="`date -d "1 day ago" +%d-%b-%Y`" CUR_DAY=`date +%d` CUR_MON=`date +%b` CUR_YEAR=`date +%Y` C_DAY=`expr ${CUR_DAY} - 1` LDATE=`echo $C_DAY-$CUR_MON-$CUR_YEAR` VARIABLE=`echo $LDATE |tr 'a-z' 'A-Z'` str="`sqlplus -V | tr -cd "[0-9]"`" ver=`expr substr "$str" 1 2` hname=`hostname` LLOG=`echo $ORACLE_BASE` LOGD=`echo ${LLOG%/*}` if [[ $ver == 11 ]];then LISTENER_LOG=`find $LOGD -name listener*.log -mtime 0` elif [[ $ver == 10 ]];then LISTENER_LOG=`lsnrctl stat |grep "Log File" | awk '{print $4}'` else echo "LISTENER_LOG:this is error" >> $LOG_FILE fi if [ $sname == 'Linux' ];then echo "" >>$LOG_FILE echo "##2.1.filesystem_size" >> $LOG_FILE echo "" >>$LOG_FILE df -h >> $LOG_FILE echo "" >>$LOG_FILE echo "##2.2.tail -200 /var/log/messages***" >> $LOG_FILE echo "" >>$LOG_FILE tail -200 /var/log/messages >> $LOG_FILE elif [ $sname == 'AIX' ];then echo "" >>$LOG_FILE echo "##2.1.filesystem_size" >> $LOG_FILE echo "" >>$LOG_FILE df -g >> $LOG_FILE echo "" >>$LOG_FILE echo "##2.2.errpt" >> $LOG_FILE echo "" >>$LOG_FILE errpt >> $LOG_FILE elif [ $sname == 'HP-UX' ];then echo "" >>$LOG_FILE echo "##2.1.filesystem_size" >> $LOG_FILE echo "" >>$LOG_FILE bdf >> $LOG_FILE echo "" >>$LOG_FILE echo "##2.2.tail -200 /var/adm/syslog/syslog***" >> $LOG_FILE echo "" >>$LOG_FILE tail -200 /var/adm/syslog/syslog >> $LOG_FILE else echo "os log is error" >> $LOG_FILE fi if [[ $LISTENER_LOG == '' ]];then echo "" >> $LOG_FILE echo "" >> $LOG_FILE echo "LISTENER_LOG:this is null" >> $LOG_FILE rm -f $SH_HOME/listener`date +'%Y%m%d'`.log echo "" >> $LOG_FILE echo "" >> $LOG_FILE else echo "" >>$LOG_FILE echo "-- 3.listener_log_info_for_yesterday" echo "*************3.listener_log_info_for_yesterday****************" >> $LOG_FILE echo "" >>$LOG_FILE grep "`echo $VARIABLE`" $LISTENER_LOG > $SH_HOME/listener`date +'%Y%m%d'`.log echo " count IP " >> $LOG_FILE echo "" >>$LOG_FILE grep -i -o -E -r -e "([0-9]{1,3}\.){3}[0-9]{1,3}" $SH_HOME/listener`date +'%Y%m%d'`.log | sort -n | uniq -c | sort -nr >> $LOG_FILE #day hour echo "" >>$LOG_FILE echo " count Hour " >> $LOG_FILE echo "" >>$LOG_FILE fgrep "`echo $VARIABLE`" $SH_HOME/listener`date +'%Y%m%d'`.log |fgrep "establish" |awk '{print $1 " " $2}' | awk -F : '{print $1}'| sort -n |uniq -c |sort -nr >> $LOG_FILE rm -f $SH_HOME/listener`date +'%Y%m%d'`.log fi echo "" >> $LOG_FILE echo "" >> $LOG_FILE #cluster check echo "-- 4.cluster_info" echo "*************4.cluster_info****************" >> $LOG_FILE if [[ $ver == 11 && $CL -eq 1 ]];then echo "" >>$LOG_FILE echo "***crsctl stat res -t***" >> $LOG_FILE echo "" >>$LOG_FILE su - grid -c "crsctl stat res -t" >> $LOG_FILE elif [[ $ver == 10 && $CL -eq 1 ]];then echo "" >>$LOG_FILE echo "***crs_stat -t***" >> $LOG_FILE echo "" >>$LOG_FILE su - oracle -c "crs_stat -t" >> $LOG_FILE else echo "This is not RAC" >> $LOG_FILE fi echo "" >>$LOG_FILE echo "-- 5.os_resource_info" echo "*************5.os_resource_info****************" >> $LOG_FILE echo "" >>$LOG_FILE #echo "***vmstat 1 1 ***" to >> $LOG_FILE #vmstat 180 20 >> $LOG_FILE echo "****** check end `date +'%Y/%m/%d %H:%M:%S'` ******" >> $LOG_FILE echo -e " ** End execution..... ** File location: $LOG_FILE =========================================================================== " ```