Oracle 表空间使用率监控

生有可恋 2021-10-04

2530

表空间满了会导致数据写不进去，最终会造成业务中断。

一劳永逸的方法是使用BIGFILE类型的表空间，我们平时使用的表空间是SMALLFILE类型的，一般一个文件最大32G，如果使用BIGFILE可以突破32G的限制。那为什么不用了，BIGFILE优点明显，缺点也非常明显，你以前用迅雷下载时有没有遇到过所有文件都下载完了，最后花了好几倍的时间在等那个最大的文件下载完？在做备份时，大文件会导致备份困难。虽然后来rman解决了大文件分片备份的问题，但大文件给人的感觉就是不可靠，IO会不会出问题，虽然很想尝试不用扩容的优点，但上10T的表空间，而且是单文件，想想也就算了，大文件让人没有尝试的欲望。

我们回到默认的32G的SMALLFILE表空间文件，扩容前要先做一下表空间的监控。最快速知道有没有扩容需求的方法就是查Oracle的dba_outstanding_alerts表，Oracle自己有一套阈值管理，当达到阈值时就会将告警信息写到这个表里。

col OBJECT_NAME for a15;
col reason for a30;
col SUGGESTED_ACTION for a25;
col METRIC_VALUE for 999;
select OBJECT_NAME, reason,
        suggested_action, metric_value
from dba_outstanding_alerts;

通过查询这个表，可以快速知道系统中的高危风险。

当查到有告警后，再对表空间的具体使用情况进行查询

-- 按最终使用比排序


select UPPER(F.TABLESPACE_NAME) as "表空间名称",
       ROUND(D.AVAILB_BYTES, 2) as "表空间大小(G)",
       ROUND(D.MAX_BYTES, 2) as "最终表空间大小(G)",
       ROUND((D.AVAILB_BYTES - F.USED_BYTES), 2) as "已使用空间(G)",
       TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES)  D.AVAILB_BYTES * 100,
                     2),
               '999.99') as "使用比",
       TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES)  D.MAX_BYTES * 100, 2),
               '999.99') as "最终使用比",
       ROUND(F.USED_BYTES, 6) as "空闲空间(G)",
       ROUND(D.MAX_BYTES - D.AVAILB_BYTES, 2) as "最终空闲可用空间(G)"


  from (select TABLESPACE_NAME,
               ROUND(sum(BYTES)  (1024 * 1024 * 1024), 6) USED_BYTES,
               ROUND(max(BYTES)  (1024 * 1024 * 1024), 6) MAX_BYTES
          from SYS.DBA_FREE_SPACE
         group by TABLESPACE_NAME) F,
       (select DD.TABLESPACE_NAME,
               ROUND(sum(DD.BYTES)  (1024 * 1024 * 1024), 6) AVAILB_BYTES,
               ROUND(sum(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES)) 
                     (1024 * 1024 * 1024),
                     6) MAX_BYTES
          from SYS.DBA_DATA_FILES DD
         group by DD.TABLESPACE_NAME) D
 where D.TABLESPACE_NAME = F.TABLESPACE_NAME
 order by 6 desc;

表空间是由多个数据文件组成，每个数据文件有个属性叫AUTOEXTENSIBLE，YES代表可扩容，NO表示不可扩容。如果可扩容，最终文件大小会随着表空间的使用慢慢增长直到达到最大，即32G。不可扩容代表容量固定。

表空间满了写不进去，是最终可用空间不足，这时候有两种扩容方案。一种是将以前不是32G的数据文件扩成32G，第二种是增加数据文件。

我们查一下当前达到85%阈值的表空间

当前达到阈值的表空间有两个，Oracle的报警也是按最终可使用空间比率来报的，说明已超过85%的阈值，不扩就真写不进去了。我们还是按要求将表空间使用率降到85%以下。

扩容前要查一下，Oracle是否启用了OMF

sys@HIS>show parameter DB_CREATE_FILE_DEST
NAME                TYPE   VALUE
------------------- ------ --------
db_create_file_dest string +HISDATA

如果启用了OMF，增加数据文件时就不用写具体文件路径了。

然后查询需要扩容的表空间的数据文件容量使用情况

select T.FILE_ID, t.FILE_NAME ,
       T.BYTES  (1024 * 1024 * 1024) as GB,
       T.AUTOEXTENSIBLE,
       T.MAXBYTES,
       T.USER_BYTES
  from DBA_DATA_FILES T
 where T.TABLESPACE_NAME = 'EMR5' order by 1;

对表空间扩容，增加数据文件，空间不够可以多执行几遍。

alter tablespace EMR5 add datafile;
alter tablespace FS_BUSI_TBS_2021 add datafile;

因为这套库启用了OMF，所以扩容语句可以写的很短，不用指定长度。新扩的文件都是直接自动增长的，如果对增长有预期可以一次性把文件分到指定大小而不用100M、100M的自动扩。

扩容后再次查询Oracle的告警表，发现已经没有告警信息了

查看最终表空间占比，都降到了安全阈值以下

扩容工作已经完了，记录扩容的时间，定期检查表空间使用情况，保持正常的数据增长，如果有新业务上线，要观察数据增长变化，及时调整，防止因管理混乱出现的业务中断。

常用表空间维护语句：

-- 对指定文件扩容
alter database datafile 67 resize 32767M;
-- 将文件类型改为自动增长
alter database datafile 49  autoextend on maxsize unlimited;
-- 增加文件，指定文件路径和大小
ALTER TABLESPACE "EMR5"  ADD  DATAFILE '+HISDATA/hisdb/datafile/emrone001.dbf' SIZE 32767M;

oracle

文章转载自生有可恋，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 表空间使用率监控

评论