SYSAUX表空间满对数据库的影响以及解决措施是什么

共计 9149 个字符，预计需要花费 23 分钟才能阅读完成。

SYSAUX 表空间满对数据库的影响以及解决措施是什么，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

1. 概要

SYSAUX 表空间满了，会影响登录嘛？会影响数据库正常运行吗？怎么处理呢？容易的想到，增加空间，删除被耗的空间，首先恢复生产业务为重。对于排查消耗 SYSAUX 空间大的对象，无论哪个版本，都一样，查看是否存放较大的业务表，或者个人的中间表。对于 12C 来说，更加关注的一个就是：数据库的 audit_trail 审计参数是否为开启的，如果该参数值为 DB，则关注 ausdsys 模式下的 audsys 组件，即 audsys.CLI_SWP$459d3b9$1$1 表的 lob 段 SYS_LOB0000091784C00014$$ 的大小。如果为 NONE，则和 10G 与 11G 的排查一样的思路去排查 SYSAUX 里的大对象。

2. 案例分析

1、备份检查

在日常的备份维护当中，从备份检查集中采集的结果发现，多个库的归档备份失败。

2、检查归档备份或者全库的备份的日志输出

3、检查 catalog 库的 audit_trail 参数 values=’DB’.

4、查看该库的 alert 日志：

5、查看消耗 SYSAUX 表空间大的对象（段）：

其中，audsys 组件的 lob 段占了 31.5G。

6、尝试使用 sys 用户对该 lob 段对应的表进行 truncate：

发现 sys 用户也没有权限。

7、使用存储过程执行清理：

begin

 dbms_audit_mgmt.clean_audit_trail(

 audit_trail_type = DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

 use_last_arch_timestamp  =  FALSE);

 end;

/

附：oracle 官网提供的清理方法，查看：

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_audit_mgmt.htm#ARPLS65414

1 .exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (–

 AUDIT_TRAIL_TYPE  = DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, –

 LAST_ARCHIVE_TIME = sysdate);

2 .exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(–

 AUDIT_TRAIL_TYPE  = DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, –  

 USE_LAST_ARCH_TIMESTAMP = TRUE);

8、检查 sysaux 表空间释放，调整审计参数 audit_trail 参数 values=’NONE’。

9、重启数据库，多个库的归档备份恢复正常。

3.SYSAUX 表空间描述 3.1SYSAUX 表空间官网的描述

The SYSAUX tablespace is always created at database creation. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEMtablespace. Because it is the default tablespace for many Oracle Database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database. It also reduces the load on the SYSTEM tablespace.

You can specify only datafile attributes for the SYSAUX tablespace, using the SYSAUX DATAFILE clause in the CREATE DATABASE statement. Mandatory attributes of the SYSAUX tablespace are set by Oracle Database and include:

PERMANENT

READ WRITE

EXTENT MANAGMENT LOCAL

SEGMENT SPACE MANAGMENT AUTO

You cannot alter these attributes with an ALTER TABLESPACE statement, and any attempt to do so will result in an error. You cannot drop or rename the SYSAUX tablespace.

The size of the SYSAUX tablespace is determined by the size of the database components that occupy SYSAUX. See a list of allSYSAUX occupants. Based on the initial sizes of these components, the SYSAUX tablespace needs to be at least 240 MB at the time of database creation. The space requirements of the SYSAUX tablespace will increase after the database is fully deployed, depending on the nature of its use and workload. For more information on how to manage the space consumption of the SYSAUX tablespace on an ongoing basis, please refer to the Managing the SYSAUX Tablespace

3.2SYSAUX 表空间中文简述

Oracle 从 10G 开始引入的, 以前一些使用独立表空间或系统表空间的数据库组件现在在 SYSAUX 表空间中创建. 通过分离这些组件和功能，SYSTEM 表空间的负荷得以减轻. 反复创建一些相关对象及组件引起 SYSTEM 表空间的碎片问题得以避免。如果 SYSAUX 表空间不可用, 数据库核心功能将保持有效; 使用 SYSAUX 表空间的特点将会失败或功能受限.

