网站首页的尺寸,md风格wordpress主题,网站内部结构优化,龙岗 网站建设之前我曾经发过一个自动处理表空间的脚本#xff0c;可以通过定时任务自动处理表空间不足的问题#xff1b;但是之前那个脚本没有涵盖CDB模式下的PDB#xff0c;这里将脚本做了一下更新#xff0c;可以处理CDB模式下多PDB的表空间问题。
传统模式的脚本请参考这个链接
Or…之前我曾经发过一个自动处理表空间的脚本可以通过定时任务自动处理表空间不足的问题但是之前那个脚本没有涵盖CDB模式下的PDB这里将脚本做了一下更新可以处理CDB模式下多PDB的表空间问题。
传统模式的脚本请参考这个链接
Oracle自动处理表空间不足脚本 - 墨天轮
表空间问题引起的问题请看如下
Oracle 11G还有新BUGORACLE 表空间迷案 - 墨天轮
脚本如下
#!/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_RECIPIENTSxxxxxxmail.com # 设置告警邮件mailDATA_FILE_ADDEDfalse #设置是否加数据文件的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_ADDEDtrue $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_FILEfi# Clean up temporary filerm -f $EMAIL_CONTENT_FILE
比如有多个PDB可以一个脚本搞定设置定时任务就可以自动处理表空间问题无需人工介入。
[oraclessycmes01 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.
如果达到阈值添加了数据文件则发送邮件告警
