脚本内容：
#!/bin/bash
# 设置 Oracle 环境变量
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=orcl11g
export PATH=$ORACLE_HOME/bin:$PATH
function separator() {
local Line= Title= Bytes= Xlength=
Title="$*"
Line='*'
if [ -n "${Title}" ]; then
Bytes=`echo "${Title}" | wc -c`
else
Bytes=1
fi
len=179
Xlength=$((len - Bytes))
printf '%s' "${Title}"
printf "%${Xlength}s\n" "${Line}"|sed "s/ /${Line}/g"
}
function check_server() {
# 存储使用情况
storage=$(df -h | awk '$NF=="/"{printf "Disk Usage: %d/%dGB (%s)\n", $3,$2,$5}')
# 内存使用情况
memory=$(free -h | awk 'NR==2{printf "Total Memory: %s, Used Memory: %s, Memory Usage: %.2f%%\n", $2,$3,$3*0.1/$2 }')
# CPU 使用情况
cpu=$(top -bn1 | grep "Cpu(s)" | awk '{print $2 + $4}')
# 主机名
hostname=$(hostname)
# 操作系统版本
os_version=$(cat /etc/os-release | grep -w PRETTY_NAME | cut -d'"' -f2)
# 输出 html 头部
echo_head > $HTML_FILE
# 服务器巡检部分输出为 HTML 表格
echo "<h2>服务器层面巡检</h2>" >> $HTML_FILE
echo "<table border='1'>" >> $HTML_FILE
echo "<tr><th>巡检项</th><th>结果</th></tr>" >> $HTML_FILE
echo "<tr><td>主机名</td><td>$(hostname)</td></tr>" >> $HTML_FILE
echo "<tr><td>操作系统版本</td><td>$os_version</td></tr>" >> $HTML_FILE
echo "<tr><td>存储使用情况</td><td>$storage</td></tr>" >> $HTML_FILE
echo "<tr><td>内存使用情况</td><td>$memory</td></tr>" >> $HTML_FILE
echo "<tr><td>CPU 使用情况</td><td>$cpu</td></tr>" >> $HTML_FILE
echo "</table>" >> $HTML_FILE
}
function check_db() {
# 数据库异常日志
log_cmd="tail /u01/app/oracle/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace/*.log -n 200 | grep -i err"
logs=$(eval $log_cmd || echo "${ORACLE_SID} 无报错信息")
# 数据库备份情况
backups=$(sqlplus -S / as sysdba <<EOF
set lines 200 pages 200
col backup_file_name for a30
col device_type for a20
col backup_finish_time for a30
select fname backup_file_name,status,device_type,completion_time backup_finish_time from v\$backup_files where file_type = 'PIECE' and bs_completion_time > sysdate -1;
EOF
)
# 异常计划任务
tasks=$(sqlplus -S / as sysdba <<EOF
set lines 200 pages 200
col job_name for a30
col job_status for a20
col job_start_time for a30
select job_name,job_status,job_start_time as num_of_failed_jobs from dba_autotask_job_history where job_start_time > sysdate -2 and job_status!= 'SUCCEEDED';
EOF
)
# 数据库会话
sessions1=$(sqlplus -S / as sysdba <<EOF
set lines 200 pages 200
select vs.username,count(*) active_session from v\$session vs where vs.status='ACTIVE' group by vs.username;
EOF
)
if [ -n "$sessions1" ]; then
sessions_no_empty=$(echo "$sessions1" | sed '/^$/d')
echo "数据库会话信息："
IFS=$'\n'
for line in $sessions_no_empty; do
if [[ $line =~ ^[[:space:]]*[A-Za-z]+ ]]; then
username=$(echo "$line" | awk '{print $1}')
active_session=$(echo "$line" | awk '{print $2}')
echo "用户名：$username，活动会话数量：$active_session"
sessions1="用户名：$username，活动会话数量：$active_session"
fi
done
else
echo "无数据库会话信息"
fi
# 数据库归档日志频率
archives=$(sqlplus -S / as sysdba <<EOF
set lines 200 pages 200
select count(*) as num_of_archived_logs from v\$archived_log where first_time > sysdate -1;
EOF
)
if [ -n "$archives" ]; then
archives_no_empty=$(echo "$archives" | sed '/^$/d')
num_of_archived_logs=$(echo "$archives_no_empty" | awk '{print $1}')
result="${num_of_archived_logs//[[:space:]]}"
else
result="0"
fi
# 使用正则表达式提取数字部分
archives1=$(echo "$result" | grep -o '[0-9]*')
echo "$archives1"
# 数据库数据文件使用率
usage1=$(sqlplus -S / as sysdba <<EOF
set lines 200 pages 200
SELECT
df.tablespace_name,
round((df.bytes - NVL(free.bytes, 0)) / df.bytes * 100, 2) as used_percent
FROM
(SELECT
tablespace_name,
SUM(bytes) bytes
FROM
dba_data_files
GROUP BY
tablespace_name) df,
(SELECT
tablespace_name,
SUM(bytes) bytes
FROM
dba_free_space
GROUP BY
tablespace_name) free
WHERE
df.tablespace_name = free.tablespace_name;
EOF
)
if [ -n "$usage1" ]; then
usage_no_empty=$(echo "$usage1" | sed '/^$/d')
echo "数据库数据文件使用率信息："
IFS=$'\n'
for line in $usage_no_empty; do
if [[ $line =~ ^[[:space:]]*[A-Za-z]+ ]]; then
tablespace_name=$(echo "$line" | awk '{print $1}')
used_percent=$(echo "$line" | awk '{print $2}')
echo "表空间名称：$tablespace_name，使用率：$used_percent%"
usage="表空间名称：$tablespace_name，使用率：$used_percent%"
fi
done
else
echo "无数据库数据文件使用率信息"
fi
# 异常状态数据库用户
users=$(sqlplus -S / as sysdba <<EOF
set lines 200 pages 200
select username,lock_date from dba_users where account_status!= 'OPEN' and created >= (select trunc(created) from dba_users where username='SYS')+0.99999 and (lock_date >= sysdate - 30 or (expiry_date <= sysdate +7 and expiry_date >= sysdate - 7)) order by created;
EOF
)
# 开启归档日志
archive_status1=$(sqlplus -S / as sysdba <<EOF
set lines 200 pages 200
col value for a30
select value from v\$parameter where name = 'log_archive_start';
EOF
)
if [ -n "$archive_status1" ]; then
archive_status=$(echo "$archive_status1" | tr -d '\n' | tr -d '-' | tr -d ' ' | tr -d '\t' | sed 's/VALUE//')
echo "$archive_status"
else
echo "未获取到状态"
fi
# 闪回区使用率
flashback_usage1=$(sqlplus -S / as sysdba <<EOF
set lines 200 pages 200
col name for a30
col value for a30
select name, value from v\$parameter where name = 'db_recovery_file_dest_size';
EOF
)
if [ -n "$flashback_usage1" ]; then
value=$(echo "$flashback_usage1" | awk '{print $2}')
# 使用正则表达式去除开头的字符串部分
flashback_usage=$(echo "$value" | grep -o '[0-9]*')
echo "$flashback_usage"
else
echo "未获取到闪回区使用率相关信息"
fi
# 锁表信息
lock_info=$(sqlplus -S / as sysdba <<EOF
set pagesize 0 feedback off heading off
select count(*) from v\$lock group by decode(request, 0, 'held', 'waiting');
EOF
)
count_value=$(echo "$lock_info" | sed '/^$/d')
echo "锁表信息（COUNT 结果）：$count_value"
# 错误日志
ERROR_LOG=$(tail -n 5 /u01/app/oracle/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace/*.log|grep -i error)
error_log_cmd="tail -n 5 /u01/app/oracle/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace/*.log"
# 数据库巡检部分输出为 HTML 表格
echo "<h2>数据库巡检（${ORACLE_SID}）</h2>" >> $HTML_FILE
echo "<table border='1'>" >> $HTML_FILE
echo "<tr><th>巡检项</th><th>巡检命令</th><th>结果</th></tr>" >> $HTML_FILE
echo "<tr><td>数据库异常日志</td><td>数据库异常日志查询 SQL</td><td>"$(if [ -n "$logs" ]; then echo "$logs"; else echo "无报错信息"; fi)"</td></tr>" >> $HTML_FILE
echo "<tr><td>数据库备份情况</td><td>备份查询 SQL</td><td>$backups</td></tr>" >> $HTML_FILE
echo "<tr><td>异常计划任务</td><td>任务查询 SQL</td><td>$tasks</td></tr>" >> $HTML_FILE
echo "<tr><td>数据库会话</td><td>会话查询 SQL</td><td>$sessions1</td></tr>" >> $HTML_FILE
echo "<tr><td>数据库归档日志频率</td><td>归档日志查询 SQL</td><td>$archives1</td></tr>" >> $HTML_FILE
echo "<tr><td>数据库数据文件使用率</td><td>数据文件使用率查询 SQL</td><td>$usage</td></tr>" >> $HTML_FILE
echo "<tr><td>异常状态数据库用户</td><td>异常用户查询 SQL</td><td>$users</td></tr>" >> $HTML_FILE
echo "<tr><td>开启归档日志</td><td>归档日志参数查询 SQL</td><td>$archive_status</td></tr>" >> $HTML_FILE
echo "<tr><td>闪回区使用率</td><td>闪回区参数查询 SQL</td><td>$flashback_usage</td></tr>" >> $HTML_FILE
echo "<tr><td>锁表信息</td><td>锁表查询 SQL</td><td>$count_value</td></tr>" >> $HTML_FILE
echo "<tr><td>错误日志</td><td>错误日志 SQL</td><td>"$(if [ -n "$ERROR_LOG" ]; then echo "$ERROR_LOG"; else echo "无错误日志"; fi)"</td></tr>" >> $HTML_FILE
echo "</table>" >> $HTML_FILE
}
function echo_head() {
echo "<html lang=\"zh-CN\"><meta charset=\"UTF-8\"><head><title>Oracle 健康巡检报告</title></head><body>"
echo "<style>"
echo "body { font-family: Arial, sans-serif; }"
echo "table { width: 100%; border-collapse: collapse; }"
echo "th, td { border: 1px solid #ddd; padding: 6px; text-align: left; }"
echo "th {background-color: #f2f2f2; }"
echo "</style>"
echo "<body>"
echo "<h1>XXXX项目</h1>"
echo "<h2>Oracle 健康巡检报告 - ${ORACLE_SID}</h2>"
echo "<h2>巡检时间：$(date)   巡检人员：zhh</h2>"
}
function echo_foot() {
echo "</body></html>"
}
HTML_FILE="./oracle_Healthcheck_${ORACLE_SID}.html"
# 输出 html 底部
echo_foot >> $HTML_FILE
check_server
check_db
二、总结：
脚本还需优化和完善，后续会进行更新。
欢迎关注公众号《小周的数据库进阶之路》，更多精彩知识和干货尽在其中。
