Shell自动化管理 for ORACLE DBA

1.自动收集每天早上9点到晚上8点之间的AWR报告。 auto_awr.sh

#!/bin/bash# Set variables
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
ORACLE_SID=orcl
AWR_DIR=/home/oracle/AWR# Set date format for file naming
DATE=$(date +%Y%m%d%H%M%S)# Check current time - only run between 9am and 8pm
HOUR=$(date +%H)
if [[ "$HOUR" -lt 9 || "$HOUR" -ge 20 ]]; thenecho "Not within collection window. Exiting."exit
fi# Create AWR directory if it does not exist
mkdir -p $AWR_DIR# Run AWR report for the last hour
$ORACLE_HOME/bin/sqlplus / as sysdba <<EOF
set pagesize 0
set linesize 1000
set trimspool on
set feedback off
set echo off
define report_type='html'
define num_days=1
define begin_snap='${ORACLE_SID}_\${DATE}'
define end_snap='&begin_snap'
define dbid=''
define inst_num=''
define report_name='$AWR_DIR/awr_\${DATE}.html'
@?\rdbms\admin\awrrpt.sql
EOF

2.一个check_ccps.sh 检查待推送，监控上送，监控8999和返回代码为空的交易、检查是否对账、监控未对账。

可以执行以下检查：

• 检查待推送交易
• 监控上送交易
• 监控8999返回代码为空的交易
• 检查是否对账
• 监控未对账的交易

#!/bin/bash# Set variables
LOG_DIR=/var/log/ccps
WORK_DIR=/opt/ccps
CHECK_DATE=$(date +%Y%m%d)# Check for pending transactions
PENDING_COUNT=$(grep -c "Pending transaction" $LOG_DIR/ccps_${CHECK_DATE}.log)
if [[ "$PENDING_COUNT" -gt 0 ]]; thenecho "There are $PENDING_COUNT pending transactions."
fi# Monitor submitted transactions
SUBMITTED_COUNT=$(grep -c "Submitted transaction" $LOG_DIR/ccps_${CHECK_DATE}.log)
if [[ "$SUBMITTED_COUNT" -gt 0 ]]; thenecho "There are $SUBMITTED_COUNT submitted transactions."
fi# Monitor 8999 response code
NO_RESPONSE_COUNT=$(grep -c "Response code: " $LOG_DIR/ccps_${CHECK_DATE}.log | grep -c "Response code: [[:space:]]*$")
if [[ "$NO_RESPONSE_COUNT" -gt 0 ]]; thenecho "There are $NO_RESPONSE_COUNT transactions with no response code."
fi# Check reconciliation status
RECON_STATUS=$(grep "Reconciliation completed" $LOG_DIR/ccps_${CHECK_DATE}.log | tail -n1)
if [[ -z "$RECON_STATUS" ]]; thenecho "Reconciliation has not been completed."
elseecho "Reconciliation has been completed."
fi# Monitor unreconciled transactions
UNRECONCILED_COUNT=$(find $WORK_DIR -name "*_${CHECK_DATE}.txt" | wc -l)
if [[ "$UNRECONCILED_COUNT" -gt 0 ]]; thenecho "There are $UNRECONCILED_COUNT unreconciled transactions."
fi

该脚本假定CCPS系统日志文件名为ccps_<DATE>.log，并且所有待处理的交易文件都保存在工作目录中。您需要根据实际情况更新变量LOG_DIR和WORK_DIR。

该脚本通过搜索日志文件中的特定字符串来执行各种检查，并在发现问题时输出相应的消息。例如，如果有待推送的交易，将输出类似于“有X个等待处理的交易”的消息。

请注意，此脚本只是一个示例，并且可能需要根据您的具体需求进行修改和自定义。

Tips:

CCPS是“跨行交换清算系统”的缩写，是中国人民银行推出的用于实现银行间资金结算、交易清算和风险控制的统一平台。其主要功能包括支付结算、融资融券、黄金业务等。

CCPS通过建立一个统一的、高效的资金交换和清算机制，为银行业提供了更加便捷、安全、高效的资金结算服务，同时还可以起到监管和风险防控的作用。它支持多种付款方式，包括即时支付、定向支付、批量代发等，并且具有实时处理、高可靠性和大容量的特点。

在CCPS系统中，各家银行通过网络与CCPS系统进行连接，完成资金交换和清算。在交易过程中，CCPS系统将根据规则对交易进行审核和结算，保证交易的合法性和安全性。同时，CCPS系统还提供了实时交易监控和风险控制服务，能够及时响应各类异常情况和风险事件。

