> 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 [[ $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 ' | '

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

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

相關文章