oracle 巡检脚本

执行该脚本的前提是，在脚本同路径下有个config.cfg文件
config.cfg文件包含每行格式为：host_ip TNS_ALIAS username password
将该脚本放在oracle 客户端 linux服务器上执行。

#!/bin/bash

# 定义Oracle 11g环境变量
source /home/oracle/.bash_profile

# 数据库TNS别名列表文件
db_tns_list="config.cfg"  # 文件包含每行格式为：host_ip TNS_ALIAS username password

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

# 检查数据库状态
check_db_status() {
    db_status=$(run_sqlplus "select trim(database_role) || ',' || trim(open_mode) from v\$database;")
    db_status=$(echo "$db_status" | tr -d '\r' | tr -d '\n' | sed 's/ //g')

    database_role=$(echo "$db_status" | cut -d',' -f1)
    open_mode=$(echo "$db_status" | cut -d',' -f2)

    db_status_value="$db_status,异常"

    if [[ "$database_role" == "PRIMARY" && "$open_mode" == "READWRITE" ]]; then
        db_status_value="$db_status,正常"
    elif [[ "$database_role" == "PHYSICALSTANDBY" && "$open_mode" == "READONLYWITHAPPLY" ]]; then
        db_status_value="$db_status,正常"
    fi

    echo "$db_status_value"
}

# 检查归档日志空间使用率
check_archive_space() {
    archive_dest=$(run_sqlplus "select value from v\$parameter where name = 'log_archive_dest_1';")
    
    if [[ "$archive_dest" =~ "location" ]]; then
        archive_location=$(echo "$archive_dest" | grep -oP "location=\K[^ ]+")
        echo "$archive_location,正常"
    else
        archive_usage=$(run_sqlplus "select round((space_used/space_limit)*100,2) from v\$recovery_file_dest;")
        archive_usage=$(echo "$archive_usage" | tr -d '\r' | tr -d '\n' | sed 's/^[[:space:]]*//;s/[[:space:]]*$//')

        if (( $(echo "$archive_usage < 80.0" | bc -l) )); then
            echo "$archive_usage%,正常"
        else
            echo "$archive_usage%,需关注"
        fi
    fi
}

# 检查数据文件状态
check_datafile_status() {
    datafile_status=$(run_sqlplus "select file_id, status from dba_data_files where status != 'AVAILABLE';")
    datafile_status=$(echo "$datafile_status" | tr -d '\r' | tr -d '\n' | sed 's/^[[:space:]]*//;s/[[:space:]]*$//')
    [[ -z "$datafile_status" ]] && echo "数据文件状态,正常" || 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;")
    dg_gap=$(echo "$dg_gap" | tr -d '\r' | tr -d '\n' | sed 's/^[[:space:]]*//;s/[[:space:]]*$//')
    [[ -z "$dg_gap" ]] && echo "$dg_gap,正常" || echo "GAP值为: $dg_gap,异常"
}

# 检查CPU使用率
check_cpu_usage() {
    cpu_usage=$(vmstat 1 2 | tail -1 | awk '{print 100 - $15}')
    if (( $(echo "$cpu_usage < 70" | bc -l) )); then
        echo "$cpu_usage%,正常"
    elif (( $(echo "$cpu_usage >= 70 && $cpu_usage < 90" | bc -l) )); then
        echo "$cpu_usage%,需关注"
    else
        echo "$cpu_usage%,异常"
    fi
}

# 检查内存使用率
check_mem_usage() {
    mem_usage=$(free | grep Mem | awk '{print $3/$2 * 100.0}')
    if (( $(echo "$mem_usage < 70" | bc -l) )); then
        echo "$mem_usage%,正常"
    elif (( $(echo "$mem_usage >= 70 && $mem_usage < 90" | bc -l) )); then
        echo "$mem_usage%,需关注"
    else
        echo "$mem_usage%,异常"
    fi
}