3.3SYSAUX 表空间的主要组件

查看 SYSAUX 表空间组件的信息语句：

col Item For a30

col Space Used(GB) For a10

col Schema For a20

col MoveProcedure For a40

SELECT occupant_name Item ,

 round(space_usage_kbytes/1024,3) Space Used (MB) ,

 schema_name Schema ,

 move_procedure MoveProcedure

 FROM v$sysaux_occupants

ORDER BY 2 Desc;

这个脚本执行出来的结果包括有：组件名、组件消耗的空间大小、对象模式和组件移动使用的存储过程。

3.3.1 11G 的组件

3.3.2 12C 的组件

4. 防范 SYSAUX 表空间满的措施

根据 sysaux 表空间的使用情况，在 10G 和 11G，如果在建库后给 sysaux 补充一次数据文件，没有生产业务表或者个人中间表放进来，改表空间增长较为缓慢，相对稳定。

4.1 权限防范

权限上，防止生产用户或者个人用把表放到 SYSAUX 表空间。

1、创建账号时，禁止配置用户的 DEFAUL TABLESPACE 为 SYSAUX;

2、不管生产账号或者个人账号，尽量少授权 UNLIMITED TABLESPACE 给到这些用户，或者 quota unlimited on SYSAUX, 当然根据实际生产业务定。

4.2 参数防范

对于 12C 而言，如果没有特殊需求，调整参数 audit_trail 为 NONE 并重启数据库生效。

4.3 存储防范

适当增大 SYSAUX 表空间。

4.4 快照和统计信息保留策略防范 4.4.1 调整数据统计信息和快照的保存策略

1、调整历史统计信息保留时间：

修改统计信息的保持时间，默认为 31 天，这里修改为 7 天，过期的统计信息会自动被删除。

sys@PROD select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

—————————

 31

2、修改统计信息保留时间：

sys@PROD exec dbms_stats.alter_stats_history_retention(10);

PL/SQL procedure successfully completed.

3、再次查看历史统计信息保留的天数：

sys@PROD select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

—————————

 10

4.4.2 调整 AWR 快照的保存时间

例如改为 7 天 (7*24*60), 每小时收集一次，默认情况下当前系统的保留时间为 8 天,1 小时采样一次。

1、查看当前的快照保留时间和采集周期：

sys@PROD col RETENTION for a20

sys@PROD col SNAP_INTERVAL for a25

sys@PROD select * from dba_hist_wr_control;

 DBID SNAP_INTERVAL  RETENTION  TOPNSQL

———- ————————- ——————– ———-

 338469376 +00000 01:00:00.0  +00008 00:00:00.0  DEFAULT

2、查看系统的当前的 MOVING_WINDOW_SIZE：

sys@PROD select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;

 DBID BASELINE_NAME  BASELINE_TYPE MOVING_WINDOW_SIZE

———- —————————————————————- ————- ——————

 338469376 SYSTEM_MOVING_WINDOW  MOVING_WINDOW  8

3、尝试调整 AWR 的快照的收集时间 60 分钟一次和保留时间 7 天：

sys@PROD begin

 2  dbms_workload_repository.modify_snapshot_settings(

 3  interval = 60,

 4  retention = 10080,

 5  topnsql = 50

 6  );

 7  end;

 8  /

begin

*

ERROR at line 1:

ORA-13541: system moving window baseline size (691200) greater than retention (604800)

ORA-06512: at SYS.DBMS_WORKLOAD_REPOSITORY , line 174

ORA-06512: at SYS.DBMS_WORKLOAD_REPOSITORY , line 222

ORA-06512: at line 2

4、先调整小于当前保留的时间窗口：

sys@PROD exec dbms_workload_repository.modify_baseline_window_size(7);

PL/SQL procedure successfully completed.

