oracle19c巡检脚本

以下是一个 Oracle 19c 数据库巡检脚本的示例，您可以根据自己的需要进行修改和调整：

#!/bin/bash
# Author: Your Name
# Date: 2021-06-01
# Description: A script to perform Oracle 19c database health check

# Define variables
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
LOG_FILE=/var/log/oracle/check.log
DATE=$(date '+%Y-%m-%d %H:%M:%S')

# Define functions
function check_database_status {
  $ORACLE_HOME/bin/sqlplus -S / as sysdba << EOF
    SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
    SELECT STATUS FROM V\\$INSTANCE;
    EXIT;
EOF
  if [ $? -ne 0 ]; then
    echo "$DATE ERROR: Failed to connect to database" >> $LOG_FILE
    return 1
  fi
  return 0
}

function check_alert_log {
  ALERT_LOG=$ORACLE_HOME/diag/rdbms/*/ORCL/alert/log.xml
  if [ ! -f $ALERT_LOG ]; then
    echo "$DATE ERROR: Alert log not found" >> $LOG_FILE
    return 1
  fi
  ERRORS=$(grep -ic 'ORA-' $ALERT_LOG)
  if [ $ERRORS -gt 0 ]; then
    echo "$DATE ERROR: Found $ERRORS errors in alert log" >> $LOG_FILE
    return 1
  fi
  return 0
}

function check_undotbs {
  $ORACLE_HOME/bin/sqlplus -S / as sysdba << EOF
    SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
    SELECT COUNT(*) FROM DBA_ROLLBACK_SEGS WHERE STATUS != 'ONLINE';
    EXIT;
EOF
  if [ $? -ne 0 ]; then
    echo "$DATE ERROR: Failed to connect to database" >> $LOG_FILE
    return 1
  fi
  return 0
}

function check_tempfiles {
  $ORACLE_HOME/bin/sqlplus -S / as sysdba << EOF
    SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
    SELECT COUNT(*) FROM DBA_TEMP_FILES WHERE STATUS != 'AVAILABLE';
    EXIT;
EOF
  if [ $? -ne 0 ]; then
    echo "$DATE ERROR: Failed to connect to database" >> $LOG_FILE
    return 1
  fi
  return 0
}

# Main script
echo "$DATE Starting Oracle 19c database health check" >> $LOG_FILE

check_database_status && echo "$DATE Database status is OK" >> $LOG_FILE
check_alert_log && echo "$DATE Alert log is clean" >> $LOG_FILE
check_undotbs && echo "$DATE No offline undo tablespace found" >> $LOG_FILE
check_tempfiles && echo "$DATE No unavailable temp file found" >> $LOG_FILE

echo "$DATE Oracle 19c database health check completed" >> $LOG_FILE

此脚本使用了多个 Oracle 命令来检查数据库的运行状态，包括数据库本身、告警日志、未提交事务表空间和临时文件。脚本输出会写入日志文件中，您可以根据需要将其添加到定时任务中进行定期检查。