总之，CCPS是中国银行业重要的基础设施之一，为实现快速、安全、高效的资金结算提供了必要的支持。

3.check_database.sh 检查数据库是否处于打开状态、控制文件和日志文件是否正常、表空间是否在线、以及数据文件是否备份。

check_database.sh脚本，可以监控Oracle数据库的各种状态：

#!/bin/bash# Set variables
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
ORACLE_SID=orcl
LOG_DIR=/var/log/oracle# Check database status
DB_STATUS=$(echo "select status from v\$instance;" | $ORACLE_HOME/bin/sqlplus -S / as sysdba)
if [[ "$DB_STATUS" != "OPEN" ]]; thenecho "Database is not open."
fi# Check control file status
CONTROL_STATUS=$($ORACLE_HOME/bin/sqlplus -S / as sysdba <<EOF
set heading off feedback off verify off
select status from v\$controlfile;
EOF
)
if [[ "$CONTROL_STATUS" != "ONLINE" ]]; thenecho "Control file is not online."
fi# Check log file status
LOG_STATUS=$($ORACLE_HOME/bin/sqlplus -S / as sysdba <<EOF
set heading off feedback off verify off
select status from v\$logfile;
EOF
)
if [[ "$LOG_STATUS" != "VALID" ]]; thenecho "Log file is not valid."
fi# Check tablespace status
TABLESPACE_ALERT=$($ORACLE_HOME/bin/sqlplus -S / as sysdba <<EOF
set heading off feedback off verify off
SELECT t.tablespace_name, t.status FROM dba_tablespaces t WHERE t.status NOT IN ('ONLINE', 'READ ONLY');
EOF
)
if [[ -n "$TABLESPACE_ALERT" ]]; thenecho "One or more tablespaces are not online:"echo "$TABLESPACE_ALERT"
fi# Check datafile status
DATAFILE_ALERT=$(find $ORACLE_HOME/dbs -name "*.dbf" -type f -mtime +7 -exec ls -l {} \;)
if [[ -n "$DATAFILE_ALERT" ]]; thenecho "One or more datafiles have not been backed up in the last 7 days:"echo "$DATAFILE_ALERT"
fi

该脚本通过运行SQL查询或查找文件来检查数据库的各种状态。例如，它会检查数据库是否处于打开状态、控制文件和日志文件是否正常、表空间是否在线、以及数据文件是否备份。

请注意，此脚本只是一个示例，并且您可能需要根据您的具体需求进行修改和自定义。特别是对于tablespace的status判断以及datafile的备份情况判断，您需要根据实际情况来调整相应的阈值和条件。

4. 登录监控，check_login.sh脚本，可以监控Oracle数据库的登录情况：

#!/bin/bash# Set variables
LOG_DIR=/var/log/oracle
CHECK_DATE=$(date +%Y%m%d)
CHECK_TIME=$(date +%H:%M:%S)# Check for successful logins in the last hour
LOGIN_COUNT=$(grep -c "Successful login:" $LOG_DIR/listener.log | grep "$CHECK_DATE $CHECK_TIME" -C 60)
if [[ "$LOGIN_COUNT" -gt 0 ]]; thenecho "There were $LOGIN_COUNT successful logins in the last hour."
fi# Check for failed logins in the last hour
FAILED_COUNT=$(grep -c "Failed login:" $LOG_DIR/listener.log | grep "$CHECK_DATE $CHECK_TIME" -C 60)
if [[ "$FAILED_COUNT" -gt 0 ]]; thenecho "There were $FAILED_COUNT failed logins in the last hour."
fi# Check for locked accounts
LOCKED_ACCOUNTS=$($ORACLE_HOME/bin/sqlplus -S / as sysdba <<EOF
set heading off feedback off verify off
SELECT username FROM dba_users WHERE account_status = 'LOCKED';
EOF
)
if [[ -n "$LOCKED_ACCOUNTS" ]]; thenecho "The following database accounts are locked:"echo "$LOCKED_ACCOUNTS"
fi

该脚本通过搜索数据库监听器日志文件来检查成功和失败的登录次数，并在必要时输出相应的消息。它还查询数据库中的所有用户账户状态，以查找已锁定的账户。

请注意，此脚本只是一个示例，并且您可能需要根据您的具体需求进行修改和自定义。特别是对于登录时限、账户锁定的阈值等，需要根据实际情况来设置相应的参数。

