家里公网宽带做网站要备案么,网站有访问量 为什么没有询盘,中国建筑工业出版社,pc网站如何做seo目录 前言思考
一、第一种增加表空间的数据文件数量达到总容量的提升
二、第二种解决方案针对system和sysaux的操作
2.1SYSTEM表空间优化
2.2sysaux表空间回收
2.2.1针对sysaux的表空间爆满还有第二套方案维护
三、第三种解决方案使用alter tablespace resize更改表空间的…
目录 前言思考
一、第一种增加表空间的数据文件数量达到总容量的提升
二、第二种解决方案针对system和sysaux的操作
2.1SYSTEM表空间优化
2.2sysaux表空间回收
2.2.1针对sysaux的表空间爆满还有第二套方案维护
三、第三种解决方案使用alter tablespace resize更改表空间的数据文件的容量
编辑 前言思考 刚接触Oracle的项目时候发现我们业务系统的oracle使用3年了各个表空间都使用快爆满了。经过一段时间的研究分享两套解决方案给大家首先分两种表空间的扩容和回收。
表空间的分类
第一种表空间是系统自带的表空间system和sysaux每个表空间的作用和说明可以翻阅Oracle的官方文档。第二种表空间是我们业务系统创建的表空间用于存放数据的了比如HS_RISK_DATAHS_INFO_IDX等是使用数据存储时候表空间会自动增加和扩容。
表空间的扩容和回收
第一种解决方案是在磁盘还有一定量的空间情况下面增加表空间的数据文件让表空间的数据总量增大。因为增加表空间的数据文件是占磁盘存储量。针对SYSTEM,SYSAUX和HS_RISK_DATA、HS_INFO_IDX的表空间都适用并且每日的数据都会双向写入表空。
第二种解决方案针对系统自带表空间system和sysaux的操作针对system的表空间是减少用到的SM/AWR组件表使用空间使用truncate对表空间收缩针对sysaux的表空间爆满是删除AWR数据在通过Move操作回收表的水平线在重建表的索引。
针对sysaux的表空间爆满还有第三套方案删除EM相关的内容统计信息AWR快照、审计信息等。利用SQL语句找出快照最小最大的ID进行删除快照。
第三种解决方案在磁盘空间充沛的情况下面使用alter tablespace resize更改表空间的数据文件的容量。最好每次增加的容量在1g-2g之间因为一下增加太多系统磁盘空间需要划分更多存储空间过去并且耗费时间。
实验环节展示一下三种扩容方案
实验环境配置centos6.9, Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
一、第一种增加表空间的数据文件数量达到总容量的提升
使用下列SQL语句查询表空间使用率和空闲率注保证磁盘存储空间足够多每增一个数据文件会占据存储空间值。
SELECT UPPER(F.TABLESPACE_NAME) 表空间名,D.TOT_GROOTTE_MB 表空间大小(M),D.TOT_GROOTTE_MB - F.TOTAL_BYTES 已使用空间(M),TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), 990.99) 使用比,F.TOTAL_BYTES 空闲空间(M),F.MAX_BYTES 最大块(M)
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME F.TABLESPACE_NAME
ORDER BY 4 DESC;
记录一下没增加数据文件之前的参数值。待会增加以后表空间使用比会降下来 使用SQL语句查询表空间的数据文件存放的位置。
--查询表空间各个数据文件路径
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024*1024),0) total_space
from dba_data_files
order by tablespace_name; 使用SQL在表空间存放在位置添加数据文件注意按照顺序增加并且size参数后面增加数据文件容量最好是按照Gb增加避免增加过多数据文件。并且自动满后扩展到8Gb接触限制条件无限增长了。
--增加表空间数据文件路径
alter tablespace SYSAUX add datafile /u01/oradata/uatdb/sysaux02.dbf size 2G autoextend on next 8G maxsize unlimited;
alter tablespace SYSTEM add datafile /u01/oradata/uatdb/system02.dbf size 2G autoextend on next 8G maxsize unlimited;
alter tablespace HS_HIS_DATA add datafile /u01/oradata/uatdb/hisdat02.dbf size 2G autoextend on next 8G maxsize unlimited;
alter tablespace HS_HIS_IDX add datafile /u01/oradata/uatdb/hisidx02.dbf size 2G autoextend on next 8G maxsize unlimited;
增加表空间的数据文件以后在使用SQL查询之后表空间的数据文件会增加一个。
再次查询总的表空间容量参数之后表空间使用比会降低。表空间容量会上涨了。不管是系统表空间还是业务表空间都得到缓解了。 centos6.9系统中设置oracle中表空间的配置文件的路径也会随着数据文件增加容量上涨。 二、第二种解决方案针对system和sysaux的操作
2.1SYSTEM表空间优化 首先我们先用SQL语句查询SYSTEM和SYSAUX用了多少表空间。基本上都快爆满了sysaux占据97.22%system占据97.03%记得这个数值。后期优化时候这个数值会下来
SELECT occupant_name Item, space_usage_kbytes / 1048576 Space Used (GB), schema_name Schema, move_procedure Move Procedure FROM v$sysaux_occupants where occupant_nameSM/AWR;SELECT * FROM ( SELECT D.TABLESPACE_NAME, SPACE || M SUM_SPACE(M), BLOCKS SUM_BLOCKS, SPACE - NVL (FREE_SPACE, 0) || M USED_SPACE(M), ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || % USED_RATE(%), FREE_SPACE || M FREE_SPACE(M) FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME F.TABLESPACE_NAME() UNION ALL SELECT D.TABLESPACE_NAME, SPACE || M SUM_SPACE(M), BLOCKS SUM_BLOCKS, USED_SPACE || M USED_SPACE(M), ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || % USED_RATE(%), NVL (FREE_SPACE, 0) || M FREE_SPACE(M) FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME F.TABLESPACE_NAME() ORDER BY 1) WHERE TABLESPACE_NAME IN (SYSAUX,SYSTEM); 下面查看下使用SYSTEM和SYSAUX表空间的比较大的表有哪些。 可见大表大部分都是AUD$和WRH$开头的AWR基表AUD$使用SYSTEM表空间AWR的基表使用SYSAUX表空间下面再查看下SYSAUX表空间的使用情况可以通过v$sysaux_occupants视图查询到。 可见AUD组件就使用了将近6GB的SYSAUX表空间也就是说审计和AWR占用了大量的SYSTEM和SYSAUX表空间而这些数据是可以定期清理的都没有必要保留太长的时间。 下面先清理审计的数据如果要保留部分AUD$里面记录的审计数据可以把想要的数据插入到一张临时表然后直接truncate这张表就可以了truncate操作会直接回收AUD$占用的空间。
truncate操作需要用SYSDBA权限来执行 执行完truncate操作后system的表空间会降低很多。因为大部分AUD$里面记录的审计数据会删除掉 2.2sysaux表空间回收
可见SM/AWR组件就使用了将近6GB的SYSAUX表空间也就是说审计和AWR占用了大量的SYSAUX表空间。 下面再来回收下SYSAUX表空间这个相对比较麻烦也比较耗时。 如上文所示通过查看v$sysaux_occupants视图可以确定占用SYSAUX表空间过多的大部分都是AWR的基表这样只要删除部分AWR数据理论上就可以回收一部分SYSAUX表空间通常AWR的数据都会设置保留期限10g版本默认保留7天11g版本默认保留8天可以通过dba_hist_wr_control视图来查看(注并不是所有DBA开头的表都是数据字典也有很多是视图 大家可能会有疑问了AWR的数据既然只保留七八天为什么还会占用这么多的SYSAUX表空间呢这个问题我个人认为主要有以下两个原因首先AWR删除过期的数据是通过DELETE操作完成的这样就会产生大量的碎片特别是SYSAUX表空间存在自动扩展的数据文件而且这个数据文件没有扩展到最大还有扩展的空间情况下会很明显其次就是ASH的数据有些情况下是不受AWR的保留策略影响的这个从下面的SQL就可以看出。 可以看到ASH的数据从第一个快照开始一直都在保留导致WRH$_ACTIVE_SESSION_HISTORY表很大使用DBMS_WORKLOAD_REPOSITORY包清理过期或者不需要的AWR数据可以回收这部分空间。注意SQL语句中snap_id的最高值一定根据自己筛查出来的结果定值。 清理了AWR数据之后你会发现SYSAUX表空间的空间并没有被回收使用率还和之前一样这是因为清理AWR操作是通过DELETE操作实现的表的水位线并没有下降导致的。下面通过MOVE操作回收这个表的水位线来回收这部分被删除数据占用的空间。
注意PARTITION_NAME中的取值范围不能单纯复制粘贴注意我的结尾880025_0 下面按照分区进行MOVE操作来回收空间 对分区表进行MOVE之后需要重建索引查看这个表的索引信息。 再次查看WRH$_ACTIVE_SESSION_HISTORY的空间已经回收。 回收表空间WRH$_ACTIVE_SESSION_HISTORY后总体的SYSAUX的表空间下降些许。原来是95.02% 通过v$sysaux_occupants视图可以查询到AWR占用空间由之前的将近6GB降为了不到5GB。按照同样的方法回收下WRH$_EVENT_HISTOGRAM表的空间在通过回收下WRH$_EVENT_HISTOGRAM表的空间下图1是找到WRH$_EVENT_HISTOGRAM所在索引 同样的方法回收表空间在进行重铸索引。 回收下WRH$_EVENT_HISTOGRAM表的空间剩下的SYSAUX表空间数据 2.2.1针对sysaux的表空间爆满还有第二套方案维护 SYSAUX表空间做为SYSTEM表空间的辅助表空间主要存放EM相关的内容以及表统计信息AWR快照审计信息等。修改统计信息的保持时间默认为31天这里修改为7天过期的统计信息会自动被删除。 修改AWR快照的保存的参数我这里没有执行成功可能被某个Oracle参数限制住了
---修改AWR快照的保存时间为7天(72460),每小时收集一次
begin dbms_workload_repository.modify_snapshot_settings ( interval 60, retention 10080, topnsql 100 );
查询最最小和最大快照ID楼上我操作删除快一次快照到22513现在最小的快照值为22514开始
通过执行下图的SQL语句按照快照最小最大值来进行删除 三、第三种解决方案使用alter tablespace resize更改表空间的数据文件的容量
先查询表空间里面有多少的数据文件值知道数据文件的名字 通过下列语句可以看到每个表空间的数据文件容量为多少了。 select a.file# as 数据文件id,a.name as 数据文件路径,a.bytes / 1024 / 1024 as 当前数据文件大小(MB),ceil(HWM * a.block_size) / 1024 / 1024 as 可调整至大小(MB),(a.bytes - HWM * a.block_size) / 1024 / 1024 AS 释放空间大小(MB),alter database datafile || a.name || resize ||ceil(ceil(HWM * a.block_size) / 1024 / 1024) || M; as SQL语句from v$datafile a,(SELECT file_id, MAX(block_id blocks - 1) HWMFROM DBA_EXTENTSGROUP BY file_id) bwhere a.file# b.file_id()And (a.bytes - HWM * a.block_size) 0and rownum 30order by 释放空间大小(MB) desc 筛查一个表空间的数据文件增加容量到2GB 执行结束验证表空间的额数据文件已经增加到2GB的容量。 以上是我总结的三种扩容表空间的方法。感谢JiekeXu_DBAIT邦德老师还有无数DBA的工程支持。
参考文献
https://www.cnblogs.com/yaenli/p/16662103.html
oracle system和sysaux表空间清理和回收_oracle system表空间清理-CSDN博客
