1 前言
在生产环境中,一般设置表空间告警阈值是90%,在接到监控报警后,并不是需要立刻对表空间进行扩容。
决定是否扩容主要看表空间最近的增量是多少,假如剩余10%的空间还能支持1个月的增量,那就不需要急着扩容。如果剩余的空间只能坚持几天,那么最好是立即扩容,以防止数据突增。
接到告警后,一般工作过程如下:
- 查看表空间利用率和剩余容量;
- 查看表空间增量;
- 扩容或者释放空间;
- 找出数据增量异常的对象。
根据下面的常用sql脚本排查。
2 处理流程
2.1 查看表空间利用率
col tablespace_name for a20 col pct_used for a10 select a.tablespace_name, a.total_mb, a.total_mb - b.free_mb used_mb, b.free_mb, case when a.total_mb 0 then round((a.total_mb - b.free_mb) / a.total_mb * 100,2) else null end || '%' pct_used from (select ts.tablespace_name, round(sum(bytes) / 1024 / 1024,2) total_mb from dba_tablespaces ts, dba_data_files df where ts.tablespace_name = df.tablespace_name group by ts.tablespace_name) a, (select fs.tablespace_name, round(sum(bytes) / 1024 /1024,2) free_mb from dba_free_space fs group by fs.tablespace_name) b where a.tablespace_name = b.tablespace_name and a.tablespace_name = '&tsb_name' order by 1;
2.2 查看表空间增量
日增量:
set line 200 col ts_name for a30 col pct_used for a10 SELECT a.snap_id, c.tablespace_name ts_name, to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime, round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb, round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb, round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,2) ts_free_mb, round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) - lag(round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2),1) over(order by a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss')) inc_mb, round(a.tablespace_usedsize / a.tablespace_size * 100, 2) || '%' pct_used FROM dba_hist_tbspc_space_usage a, (SELECT tablespace_id, substr(rtime, 1, 10) rtime, max(snap_id) snap_id FROM dba_hist_tbspc_space_usage nb group by tablespace_id, substr(rtime, 1, 10)) b, dba_tablespaces c, v$tablespace d where a.snap_id = b.snap_id and a.tablespace_id = b.tablespace_id and a.tablespace_id=d.TS# and d.NAME=c.tablespace_name and d.NAME = '&tbs_name' and to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30 order by a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc;
累计增量,根据awr保留时间而定,默认为8天:
set line 200 col ts_name for a30 col pct_used for a10 with ts as( SELECT a.snap_id, c.tablespace_name ts_name, to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime, round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_mb, round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb, round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,2) ts_free_mb, round(a.tablespace_usedsize / a.tablespace_size * 100, 2) || '%' pct_used FROM dba_hist_tbspc_space_usage a, (SELECT tablespace_id, substr(rtime, 1, 10) rtime, max(snap_id) snap_id FROM dba_hist_tbspc_space_usage nb group by tablespace_id, substr(rtime, 1, 10)) b, dba_tablespaces c, v$tablespace d where a.snap_id = b.snap_id and a.tablespace_id = b.tablespace_id and a.tablespace_id=d.TS# and d.NAME=c.tablespace_name and to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30) select f.ts_name,f.ts_mb,f.ts_used_mb begin_used_mb,f.rtime begin_time, t.ts_used_mb end_used_mb,t.rtime end_time,t.ts_used_mb - f.ts_used_mb inc_mb, round(to_date(t.rtime,'yyyy-mm-dd hh24:mi:ss') - to_date(f.rtime,'yyyy-mm-dd hh24:mi:ss'),2) inc_days from (select a.*,row_number()over(partition by a.ts_name order by a.snap_id desc) rn from ts a) t, (select b.*,row_number()over(partition by b.ts_name order by b.snap_id) rn from ts b) f where t.rn = 1 and f.rn = 1 and t.ts_name = f.ts_name and t.ts_name = '&ts_name';
根据上述查出来的表空间日增量和累计增量结果,可以大概估算出剩余的空间可以坚持多久,根据实际情况决定是否扩容。
2.3 查看数据文件路径
此步骤主要是查看表空间数据文件路径,为表空间扩容添加数据文件做好环境调研。
set lines 200 set pagesize 300 col file_name for a60 col size_mb for 999999.99 select * from ( select file_name,file_id,tablespace_name,round(bytes / 1024 / 1024,2) size_mb,status,autoextensible from dba_data_files where tablespace_name = '&ts_name' order by 2 desc) where rownum
3 表空间扩容
表空间扩容可以选择添加数据文件,或者拓展数据文件。
3.1 添加数据文件
添加数据文件的时候一定要注意:
- 在RAC集群环境中,切记不要将数据文件创建到本地,这样就会造成集群节点间的不一致,可能会导致其他节点起不来。
- 也不要将数据文件创建到其他磁盘组中,这样不够规范。
以表空间ts_test为例:
--ASM: SQL> alter tablespace ts_test add datafile '+DATA' size 100M; --File System: SQL> alter tablespace ts_test datafile '/u01/app/oracle/oradata/datafile/ts_test02.dbf' size 100M;
3.2 拓展数据文件
假设原来ts_test.274.1171146701大小为100M,我们可以将其拓展到200M以达到扩容的目的:
alter database datafile'+DATA/orcl/datafile/ts_test.274.1171146701' resize 200M;
3.3 扩容后检查
扩容后需要检查表空间使用率是否下降:
col tablespace_name for a20 col pct_used for a10 select a.tablespace_name, a.total_mb, a.total_mb - b.free_mb used_mb, b.free_mb, case when a.total_mb 0 then round((a.total_mb - b.free_mb) / a.total_mb * 100,2) else null end || '%' pct_used from (select ts.tablespace_name, round(sum(bytes) / 1024 / 1024,2) total_mb from dba_tablespaces ts, dba_data_files df where ts.tablespace_name = df.tablespace_name group by ts.tablespace_name) a, (select fs.tablespace_name, round(sum(bytes) / 1024 /1024,2) free_mb from dba_free_space fs group by fs.tablespace_name) b where a.tablespace_name = b.tablespace_name and a.tablespace_name = '&tsb_name' order by 1;
4 后续排查
如果表空间时短时间内激增,则在扩容后还需要排查,找出是哪个对象数据突增影响的。
4.1 查看snap_id
set line 200 select distinct snap_id, to_char(begin_interval_time,‘yyyy-mm-dd hh24:mi:ss') begin_interval_time, to_char(end_interval_time,‘yyyy-mm-dd hh24:mi:ss') end_interval_time from dba_hist_snapshot where to_char(begin_interval_time,‘yyyy-mm-dd hh24:mi:ss') >= to_char(sysdate - &day_ago,‘yyyy-mm-dd hh24:mi:ss') order by snap_id desc;
4.2 查看某个表空间下增量最多的对象
set lines 200 col object_name for a30 select * from (select obj.owner,obj.object_name,sum(hs.db_block_changes_delta) db_block_changes_delta, round(sum(hs.space_used_delta) / 1024 / 1024,2) space_delta_mb from dba_hist_seg_stat hs, v$tablespace ts, dba_objects obj, dba_hist_snapshot sn where hs.ts# = ts.ts# and hs.snap_id = sn.snap_id and hs.obj# = obj.object_id and ts.name = ‘&tbs_name' and sn.begin_interval_time >= sysdate - &day_ago group by obj.owner,obj.object_name order by space_delta_mb desc) where rownum
以上就是Oracle表空间利用率不足的处理流程的详细内容,更多关于Oracle表空间利用率不足的资料请关注IT俱乐部其它相关文章!