5。写一个check_fund.sh异常退款

#!/bin/bash# Define variables for connecting to the database
DB_USER="username"
DB_PASSWORD="password"
DB_NAME="database_name"# Define variables for sending email notifications
EMAIL_FROM="[e-mail]"
EMAIL_TO="[e-mail]"
EMAIL_SUBJECT="Alert: Abnormal refunds detected!"# Connect to the database and execute SQL query
refund_count=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select count(*) from refunds where status='abnormal';
exit;
EOF
)# Check if refund count is greater than zero
if [ $refund_count -gt 0 ]
then# If refunds are abnormal, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Abnormal refunds detected: $refund_count refunds" >> /var/log/check_fund.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi

此脚本连接到Oracle数据库并执行SQL查询，以检查是否存在“异常”状态的退款。如果退款数量大于零，则会在日志文件中记录问题，并发送电子邮件通知给指定收件人。请注意，您需要根据自己的环境和要求修改脚本中的变量和语句。

6.check_trade_status.sh脚本，可以监控交易状态的变化。

#!/bin/bash# Define variables for connecting to the database
DB_USER="username"
DB_PASSWORD="password"
DB_NAME="database_name"# Define variables for sending email notifications
EMAIL_FROM="[e-mail]"
EMAIL_TO="[e-mail]"
EMAIL_SUBJECT="Alert: Trade status changed!"# Connect to the database and execute SQL query
trade_status=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select status from trades where id='$1';
exit;
EOF
)# Check if trade status has changed
if [ "$trade_status" != "$2" ]
then# If trade status has changed, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Trade status changed: trade id=$1, old status=$2, new status=$trade_status" >> /var/log/check_trade_status.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi

此脚本连接到Oracle数据库并执行SQL查询，以检查指定交易的当前状态。如果交易状态发生了变化，则会在日志文件中记录问题，并发送电子邮件通知给指定收件人。请注意，您需要根据自己的环境和要求修改脚本中的变量和语句。 

7. auto_immemory.sh 自动缓存每月初生成的新的分区

#!/bin/bash# Define variables for connecting to the database
DB_USER="username"
DB_PASSWORD="password"
DB_NAME="database_name"# Define variables for the partitioned table and index
TABLE_NAME="my_table"
INDEX_NAME="my_index"
PARTITION_PREFIX="PART"
PARTITION_EXPR="TO_DATE('2022-01-01','YYYY-MM-DD')"# Get current month and year
CURRENT_MONTH=$(date +%m)
CURRENT_YEAR=$(date +%Y)# Get name of partition to be cached
PARTITION_NAME="$PARTITION_PREFIX$CURRENT_YEAR$CURRENT_MONTH"# Check if partition exists and is not already cached in the In-Memory column store
partition_count=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select count(*) from user_tab_partitions where table_name='$TABLE_NAME' and partition_name='$PARTITION_NAME' and inmemory_size=0;
exit;
EOF
)# If partition exists and is not already in-memory, cache it
if [ $partition_count -eq 1 ]
thensqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOFalter table $TABLE_NAME move partition $PARTITION_NAME compress for query low;alter table $TABLE_NAME inmemory priority high memcompress for query;alter index $INDEX_NAME rebuild partition $PARTITION_NAME nologging online compress;exit;
EOFecho "$(date '+%Y-%m-%d %H:%M:%S') - Partition $PARTITION_NAME cached in-memory." >> /var/log/auto_immemory.log
fi

此脚本连接到Oracle数据库并执行SQL查询，以检查每个月开始时是否生成了新的分区，并确定该分区是否已经被缓存到In-Memory列存储器中。如果分区存在且尚未缓存，则会将其移动到In-Memory列存储器中。请注意，您需要根据自己的环境和要求修改脚本中的变量和语句。

8.诊断所有共用通道数据 check_channel.sh脚本，用于诊断所有共用通道数据：

#!/bin/bash# Define variables for connecting to the database
DB_USER="username"
DB_PASSWORD="password"
DB_NAME="database_name"# Define variables for sending email notifications
EMAIL_FROM="[e-mail]"
EMAIL_TO="[e-mail]"
EMAIL_SUBJECT="Alert: Shared channel issue detected!"# Connect to the database and execute SQL query
channel_count=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select count(*) from v\$shared_server where status='INACTIVE';
exit;
EOF
)# Check if any shared channels are inactive
if [ $channel_count -gt 0 ]
then# If shared channels are inactive, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Shared channel issue detected: $channel_count inactive channels" >> /var/log/check_channel.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi

