Oracle11.2.0.4巡检脚本

需要放在Oracle客户端Linux服务器上执行。
先部署tns和config.cfg文件。
再执行该文件
config.cfg文件格式如下：

主机IP ssh登录端口 主机用户名 主机密码 tnsname 数据库用户名 数据库密码

monitor.sh脚本内容如下：

#!/bin/bash

# 从 config.cfg 中读取数据库 TNS 别名列表文件
db_tns_list="config.cfg"

# SQLPlus 执行函数，传入 SQL 命令并返回结果
run_sqlplus() {
    local sql_command=$1
    sql_output=$(sqlplus -S ${username}/${password}@${tns_alias} <<EOF
    set heading off feedback off verify off
    $sql_command
    exit;
EOF
    )

    if [[ $? -ne 0 ]]; then
        echo "SQLPlus command failed: $sql_output" >&2
        echo "ERROR"
        return 1
    fi

    echo "$sql_output" | sed 's/^[ \t]*//;s/[ \t]*$//' | tr -d '\n'
}

# 通过 SSH 远程执行命令的函数
run_ssh_command() {
    local command=$1
    ssh_output=$(sshpass -p "$host_password" ssh -o StrictHostKeyChecking=no -p "$ssh_port" "$host_user@$host_ip" "$command" 2>&1)
    if [[ $? -ne 0 ]]; then
        echo "SSH command failed on $host_ip: $ssh_output" >&2
        echo "ERROR"
        return 1
    fi
    echo "$ssh_output" | tr -d '\n'
}

# 检查数据库状态
check_db_status() {
    db_status=$(run_sqlplus "select trim(database_role) || ',' || trim(open_mode) from v\$database;")
    [[ -z "$db_status" ]] && db_status="UNKNOWN,异常"
    role=$(echo "$db_status" | cut -d',' -f1)
    mode=$(echo "$db_status" | cut -d',' -f2)

    if [[ "$role" == "PRIMARY" && "$mode" == "READ WRITE" ]]; then
        db_status="$role,$mode,正常"
    elif [[ "$role" == "PHYSICAL STANDBY" && "$mode" == "READ ONLY WITH APPLY" ]];then
        db_status="$role,$mode,正常"
    else
        db_status="$role,$mode,异常"
    fi
    echo "$db_status"
}

# 检查归档日志空间使用率
check_archive_space() {
    archive_usage=$(run_sqlplus "select round((space_used/space_limit)*100,2) from v\$recovery_file_dest;")
    [[ -z "$archive_usage" ]] && archive_usage="0"

    if (( $(echo "$archive_usage < 80.0" | bc -l) )); then
        archive_space="$archive_usage%,正常"
    elif (( $(echo "$archive_usage >= 80.0 && $archive_usage < 95.0" | bc -l) )); then
        archive_space="$archive_usage%,需关注"
    else
        archive_space="$archive_usage%,异常"
    fi
    echo "$archive_space"
}

# 检查数据文件状态
check_datafile_status() {
    datafile_status=$(run_sqlplus "select file_id, status from dba_data_files where status != 'AVAILABLE';")
    [[ -z "$datafile_status" ]] && datafile_status="所有文件,正常" || datafile_status="$datafile_status,异常"
    echo "$datafile_status"
}

# 检查 Data Guard GAP
check_dg_gap() {
    dg_gap=$(run_sqlplus "select (select max(sequence#) from v\$archived_log where applied = 'NO') - (select max(sequence#) from v\$archived_log where applied = 'YES') gap from dual;")
    [[ -z "$dg_gap" || "$dg_gap" == "NULL" ]] && dg_gap="无,正常" || dg_gap="GAP值为: $dg_gap,异常"
    echo "$dg_gap"
}

# 检查远程服务器的 CPU 使用率
check_cpu_usage() {
    cpu_usage=$(run_ssh_command "vmstat 1 2 | tail -1 | awk '{print 100 - \$15}'")
    cpu_usage=${cpu_usage%.*}  # 去掉小数部分
    if [[ "$cpu_usage" -lt 70 ]]; then
        cpu_status="$cpu_usage%,正常"
    elif [[ "$cpu_usage" -ge 70 && "$cpu_usage" -lt 90 ]]; then
        cpu_status="$cpu_usage%,需关注"
    else
        cpu_status="$cpu_usage%,异常"
    fi
    echo "$cpu_status"
}

