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 ]]; then
    echo "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 ]]; then
    echo "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 ]]; then
    echo "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 ]]; then
    echo "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" ]]; then
    echo "Reconciliation has not been completed."
else
    echo "Reconciliation has been completed."
fi

# Monitor unreconciled transactions
UNRECONCILED_COUNT=$(find $WORK_DIR -name "*_${CHECK_DATE}.txt" | wc -l)
if [[ "$UNRECONCILED_COUNT" -gt 0 ]]; then
    echo "There are $UNRECONCILED_COUNT unreconciled transactions."
fi

该脚本假定CCPS系统日志文件名为ccps_<DATE>.log,并且所有待处理的交易文件都保存在工作目录中。您需要根据实际情况更新变量LOG_DIRWORK_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" ]]; then
    echo "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" ]]; then
    echo "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" ]]; then
    echo "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" ]]; then
    echo "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" ]]; then
    echo "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 ]]; then
    echo "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 ]]; then
    echo "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" ]]; then
    echo "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="sender@example.com"
EMAIL_TO="recipient@example.com"
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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Abnormal refunds detected: $refund_count refunds" >> /var/log/check_fund.log
    echo "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="sender@example.com"
EMAIL_TO="recipient@example.com"
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 notification
    echo "$(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.log
    echo "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 ]
then
    sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
    alter 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;
EOF
    echo "$(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="sender@example.com"
EMAIL_TO="recipient@example.com"
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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Shared channel issue detected: $channel_count inactive channels" >> /var/log/check_channel.log
    echo "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="sender@example.com"
EMAIL_TO="recipient@example.com"
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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Data Guard synchronization issue detected: primary sequence=$primary_seq, standby sequence=$standby_seq" >> /var/log/check_dg.log
    echo "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="sender@example.com"
EMAIL_TO="recipient@example.com"
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
then
    echo "$(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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - OGG process is not running." >> /var/log/check_ogg_proc.log
    echo "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="sender@example.com"
EMAIL_TO="recipient@example.com"
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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Settlement report issue detected: $report_count reports still processing" >> /var/log/check_sett_report.log
    echo "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="sender@example.com"
EMAIL_TO="recipient@example.com"
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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Unusual exchange rate issue detected: $rate_count unusual rates" >> /var/log/check_unnormal_rate.log
    echo "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="sender@example.com"
EMAIL_TO="recipient@example.com"
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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Tablespaces exceeding usage threshold detected." >> /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 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 ]
then
    sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_NAME} <<EOF
    alter 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;
EOF
    echo "$(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=1800

if [ $current_time -ge $risk_start_time ] && [ $current_time -le $risk_end_time ]
then
    echo "$(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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Current time is outside of the defined risk window." >> /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 if current time falls within defined bank hours
bank_start_time=800
bank_end_time=1700

if [ $current_time -ge $bank_start_time ] && [ $current_time -le $bank_end_time ]
then
    echo "$(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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Current time is outside of bank hours." >> /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 number of database objects and send an email notification if it exceeds a specified limit
object_limit=100000

object_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 notification
    echo "$(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 low

archive_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="sender@example.com"
EMAIL_TO="recipient@example.com"
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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Oracle database error detected: $error_count errors" >> /var/log/check_hkoral_err.log
    echo "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="sender@example.com"
EMAIL_TO="recipient@example.com"
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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Exchange rate fluctuation detected: $rate_fluct_count rates" >> /var/log/check_rate.log
    echo "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="sender@example.com"
EMAIL_TO="recipient@example.com"
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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Abnormal bank trades detected: $trade_count trades" >> /var/log/check_trade.log
    echo "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 notification
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Bank trade errors detected: $error_count errors" >> /var/log/check_trade.log
    echo "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
do
    FILE_DATE=$(echo $FILE | grep -oE "[0-9]{8}")
    if [ $FILE_DATE -lt $RETENTION_DATE ]
    then
        rm $FILE
        echo "$(date '+%Y-%m-%d %H:%M:%S') - Archive log deleted: $FILE" >> /var/log/del_archive.log
    fi
done

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

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.mfbz.cn/a/1171.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