此脚本连接到Oracle数据库并执行SQL查询，以检查所有共享通道的状态。如果任何共享通道处于“INACTIVE”状态，则会在日志文件中记录问题，并发送电子邮件通知给指定收件人。请注意，您需要根据自己的环境和要求修改脚本中的变量和语句。

9. 检查DG同步情况，check_dg.sh

#!/bin/bash# Define variables for connecting to the primary database
PRIMARY_DB_USER="primary_username"
PRIMARY_DB_PASSWORD="primary_password"
PRIMARY_DB_NAME="primary_database"# Define variables for connecting to the standby database
STANDBY_DB_USER="standby_username"
STANDBY_DB_PASSWORD="standby_password"
STANDBY_DB_NAME="standby_database"# Define variables for sending email notifications
EMAIL_FROM="[e-mail]"
EMAIL_TO="[e-mail]"
EMAIL_SUBJECT="Alert: Data Guard synchronization issue detected!"# Connect to the primary and standby databases and execute SQL queries
primary_seq=$(sqlplus -S ${PRIMARY_DB_USER}/${PRIMARY_DB_PASSWORD}@${PRIMARY_DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select max(sequence#) from v\$archived_log where applied='YES';
exit;
EOF
)standby_seq=$(sqlplus -S ${STANDBY_DB_USER}/${STANDBY_DB_PASSWORD}@${STANDBY_DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select max(sequence#) from v\$archived_log where applied='YES';
exit;
EOF
)# Check if primary and standby databases are out of sync
if [ $primary_seq -ne $standby_seq ]
then# If databases are out of sync, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Data Guard synchronization issue detected: primary sequence=$primary_seq, standby sequence=$standby_seq" >> /var/log/check_dg.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi

此脚本连接到主数据库和备库，并执行SQL查询以检查归档日志的序列号。如果主和备不同步，则会在日志文件中记录问题，并发送电子邮件通知给指定收件人。请注意，您需要根据自己的环境和要求修改脚本中的变量和语句。

10.监控OGG进程 check_ogg_proc.sh

#!/bin/bash# Define variables for the OGG process to monitor
OGG_PROCESS="my_ogg_process"# Define variables for sending email notifications
EMAIL_FROM="[e-mail]"
EMAIL_TO="[e-mail]"
EMAIL_SUBJECT="Alert: OGG process issue detected!"# Check if the OGG process is running
if ps ax | grep -v grep | grep $OGG_PROCESS > /dev/null
thenecho "$(date '+%Y-%m-%d %H:%M:%S') - OGG process is running." >> /var/log/check_ogg_proc.log
else# If the OGG process is not running, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - OGG process is not running." >> /var/log/check_ogg_proc.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi

此脚本通过检查进程列表来确定OGG进程是否正在运行。如果进程未运行，则会在日志文件中记录问题，并发送电子邮件通知给指定收件人。请注意，您需要根据自己的环境和要求修改脚本中的变量和语句。

11.监控结算报表执行 check_sett_report.sh

#!/bin/bash# Define variables for connecting to the database
DB_USER="username"
DB_PASSWORD="password"
DB_NAME="database_name"# Define variables for sending email notifications
EMAIL_FROM="[e-mail]"
EMAIL_TO="[e-mail]"
EMAIL_SUBJECT="Alert: Settlement report issue detected!"# Connect to the database and execute SQL query
report_count=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select count(*) from settlement_reports where status='processing';
exit;
EOF
)# Check if any settlement reports are still processing
if [ $report_count -gt 0 ]
then# If reports are still processing, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Settlement report issue detected: $report_count reports still processing" >> /var/log/check_sett_report.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi

12.监控最近两条汇率获取异常情况 check_unnormal_rate.sh

#!/bin/bash# Define variables for connecting to the database
DB_USER="username"
DB_PASSWORD="password"
DB_NAME="database_name"# Define variables for sending email notifications
EMAIL_FROM="[e-mail]"
EMAIL_TO="[e-mail]"
EMAIL_SUBJECT="Alert: Unusual exchange rate issue detected!"# Connect to the database and execute SQL query
rate_count=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select count(*) from exchange_rates where status='unusual' and rownum <= 2 order by date desc;
exit;
EOF
)# Check if there are any unusual exchange rates in the last two records
if [ $rate_count -gt 0 ]
then# If there are unusual exchange rates, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Unusual exchange rate issue detected: $rate_count unusual rates" >> /var/log/check_unnormal_rate.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi

13.auto_tbsp_invalid.sh 监控表空间使用情况，检查失效对象并自动重编译，监控风控时间，监控银行时间，检查数据库对象数量，检查归档空间。

#!/bin/bash# Define variables for connecting to the database
DB_USER="username"
DB_PASSWORD="password"
DB_NAME="database_name"# Define variables for sending email notifications
EMAIL_FROM="[e-mail]"
EMAIL_TO="[e-mail]"
EMAIL_SUBJECT="Alert: Oracle database issue detected!"# Define threshold values for tablespace usage (percentage)
TABLESPACE_THRESHOLD=85# Check tablespace usage and send an email notification if any tablespaces exceed the threshold
tablespace_count=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select count(*) from dba_data_files where round((bytes-free_space)/bytes*100) >= $TABLESPACE_THRESHOLD;
exit;
EOF
)if [ $tablespace_count -gt 0 ]
then# If any tablespaces exceed the threshold, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Tablespaces exceeding usage threshold detected." >> /var/log/auto_tbsp_invalid.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi# Check for invalid objects and recompile them automatically
invalid_count=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select count(*) from user_objects where status='INVALID';
exit;
EOF
)if [ $invalid_count -gt 0 ]
thensqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOFalter system disable restricted session;alter system set "_system_trig_enabled" = false scope=both;@$ORACLE_HOME/rdbms/admin/utlrp.sql;alter system reset "_system_trig_enabled" scope=both;alter system enable restricted session;exit;
EOFecho "$(date '+%Y-%m-%d %H:%M:%S') - Invalid objects detected and recompiled." >> /var/log/auto_tbsp_invalid.log
fi# Check if current time falls within defined risk window
current_time=$(date +%H%M)
risk_start_time=900
risk_end_time=1800if [ $current_time -ge $risk_start_time ] && [ $current_time -le $risk_end_time ]
thenecho "$(date '+%Y-%m-%d %H:%M:%S') - Current time is within the defined risk window." >> /var/log/auto_tbsp_invalid.log
else# If current time is outside of the defined risk window, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Current time is outside of the defined risk window." >> /var/log/auto_tbsp_invalid.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi# Check if current time falls within defined bank hours
bank_start_time=800
bank_end_time=1700if [ $current_time -ge $bank_start_time ] && [ $current_time -le $bank_end_time ]
thenecho "$(date '+%Y-%m-%d %H:%M:%S') - Current time is within bank hours." >> /var/log/auto_tbsp_invalid.log
else# If current time is outside of bank hours, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Current time is outside of bank hours." >> /var/log/auto_tbsp_invalid.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi# Check the number of database objects and send an email notification if it exceeds a specified limit
object_limit=100000object_count=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select count(*) from dba_objects;
exit;
EOF
)if [ $object_count -ge $object_limit ]
then# If the object count exceeds the limit, log the issue and send an email notificationecho "$(date '+%Y-%-%d %H:%M:%S') - Number of database objects exceeds limit: $object_count" >> /var/log/auto_tbsp_invalid.log
echo "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi# Check the available space in the archive log destination and send an email notification if it is running lowarchive_space=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select round((total_mb-free_mb)/total_mb*100) from v$recovery_file_dest;
exit;
EOF
)if [ $archive_space -ge $TABLESPACE_THRESHOLD ]
then
# If the archive log destination space is running low, log the issue and send an email notification
echo "$(date '+%Y-%m-%d %H:%M:%S') - Low space in archive log destination detected: $archive_space%" >> /var/log/auto_tbsp_invalid.log
echo "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi

