Oracle CDB自动处理表空间不足脚本

news/2025/2/8 14:12:52 标签: 数据库, oracle

之前我曾经发过一个自动处理表空间的脚本，可以通过定时任务自动处理表空间不足的问题；但是之前那个脚本没有涵盖CDB模式下的PDB，这里将脚本做了一下更新，可以处理CDB模式下多PDB的表空间问题。

传统模式的脚本请参考这个链接

Oracle自动处理表空间不足脚本 - 墨天轮

表空间问题引起的问题请看如下

Oracle 11G还有新BUG？ORACLE 表空间迷案！ - 墨天轮

脚本如下

#!/bin/bash# name: check_ts_usage.sh# purpose: Check tablespace usage in CDB and auto-extend if needed, then send email notification if data files are added.# updated by 潇湘秦#  /home/oracle/.bash_profil设置 ORACLE_S ORACLE_HOMEsource /home/oracle/.bash_profileHOST_NAME=$(uname -n)export HOST_NAMEJOB_PWD=$HOME/jobsexport JOB_PWDEMAIL_RECIPIENTS=xxxxxx@mail.com  # 设置告警邮件mailDATA_FILE_ADDED=false  #设置是否加数据文件的flag# 确认 ORACLE_SID 和 ORACLE_HOME 是否已设置if [[ -z "$ORACLE_SID" || -z "$ORACLE_HOME" ]]; then    echo "ORACLE_SID 或 ORACLE_HOME 未设置，请检查 /home/oracle/.bash_profile 文件。"    exit 1fi# 临时文件用于存放邮件内容EMAIL_CONTENT_FILE="/tmp/tablespace_alert_email.txt"#clear history info>$EMAIL_CONTENT_FILEecho "Notice: Tablespace auto-extension triggered on $HOST_NAME" > "$EMAIL_CONTENT_FILE"# Function to check tablespace usage and add datafiles if neededcheck_and_add_datafiles() {
  
      local PDB_NAME=$1    echo "Checking tablespace usage in PDB: $PDB_NAME" >> "$EMAIL_CONTENT_FILE"    CHECK_TS_USAGE=$($ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF        SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF        ALTER SESSION SET CONTAINER=$PDB_NAME;        SELECT COUNT(*)        FROM (SELECT t.tablespace_name              FROM (SELECT a.tablespace_name,                           a.unalloc_size,                           NVL(f.free_size, 0) free_size,                           a.used_size - NVL(f.free_size, 0) used_size,                           ROUND((a.used_size - NVL(f.free_size, 0)) / (a.unalloc_size + a.used_size), 2) capacity                      FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1024 / 1024) free_size                              FROM dba_free_space                             GROUP BY tablespace_name) f,                           (SELECT tablespace_name,                                   ROUND(SUM(user_bytes) / 1024 / 1024) used_size,                                   ROUND(SUM(DECODE(autoextensible, 'YES', DECODE(SIGN(maxbytes - user_bytes), -1, 0, maxbytes - user_bytes), 0)) / 1024 / 1024) unalloc_size                              FROM dba_data_files                             GROUP BY tablespace_name) a                     WHERE a.tablespace_name = f.tablespace_name(+)) t             WHERE capacity >= 0.80  # 这里的阈值按自己的需求设定                      AND (unalloc_size + free_size) < 10000                      AND (unalloc_size + free_size) < used_size / 2        );EOF    )    if [[ $CHECK_TS_USAGE -gt 0 ]]; then        echo "Threshold exceeded in PDB $PDB_NAME: initiating data file addition." >> "$EMAIL_CONTENT_FILE"        DATA_FILE_ADDED=true         $ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF >> "$EMAIL_CONTENT_FILE"            SET SERVEROUTPUT ON;            SET LINESIZE 1000;            SET PAGESIZE 0            ALTER SESSION SET CONTAINER=$PDB_NAME;            DECLARE                ts_name VARCHAR2(50);                datafile_path VARCHAR2(500);                is_omf  NUMBER;            BEGIN                -- Loop through tablespaces with low space                FOR ts IN (                    SELECT t.tablespace_name                    FROM (SELECT a.tablespace_name,                                 a.unalloc_size,                                 NVL(f.free_size, 0) free_size,                                 a.used_size - NVL(f.free_size, 0) used_size,                                 ROUND((a.used_size - NVL(f.free_size, 0)) / (a.unalloc_size + a.used_size), 2) capacity                            FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1024 / 1024) free_size                                    FROM dba_free_space                                   GROUP BY tablespace_name) f,                                 (SELECT tablespace_name,                                         ROUND(SUM(user_bytes) / 1024 / 1024) used_size,                                         ROUND(SUM(DECODE(autoextensible, 'YES', DECODE(SIGN(maxbytes - user_bytes), -1, 0, maxbytes - user_bytes), 0)) / 1024 / 1024) unalloc_size                                    FROM dba_data_files                                   GROUP BY tablespace_name) a                           WHERE a.tablespace_name = f.tablespace_name(+)) t                   WHERE capacity >= 0.80                           AND (unalloc_size + free_size) < 10000                           AND (unalloc_size + free_size) < used_size / 2                ) LOOP                    ts_name := ts.tablespace_name;                    -- Find the path of an existing datafile for this tablespace                    SELECT SUBSTR(file_name, 1, INSTR(file_name, '/', -1))                     INTO datafile_path                    FROM dba_data_files                    WHERE tablespace_name = ts_name                    AND ROWNUM = 1;                    -- Check if OMF is enabled                    SELECT COUNT(*) INTO is_omf                     FROM v\$parameter WHERE name = 'db_create_file_dest' AND value IS NOT NULL;                    IF is_omf > 0 THEN                        -- Add datafile with OMF                        EXECUTE IMMEDIATE 'ALTER TABLESPACE ' || ts_name || ' ADD DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30G';                        DBMS_OUTPUT.PUT_LINE('OMF Enabled: Added datafile for tablespace ' || ts_name || ' in PDB ' || '$PDB_NAME');                    ELSE                        -- Add datafile with custom path based on existing path                        EXECUTE IMMEDIATE 'ALTER TABLESPACE ' || ts_name ||                                           ' ADD DATAFILE ''' || datafile_path || ts_name || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '.dbf'' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30G';                         DBMS_OUTPUT.PUT_LINE('Non-OMF: Added datafile ' || datafile_path || ts_name || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '.dbf for tablespace ' || ts_name || ' in PDB ' || '$PDB_NAME');                    END IF;                END LOOP;            END;            /EOF    else        echo "No tablespaces are exceeding the threshold in PDB $PDB_NAME; no action required." >> "$EMAIL_CONTENT_FILE"    fi}# Get list of PDBs in the CDBPDB_LIST=$($ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF    SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF    SELECT name FROM v\$pdbs WHERE open_mode = 'READ WRITE';EOF)# Check and add datafiles for each PDBfor PDB in $PDB_LIST; do    check_and_add_datafiles $PDBdone# Send email notification with the contents of EMAIL_CONTENT_FILEif $DATA_FILE_ADDED; then    mailx -s "Notice: Tablespace Auto-Extension on $HOST_NAME"  "$EMAIL_RECIPIENTS" < "$EMAIL_CONTENT_FILE"fi# Clean up temporary filerm -f "$EMAIL_CONTENT_FILE"

