醋醋百科网

作为DBA，你是否每天被困在备份监控、空间告警、性能救火的循环中？本文分享3个生产级Oracle自动化脚本，专治重复性工作，效率提升90%！

一、表空间管家

以下是一个针对生产环境设计的Oracle ASM磁盘空间监控及自动添加数据文件的脚本方案，结合了ASM空间监控、表空间扩展逻辑及生产环境安全规范，适用于Oracle 11g/12c/19c等版本。

通过crontab设置每小时执行一次，避免频繁检查：
0 */1 * * * /opt/scripts/asm_auto_add_datafile.sh

#!/bin/bash
# 脚本名称: asm_auto_add_datafile.sh
# 功能: 监控ASM磁盘组空间并自动扩展表空间数据文件
# 适用环境: Oracle 11g/12c/19c, Linux/Unix


# 配置参数
ORACLE_SID=orcl
THRESHOLD_PCT=10    # ASM磁盘组剩余空间告警阈值（%）
DATAFILE_SIZE=20G    # 新增数据文件大小
LOG_FILE=/var/log/oracle/asm_auto_add.log


# 获取ASM磁盘组空间信息
get_asm_diskgroup_info() {
  sqlplus -S / as sysdba <<EOF
  SET FEEDBACK OFF HEADING OFF LINESIZE 200
  SELECT name||'|'||ROUND((free_mb/total_mb)*100,2) 
  FROM v\$asm_diskgroup
  WHERE ROUND((free_mb/total_mb)*100,2) < $THRESHOLD_PCT;
  EXIT;
EOF
}


# 获取需要扩展的表空间列表
get_tablespace_list() {
local dg_name=$1
  sqlplus -S / as sysdba <<EOF
  SET FEEDBACK OFF HEADING OFF LINESIZE 200
  SELECT a.tablespace_name 
  FROM (
    SELECT tablespace_name, SUM(bytes)/1024/1024/1024 used_gb
    FROM dba_segments 
    GROUP BY tablespace_name
  ) a, dba_tablespaces b 
  WHERE a.tablespace_name = b.tablespace_name 
  AND b.contents = 'PERMANENT'
  AND a.used_gb / (SELECT SUM(bytes)/1024/1024/1024 
                   FROM dba_data_files 
                   WHERE tablespace_name = a.tablespace_name) > 0.9
  AND EXISTS (
    SELECT 1 
    FROM dba_data_files 
    WHERE tablespace_name = a.tablespace_name 
    AND file_name LIKE '+${dg_name}%'
  ) AND ROWNUM <= 2;  # 限制单次处理表空间数量
  EXIT;
EOF
}


# 主逻辑
main() {
echo"$(date '+%Y-%m-%d %H:%M:%S') - 开始执行ASM空间监控..." >> $LOG_FILE


# 遍历空间不足的ASM磁盘组
  get_asm_diskgroup_info | while IFS='|'read dg_name free_pct; do
    echo"$(date) - 检测到磁盘组 ${dg_name} 剩余空间不足: ${free_pct}%" >> $LOG_FILE
    
    # 获取需扩展的表空间
    get_tablespace_list "$dg_name" | whileread ts_name; do
      datafile_path="+${dg_name}"
      
      # 执行数据文件添加
      sqlplus -S / as sysdba <<EOF >> $LOG_FILE 2>&1
      WHENEVER SQLERROR EXIT SQL.SQLCODE
      ALTER TABLESPACE ${ts_name} ADD DATAFILE '${datafile_path}'
        SIZE ${DATAFILE_SIZE} AUTOEXTEND ON NEXT 1G MAXSIZE 32G;
      EXIT;
EOF
      


      # 错误处理
      if [ $? -eq 0 ]; then
        echo"$(date) - 成功添加数据文件: ${datafile_path} 到表空间 ${ts_name}" >> $LOG_FILE
      else
        echo"$(date) - 错误: 添加数据文件到 ${ts_name} 失败! 检查ORA错误码." >> $LOG_FILE
      fi
    done
done
echo"$(date) - 监控执行完成." >> $LOG_FILE
}


# 执行主函数
main

二、自动生成AWR报告