此脚本监控数据库表空间使用情况，检查失效对象并自动重编译，监控风险时间和银行时间，检查数据库对象数量，以及检查归档空间。如果任何问题超过阈值，则在日志文件中记录问题，并发送电子邮件通知给指定收件人。请注意，您需要根据自己的环境和要求修改脚本中的变量和语句。

14.监控数据库错误日志 check_hkoral_err.sh

#!/bin/bash# Define variables for accessing the alert log
ORACLE_SID="sid"
ALERT_LOG="/u01/app/oracle/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log"# Define variables for sending email notifications
EMAIL_FROM="[e-mail]"
EMAIL_TO="[e-mail]"
EMAIL_SUBJECT="Alert: Oracle database error detected!"# Check the alert log for ORA- errors
error_count=$(grep -ic "ORA-" $ALERT_LOG)if [ $error_count -gt 0 ]
then# If any errors are found, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Oracle database error detected: $error_count errors" >> /var/log/check_hkoral_err.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi

此脚本检查Oracle数据库的警报日志，以查找任何ORA-错误。如果发现错误，则会在日志文件中记录问题，并发送电子邮件通知给指定收件人。请注意，您需要根据自己的环境和要求修改脚本中的变量和语句.

15.监控汇率波动情况 check_rate.sh

#!/bin/bash# Define variables for connecting to the database
DB_USER="username"
DB_PASSWORD="password"
DB_NAME="database_name"# Define variables for sending email notifications
EMAIL_FROM="[e-mail]"
EMAIL_TO="[e-mail]"
EMAIL_SUBJECT="Alert: Exchange rate fluctuation detected!"# Define threshold values for exchange rate fluctuations (percentage)
RATE_THRESHOLD=5# Connect to the database and execute SQL query
rate_fluct_count=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select count(*) from exchange_rates where abs((rate-prev_rate)/prev_rate*100) >= $RATE_THRESHOLD;
exit;
EOF
)# Check if any exchange rates have fluctuated beyond the threshold
if [ $rate_fluct_count -gt 0 ]
then# If exchange rates have fluctuated beyond the threshold, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Exchange rate fluctuation detected: $rate_fluct_count rates" >> /var/log/check_rate.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi

