Oracle12c+ 日常检查脚本，如表空间、磁盘空间、相关日志信息等情况
#!/bin/bash
#=====================================================
# note:root_exec.linux:/bin/bash unix:/usr/bin/ksh
# support: Oracle12c+ linux/aix/unix
# default logfile: /opt/xunjian/logs
# 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 < /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 ' | '
--DB VERSION
prompt ##1.1.version
prompt
select patch_id,patch_type,action,status,target_version,ACTION_TIME from dba_registry_sqlpatch;
--DB sum size Gb
prompt
prompt ##1.2.Db_size
prompt
col cdbname for a20
select c.name cdbname,trunc(sum(s.bytes/1024/1024/1024),2) size_gb from cdb_segments s,v\$containers c where c.con_id=s.con_id group by c.name order by 2 desc;
--transactions Per Day
prompt
prompt ##1.3.Transaction_per_day
prompt
select instance_number,
metric_unit,
trunc(begin_time) 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
break on cdbname skip 1
col tablespace_name for a30
col cdbname for a20
set linesize 200 pagesize 500
select c.name cdbname,f.tablespace_name tablespace_name,
round((d.sumbytes/1024/1024/1024),2) total_without_extend_GB,
round(((d.sumbytes+d.extend_bytes)/1024/1024/1024),2) total_with_extend_GB,
round((f.sumbytes+d.Extend_bytes)/1024/1024/1024,2) free_with_extend_GB,
round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_GB,
round((d.sumbytes-f.sumbytes)*100/(d.sumbytes+d.extend_bytes),2) used_percent_with_extend
from (select con_id,tablespace_name,sum(bytes) sumbytes from cdb_free_space group by con_id,tablespace_name) f,
(select con_id,tablespace_name,sum(aa.bytes) sumbytes,sum(aa.extend_bytes) extend_bytes from
(select con_id,nvl(case when autoextensible ='YES' then (case when (maxbytes-bytes)>=0 then (maxbytes-bytes) end) end,0) Extend_bytes
,tablespace_name,bytes from cdb_data_files) aa group by con_id,tablespace_name) d,v\$containers c
where f.con_id=d.con_id and f.tablespace_name=d.tablespace_name and f.con_id=c.con_id
order by name,used_percent_with_extend desc;
clear breaks;
--top 30 segment size
prompt
prompt ##1.5.Segment_size_top30
prompt
col cdbname for a10
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 c.name cdbname,s.owner,s.Segment_Name,s.segment_type,s.tablespace_name,round(Sum(s.bytes)/1024/1024/1024,2) as GB From cdb_segments s,v\$containers c where s.con_id=c.con_id
group by c.name,s.owner,s.Segment_Name,s.segment_type,s.tablespace_name order by GB desc) where rownum < 31) select * from (select t.*,l.table_name as object_name from temp_select t,cdb_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
break on cdbname skip 1
col owner for a20
select c.name cdbname,s.owner,round(sum(s.bytes/1024/1024/1024),2) size_gb from cdb_segments s,v\$containers c
where c.con_id=s.con_id group by c.name,s.owner order by 1,3 desc;
clear breaks;
--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 trunc(completion_time) 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.13 alert_log:tail rownum<=300 not support win platform:
prompt
column instancename new_value instname
column databasename new_value dbname
column alertfile new_value alertfiledr
select instance_name as instancename from v\$instance;
select lower(name) as databasename from v\$database;
col alertfile for a60
select value||'/diag/rdbms/&&dbname/&&instname/trace' as alertfile from v\$parameter where name='diagnostic_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`
LISTENER_LOG=`find \$ORACLE_BASE/diag/tnslsnr/\$hname/listener/trace/listener*.log -mtime 0`
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
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 [[ $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
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 180 20 ***" 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
===========================================================================
"