UOS统信操作系统下达梦数据库巡检Shell脚本

Arvin_Rong
于 2024-03-18 12:58:04 发布
阅读量148
点赞数 3
文章标签：数据库
本文链接：https://blog.csdn.net/arvinrong/article/details/136805831
版权
此方法脚本中使用了明文密码，脚本文件保存需要注意环境安全。
#!/bin/bash

# Define output file
current_date=$(date "+%Y%m%d") # Gets the current date in YYYYMMDD format
output_file="dmdb_inspection_report_${current_date}.txt"

# 获取巡检时间
inspection_time=$(date "+%Y-%m-%d %H:%M:%S")

# 获取主机名称
hostname=$(hostname)

# 获取主机IP地址
host_ip=$(hostname -I | awk '{print $1}')


# 检查sysstat是否安装，没有的根据系统版本自动安装
if ! command -v mpstat &> /dev/null; then
    echo "sysstat 未安装. 尝试安装中..."

    # (Debian, Ubuntu, UOS and derivatives)
    if command -v apt &> /dev/null; then
        echo "Using apt to install sysstat..."
        apt update &&  apt install -y sysstat

    # (RedHat, CentOS, Fedora, 麒麟 and derivatives)
    elif command -v yum &> /dev/null; then
        echo "Using yum to install sysstat..."
        yum install -y sysstat

    else
        echo "yum 或 apt 包管理安装工具未找到无法自动安装，请手动安装."
    fi
fi

cpu_usage=$(mpstat 1 190 | awk '$0 ~ /Average|平均时间/ {print 100 - $12}')

# 获取内存使用情况
mem_available=$(free -mh | grep -E "Mem|内存" | awk '{print $7}')


# 初始化报告文件
echo "===============================================" > $output_file
echo "-- 巡检时间：$inspection_time" >> $output_file
echo "-- 主机IP地址：$host_ip" >> $output_file
echo "-- 主机名称：$hostname" >> $output_file
echo "-- 内存状态（参考值：available 大于1GB）：$mem_available" >> $output_file
echo "-- CPU状态（参考值：小于80%）：$cpu_usage%" >> $output_file
echo "" >> $output_file
disk_warning=0
echo "-- LVM卷组空间占用：" >> $output_file
lvs --noheadings --separator=" " -o lv_path,lv_name,vg_name,lv_attr | while read lv_path lv_name vg_name lv_attr; do
    # LV 读写情况 
    lv_write_access=$(if [[ ${lv_attr:1:1} == "w" ]]; then echo "read/write"; else echo "read"; fi)
    
    # LV 活动状态 
    lv_status=$(if [[ ${lv_attr:4:1} == "a" ]]; then echo "available"; else echo "not available"; fi)
    
    # ******LV 逻辑卷路径转换为设备映射路径，以便获取使用情况，这个规则可能因系统不同需要手工修改***** 默认转换规则为  /dev/centos/root 会转换为 /dev/mapper/centos-root  
    dm_path=$(echo "$lv_path" | sed 's;/dev/;/dev/mapper/;' | sed 's|\(.*\)/|\1-|')
    # LV 使用情况
    use_percent=$(df -h | grep "$dm_path" | awk '{ print $5 }')
    use_percent_num=$(echo "$use_percent"| sed 's/%//')
    # LV未mount时，使用率为空
    if [ -z "$use_percent" ]; then
        use_percent="N/A"
    fi

    # 格式化输出
    echo "  --- Logical volume ---" >> $output_file
    echo "    LV Path               $lv_path" >> $output_file
    echo "    LV Name               $lv_name" >> $output_file
    echo "    VG Name               $vg_name" >> $output_file
    echo "    LV Write Access       $lv_write_access" >> $output_file
    echo "    LV Status             $lv_status" >> $output_file
    echo "    Use%                  $use_percent" >> $output_file 
    if [ "$use_percent_num" -gt 80 ]; then
        echo "【警告】 $lv_path 的使用率大于 80% ." >> "$output_file"
    fi
    echo "" >> $output_file
done
# 查看达梦服务进程是否正常
# 替换为实际的用户名
DB_USER="SYSDBA"       
# 替换为实际的密码或从环境变量获取密码       
DB_PASSWORD="SYSDBA001"      
# 替换为实际的达梦数据库安装目录的bin目录路径
DB_BIN_DIR="/opt/dmdbms/bin"  
# 替换为实际的数据库主机地址
DB_HOST="localhost"           
# 替换为实际的数据库端口
DB_PORT="5236"                

process_check=$(ps -ef | grep 'dmserver' | grep -v 'grep')

if [ -n "$process_check" ]; then
  echo "-- 查看达梦服务进程是否正常: 【正常】" >> $output_file