蓝桥杯嵌入式第四课--定时器

前言蓝桥杯对于定时器这部分的考察主要集中在定时器中断、PWM输出以及输入捕获三个方面&#xff0c;本节课着眼于应用&#xff0c;介绍一下定时器的使用。定时器中断一、基础概念对没接触过定时器的新手来说&#xff0c;如果想要快速上手定时器的使用&#xff0c;首先要先对定时…

Python每日一练(20230318)

目录 1. 排序链表 ★★ 2. 最长连续序列 ★★ 3. 扰乱字符串 ★★★ &#x1f31f; 每日一练刷题专栏 &#x1f31f; Golang每日一练 专栏 Python每日一练 专栏 C/C每日一练 专栏 Java每日一练 专栏 1. 排序链表 给你链表的头结点 head &#xff0c;请将其按 升序 …

卷积神经网络CNN识别MNIST数据集

这次我们将建立一个卷积神经网络&#xff0c;它可以把MNIST手写字符的识别准确率提升到99%&#xff0c;读者可能需要一些卷积神经网络的基础知识才能更好的理解本节的内容。 程序的开头是导入TensorFlow&#xff1a; import tensorflow as tf from tensorflow.examples.tutor…

C语言老题新解16-20 用命令行打印一些图案

文章目录11 打印字母C12 输出国际象棋棋盘。13 打印楼梯&#xff0c;同时在楼梯上方打印两个笑脸。14 输出9*9 口诀。15 有一道题要输出一个图形&#xff0c;然后Very Beautiful。11 打印字母C 11 用*号输出字母C的图案。 讲道理这绝对不该是个新人能整出来的活儿&#xff0c…

TCP/IP协议栈之数据包如何穿越各层协议(绝对干货)

所有互联网服务&#xff0c;均依赖于TCP/IP协议栈。懂得数据是如何在协议栈传输的&#xff0c;将会帮助你提升互联网程序的性能和解决TCP相关问题的能力。 我们讲述在Linux场景下数据包是如何在协议层传输的。 1、发送数据 应用层发送数据的过程大致如下&#xff1a; 我们把…

蓝桥杯嵌入式第五课--输入捕获

前言输入捕获的考题十分明确&#xff0c;就是测量输入脉冲波形的占空比和频率&#xff0c;对我们的板子而言&#xff0c;就是检测板载的两个信号发生器产生的信号&#xff1a;具体来说就是使用PA15和PB4来做输入捕获。输入捕获原理简介输入捕获能够对输入信号的上升沿和下降沿进…

WorkTool企微机器人接入智能问答

一、前言 最新版的企微机器人已经集成 Chat &#xff0c;无需开发可快速搭建智能对话机器人。 从官方介绍看目前集成版本使用模型为 3.5-turbo。 二、入门 创建 WorkTool 机器人 你可以通过这篇快速入门教程&#xff0c;来快速配置一个自己的企微机器人。 实现的流程如图&…

Windows与Linux端口占用、查看的方法总结

Windows与Linux端口占用、查看的方法总结 文章目录Windows与Linux端口占用、查看的方法总结一、Windows1.1Windows查看所有的端口1.2查询指定的端口占用1.3查询PID对应的进程1.4查杀死/结束/终止进程二、Linux2.1lsof命令2.2netstat命令一、Windows 1.1Windows查看所有的端口 …

基于GPT-4的免费代码生成工具

大家好,我是herosunly。985院校硕士毕业,现担任算法研究员一职,热衷于机器学习算法研究与应用。曾获得阿里云天池比赛第一名,CCF比赛第二名,科大讯飞比赛第三名。拥有多项发明专利。对机器学习和深度学习拥有自己独到的见解。曾经辅导过若干个非计算机专业的学生进入到算法…

SpringCloud五大核心组件

Consul 等&#xff0c;提供了搭建分布式系统及微服务常用的工具&#xff0c;如配置管理、服务发现、断路器、智能路由、微代理、控制总线、一次性token、全局锁、选主、分布式会话和集群状态等&#xff0c;满足了构建微服务所需的所有解决方案。 服务发现——Netflix Eureka …

