概述

数据库巡检是一项非常重要的任务，它有以下几个方面的重要性：

• 保证数据库的稳定性和可靠性：巡检可以帮助管理员及时发现数据库中存在的问题，如性能瓶颈、资源竞争、死锁、数据丢失等，以便及时采取措施解决这些问题，保证数据库的稳定性和可靠性。
• 提高数据库的性能：通过巡检，可以分析数据库的性能瓶颈，优化数据库的配置、索引、表结构等，从而提高数据库的性能。
• 提高数据库的安全性：巡检可以帮助管理员及时发现数据库中存在的安全隐患，如未授权的访问、恶意攻击等，以及及时采取措施加强数据库的安全性。
• 遵守合规要求：对于一些行业，如金融、医疗等，存在严格的合规要求，巡检可以帮助管理员检查数据库是否符合这些合规要求，以确保企业的合法经营。
• 提高管理员的工作效率：巡检可以帮助管理员及时发现问题，减少日常维护的工作量，提高管理员的工作效率。

解决方案

#!/bin/bash

# 设置Oracle环境变量
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=your_oracle_sid

# 设置变量
DATE=$(date +%Y%m%d_%H%M%S)
LOGFILE=oracle_healthcheck_$DATE.log

# 创建日志文件
touch $LOGFILE

# 执行巡检命令并将结果写入日志文件
echo "Oracle Healthcheck Report" >> $LOGFILE
echo "------------------------" >> $LOGFILE
echo "" >> $LOGFILE

# 检查Oracle实例状态
echo "Checking Oracle instance status..." >> $LOGFILE
ps -ef | grep ora_pmon | grep -v grep > /dev/null
if [ $? -eq 0 ]
then
    echo "Oracle instance is running." >> $LOGFILE
else
    echo "Oracle instance is not running." >> $LOGFILE
fi
echo "" >> $LOGFILE

# 检查Oracle监听状态
echo "Checking Oracle listener status..." >> $LOGFILE
lsnrctl status > /dev/null
if [ $? -eq 0 ]
then
    echo "Oracle listener is running." >> $LOGFILE
else
    echo "Oracle listener is not running." >> $LOGFILE
fi
echo "" >> $LOGFILE

# 检查控制文件状态
echo "Checking control file status..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
select name, status from v\$controlfile;
exit;
EOF
echo "" >> $LOGFILE

# 检查在线日志状态
echo "Checking online redo log status..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
select group#, status from v\$log;
exit;
EOF
echo "" >> $LOGFILE

# 检查表空间状态
echo "Checking tablespace status..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
select tablespace_name, status from dba_tablespaces;
exit;
EOF
echo "" >> $LOGFILE

# 检查数据文件状态
echo "Checking datafile status..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
select file_name, status from dba_data_files;
exit;
EOF
echo "" >> $LOGFILE

# 检查数据库连接情况
echo "Checking database connections..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
select count(*) from v\$session;
exit;
EOF
echo "" >> $LOGFILE

# 检查系统磁盘空间
echo "Checking system disk space..." >> $LOGFILE
df -h >> $LOGFILE
echo "" >> $LOGFILE

# 检查表空间使用情况
echo "Checking tablespace usage..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
col "Tablespace Name" format a20
col "Total Size (MB)" format 999,999,999
col "Used Size (MB)" format 999,999,999
col "Free Size (MB)" format 999,999,999
select df.tablespace_name "Tablespace Name", totalusedspace/1024/1024 "Used Size (MB)",
(df.totalspace - totalusedspace)/1024/1024 "Free Size (MB)", df.totalspace/1024/1024 "Total Size (MB)"
from
(select tablespace_name, sum(bytes) totalusedspace
from dba_segments
group by tablespace_name) s,
(select tablespace_name, sum(bytes) totalspace
from dba_data_files
group by tablespace_name) df
where s.tablespace_name = df.tablespace_name;
exit;
EOF
echo "" >> $LOGFILE

# 检查消耗CPU最高的进程
echo "Checking processes consuming the most CPU..." >> $LOGFILE
ps -eo pid,ppid,cmd,%cpu,%mem --sort=-%cpu | head >> $LOGFILE
echo "" >> $LOGFILE

# 检查DISK READ最高的SQL语句
echo "Checking SQL statements with the highest DISK READs..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
col "SQL ID" format a15
col "DISK READS" format 999,999,999
select sql_id "SQL ID", disk_reads "DISK READS"
from v\$sqlarea
where rownum <= 10
order by disk_reads desc;
exit;
EOF
echo "" >> $LOGFILE

# 检查前十条性能差的SQL
echo "Checking the top 10 worst-performing SQL statements..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
col "SQL ID" format a15
col "ELAPSED TIME (s)" format 999,999,999
select sql_id "SQL ID", elapsed_time/1000000 "ELAPSED TIME (s)"
from v\$sqlarea
where rownum <= 10
order by elapsed_time desc;
exit;
EOF
echo "" >> $LOGFILE

# 检查运行很久的SQL
echo "Checking long-running SQL statements..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
col "SQL ID" format a15
col "RUNNING TIME (s)" format 999,999,999
select sql_id "SQL ID", last_active_time, round((sysdate - last_active_time)*86400) "RUNNING TIME (s)"
from v\$session
where type = 'USER'
order by running_time desc;
exit;
EOF
echo "" >> $LOGFILE

# 检查死锁及处理
echo "Checking for deadlocks..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
col blocker format a15
col wait_time format a15
select l1.sid blocker, l2.sid waiter, l1.username blocker_user, l2.username waiter_user, l2.event wait_event, l2.seconds_in_wait wait_time
from v\$lock l1, v\$lock l2, v\$session s1, v\$session s2
where l1.block = 1 and l2.request > 0
and l1.id1 = l2.id1 and l1.id2 = l2.id2
and l1.sid = s1.sid and l2.sid = s2.sid;
exit;
EOF
echo "" >> $LOGFILE

# 检查缓冲区命中率
echo "Checking buffer cache hit ratio..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
select name, value from v\$sysstat where name = 'buffer cache hit ratio';
exit;
EOF
echo "" >> $LOGFILE

# 检查共享池命中率
echo "Checking shared pool hit ratio..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
select name, value from v\$sysstat where name = 'shared pool hit ratio';
exit;
EOF
echo "" >> $LOGFILE

# 检查排序区状态
echo "Checking sort area status..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
select tablespace_name, max_size, used_size, alloc_size from v\$sort_segment;
exit;
EOF
echo "" >> $LOGFILE

# 检查日志缓冲区状态
echo "Checking redo log buffer status..." >> $LOGFILE
sqlplus -s /nolog <<EOF >> $LOGFILE
conn / as sysdba
set heading off
set feedback off
select name, value from v\$sysstat where name = 'redo log space requests';
exit;
EOF
echo "" >> $LOGFILE

# 检查ORACLE数据库备份状态
echo "Checking Oracle database backup status..." >> $LOGFILE
rman target / catalog rman/rman@rcat <<EOF >> $LOGFILE
list backup;
EOF
echo "" >> $LOGFILE

# 发送巡检报告邮件
mail -s "Oracle Healthcheck Report" your@email.com < $LOGFILE

exit

小结

通过脚本来使用我们的巡检任务自动化，来减轻常规的工作量，用更多的时间来专注于数据库更核心的维护和更深技术的探究！