else
  echo "-- 查看达梦服务进程是否正常: 进程未运行【告警】" >> $output_file
fi

# 检查达梦实例运行状态，为了避免密码泄露，建议使用环境变量或者其他安全的方式传递密码
status=$("$DB_BIN_DIR"/disql -S "$DB_USER"/"$DB_PASSWORD"@"$DB_HOST":"$DB_PORT" -e "select STATUS\$ from V\$INSTANCE;")
db_status=$(echo $status | awk '{print $NF}') 

if [ "$db_status" = "OPEN" ]; then
    echo "-- 检查达梦实例运行状态：【正常】" >> $output_file
else
    echo "-- 检查达梦实例运行状态：异常【告警】" >> $output_file
fi

# 查看达梦数据库锁査询 
lock_status=$("$DB_BIN_DIR"/disql -S "$DB_USER"/"$DB_PASSWORD"@"$DB_HOST":"$DB_PORT" -e "select o.name,l.* from v\$lock l,sysobjects o where l.table_id=o.id and blocked=1;")
# 检查lock_status是否为空
if [ -z "$lock_status" ]; then
    echo "-- 达梦数据库锁査询：未发现锁. 【正常】" >> $output_file
else
    echo "发现锁: 【告警】" >> $output_file
    echo "$lock_status" >> $output_file
fi

# 查看达梦数据连接数
conn_status=$("$DB_BIN_DIR"/disql -S "$DB_USER"/"$DB_PASSWORD"@"$DB_HOST":"$DB_PORT" -e "select count(*) from V\$SESSIONs where state='ACTIVE';")
echo "-- 达梦数据库连接数：" >> $output_file
echo "$conn_status" >> $output_file
echo "" >> $output_file

# 检查达梦数据库归档状态
archive_result=$("$DB_BIN_DIR"/disql -S "$DB_USER"/"$DB_PASSWORD"@"$DB_HOST":"$DB_PORT" -e "select arch_mode from v\$database;")
archive_status=$(echo $archive_result | awk '{print $NF}') 
echo "-- 达梦数据库归档是否开启：$archive_status" >> $output_file

# 检查达梦数据库表空间使用情况
tablespace_status_sql=$(cat <<'EOF'
SELECT F.TABLESPACE_NAME  TablespaceName , 
       ROUND((T.TOTAL_SPACE - F.FREE_SPACE) / 1024, 2)  Used,
       ROUND(F.FREE_SPACE / 1024, 2)  Free,
       ROUND(T.TOTAL_SPACE / 1024, 2) Total,
       CASE WHEN T.TOTAL_SPACE == 0 THEN '' ELSE (ROUND((F.FREE_SPACE / T.TOTAL_SPACE), 4)* 100) || '% ' END FREE_PERCENTAGE,
       CASE WHEN T.TOTAL_SPACE == 0 THEN '' ELSE (ROUND((T.TOTAL_SPACE - F.FREE_SPACE) / T.TOTAL_SPACE, 4) * 100)||'%' END USED_PERCENTAGE 
  FROM ( SELECT TABLESPACE_NAME, 
                ROUND(SUM(BLOCKS * ( SELECT PARA_VALUE / 1024 
                   FROM V$DM_INI 
                  WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE' ) / 1024)) FREE_SPACE 
           FROM DBA_FREE_SPACE 
       GROUP BY TABLESPACE_NAME ) F, ( SELECT TABLESPACE_NAME, 
                ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE 
           FROM DBA_DATA_FILES 
       GROUP BY TABLESPACE_NAME ) T, ( SELECT TABLESPACE_NAME, 
                ROUND(SUM(MAXBYTES / 1048576)) TOTAL_MAX_SPACE 
           FROM DBA_DATA_FILES 
       GROUP BY TABLESPACE_NAME ) H 
 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME AND F.TABLESPACE_NAME =H.TABLESPACE_NAME;
EOF
)
tablespace_status=$("$DB_BIN_DIR"/disql -S "$DB_USER"/"$DB_PASSWORD"@"$DB_HOST":"$DB_PORT" -e "$tablespace_status_sql")
echo "-- 达梦数据库表空间使用情况：" >> $output_file
echo "$tablespace_status" >> $output_file
echo "" >> $output_file


# 检查达梦数据库表空间可用性情况 
tablespace_availability_sql="select tablespace_name, status FROM dba_tablespaces;"
tablespace_availability=$("$DB_BIN_DIR"/disql -S "$DB_USER"/"$DB_PASSWORD"@"$DB_HOST":"$DB_PORT" -e "$tablespace_availability_sql")
echo "-- 达梦数据库表空间可用性情况：" >> $output_file
echo "$tablespace_availability" >> $output_file

echo "===============================================" >> $output_file

# 显示报告
cat $output_file
巡检结果样例截图：