达梦数据库shell脚本之查看数据库运行状况

 我们经常使用一些数据库监控工具来监控数据库的运行状况。以下脚本可以简单查询达梦数据库的运行情况，数据库的基本信息，数据库表空间占用磁盘大小，备份作业执行情况等。

#! /bin/bash

v_date=$(date)
echo 检查时间:$v_date >>db_check.txt

dm_service=("dmserver" "dmwather" "dmmmonitor" "dmap" "dmagent")
for dm in ${dm_service[@]}
do
        ps_out=`ps -ef | grep $dm | grep -v 'grep'`
        result=$(echo $ps_out | grep "$dm")
        if [[ "$result" == "" ]];then
          echo "$dm 服务未运行" >>db_check.txt
        fi
done

db_instance_name=$(disql -s SYSDBA/SYSDBA <<"EOF"
SET ECHO OFF;
SET FEED OFF;
SET HEA OFF;
SET LINESHOW OFF;
SET NEWP 0;
SET PAGES 0;
SET TIMING OFF;
select INSTANCE_NAME from v$instance;
exit
EOF
)
echo 数据库实例名:$db_name >> db_check.txt

db_stat=$(disql -s SYSDBA/SYSDBA <<"EOF"
SET ECHO OFF;
SET FEED OFF;
SET HEA OFF;
SET LINESHOW OFF;
SET NEWP 0;
SET PAGES 0;
SET TIMING OFF;
select NAME from v$database;
exit
EOF
)
echo 数据库名:$db_name >> db_check.txt

db_stat=$(disql -s SYSDBA/SYSDBA <<"EOF"
SET ECHO OFF;
SET FEED OFF;
SET HEA OFF;
SET LINESHOW OFF;
SET NEWP 0;
SET PAGES 0;
SET TIMING OFF;
select STATUS$ from v$instance;
exit
EOF
)

if [ "$db_stat" == 'OPEN' ];then
  echo 数据库状态:OPEN >>db_check.txt
else
  echo 数据库状态:DBERROR >>db_check.txt
  echo 数据库状态:$db_stat >>db_check.txt
fi

#检查数据库最后一次启动时间
#db_last_start_time=$(disql -s SYSDBA/SYSDBA  <<"EOF"
#SET ECHO OFF;
#SET FEED OFF;
#SET HEA OFF;
#SET LINESHOW OFF;
#SET NEWP 0;
#SET PAGES 0;
#SET TIMING OFF;
#select '最近一次启动时间:', LAST_STARTUP_TIME from v$database;
#exit
#EOF
#)
#echo $db_last_start_time >>db_check.txt

echo 数据库表空间使用情况: >>db_check.txt

db_tablespace=$(disql -s SYSDBA/SYSDBA <<"EOF"
SET ECHO OFF;
SET FEED OFF;
SET HEA OFF;
SET LINESHOW OFF;
SET NEWP 0;
SET PAGES 0;
SET TIMING OFF;
SELECT NAME,CAST((a.TOTAL_SIZE-a.FREE_SIZE)*100/a.TOTAL_SIZE AS NUMERIC(3,0)) USED_RATE FROM V$DATAFILE a ,v$TABLESPACE b where a.group_id=b.id;
exit
EOF
)
array_tb=($db_tablespace)
array_num=${#array_tb[*]}
for i in $(seq 1 2 $array_num)
do
  if((${array_tb[i]}> 70));then
    echo 表空间${array_tb[i-1]}:${array_tb[i]}% \> 70% 警告 >>db_check.txt
  else
    echo 表空间${array_tb[i-1]}:${array_tb[i]}% \< 70% 正常 >>db_check.txt
  fi
done

db_backup=$(disql -s SYSDBA/SYSDBA <<"EOF"
SET ECHO OFF;
SET FEED OFF;
SET HEA OFF;
SET LINESHOW OFF;
SET NEWP 0;
SET PAGES 0;
SET TIMING OFF;
select count(1) from "SYSJOB"."SYSJOBHISTORIES2" where NAME='每日全备' and ERRCODE=0 and START_TIME > SYSDATE-1;
exit;
EOF
)
if(($db_backup > 0));then
  echo $(date +%Y-%m-%d)日备份正常 >>db_check.txt
else
  echo $(date +%Y-%m-%d)日备份异常 >>db_check.txt
fi