# 检查所有挂载磁盘空间使用率
check_disk_usage() {
    disk_status=""
    while IFS= read -r line; do
        mount_point=$(echo "$line" | awk '{print $6}')
        usage=$(echo "$line" | awk '{print $5}' | sed 's/%//')
        
        if (( $(echo "$usage < 70" | bc -l) )); then
            status="$usage%,路径${mount_point},正常"
        elif (( $(echo "$usage >= 70 && $usage < 95" | bc -l) )); then
            status="$usage%,路径${mount_point},需关注"
        else
            status="$usage%,路径${mount_point},异常"
        fi

        if [[ -n "$disk_status" ]]; then
            disk_status="${disk_status}; $status"
        else
            disk_status="$status"
        fi
    done <<< "$(df -h | grep '^/dev')"

    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=$(echo "$deadlock_count" | tr -d '\r' | tr -d '\n' | sed 's/^[[:space:]]*//;s/[[:space:]]*$//')

    if [[ "$deadlock_count" -eq 0 ]]; then
        echo "$deadlock_count,正常"
    else
        echo "$deadlock_count,异常"
    fi
}

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

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

wb = openpyxl.Workbook()

def create_sheet(wb, host_ip, db_instance_name):
    sheet_name = f"{host_ip}_{db_instance_name}"
    if sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
    else:
        ws = wb.create_sheet(title=sheet_name)
    return ws

def set_column_width(ws, width):
    for col in ws.columns:
        col_letter = col[0].column_letter
        ws.column_dimensions[col_letter].width = width

def apply_fill(cell, value):
    value_str = str(value)
    if "异常" in value_str:
        key_status = "异常"
    elif "正常" in value_str:
        key_status = "正常"
    elif "需关注" in value_str:
        key_status = "需关注"
    else:
        key_status = None
    
    if key_status == "异常":
        cell.fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
    elif key_status == "正常":
        cell.fill = PatternFill(start_color="00FF00", end_color="00FF00", fill_type="solid")
    elif key_status == "需关注":
        cell.fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

def summarize_status(status_value):
    if "异常" in status_value:
        return "异常"
    elif "正常" in status_value:
        return "正常"
    elif "需关注" in status_value:
        return "需关注"
    else:
        return ""

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

def set_summary_column_width(widths):
    for i, width in enumerate(widths, start=1):
        col_letter = openpyxl.utils.get_column_letter(i)
        summary_ws.column_dimensions[col_letter].width = width

column_widths = [20] * len(summary_headers)
EOF

# 巡检每个数据库
while read -r host_ip tns_alias username password; do
    export tns_alias username password

    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:]')

    # 生成Python脚本中对应的每个数据库的部分
    cat <<EOF >> "$python_script"
ws = create_sheet(wb, "$host_ip", "$db_instance_name")

if ws.max_row == 1:
    headers = ["主机IP", "数据库实例名", "数据库状态", "归档日志空间", "数据文件状态", "Data Guard GAP", "CPU使用率", "内存使用率", "磁盘空间使用率", "死锁状态"]
    ws.append(headers)

data = [("$host_ip", "$db_instance_name", "$db_status", "$archive_space", "$datafile_status", "$dg_gap", "$cpu_usage", "$mem_usage", "$disk_usage", "$deadlock_status")]
for row in data:
    ws.append(row)

set_column_width(ws, 20)

column_widths = [max(column_widths[i], len(str(row[i])) + 2) for i in range(len(row))]

summary_data = [
    ("$host_ip", "$db_instance_name", summarize_status("$db_status"), summarize_status("$archive_space"), 
     summarize_status("$datafile_status"), summarize_status("$dg_gap"), summarize_status("$cpu_usage"), 
     summarize_status("$mem_usage"), summarize_status("$disk_usage"), summarize_status("$deadlock_status"))
]
for row in summary_data:
    summary_ws.append(row)
    for i, cell in enumerate(summary_ws[summary_ws.max_row]):
        apply_fill(cell, row[i])

# WPS适配，添加跳转链接到汇总表
summary_ws.cell(row=summary_ws.max_row, column=2).hyperlink = "#'${host_ip}_${db_instance_name}'!A1"
summary_ws.cell(row=summary_ws.max_row, column=2).value = "$db_instance_name"
summary_ws.cell(row=summary_ws.max_row, column=2).style = "Hyperlink"

EOF

done < "$db_tns_list"

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

# 执行Python脚本
python3 "$python_script"

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