5、再次调整 AWR 的快照的收集时间 60 分钟一次和保留时间 7 天：

sys@PROD begin

 2  dbms_workload_repository.modify_snapshot_settings(

 3  interval = 60,

 4  retention = 10080,

 5  topnsql = 50

 6  );

 7  end;

 8  /

PL/SQL procedure successfully completed.

6、调整后，确认 AWR 的快照的保留时间：

sys@PROD select * from dba_hist_wr_control;

 DBID SNAP_INTERVAL  RETENTION  TOPNSQL

———- ————————- ——————– ———-

 338469376 +00000 01:00:00.0  +00007 00:00:00.0  50

5. 清理 SYSAUX 消耗空间大的组件内容 5.1 通过删除 AWR 快照清理空间

1、查看当前保留的快照的 SNAP_ID：

sys@PROD select min(snap_id),max(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)

———— ————

 2561  2570

2、尝试删除较早的几个快照，例如删除最前的 5 个快照：

sys@PROD exec dbms_workload_repository.drop_snapshot_range(low_snap_id = 2561,high_snap_id = 2561+4);

或者：

exec dbms_workload_repository.drop_snapshot_range(2561,2565,338469376)；

3、查看当前保留的快照的 SNAP_ID：

sys@PROD select min(snap_id),max(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)

———— ————

 2566  2570

如果快照保留得比较多的话，在删除快照的过程，比较消耗 CPU 资源，比较慢，当中会对以 WRH$_开头的分区表进行 delete 操作。所以当我们通过查看 SNAP_ID，旧的快照已经被删除，但是 sysaux 里面的组件大小没有改变，依然原来大小，sysaux 表空间没有释放。接着就是对 WRH$_相关表进行高水位回收，收完就是收集相关表的统计信息。

4、删除旧的快照后进行高水位回收，例如：

11:40:03 sys@PROD ALTER TABLE SYS.WRH$_LATCH MODIFY PARTITION WRH$_LATCH_338469376_0 SHRINK SPACE;

Table altered.

Elapsed: 00:00:13.11

11:42:36 sys@PROD ALTER TABLE SYS.WRH$_SQLSTAT MODIFY PARTITION WRH$_SQLSTA_338469376_0 SHRINK SPACE;

Table altered.

Elapsed: 00:00:12.55

5、或者对更多的表进行高水位回收：

alter table WRH$_SQLSTAT shrink space;

alter table WRH$_SYSSTAT shrink space;

alter table WRH$_SEG_STAT shrink space;

alter table WRH$_LATCH shrink space;

… …

6、收集回收高水位之后的表的统计信息：

