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 [[ $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="" 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
===========================================================================
"
```</eof>

【SCRIPT】Oracle日常巡檢指令碼通用版

相關文章