每天生成关键数据库的AWR报告，自动分析TOP SQL和性能瓶颈，提前发现慢查询，避免业务高峰期的性能雪崩，脚本具体跳转链接查看。

告别人肉运维，AWR/ASH定时自动生成

三、自动删除分区

相信很多小伙伴因为删除数据而烦恼，有其一些历史数据长时间存在数据库，存储空间岌岌可危，那么下面这个定时删除，一定会给你带来帮助，其实就是一个存储过程，挂一个定时任务就行。

--存储过程脚本
GRANT SELECT ON dba_tab_partitions TO SYSTEM;


CREATE OR REPLACE FUNCTION high_value_to_date(
  p_table_name      VARCHAR2,
  p_partition_name  VARCHAR2
) RETURN DATE IS
  v_high_value LONG;
  v_date       DATE;
BEGIN
  SELECT high_value
  INTO v_high_value
  FROM dba_tab_partitions
  WHERE table_name = p_table_name
    AND partition_name = p_partition_name;


  EXECUTE IMMEDIATE 'BEGIN :1 := ' || v_high_value || '; END;'
    USING OUT v_date;


  RETURN v_date;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;


exec DROP_OLD_PARTITIONS('SYSTEM');


SELECT * FROM DBA_ERRORS  WHERE name='PURGE_OLD_PARTITIONS'


CREATE OR REPLACE PROCEDURE drop_old_partitions(p_owner IN VARCHAR2) IS
    v_cutoff_date          DATE;
    v_partition_high_value DATE;
    v_sql                  VARCHAR2(4000);
BEGIN
    -- 计算截止日期（保留数据的起始时间点）
    v_cutoff_date := TRUNC(SYSDATE) - 1;
    -- 遍历目标表所有分区
    FOR part_rec IN (
       SELECT 
         table_owner,
         table_name,
         partition_name,
         high_value
       FROM 
         dba_tab_partitions
       WHERE
         TABLE_OWNER= upper(p_owner)
         AND interval = 'YES'
         AND high_value IS NOT NULL
    ) LOOP
        BEGIN
            -- 动态执行HIGH_VALUE转换
            EXECUTE IMMEDIATE 'BEGIN :1 := ' || part_rec.high_value || '; END;'
                USING OUT v_partition_high_value;


            DBMS_OUTPUT.PUT_LINE('Partition: ' || part_rec.partition_name 
                || ', High Value: ' || TO_CHAR(v_partition_high_value, 'YYYY-MM-DD'));


            -- 检查分区是否需要删除
            IF v_partition_high_value < v_cutoff_date THEN
                v_sql := 'ALTER TABLE ' || part_rec.table_owner || '.' || part_rec.table_name 
                       || ' DROP PARTITION ' || part_rec.partition_name;
                DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
                EXECUTE IMMEDIATE v_sql;
                
                DBMS_OUTPUT.PUT_LINE('Successfully dropped partition: ' || part_rec.partition_name);
            END IF;


        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error processing partition ' || part_rec.table_owner || '.' || part_rec.table_name
                    || ': ' || SQLERRM);
        END;
    END LOOP;


    DBMS_OUTPUT.PUT_LINE('Partition cleanup completed.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Critical error: ' || SQLERRM);
        RAISE;
END drop_old_partitions;
/




BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'auto_drop_old_partitions',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN drop_old_partitions(''SYSTEM''); END;', 
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2;',
        enabled         => TRUE,
        comments        => 'Automatically drop old interval partitions'
    );
END;
/




-- 查询所有定时任务（当前用户）
SELECT job_name, enabled, state, last_start_date, next_run_date, repeat_interval
FROM dba_scheduler_jobs WHERE JOB_NAME='AUTO_DROP_OLD_PARTITIONS'


-- 查询指定任务详情
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'AUTO_DROP_OLD_PARTITIONS';


-- 查看任务失败详情
SELECT job_name, actual_start_date, run_duration, error#, additional_info
FROM dba_scheduler_job_run_details
WHERE job_name = 'AUTO_DROP_OLD_PARTITIONS'
AND status = 'FAILED';

四、总结

自动化不是取代DBA，而是将精力从重复劳动转移到架构优化，立即部署这些脚本，告别996！

作者丨詹姆斯邦德007

来源丨公众号：IT邦德（ID：jeamesDB）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn