Oracle CDB自动处理表空间不足脚本
之前我曾经发过一个自动处理表空间的脚本，可以通过定时任务自动处理表空间不足的问题；但是之前那个脚本没有涵盖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" ]]; thenecho "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=$1echo "Checking tablespace usage in PDB: $PDB_NAME" >> "$EMAIL_CONTENT_FILE"CHECK_TS_USAGE=$($ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOFSET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFFALTER SESSION SET CONTAINER=$PDB_NAME;SELECT COUNT(*)FROM (SELECT t.tablespace_nameFROM (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) capacityFROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1024 / 1024) free_sizeFROM dba_free_spaceGROUP 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_sizeFROM dba_data_filesGROUP BY tablespace_name) aWHERE a.tablespace_name = f.tablespace_name(+)) tWHERE capacity >= 0.80 # 这里的阈值按自己的需求设定AND (unalloc_size + free_size) < 10000AND (unalloc_size + free_size) < used_size / 2);EOF)if [[ $CHECK_TS_USAGE -gt 0 ]]; thenecho "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 0ALTER SESSION SET CONTAINER=$PDB_NAME;DECLAREts_name VARCHAR2(50);datafile_path VARCHAR2(500);is_omf NUMBER;BEGIN-- Loop through tablespaces with low spaceFOR ts IN (SELECT t.tablespace_nameFROM (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) capacityFROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1024 / 1024) free_sizeFROM dba_free_spaceGROUP 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_sizeFROM dba_data_filesGROUP BY tablespace_name) aWHERE a.tablespace_name = f.tablespace_name(+)) tWHERE capacity >= 0.80AND (unalloc_size + free_size) < 10000AND (unalloc_size + free_size) < used_size / 2) LOOPts_name := ts.tablespace_name;-- Find the path of an existing datafile for this tablespaceSELECT SUBSTR(file_name, 1, INSTR(file_name, '/', -1))INTO datafile_pathFROM dba_data_filesWHERE tablespace_name = ts_nameAND ROWNUM = 1;-- Check if OMF is enabledSELECT COUNT(*) INTO is_omfFROM v\$parameter WHERE name = 'db_create_file_dest' AND value IS NOT NULL;IF is_omf > 0 THEN-- Add datafile with OMFEXECUTE 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 pathEXECUTE 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;/EOFelseecho "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" <<EOFSET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFFSELECT name FROM v\$pdbs WHERE open_mode = 'READ WRITE';EOF)# Check and add datafiles for each PDBfor PDB in $PDB_LIST; docheck_and_add_datafiles $PDBdone# Send email notification with the contents of EMAIL_CONTENT_FILEif $DATA_FILE_ADDED; thenmailx -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.
如果达到阈值添加了数据文件则发送邮件告警