7个最受欢迎的Python库,大大提高开发效率

当第三方库可以帮我们完成需求时&#xff0c;就不要重复造轮子了 整理了GitHub上7个最受好评的Python库&#xff0c;将在你的开发之旅中提供帮助 PySnooper 很多时候时间都花在了Debug上&#xff0c;大多数人呢会在出错位置的附近使用print&#xff0c;打印某些变量的值 这个…

算法竞赛必考算法——动态规划(01背包和完全背包)

动态规划(一) 目录动态规划(一)1.01背包问题1.1题目介绍1.2思路一介绍(二维数组)1.3思路二介绍(一维数组) 空间优化1.4思路三介绍(输入数据优化)2.完全背包问题2.1题目描述&#xff1a;2.2思路一(朴素算法)2.3思路二(将k优化处理掉)2.4思路三(优化j的初始条件)总结1.01背包问题…

Spring Cloud Alibaba全家桶(四)——微服务调用组件Feign

前言 本文小新为大家带来 微服务调用组件Feign 的相关知识&#xff0c;具体内容包含什么是Feign&#xff0c;Spring Cloud Alibaba快速整合OpenFeign&#xff0c;Spring Cloud Feign的自定义配置及使用&#xff08;包括&#xff1a;日志配置、契约配置、自定义拦截器实现认证逻…

Autosar-ComM浅谈

文章目录 一、ComM概述二、和其他模块的依赖关系三、ComM通道状态机ComM模式与通讯能力关系表四、ComM中的PNC一、ComM概述 ComM全称是Communication Manager,顾名思义就是通信的管理,是BSW(基本软件)服务层的一个组件。 ComM的作用: 为用户简化Communication Stack的使用…

中断控制器

在Linux内核中&#xff0c;各个设备驱动可以简单地调用request_irq&#xff08;&#xff09;、enable_irq&#xff08;&#xff09;、disable_irq&#xff08;&#xff09;、 local_irq_disable&#xff08;&#xff09;、local_irq_enable&#xff08;&#xff09;等通用API来…

STM32----MPU6050

前言&#xff1a;最近几个月没有写文章了&#xff0c;因为这学期的事情真的有点多&#xff0c;但是想了想&#xff0c;文章还是要更新&#xff0c;总结自己学习的知识&#xff0c;真的很重要&#xff01;&#xff01;&#xff01; 废话不多说&#xff0c;正文开始&#xff1a;…

【vue.js】在网页中实现一个金属抛光质感的按钮

文章目录前言效果电脑效果手机效果说明完整代码index.html前言 诶&#xff1f;这有一个按钮(&#xff5e;&#xffe3;▽&#xffe3;)&#xff5e;&#xff0c;这是一个在html中实现的具有金属质感并且能镜面反射的按钮~ 效果 电脑效果 手机效果 说明 主要思路是使用 navig…

【算法基础】二分图(染色法 匈牙利算法)

一、二分图 1. 染色法 一个图是二分图,当且仅当,图中不含奇数环。在判别一个图是否为二分图⑩,其实相当于染色问题,每条边的两个点必须是不同的颜色,一共有两种颜色,如果染色过程中出现矛盾,则说明不是二分图。 for i = 1 to n:if i 未染色DFS(i, 1); //将i号点染色未…

Leetcode138. 复制带随机指针的链表

复制带随机指针的链表 第一步 拷贝节点链接在原节点的后面 第二步拷贝原节点的random &#xff0c; 拷贝节点的 random 在原节点 random 的 next 第三步 将拷贝的节点尾插到一个新链表 ,并且将原链表恢复 从前往后遍历链表 ,将原链表的每个节点进行复制&#xff0c;并l链接到原…

【STL二】STL序列式容器(array、vector、deque、list、forward_list)

【STL二】STL序列式容器&#xff08;array、vector、deque、list、forward_list&#xff09;1.array<T,N>&#xff08;数组容器&#xff09;2.vector<T>&#xff08;向量容器&#xff09;3.deque<T>&#xff08;双端队列容器&#xff09;&#xff1a;4.list&…
最新文章