此脚本连接到Oracle数据库并执行SQL查询，以检查是否有超过阈值的汇率波动。如果发现波动，则会在日志文件中记录问题，并发送电子邮件通知给指定收件人。请注意，您需要根据自己的环境和要求修改脚本中的变量和语句。 

16.监控异常银行订单 check_trade.sh

#!/bin/bash# Define variables for connecting to the database and accessing the alert log
DB_USER="username"
DB_PASSWORD="password"
DB_NAME="database_name"
ORACLE_SID="sid"
ALERT_LOG="/u01/app/oracle/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log"# Define variables for sending email notifications
EMAIL_FROM="[e-mail]"
EMAIL_TO="[e-mail]"
EMAIL_SUBJECT="Alert: Abnormal bank trades detected!"# Check for abnormal bank trades in the database
trade_count=$(sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
set feedback off;
set pagesize 0;
select count(*) from bank_trades where status='abnormal' and rownum <= 10 order by trade_date desc;
exit;
EOF
)if [ $trade_count -gt 0 ]
then# If abnormal bank trades are found, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Abnormal bank trades detected: $trade_count trades" >> /var/log/check_trade.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi# Check the alert log for any errors related to bank trades
error_count=$(grep -ic "bank trade error" $ALERT_LOG)if [ $error_count -gt 0 ]
then# If errors related to bank trades are found, log the issue and send an email notificationecho "$(date '+%Y-%m-%d %H:%M:%S') - Bank trade errors detected: $error_count errors" >> /var/log/check_trade.logecho "Please investigate and take appropriate action immediately." | mail -s "$EMAIL_SUBJECT" -r "$EMAIL_FROM" "$EMAIL_TO"
fi

此脚本检查数据库中是否有异常银行订单，并检查警报日志中是否有与银行订单相关的错误。如果发现问题，则会在日志文件中记录问题，并发送电子邮件通知给指定收件人。请注意，您需要根据自己的环境和要求修改脚本中的变量和语句。

17.删除14天之前的归档日志 del_archive.sh

#!/bin/bash# Define variables for accessing the archive log destination and setting retention period (days)
ARCHIVE_DEST="/u01/app/oracle/fast_recovery_area/${ORACLE_SID}/archivelog"
RETENTION_DAYS=14# Calculate the date for retention
RETENTION_DATE=$(date +%Y%m%d --date="${RETENTION_DAYS} days ago")# Delete archive logs older than the retention date
find $ARCHIVE_DEST -name "*.arc" -o -name "*.log" | grep -E ".*[0-9]{8}.*" | while read FILE
doFILE_DATE=$(echo $FILE | grep -oE "[0-9]{8}")if [ $FILE_DATE -lt $RETENTION_DATE ]thenrm $FILEecho "$(date '+%Y-%m-%d %H:%M:%S') - Archive log deleted: $FILE" >> /var/log/del_archive.logfi
done

此脚本遍历归档日志目录以查找14天之前的归档日志，并将其删除。请注意，您需要根据自己的环境和要求修改脚本中的变量和语句。在使用此脚本之前，请确保理解脚本的作用并进行适当的测试。