# 检查远程服务器的内存使用率
check_mem_usage() {
    mem_usage=$(run_ssh_command "free | grep Mem | awk '{print \$3/\$2 * 100.0}'")
    mem_usage=${mem_usage%.*}  # 去掉小数部分
    if [[ "$mem_usage" -lt 70 ]]; then
        mem_status="$mem_usage%,正常"
    elif [[ "$mem_usage" -ge 70 && "$mem_usage" -lt 90 ]]; then
        mem_status="$mem_usage%,需关注"
    else
        mem_status="$mem_usage%,异常"
    fi
    echo "$mem_status"
}

# 检查远程服务器的磁盘空间使用率
check_disk_usage() {
    disk_status=()
    df_output=$(run_ssh_command "df -h | grep '^/dev'")

    while IFS= read -r line; do
        mount_point=$(echo "$line" | awk '{print $6}')
        usage=$(echo "$line" | awk '{print $5}' | sed 's/%//')
        if [[ "$usage" -lt 70 ]]; then
            status="正常"
        elif [[ "$usage" -ge 70 && "$usage" -lt 95 ]]; then
            status="需关注"
        else
            status="异常"
        fi
        disk_status+=("$mount_point,$usage%,$status")
    done <<< "$df_output"

    echo "$(IFS=';'; echo "${disk_status[*]}")"
}

# 检查死锁情况
check_deadlock() {
    deadlock_count=$(run_sqlplus "select count(*) from v\$lock l1, v\$lock l2 where l1.block = 1 and l2.request > 0 and l1.id1 = l2.id1 and l1.id2 = l2.id2;")
    [[ "$deadlock_count" -eq 0 ]] && deadlock_status="无死锁,正常" || deadlock_status="$deadlock_count 个死锁,异常"
    echo "$deadlock_status"
}

# 初始化 Python 脚本
python_script="/tmp/oracle_monitor.py"
> "$python_script"

# 生成 Python 文件头部
cat <<EOF > "$python_script"
import openpyxl
from openpyxl.styles import PatternFill

wb = openpyxl.Workbook()
summary_ws = wb.active
summary_ws.title = "Oracledb"
summary_headers = ["主机IP", "数据库实例名", "数据库状态", "归档日志空间", "数据文件状态", "Data Guard GAP", "CPU 使用率", "内存使用率", "磁盘空间使用率", "死锁状态"]
summary_ws.append(summary_headers)

# 设置每个列宽为 20
for col in summary_ws.columns:
    summary_ws.column_dimensions[col[0].column_letter].width = 20

# 定义颜色填充
def fill_cell_based_on_status(cell, status):
    if "异常" in status:
        cell.fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
    elif "需关注" in status:
        cell.fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
    elif "正常" in status:
        cell.fill = PatternFill(start_color="00FF00", end_color="00FF00", fill_type="solid")

EOF

# 巡检每个数据库
while read -r host_ip ssh_port host_user host_password tns_alias username password; do
    # 将变量导出到环境中
    tns_alias="$tns_alias"
    username="$username"
    password="$password"
    host_ip="$host_ip"
    host_user="$host_user"
    host_password="$host_password"
    ssh_port="$ssh_port"

    db_status=$(check_db_status)
    archive_space=$(check_archive_space)
    datafile_status=$(check_datafile_status)
    dg_gap=$(check_dg_gap)
    cpu_usage=$(check_cpu_usage)
    mem_usage=$(check_mem_usage)
    disk_usage=$(check_disk_usage)
    deadlock_status=$(check_deadlock)

    db_instance_name=$(run_sqlplus "select instance_name from v\$instance;")
    db_instance_name=$(echo "$db_instance_name" | tr -d '[:space:]')

    # 如果数据库名为空或有异常，则继续下一个数据库
    if [[ "$db_instance_name" == "ERROR" || -z "$db_instance_name" ]]; then
        echo "Skipping $host_ip due to an error"
        continue
    fi

    # 追加数据到 Python 脚本
    cat <<EOF >> "$python_script"
row = ["$host_ip", "$db_instance_name", "$db_status", "$archive_space", "$datafile_status", "$dg_gap", "$cpu_usage", "$mem_usage", "$disk_usage", "$deadlock_status"]
summary_ws.append(row)

# 给每行的每个单元格设置颜色
for row in summary_ws.iter_rows(min_row=summary_ws.max_row, max_row=summary_ws.max_row):
    for cell in row[2:]:  # 从第三个单元格开始（数据库状态）
        fill_cell_based_on_status(cell, cell.value)
EOF

done < "$db_tns_list"

# 生成 Python 文件尾部，保存 Excel 文件
cat <<EOF >> "$python_script"
wb.save("/tmp/monitor.xlsx")
EOF

# 执行 Python 脚本
python3 "$python_script"

echo "Oracle数据库监控报告已生成至 /tmp/monitor.xlsx"