#!/bin/bash
source ~/.bash_profile
# -------------------------------
# MySQL 配置（请根据实际情况修改）
ORA_USER="ZDbcheck"
ORA_PASSWORD="ZD"
ORA_HOST=""
ORA_PORT="1521"
ORA_CMD="sqlplus"
ORA_SRV="orcl"
CRS_PATH="/home/u01/11.2.0/grid/bin"
OS_LOG="/var/log/messages"
DB_LOG="/home/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/alert_orcl1.log"
GI_LOG="/home/u01/11.2.0/grid/log/host01/alerthost01.log"
ASM_LOG="/home/u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log"
DBA="数据库工程师："
KH="北"
DD=$(date +%Y-%m-%d)
# 下面的一般不用修改
# 获取mysql 错误日志的多少航
LOG_LINES="2000"
# -------------------------------
# 输出文件
REPORT_FILE="/home/oracle/cismon2/auto/oracle_report_${ORA_HOST}_${ORA_SRV}.log"
MLOG_FILE="/home/oracle/cismon2/all/oracle_logerr_${ORA_SRV}.log"
SYLOG_FILE="/home/oracle/cismon2/all/oracle_logerr_${ORA_SRV}.log"
REPORT_ERR="/home/oracle/cismon2/all/oracle_err_report_${ORA_SRV}.log"
echo >$MLOG_FILE
echo >$REPORT_ERR
echo >$REPORT_FILE
# 清空报告文件并写入标题
echo "ORACLE & 操作系统巡检报告 - $(date)" > $REPORT_FILE
echo ${DBA} >> $REPORT_FILE
echo ${KH} >> $REPORT_FILE
echo "=====================================" >> $REPORT_FILE
###############################################################################
#【1】操作系统基础信息（一级标题）
###############################################################################
echo "#**【1】操作系统基础信息" >> $REPORT_FILE
echo "##1.1 主机名 " >> $REPORT_FILE
echo "主机名： $(hostname)" >> $REPORT_FILE
echo "##1.2 系统发行版：" >> $REPORT_FILE
cat /etc/redhat-release >> $REPORT_FILE
echo "##1.3 内核版本：" >> $REPORT_FILE
uname -r >> $REPORT_FILE
echo "##1.4 运行时间：" >> $REPORT_FILE
uptime >> $REPORT_FILE
echo "##1.5 服务器型号：" >> $REPORT_FILE
dmidecode | grep "Product Name" >> $REPORT_FILE 2>/dev/null
echo "##1.6 CPU 信息：" >> $REPORT_FILE
cat /proc/cpuinfo | grep 'model name' | uniq -c >> $REPORT_FILE
echo "##1.7 内存信息：" >> $REPORT_FILE
cat /proc/meminfo >> $REPORT_FILE
echo "##1.8 IP 地址及网络接口：" >> $REPORT_FILE
ip address|grep inet >> $REPORT_FILE
echo "##1.9 操作系统版本：" >> $REPORT_FILE
lsb_release -a 2>/dev/null || cat /proc/version >> $REPORT_FILE
echo "" >> $REPORT_FILE
###############################################################################
#【2】性能检查（操作系统层面）
###############################################################################
echo "#**【2】性能检查 操作系统层面" >> $REPORT_FILE
echo "##2.1 CPU 占用率：" >> $REPORT_FILE
top -b -n1 | grep "Cpu(s)" >> $REPORT_FILE
# 设置采样次数和告警阈值
COUNT=5
THRESHOLD=50
echo "开始快速检测CPU使用率，将采样$COUNT次，阈值设置为: ${THRESHOLD}%"
echo "------------------------"
# 初始化总使用率
TOTAL_USAGE=0
for ((i=1; i<=$COUNT; i++))
do
# 获取当前CPU使用率（取1秒内的平均值）
CPU_USAGE=$(top -bn1 | grep "Cpu(s)" | sed "s/.*, *\([0-9.]*\)%* id.*/\1/" | awk '{print 100 - $1}')
CPU_USAGE=${CPU_USAGE%.*} # 去掉小数部分，只保留整数
# 获取当前时间
TIMESTAMP=$(date "+%Y-%m-%d %H:%M:%S")
# 累加使用率
TOTAL_USAGE=$(echo "$TOTAL_USAGE + $CPU_USAGE" | bc)
# 打印单次采样结果
echo "[$TIMESTAMP] 第$i次采样: ${CPU_USAGE}%"
# 如果不是最后一次采样，则等待1秒
if [ $i -lt $COUNT ]; then
sleep 2
fi
done
# 计算平均使用率
AVG_USAGE=$(echo "scale=2; $TOTAL_USAGE / $COUNT" | bc)
AVG_USAGE_INT=$(echo "($TOTAL_USAGE + $COUNT/2) / $COUNT" | bc) # 四舍五入到整数，用于比较
echo "------------------------"
echo "采样完成！$COUNT 次采样的平均CPU使用率为: ${AVG_USAGE}%"
# 检查平均使用率是否超过阈值
if [ "$AVG_USAGE_INT" -gt "$THRESHOLD" ]; then
echo "** WARNING : 平均CPU使用率超过阈值！当前: ${AVG_USAGE}% (阈值: ${THRESHOLD}%)**" >> $REPORT_FILE
echo "2.1 CPU 占用率： WARNING 平均CPU使用率超过阈值！当前: ${AVG_USAGE}% (阈值: ${THRESHOLD}%)**" >> $REPORT_ERR
else
echo "提示: 平均CPU使用率正常。" >> $REPORT_FILE
fi
echo "##2.2 内存使用情况 & Swap：" >> $REPORT_FILE
free -m >> $REPORT_FILE
# 获取内存信息（单位:MB）
total_mem=$(free -m | awk 'NR==2{print $2}')
available_mem=$(free -m | awk 'NR==2{print $7}') # 使用 Available 列
# 根据总内存大小设置不同的告警阈值
if [ $total_mem -lt 8192 ]; then
# 总内存8G以下
threshold=1024
mem_type="8G以下"
elif [ $total_mem -lt 32768 ]; then
# 总内存大于8G小于32G
threshold=1536
mem_type="8G-32G"
elif [ $total_mem -lt 102400 ]; then
# 总内存大于32G小于100G
threshold=2048
mem_type="32G-100G"
else
# 总内存大于100G
threshold=5120
mem_type="100G以上"
fi
# 判断剩余内存是否低于阈值
if [ $available_mem -lt $threshold ]; then
echo "[ WARNING ] 内存告警！当前总内存: ${total_mem}M (${mem_type})，可用内存: ${available_mem}M，低于阈值: ${threshold}M。请及时处理！" >> $REPORT_FILE
echo "2.2 内存使用情况 [ WARNING ] 内存告警！当前总内存: ${total_mem}M (${mem_type})，可用内存: ${available_mem}M，低于阈值: ${threshold}M。请及时处理！" >> $REPORT_ERR
else
echo "[正常] 内存状态正常。当前总内存: ${total_mem}M (${mem_type})，可用内存: ${available_mem}M，阈值: ${threshold}M。" >> $REPORT_FILE
fi
# 设置告警阈值（10%）
THRESHOLD=10
# 获取 Swap 使用情况
# 使用 `free` 命令获取 Swap 的总量和已用量（单位为 MB）
SWAP_TOTAL=$(free -m | awk '/Swap:/ {print $2}')
SWAP_USED=$(free -m | awk '/Swap:/ {print $3}')
# 计算 Swap 使用率（如果总大小为0，则使用率为0）
if [ "$SWAP_TOTAL" -eq 0 ]; then
SWAP_USAGE_PERCENT=0
echo "系统未启用 Swap 空间或 Swap 总大小为 0。"
else
SWAP_USAGE_PERCENT=$(( (SWAP_USED * 100) / SWAP_TOTAL ))
fi
# 获取当前时间戳
TIMESTAMP=$(date "+%Y-%m-%d %H:%M:%S")
# 判断并使用率是否超过阈值
if [ "$SWAP_USAGE_PERCENT" -gt "$THRESHOLD" ]; then
echo "[$TIMESTAMP] ** WARNING ：Swap 使用率过高！** 当前使用率: ${SWAP_USAGE_PERCENT}% (阈值: ${THRESHOLD}%)" >> $REPORT_FILE
echo "2.2 内存使用情况 [$TIMESTAMP] ** WARNING ：Swap 使用率过高！** 当前使用率: ${SWAP_USAGE_PERCENT}% (阈值: ${THRESHOLD}%)" >> $REPORT_ERR
else
echo "[$TIMESTAMP] Swap 使用率正常。当前使用率: ${SWAP_USAGE_PERCENT}%" >> $REPORT_FILE
fi
echo "##2.3 磁盘 I/O 统计：" >> $REPORT_FILE
iostat -xkd 1 1 >> $REPORT_FILE
iostat -x 1 1 | awk '/^[shv]d[a-z]/ || /^xvd[a-z]/ || /^nvme/ {if ($10 > 100) print " WARNING 设备: " $1 ", await: " $10 " ms"}' >> $REPORT_FILE
iostat -x 1 1 | awk '/^[shv]d[a-z]/ || /^xvd[a-z]/ || /^nvme/ {if ($10 > 100) print " WARNING 设备: " $1 ", await: " $10 " ms"}' >> $REPORT_ERR
iostat -x 1 1 | awk '/^[shv]d[a-z]/ || /^xvd[a-z]/ || /^nvme/ {if ($10 > 100) print " WARNING 设备: " $1 ", await: " $10 " ms"}' >> $REPORT_FILE
iostat -x 1 1 | awk '/^[shv]d[a-z]/ || /^xvd[a-z]/ || /^nvme/ {if ($10 > 100) print " WARNING 设备: " $1 ", await: " $10 " ms"}' >> $REPORT_ERR
iostat -x 1 1 | awk '/^[shv]d[a-z]/ || /^xvd[a-z]/ || /^nvme/ {if ($10 > 100) print " WARNING 设备: " $1 ", await: " $10 " ms"}' >> $REPORT_FILE
iostat -x 1 1 | awk '/^[shv]d[a-z]/ || /^xvd[a-z]/ || /^nvme/ {if ($10 > 100) print " WARNING 设备: " $1 ", await: " $10 " ms"}' >> $REPORT_ERR
echo "##2.4 磁盘空间：" >> $REPORT_FILE
df -h 2>/dev/null >> $REPORT_FILE
df -i 2>/dev/null >> $REPORT_FILE
# 设置告警阈值（90%）
THRESHOLD=90
echo "开始检查文件系统使用情况，阈值: ${THRESHOLD}%"
echo "=================================================="
# 使用df命令获取磁盘信息，排除临时文件系统
df -hP 2>/dev/null | awk 'NR>1' | while read filesystem total used available percent mountpoint; do
# 提取使用率百分比数字（去掉百分号）
usage_percent=$(echo $percent | tr -d '%')
# 检查使用率是否超过阈值
if [ $usage_percent -gt $THRESHOLD ]; then
echo "[ WARNING ] 文件系统 $filesystem ($mountpoint) 使用率过高: ${usage_percent}%" >> $REPORT_FILE
echo "2.4 磁盘空间 [ WARNING ] 文件系统 $filesystem ($mountpoint) 使用率过高: ${usage_percent}%" >> $REPORT_ERR
else
echo "[正常] 文件系统 $filesystem ($mountpoint) 使用率: ${usage_percent}%" >> $REPORT_FILE
fi
done
# 设置告警阈值（90%）
THRESHOLD=90
echo "开始检查文件系统使用情况，阈值: ${THRESHOLD}%"
echo "=================================================="
# 使用df命令获取磁盘信息，排除临时文件系统
df -iP 2>/dev/null| awk 'NR>1' | while read filesystem total used available percent mountpoint; do
# 提取使用率百分比数字（去掉百分号）
usage_percent=$(echo $percent | tr -d '%')
# 检查使用率是否超过阈值
if [ $usage_percent -gt $THRESHOLD ]; then
echo "[ WARNING ] 文件系统 $filesystem ($mountpoint) inode使用率过高: ${usage_percent}%" >> $REPORT_FILE
echo "2.4 磁盘空间 [ WARNING ] 文件系统 $filesystem ($mountpoint) inode使用率过高: ${usage_percent}%" >> $REPORT_ERR
else
echo "[正常] 文件系统 $filesystem ($mountpoint) inode使用率: ${usage_percent}%" >> $REPORT_FILE
fi
done
echo "##2.5 系统开放端口（LISTEN 状态）：" >> $REPORT_FILE
netstat -anp | grep LISTEN|grep -v LISTENING>> $REPORT_FILE
echo "##2.6 进程检查（系统进程）：" >> $REPORT_FILE
ps aux | head -n 7 >> $REPORT_FILE
echo "##2.7 最近系统日志 (/var/log/messages)：" >> $REPORT_FILE
tail -n 200 /var/log/messages >> $SYLOG_FILE
###############################################################################
#【3】集群检查
###############################################################################
echo "#**【3】集群检查" >> $REPORT_FILE
echo "##3.1 集群资源状态：" >> $REPORT_FILE
${CRS_PATH}/crs_stat -t >> $REPORT_FILE 2>/dev/null
echo "##3.2 Cluster Resources 和 Local Resources：" >> $REPORT_FILE
${CRS_PATH}/crsctl status res -t >> $REPORT_FILE 2>/dev/null
echo "##3.3 votedisk 信息：" >> $REPORT_FILE
${CRS_PATH}/crsctl query css votedisk >> $REPORT_FILE 2>/dev/null
echo "##3.4 OCR 信息：" >> $REPORT_FILE
${CRS_PATH}/ocrcheck >> $REPORT_FILE 2>/dev/null
echo "##3.5 cluster 信息：" >> $REPORT_FILE
${CRS_PATH}/crsctl check cluster -all >> $REPORT_FILE 2>/dev/null
#3.5
result=$(${CRS_PATH}/crsctl check cluster -all 2>&1)
exit_code=$?
if [ $exit_code -ne 0 ]; then
echo " WARNING 检查失败: crsctl check cluster命令执行错误" >> $REPORT_ERR
fi
# 检查是否有非online状态
if echo "$result" | grep -q -v "online" | grep -q "CRS-"; then
echo "WARNING 集群资源发现异常状态,建议执行: crsctl status res -t 查看详细状态" >> $REPORT_ERR
echo "$result" | grep -v "online" | grep "CRS-"
else
echo "✅ 所有集群服务状态正常"
echo "$result"
fi
###############################################################################
#【4】数据库巡检
###############################################################################
echo "#**【4】数据库巡检" >> $REPORT_FILE
echo "##4.1 实例信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 1000
set feedback off
SET HEADING ON
col HOST_NAME format a15
SELECT INSTANCE_NAME, HOST_NAME, VERSION, STATUS AS INSTANCE_STATUS,
ARCHIVER, STARTUP_TIME AS START_TIME,
ROUND(SYSDATE - STARTUP_TIME, 2) AS BOOTED_TIME_DAYS
FROM GV\$INSTANCE;
exit;
EOF
echo "##4.2 数据库信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col DB_UNIQUE_NAME format a10
col NAME format a10
col PLATFORM_NAME format a16
col FORCE_LOGGING format a5
col DATABASE_ROLE format a7
col OPEN_MODE format a10
select DBID,DB_UNIQUE_NAME ,NAME, LOG_MODE, PLATFORM_NAME, OPEN_MODE ,DATABASE_ROLE ,SUPPLEMENTAL_LOG_DATA_MIN ,FORCE_LOGGING from v\$database;
exit;
EOF
echo "##4.3 数据库字符集：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col VALUE format a15
col PARAMETER format a25
select * from v\$nls_parameters where PARAMETER in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
exit;
EOF
echo "##4.4 查询补丁信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
COLUMN ACTION_TIME FORMAT A20
COLUMN ACTION FORMAT A20
COLUMN NAMESPACE FORMAT A20
COLUMN VERSION FORMAT A10
COLUMN COMMENTS FORMAT A30
COLUMN BUNDLE_SERIES FORMAT A10
SELECT TO_CHAR(ACTION_TIME, 'YYYYMMDD HH24:MI:SS') AS ACTION_TIME
,ACTION
,NAMESPACE
,VERSION
,ID
,COMMENTS
,BUNDLE_SERIES
FROM DBA_REGISTRY_HISTORY
ORDER BY ACTION_TIME;
exit;
EOF
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
COLUMN ACTION_TIME FORMAT A20
COLUMN ACTION FORMAT A10
COLUMN STATUS FORMAT A10
COLUMN DESCRIPTION FORMAT A60
COLUMN VERSION FORMAT A10
SELECT TO_CHAR(ACTION_TIME, 'YYYYMMDD HH24:MI:SS') AS ACTION_TIME
,ACTION
,STATUS
,DESCRIPTION
,PATCH_ID
FROM DBA_REGISTRY_SQLPATCH
ORDER BY ACTION_TIME;
exit;
EOF
echo "##4.5 查询 表空间 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
COLUMN TABLESPACE_NAME FORMAT A25
COLUMN LOGGING FORMAT A10
COLUMN STATUS FORMAT A10
COLUMN EXTENT_MANAGEMENT FORMAT A8
COLUMN SEGMENT_SPACE_MANAGEMENT FORMAT A6
COLUMN BIGFILE FORMAT A4
COLUMN CONTENTS FORMAT A10
SELECT
TABLESPACE_NAME,
STATUS,
LOGGING,
EXTENT_MANAGEMENT,
SEGMENT_SPACE_MANAGEMENT,
BIGFILE,
CONTENTS
FROM
DBA_TABLESPACES
ORDER BY
TABLESPACE_NAME;
exit;
EOF
echo "##4.6 查询 scn 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
define LOWTHRESHOLD=10
define MIDTHRESHOLD=62
define VERBOSE=FALSE
set veri off;
set feedback off;
set serverout on
DECLARE
verbose boolean:=&&VERBOSE;
BEGIN
For C in (
select
version,
date_time,
dbms_flashback.get_system_change_number current_scn,
indicator
from
(
select
version,
to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') DATE_TIME,
((((
((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +
((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
(((to_number(to_char(sysdate,'DD'))-1))*24*60*60) +
(to_number(to_char(sysdate,'HH24'))*60*60) +
(to_number(to_char(sysdate,'MI'))*60) +
(to_number(to_char(sysdate,'SS')))
) * (16*1024)) - dbms_flashback.get_system_change_number)
/ (16*1024*60*60*24)
) indicator
from v\$instance
)
) LOOP
dbms_output.put_line( '-----------------------------------------------------'
|| '---------' );
dbms_output.put_line( 'ScnHealthCheck' );
dbms_output.put_line( '-----------------------------------------------------'
|| '---------' );
dbms_output.put_line( 'Current Date: '||C.date_time );
dbms_output.put_line( 'Current SCN: '||C.current_scn );
if (verbose) then
dbms_output.put_line( 'SCN Headroom: '||round(C.indicator,2) );
end if;
dbms_output.put_line( 'Version: '||C.version );
dbms_output.put_line( '-----------------------------------------------------'
|| '---------' );
IF C.version > '10.2.0.5.0' and
C.version NOT LIKE '9.2%' THEN
IF C.indicator>&MIDTHRESHOLD THEN
dbms_output.put_line('Result: A - SCN Headroom is good');
dbms_output.put_line('Apply the latest recommended patches');
dbms_output.put_line('based on your maintenance schedule');
IF (C.version < '11.2.0.2') THEN
dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='
|| '24 after apply.');
END IF;
ELSIF C.indicator<=&LOWTHRESHOLD THEN
dbms_output.put_line('Result: C - SCN Headroom is low');
dbms_output.put_line('If you have not already done so apply' );
dbms_output.put_line('the latest recommended patches right now' );
IF (C.version < '11.2.0.2') THEN
dbms_output.put_line('set _external_scn_rejection_threshold_hours=24 '
|| 'after apply');
END IF;
dbms_output.put_line('AND contact Oracle support immediately.' );
ELSE
dbms_output.put_line('Result: B - SCN Headroom is low');
dbms_output.put_line('If you have not already done so apply' );
dbms_output.put_line('the latest recommended patches right now');
IF (C.version < '11.2.0.2') THEN
dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='
||'24 after apply.');
END IF;
END IF;
ELSE
IF C.indicator<=&MIDTHRESHOLD THEN
dbms_output.put_line('Result: C - SCN Headroom is low');
dbms_output.put_line('If you have not already done so apply' );
dbms_output.put_line('the latest recommended patches right now' );
IF (C.version >= '10.1.0.5.0' and
C.version <= '10.2.0.5.0' and
C.version NOT LIKE '9.2%') THEN
dbms_output.put_line(', set _external_scn_rejection_threshold_hours=24'
|| ' after apply');
END IF;
dbms_output.put_line('AND contact Oracle support immediately.' );
ELSE
dbms_output.put_line('Result: A - SCN Headroom is good');
dbms_output.put_line('Apply the latest recommended patches');
dbms_output.put_line('based on your maintenance schedule ');
IF (C.version >= '10.1.0.5.0' and
C.version <= '10.2.0.5.0' and
C.version NOT LIKE '9.2%') THEN
dbms_output.put_line('AND set _external_scn_rejection_threshold_hours=24'
|| ' after apply.');
END IF;
END IF;
END IF;
dbms_output.put_line(
'For further information review MOS document id 1393363.1');
dbms_output.put_line( '-----------------------------------------------------'
|| '---------' );
END LOOP;
end;
/
exit;
EOF
echo "##4.7 查询 数据库大小 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
SELECT 'Physical Size' AS SIZE_TYPE, ROUND(SUM(bytes)/1024/1024/1024, 2) AS SIZE_GB
FROM dba_data_files
UNION ALL
SELECT 'Logical Size' AS SIZE_TYPE, ROUND(SUM(bytes)/1024/1024/1024, 2) AS SIZE_GB
FROM dba_segments;
exit;
EOF
echo "##4.8 查询 表空间剩余 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
select /*+rule */ all_tp.TP_NAME as "TABLESPACE_NAME",
to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') || 'GB' as "TP_REAL_SIZE",
to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') || 'GB' as "TP_REAL_FREE_SIZE",
to_char(trunc(free_tp.TP_FREE_SIZE_KB * 100 / all_tp.TP_ALL_SIZE_KB,
2),
'FM9999990.0099') || '%' as "TP_FREE_RATING"
from (
select /*+rule */ sum(decode(sign(ddf.maxbytes - ddf.bytes),
1,
ddf.maxbytes,
ddf.bytes)) / 1024 as "TP_ALL_SIZE_KB",
ddf.tablespace_name as "TP_NAME"
from DBA_DATA_FILES ddf
group by ddf.tablespace_name) all_tp,
(
select /*+rule */ sum(ifree_tp.TPF_SIZE_KB) as "TP_FREE_SIZE_KB",
ifree_tp.TP_NAME
from (
select /*+rule */ dfs.bytes / 1024 as "TPF_SIZE_KB",
dfs.tablespace_name as "TP_NAME"
from DBA_FREE_SPACE dfs
union all
select /*+rule */ decode(sign(ddf.maxbytes - ddf.bytes),
1,
ddf.maxbytes - ddf.bytes,
0) / 1024 as "TPF_SIZE_KB",
ddf.tablespace_name as "TP_NAME"
from DBA_DATA_FILES ddf
where ddf.autoextensible = 'YES') ifree_tp
group by ifree_tp.TP_NAME) free_tp
where all_tp.TP_NAME = free_tp.TP_NAME(+) ;
exit;
EOF
echo "##4.9 查询 数据库参数文件 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col name format a10
col VALUE format a80
SELECT NAME, VALUE FROM V\$PARAMETER WHERE NAME = 'spfile';
exit;
EOF
echo "##4.10 查询 数据库控制文件 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col name format a60
SELECT name, status,FILE_SIZE_BLKS*BLOCK_SIZE/1024/1024 as size_mb from v\$controlfile;
exit;
EOF
echo "##4.11 查询 数据文件 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col FILE_NAME format a60
col TABLESPACE_NAME format a20
select FILE_NAME,FILE_ID ,TABLESPACE_NAME, BYTES/1024/1024/1024 as size_GB ,AUTOEXTENSIBLE from dba_data_files;
exit;
EOF
echo "##4.12 查询 临时文件 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col FILE_NAME format a60
col TABLESPACE_NAME format a20
select FILE_NAME,FILE_ID ,TABLESPACE_NAME,BYTES/1024/1024/1024 as size_GB,AUTOEXTENSIBLE from dba_temp_files;
exit;
EOF
echo "##4.13 查询 归档路径 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col STATUS format a5
col SCHEDULE format a9
col PROCESS format a9
col PROCESS format a9
col DESTINATION format a10
col ERROR format a10
select
DEST_ID ,
STATUS ,
SCHEDULE ,
PROCESS ,
VALID_NOW ,
VALID_TYPE ,
TARGET ,
ARCHIVER ,
DESTINATION ,
ERROR ,
TRANSMIT_MODE ,
DB_UNIQUE_NAME from V\$ARCHIVE_DEST vad
where DESTINATION is not null;
exit;
EOF
echo "##4.14 查询 归档日志 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col SIZE_MB format a15
select
al.date_time ,
trunc(sum(al.every_arch_size), 2) || 'MB' SIZE_MB ,
count(1)
from (SELECT distinct ial.SEQUENCE# "sequence",
ial.THREAD# "thread",
ial.BLOCKS * ial.BLOCK_SIZE / 1024 / 1024 as every_arch_size,
to_char(ial.COMPLETION_TIME, 'yyyy-mm-dd') as date_time
FROM v\$archived_log ial
WHERE ial.COMPLETION_TIME > sysdate - 20) al
group by al.date_time
order by al.date_time desc;
exit;
EOF
echo "##4.15 查询 standby redo 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
column MB format a8;
column STATUS format a12;
column MEMBER format a50;
select sl.GROUP#,sl.THREAD#,sl.BYTES/1024/1024 "MB",sl.STATUS, lf.TYPE,lf.MEMBER from v\$standby_log sl,v\$logfile lf where sl.GROUP#=lf.GROUP#;
exit;
EOF
echo "##4.16 查询 redo 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
column MB format a8;
column STATUS format a12;
column MEMBER format a50;
select l.GROUP#,l.THREAD#,l.BYTES/1024/1024||'MB' MB,l.STATUS, lf.TYPE,lf.MEMBER from v\$log l,v\$logfile lf where l.GROUP#=lf.GROUP#;
exit;
EOF
echo "##4.17 查询 外部表 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col WRL_TYPE format a5
col WRL_PARAMETER format a50
col STATUS format a15
select
INST_ID,
WRL_TYPE,
WRL_PARAMETER,
STATUS
from gv\$encryption_wallet;
exit;
EOF
echo "##4.18 查询 失效对象 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col owner format a15
col object_type format a15
SELECT owner,object_type,COUNT(*) AS "num_INVALID" FROM dba_objects WHERE status='INVALID' GROUP BY owner,object_type ORDER BY owner,object_type;
exit;
EOF
echo "##4.19 查询 失效索引 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col owner format a15
col object_type format a15
SELECT owner , object_type , object_name FROM dba_objects WHERE status = 'INVALID' and object_type='INDEX';
exit;
EOF
echo "##4.20 查询 SYSTEM大对象 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col owner format a10
col segment_name format a30
col partition_name format a35
SELECT owner, segment_name, partition_name, bytes/1024/1024/1024 as size_GB
FROM (
SELECT owner, segment_name, partition_name, bytes
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY bytes DESC
)
WHERE ROWNUM <= 10;
exit;
EOF
echo "##4.21 查询 SYSAUX大对象 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col owner format a10
col segment_name format a30
col partition_name format a35
SELECT owner, segment_name, partition_name, bytes/1024/1024/1024 as size_GB
FROM (
SELECT owner, segment_name, partition_name, bytes
FROM dba_segments
WHERE tablespace_name = 'SYSAUX'
ORDER BY bytes DESC
)
WHERE ROWNUM <= 10;
exit;
EOF
echo "##4.22 查询 resource 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col RESOURCE_NAME format a30
col INITIAL_ALLOCATION format a30
col INITIAL_ALLOCATION format a25
select
INST_ID,
RESOURCE_NAME,
CURRENT_UTILIZATION ,
MAX_UTILIZATION ,
INITIAL_ALLOCATION,
LIMIT_VALUE
from gv\$resource_limit;
exit;
EOF
echo "##4.23 查询 用户 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col username format a8
col account_status format a8
col default_tablespace format a8
col du.TEMPORARY_TABLESPACE format a8
col profile format a8
select
du.username,
du.account_status,
du.default_tablespace ,
du.TEMPORARY_TABLESPACE ,
du.profile,
to_char(du.lock_date, 'yyyy-mm-dd hh24:mi:ss'),
to_char(du.created, 'yyyy-mm-dd hh24:mi:ss'),
to_char(du.expiry_date, 'yyyy-mm-dd hh24:mi:ss'),
TRUNC(du.expiry_date - SYSDATE)
from dba_users du
where account_status not like 'EXPIRED%LOCKED'
order by du.username;
exit;
EOF
echo "##4.24 查询 profile 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col profile format a10
col resource_name format a28
col resource_type format a10
col limit format a10
select
dp.profile ,
dp.resource_name,
dp.resource_type,
dp.limit
from dba_profiles dp
order by dp.profile, dp.resource_name;
exit;
EOF
echo "##4.25 查询 非默认参数 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col name format a30
col instance_name format a28
col value format a28
select
p.name ,
i.instance_name ,
p.value
from gv\$parameter p, gv\$instance i
WHERE p.inst_id = i.inst_id
and isdefault = 'FALSE'
ORDER BY p.name, i.instance_name;
exit;
EOF
echo "##4.26 查询 asm diskgroup 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col namt format a10
col COMPATIBILITY format a10
select /*+rule*/
adg.NAME,
adg.TYPE ,
to_char(trunc(adg.TOTAL_MB / 1024, 2), 'FM9999990.0099') || 'GB' ,
to_char(trunc(adg.FREE_MB / 1024, 2), 'FM9999990.0099') || 'GB' ,
adg.OFFLINE_DISKS ,
adg.COMPATIBILITY ,
adg.VOTING_FILES
from V\$ASM_DISKGROUP adg;
exit;
EOF
echo "##4.27 查询 asm offline disk 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col dg_name format a10
col disk_name format a10
col PATH format a60
col FAILGROUP format a10
select /*+rule*/
adgs.NAME dg_name ,
ads.NAME disk_name,
ads.STATE ,
adgs.TYPE ,
ads.FAILGROUP ,
ads.PATH ,
adgs.OFFLINE_DISKS
from v\$asm_disk ads, v\$asm_diskgroup adgs
where ads.GROUP_NUMBER = adgs.GROUP_NUMBER
and ads.MODE_STATUS = 'OFFLINE'
order by adgs.NAME, ads.NAME;
exit;
EOF
echo "##4.28 查询 备份作业 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col STATUS format a25
col OUTPUT_DEVICE_TYPE format a12
col "START_TIME" format a20
col "INPUT_TYPE" format a15
col "ELAPSED_MIN" format 9999.99
select /*+RULE*/
to_char(rbjd.START_TIME, 'yyyy-mm-dd hh24:mi:ss') as "START_TIME",
rbjd.INPUT_TYPE as "INPUT_TYPE",
rbjd.STATUS as "STATUS",
round(rbjd.ELAPSED_SECONDS / 60, 2) as "ELAPSED_MIN",
rbjd.OUTPUT_DEVICE_TYPE as "OUTPUT_DEVICE_TYPE"
from v\$RMAN_BACKUP_JOB_DETAILS rbjd
where rbjd.START_TIME > sysdate - 14
order by rbjd.START_TIME desc;
exit;
EOF
echo "##4.29 查询 数据库自动作业 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col client_name format a35
col status format a12
select /*+rule*/
client_name,
status
from dba_autotask_client;
exit;
EOF
echo "##4.30 查询 常见勒索病毒 信息：" >> $REPORT_FILE
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_FILE}
set linesize 200 verify off echo off timing off pagesize 10000
set linesize 200 verify off echo off timing off pagesize 10000
set feedback off
SET HEADING ON
col object_name format a15
col object_type format a12
col owner format a12
select
o.object_name ,
o.object_type ,
o.owner ,
o.CREATE_TIME ,
o.LAST_CHANGE_TIME
from (select do.object_name,
do.object_type,
do.owner,
to_char(do.created, 'yyyy-mm-dd hh24:mi:ss') as "CREATE_TIME",
to_char(do.last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') as "LAST_CHANGE_TIME"
from dba_objects do
where do.object_name like 'DBMS_STANDARD_FUN9 %'
union all
select do.object_name,
do.object_type,
do.owner,
to_char(do.created, 'yyyy-mm-dd hh24:mi:ss') as "CREATE_TIME",
to_char(do.last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') as "LAST_CHANGE_TIME"
from dba_objects do
where do.object_name like 'DBMS_CORE_INTERNAL %'
union all
select do.object_name,
do.object_type,
do.owner,
to_char(do.created, 'yyyy-mm-dd hh24:mi:ss') as "CREATE_TIME",
to_char(do.last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') as "LAST_CHANGE_TIME"
from dba_objects do
where do.object_name like 'DBMS_SYSTEM_INTERNAL %'
union all
select do.object_name,
do.object_type,
do.owner,
to_char(do.created, 'yyyy-mm-dd hh24:mi:ss') as "CREATE_TIME",
to_char(do.last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') as "LAST_CHANGE_TIME"
from dba_objects do
where do.object_name like 'DBMS_SUPPORT_INTERNAL %'
union all
select do.object_name,
do.object_type,
do.owner,
to_char(do.created, 'yyyy-mm-dd hh24:mi:ss') as "CREATE_TIME",
to_char(do.last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') as "LAST_CHANGE_TIME"
from dba_objects do
where do.object_name like 'DBMS_SUPPORT%') o;
exit;
EOF
###############################################################################
#【5】日志和告警汇总
###############################################################################
echo "#**【5】日志信息检查 " >> $REPORT_FILE
echo "##5.1 查询 dmesg 信息：" >> $REPORT_FILE
dmesg -l err >> $REPORT_FILE
echo "##5.2 查询 系统日志 信息：" >> $REPORT_FILE
tail -100 ${OS_LOG}| grep -A 2 -B 2 -i -E "error|fail|critical|warning" >> $REPORT_FILE
echo "##5.3 查询 数据库错误日志 信息：" >> $REPORT_FILE
tail -200 ${DB_LOG} |grep -A 2 -B 2 -i -E "ORA-" >> $REPORT_FILE
echo "##5.3 查询 集群日志 信息：" >> $REPORT_FILE
tail -50 ${GI_LOG} |grep -A 2 -B 2 -i -E "unreachable|ORA-|CRS-" >> $REPORT_FILE
echo "##5.4 查询 asm日志 信息：" >> $REPORT_FILE
tail -200 ${ASM_LOG} |grep -A 2 -B 2 -i -E "ORA-" >> $REPORT_FILE
error_output=$(dmesg -l err 2>/dev/null)
# 判断变量 error_output 是否为空字符串
if [ -z "$error_output" ]; then
echo "系统未发现错误级别的内核告警信息。"
else
echo " WARNING dmesg 有告警信息,请检查详细日志" >> $REPORT_ERR
# 如果需要，也可以在这里直接输出错误信息：echo "$error_output"
fi
error_output1=$(tail -2000 ${DB_LOG} |grep -A 2 -B 2 -i -E "ORA-" 2>/dev/null)
# 判断变量 error_output 是否为空字符串
if [ -z "$error_output1" ]; then
echo "系统未发现数据库告警信息。"
else
echo " WARNING 数据库日志 有告警信息,请检查详细日志" >> $REPORT_ERR
# 如果需要，也可以在这里直接输出错误信息：echo "$error_output"
fi
error_output2=$(tail -100 ${OS_LOG}| grep -A 2 -B 2 -i -E "error|fail|critical|warning" 2>/dev/null)
# 判断变量 error_output 是否为空字符串
if [ -z "$error_output2" ]; then
echo "系统日志未发现告警信息。"
else
echo " WARNING 系统日志 有告警信息,请检查详细日志" >> $REPORT_ERR
# 如果需要，也可以在这里直接输出错误信息：echo "$error_output"
fi
error_output3=$(tail -100 ${GI_LOG} |grep -A 2 -B 2 -i -E "unreachable|ORA-|CRS-" 2>/dev/null)
# 判断变量 error_output 是否为空字符串
if [ -z "$error_output3" ]; then
echo "集群日志未发现告警信息。"
else
echo " WARNING 集群日志 有告警信息,请检查详细日志" >> $REPORT_ERR
# 如果需要，也可以在这里直接输出错误信息：echo "$error_output"
fi
error_output3=$(tail -200 ${ASM_LOG} |grep -A 2 -B 2 -i -E "ORA-" 2>/dev/null)
# 判断变量 error_output 是否为空字符串
if [ -z "$error_output3" ]; then
echo "ASM日志未发现告警信息。"
else
echo " WARNING ASM日志 有告警信息,请检查详细日志" >> $REPORT_ERR
# 如果需要，也可以在这里直接输出错误信息：echo "$error_output"
fi
sqlplus -S ${ORA_USER}/${ORA_PASSWORD}@${ORA_HOST}:${ORA_PORT}/${ORA_SRV} << EOF >> ${REPORT_ERR}
set linesize 200 verify off echo off timing off pagesize 10000
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT
CASE
WHEN force_logging = 'NO' THEN 'WARNING: database not in force_logging mode '
WHEN log_mode != 'ARCHIVELOG' THEN 'WARNING: database not in archive mode '
WHEN open_mode != 'READ WRITE' THEN 'WARNING: database not in READ WRITE mode '
END AS status_check
FROM v\$database
WHERE force_logging = 'NO'
OR log_mode != 'ARCHIVELOG'
OR open_mode != 'READ WRITE';
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
select 'WARNING: Tablespace ' || TABLESPACE_NAME ||
' has only ' || TP_FREE_RATING || '% free space (' || TP_REAL_FREE_SIZE || '), needs attention!'
AS WARNING_MESSAGE from (select /*+rule */ all_tp.TP_NAME as "TABLESPACE_NAME",
to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') || 'GB' as "TP_REAL_SIZE",
to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') as "TP_REAL_FREE_SIZE",
to_char(trunc(free_tp.TP_FREE_SIZE_KB * 100 / all_tp.TP_ALL_SIZE_KB,
2),
'FM9999990.0099') as "TP_FREE_RATING"
from (
select /*+rule */ sum(decode(sign(ddf.maxbytes - ddf.bytes),
1,
ddf.maxbytes,
ddf.bytes)) / 1024 as "TP_ALL_SIZE_KB",
ddf.tablespace_name as "TP_NAME"
from DBA_DATA_FILES ddf
group by ddf.tablespace_name) all_tp,
(
select /*+rule */ sum(ifree_tp.TPF_SIZE_KB) as "TP_FREE_SIZE_KB",
ifree_tp.TP_NAME
from (
select /*+rule */ dfs.bytes / 1024 as "TPF_SIZE_KB",
dfs.tablespace_name as "TP_NAME"
from DBA_FREE_SPACE dfs
union all
select /*+rule */ decode(sign(ddf.maxbytes - ddf.bytes),
1,
ddf.maxbytes - ddf.bytes,
0) / 1024 as "TPF_SIZE_KB",
ddf.tablespace_name as "TP_NAME"
from DBA_DATA_FILES ddf
where ddf.autoextensible = 'YES') ifree_tp
group by ifree_tp.TP_NAME) free_tp
where all_tp.TP_NAME = free_tp.TP_NAME(+) ) WHERE TP_FREE_RATING < 20
AND TP_REAL_FREE_SIZE < 30;
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT
'WARNING: datafile ' || name || ' (file id: ' || file# || ') Abnormal status:' || status
AS ALERT_MESSAGE
FROM v\$datafile
WHERE status NOT IN ('ONLINE','SYSTEM');
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT
'WARNING: Control file size is ' ||
ROUND((f.block_size * f.file_size_blks) / 1024 / 1024, 2) ||
'MB' AS alert_message
FROM v\$controlfile f
WHERE (f.block_size * f.file_size_blks) / 1024 / 1024 > 500;
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT
CASE
WHEN (adg.FREE_MB / 1024 < 100) AND ((adg.FREE_MB / adg.TOTAL_MB) * 100 < 20)
THEN 'WARNING: DG '|| name||' Less than 100 GB of free space remaining, with utilization exceeding 80%.'
END AS alert_status
FROM V\$ASM_DISKGROUP adg;
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT
'WARNING: DG '|| name||' in Abnormal status' AS alert_message
FROM v\$ASM_DISKGROUP f
WHERE state not in ('CONNECTED','MOUNTED');
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
select 'WARNING: Process has reached the limit.' AS alert_message from v\$resource_limit where RESOURCE_NAME in ('processes') and MAX_UTILIZATION = LIMIT_VALUE;
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT
'WARNING: User ' || username || '''s password ' ||
CASE
WHEN expiry_date < SYSDATE THEN 'has EXPIRED, please change it immediately'
WHEN expiry_date <= SYSDATE + 180 THEN 'will expire in ' || ROUND(expiry_date - SYSDATE) || ' days, please handle promptly'
END AS alert_message
FROM dba_users
WHERE account_status NOT LIKE '%EXPIRED%LOCKED%'
AND (expiry_date < SYSDATE OR expiry_date <= SYSDATE + 180)
AND expiry_date IS NOT NULL and account_status not in ('OPEN')
ORDER BY expiry_date ASC;
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT
'WARNING: User ' || username || '''s password has EXPIRED, please check it immediately'
FROM dba_users
WHERE account_status NOT LIKE '%EXPIRED%LOCKED%' and user not in ('WMSYS')
and account_status = 'EXPIRED' ORDER BY expiry_date ASC;
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
select 'WARNING: profile '|| PROFILE||' RESOURCE_NAME '|| RESOURCE_NAME ||' is ' ||LIMIT||' day.' from dba_profiles where resource_name ='PASSWORD_LIFE_TIME' and LIMIT not in ('UNLIMITED');
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT 'WARNING: database has ' || COUNT(*) || ' invalid objects' AS alert_message
FROM dba_objects
WHERE STATUS != 'VALID'
HAVING COUNT(*) > 1;
SELECT 'WARNING: database has ' || COUNT(*) || ' invalid indexes' AS alert_message
FROM dba_objects
WHERE STATUS != 'VALID'
AND object_type = 'INDEX'
HAVING COUNT(*) > 1;
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT
'WARNING: Disk ' || ads.NAME || ' in diskgroup ' || adgs.NAME ||
' is OFFLINE. Path: ' || ads.PATH || ', Failgroup: ' || ads.FAILGROUP ||
', Disk State: ' || ads.STATE AS ALERT_MESSAGE
FROM v\$asm_disk ads, v\$asm_diskgroup adgs
WHERE ads.GROUP_NUMBER = adgs.GROUP_NUMBER
AND ads.MODE_STATUS = 'OFFLINE'
ORDER BY adgs.NAME, ads.NAME;
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT
'WARNING: Backup job started at ' ||
TO_CHAR(rbjd.START_TIME, 'yyyy-mm-dd hh24:mi:ss') ||
' has abnormal status: ' || rbjd.STATUS ||
'. Device: ' || rbjd.OUTPUT_DEVICE_TYPE ||
', Elapsed: ' || ROUND(rbjd.ELAPSED_SECONDS / 60, 2) || ' minutes'
AS ALERT_MESSAGE
FROM V\$RMAN_BACKUP_JOB_DETAILS rbjd
WHERE rbjd.START_TIME > SYSDATE - 14
AND rbjd.STATUS != 'COMPLETED'
AND rbjd.STATUS IS NOT NULL
ORDER BY rbjd.START_TIME DESC;
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT
CASE
WHEN client_name IN ('auto space advisor', 'sql tuning advisor')
AND status = 'ENABLED'
THEN 'WARNING: ' || client_name || ' is ENABLED. suggest disable it'
END AS alert_message
FROM dba_autotask_client
WHERE client_name IN ('auto space advisor', 'sql tuning advisor');
set linesize 200 verify off echo off timing off pagesize 0
set feedback off
SET HEADING ON
SELECT
'WARNING: No tables have had statistics gathered in the last 7 days. Last collection was ' ||
TO_CHAR(MAX(stats_update_time), 'YYYY-MM-DD HH24:MI:SS') ||
' for table ' || MAX(table_name) || ' in schema ' || MAX(owner) AS alert_message
FROM dba_tab_stats_history
HAVING COUNT(CASE WHEN stats_update_time > SYSDATE - 7 THEN 1 END) = 0
AND MAX(stats_update_time) IS NOT NULL;
exit;
EOF
###############################################################################
#【6】巡检发现的问题
###############################################################################
echo "#**【6】巡检发现的问题" >> $REPORT_FILE
echo "##6.1 存在问题的项目：" >> $REPORT_FILE
cat $REPORT_ERR >> $REPORT_FILE
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