execute dbms_stats.gather_table_stats(ownname = sys ,tabname = WRH$_LATCH MODIFY ,partname = WRH$_LATCH_338469376_0 ,DEGREE=

execute dbms_stats.gather_table_stats(ownname = sys ,tabname = WRH$_SQLSTAT ,partname = WRH$_SQLSTA_338469376_0 ,DEGREE=

5.2 通过删除过旧的统计信息清理空间

23:46:54 sys@PROD exec dbms_stats.purge_stats(sysdate-6);

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.70

alter table WRH$_SQLSTAT shrink space;

alter table WRH$_SYSSTAT shrink space;

alter table WRH$_SEG_STAT shrink space;

alter table WRH$_LATCH shrink space;

… …

5.3 删除部分历史记录表清理空间

针对部分历史统计信息表进行删除收缩，例如删除无效的 ASH 历史会话记录表 wrh$_active_session_history

1、查看无效的历史会话记录：

SELECT COUNT(1) Orphaned_ASH_Rows

 FROM wrh$_active_session_history a

 WHERE NOT EXISTS

 (SELECT 1

 FROM wrm$_snapshot

 WHERE snap_id = a.snap_id

 AND dbid = a.dbid

 AND instance_number = a.instance_number

 );

2、进行删除无效的历史会话记录：

DELETE FROM wrh$_active_session_history a

WHERE NOT EXISTS (SELECT 1

FROM wrm$_snapshot

WHERE snap_id = a.snap_id

AND dbid = a.dbid

AND instance_number = a.instance_number);

23392228 rows deleted.

SQL commit;

3、回收高水位：

alter table wrh$_active_session_history shrink space;

收完之后，就是重新收集该表的统计信息。

5.4 通过移动部分组件内容到其他表空间清理空间

从上面查看 SYSAUX 表空间的组件的结果中，可以看到，结果中的 MoveProcedure 字段的内容，提供了移动该组件内容的存储过程，存储过程后接目标表空间的参数，就可以实现移动了，当然也可以重新移动回 SYSAUX 表空间，前提是确保目标表空间大小足够。例如，对 Logminer 从 SYSAUX 表空间，迁移到 users 表空间，在还原回来：

1、查看移动前的表空间：

2、移动 Logminer 到 users 表空间：

21:39:40 sys@PROD exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE(USERS

PL/SQL procedure successfully completed.

3、回迁 Logminer 组件到 SYSAUX 表空间：

22:07:55 sys@PROD exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE(SYSAUX

PL/SQL procedure successfully completed.

补充：

再补充一下第 5 部分：sysaux 消耗空间大的处理，也是以前经常遇到的案例：

1. 理解 sysaux 表空间各组件的用途，最容易遇到空间增长的应该是下面这三个组件：

 SM/ADVISOR：指的是数据库中的 sql tuning advisor、sql access advisor、ADDM 等自动维护任务产生的数据

 SM/AWR：AWR 的快照信息，空间大小取决于产生快照的频率和保留时间

 SM/OPTSTAT：用于存储优化器统计信息的历史版本数据。

2. 为什么有定期清理任务，还是会出现空间异常增长？

  情况一，数据存在保留期，例如快照保留时间默认为 31 天，过期的数据将会被数据库的 MMON 进程定期自动删除, 但是这个进程每次只运行 5 分钟，超过 5 分钟清理任务就会中断，所以数据清理可能是不成功或不彻底的。这个问题 oracle 在新版本中提供了补丁，把大表改成了按天的分区表，清理方式变成 truncate 就不会出现清不掉的情况了。

  情况二，默认的保留期太长，例如 dba_scheduler_job_classes 中任务调度的运行日志默认保留期就是 1000000 天。

3. 如何处理？

  一般不使用 delete，然后 shrink space 这样的方式去清理空间，而是调用 oracle 自己实现的清理函数，例如清理任务运行的日志使用 DBMS_SCHEDULER.PURGE_LOG(); 清理所有统计信息使用 dbms_stats.purge_stats(dbms_stats.PURGE_ALL);

总结

对于 SYSAUX 满了，数据库受到哪些影响，从目前遇到的案例来看，就是在 12C 数据库审计参数 audit_trail 为 DB 的时候，该表空间满了，直接影响用户登录数据库，10G 和 11G 未遇到受影响的情况。经过一些模拟实验测试，在 12C 和 11G 的版本里面，当 audit_trail 为 NONE 的时候,SYSAUX 满了，都不影响数据库的可用性。对于 SYSAUX 表空间的其他组件，则需要更多的测试实验区探究，各组件的内容清理与移动，大致一样。

以上是个人的亲身经历的一个案例、测试效果和一些看法，如果还有哪些纰漏或者错误，希望众大神指出并赐教，在往后的时间，加以补充，改进与学习。

附：

12C 安全审计的新特性：

Oracle Database 12c Security: New Unified Auditing

连接 1：https://blogs.oracle.com/imc/oracle-database-12c-security:-new-unified-auditing

连接 2：https://www.cnblogs.com/shenfeng/p/oracle_12c_sysaux_clean.html

## 这里 12C 的新特性意思是：两个审计（标准审计和细粒度审计合并成一个一种审计）同放在一个表中

看完上述内容，你们掌握 SYSAUX 表空间满对数据库的影响以及解决措施是什么的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！