比如有多个PDB可以一个脚本搞定，设置定时任务就可以自动处理表空间问题，无需人工介入。

[oracle@ssycmes01 jobs]$ cat /tmp/tablespace_alert_email.txtNotice: Tablespace auto-extension triggered on ssycmes01Checking tablespace usage in PDB: MESNo tablespaces are exceeding the threshold in PDB MES; no action required.Checking tablespace usage in PDB: QMSNo tablespaces are exceeding the threshold in PDB QMS; no action required.Checking tablespace usage in PDB: EAMNo tablespaces are exceeding the threshold in PDB EAM; no action required.Checking tablespace usage in PDB: SPCNo tablespaces are exceeding the threshold in PDB SPC; no action required.Checking tablespace usage in PDB: REPORTNo tablespaces are exceeding the threshold in PDB REPORT; no action required.

如果达到阈值添加了数据文件则发送邮件告警


http://www.niftyadmin.cn/n/5844943.html

相关文章

自定义v-model修饰符

自定义v-model修饰符

文章目录 引言I Vue3.4 的用法解构 defineModel() 的返回值,在子组件中访问添加v-model修饰符基于修饰符选择性地调节值的读取和写入方式带参数的 v-model 修饰符II Vue3.4 之前的用法原理带参数的 v-model 修饰符例子引言 v-model 有一些内置的修饰符，例如 .trim，.number 和…
阅读更多...
[ESP32:Vscode+PlatformIO]添加第三方库 开源库 与Arduino导入第三方库的区别

[ESP32:Vscode+PlatformIO]添加第三方库 开源库 与Arduino导入第三方库的区别

前言 PlatformIO与Arduino在添加第三方库方面的原理存在显著差异 在PlatformIO中&#xff0c;第三方库的使用是基于项目&#xff08;工程&#xff09;的。具体而言&#xff0c;只有当你为一个特定的项目添加了某个第三方库后&#xff0c;该项目才能使用该库。这些第三方库的文…
阅读更多...
虚基类和虚继承

虚基类和虚继承

抽象类(有纯虚函数的类) / 虚基类 virtual&#xff1a; 1.修饰成员方法是虚函数 2.可以修饰继承方式&#xff0c;是虚继承。被虚继承的类&#xff0c;称作虚基类。 vfptr/vbptr vftable/vbtable 在Windows vs下会存在内存释放错误的问题&#xff0c;Linux g下自动修正。 命令…
阅读更多...
[MoeCTF 2022]baby_file

[MoeCTF 2022]baby_file

题目 <html> <title>Heres a secret. Can you find it?</title> <?phpif(isset($_GET[file])){$file $_GET[file];include($file); }else{highlight_file(__FILE__); } ?> </html> 读取flag /?filephp://filter/readconvert.base64-encode…
阅读更多...
对接DeepSeek

对接DeepSeek

其实&#xff0c;整个对接过程很简单&#xff0c;就四步&#xff0c;获取key&#xff0c;找到接口文档&#xff0c;接口测试&#xff0c;代码对接。 获取 KEY https://platform.deepseek.com/transactions 直接付款就是了&#xff08;现在官网暂停充值2025年2月7日&#xff0…
阅读更多...
Leetcod59：螺旋矩阵 II

Leetcod59：螺旋矩阵 II

题目描述&#xff1a; 给你一个正整数 n &#xff0c;生成一个包含 1 到 n2 所有元素&#xff0c;且元素按顺时针顺序螺旋排列的 n x n 正方形矩阵 matrix 。 代码思路&#xff1a; 初始化矩阵&#xff1a; 创建一个 n x n 的矩阵 res&#xff0c;初始时所有元素都设为 None…
阅读更多...
Android studio 编译速度增加

Android studio 编译速度增加

在gradle.properties增加如下代码&#xff0c;作用看注释 # Gradle 和 Kotlin 支持增量编译&#xff0c;只编译有改动的部分 kotlin.incrementaltrue # Gradle 支持并行构建&#xff0c;充分利用多核 CPU org.gradle.paralleltrue # Gradle 构建缓存可以缓存任务输出&#xff…
阅读更多...
Transformer 的辉煌与大模型方向确立，点燃AGI之火把

Transformer 的辉煌与大模型方向确立，点燃AGI之火把

在人工智能的迅猛发展进程中&#xff0c;大模型已成为推动各领域变革的核心力量&#xff0c;而模型参数规模则是衡量其能力和技术水平的关键指标。从早期的基础模型到如今参数规模令人惊叹的超大型模型&#xff0c;这一发展历程见证了人工智能技术的飞速进步。本文将以 GPT 系列…
阅读更多...
最新文章

Copyright @ 2022~2023