Oracle 12c SYSAUX表空间不足处理-清理audsys.cli_swp$a9b5f52c$1$1表

2021-12-16 12:14:51

今天在检查一台测试环境的表空间时，发现SYSAUX的使用率已经达到99.91%

TABLESPACE_NAME                     FILES Freesize(MB) Usedsize(MB) Filesize(MB) Filemaxsize(MB) CurrentUsed(%) MaxUsed(%)

------------------------------ ---------- ------------ ------------ ------------ --------------- -------------- ----------

SYSAUX                                  2      28.3125 32939.671875 32967.984375    32967.984375 99.91412122840 99.9141212

清理AWR统计数据

首先查看是什么使用了空间

SELECT occupant_name "Item",

           space_usage_kbytes / 1048576 "Space Used (GB)",

           schema_name "Schema",

           move_procedure "Move Procedure"

  FROM v$sysaux_occupants

 ORDER BY 2 desc;

Item                                                             Space Used (GB) Schema                                                           Move Procedure

---------------------------------------------------------------- --------------- ---------------------------------------------------------------- ----------------------------------------------------------------

SM/AWR                                                           4.1289672851562 SYS

XDB                                                              0.1832885742187 XDB                                                              XDB.DBMS_XDB.MOVEXDB_TABLESPACE

SM/OPTSTAT                                                       0.1232299804687 SYS

SDO                                                              0.0913696289062 MDSYS                                                            MDSYS.MOVE_SDO

既然显示为AWR占用最多，查看下统计数的保存天数

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         31

SQL> exec dbms_stats.alter_stats_history_retention(7);

PL/SQL procedure successfully completed

因为数据库修改过一次dbid，实际存在两个部分的AWR信息

SQL> select dbid, min(snap_id),max(snap_id) from dba_hist_snapshot group by dbid;

      DBID MIN(SNAP_ID) MAX(SNAP_ID)

---------- ------------ ------------

3187895652        10357        10495

2750849929            1           54

清空上一个dbid下的所有snapshot

SQL> desc dbms_workload_repository.drop_snapshot_range

Parameter    Type   Mode Default?

------------ ------ ---- --------

LOW_SNAP_ID  NUMBER IN

HIGH_SNAP_ID NUMBER IN

DBID         NUMBER IN   Y        

SQL> exec dbms_workload_repository.drop_snapshot_range(10357,10495,3187895652)

PL/SQL procedure successfully completed

SQL> select dbid, min(snap_id),max(snap_id) from dba_hist_snapshot group by dbid;

      DBID MIN(SNAP_ID) MAX(SNAP_ID)

---------- ------------ ------------

2750849929            1           54

再删除历史统计数据

SQL> exec dbms_stats.purge_stats(sysdate-5);

PL/SQL procedure successfully completed

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY

--------------------------------------------------------------------------------

20-8月 -16 04.52.17.000000000 下午 +08:00

SQL> exec dbms_stats.purge_stats(sysdate-3);

PL/SQL procedure successfully completed

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY

--------------------------------------------------------------------------------

22-8月 -16 05.54.49.000000000 下午 +08:00

清除信息后，未见表空间占用减少，这时要考虑高水位的问题，对几张stat相关的表做一下表收缩。采样数据库都以WRM$_\*和WRH$_*的格式命名。



SQL> alter table WRH$_SQLSTAT shrink space;

表已更改。

SQL> alter table WRH$_SYSSTAT shrink space;

表已更改。

SQL> alter table WRH$_SEG_STAT shrink space;

表已更改。

SQL> alter table WRH$_LATCH shrink space;

表已更改。

当然并不是所有的表都支持shrink，参考此文。

这样操作之后，占用率降到96.7%。看来实际的大头并不是AWR信息。

处理LOB

使用dba_segments试图查询下这个表空间下哪个段占用最大的空间。

SQL>  select segment_name, segment_type, sum(bytes)/1024/1024 from dba_segments where tablespace_name='SYSAUX' group by segment_name,segment_type order by sum(bytes) desc;

SEGMENT_NAME                                                                     SEGMENT_TYPE       SUM(BYTES)/1024/1024

-------------------------------------------------------------------------------- ------------------ --------------------

SYS_LOB0000091751C00014$$                                                        LOB PARTITION                 27593.625

WRH$_SYSSTAT_PK                                                                  INDEX PARTITION                460.0625

WRH$_EVENT_HISTOGRAM_PK                                                          INDEX PARTITION                394.0625

WRH$_LATCH_PK                                                                    INDEX PARTITION                380.0625

WRH$_EVENT_HISTOGRAM                                                             TABLE PARTITION                242.0625

WRH$_PARAMETER_PK                                                                INDEX PARTITION                210.0625

WRH$_ACTIVE_SESSION_HISTORY                                                      TABLE PARTITION                202.0625

WRH$_PARAMETER                                                                   TABLE PARTITION                186.0625

查看LOB字段属于哪个表

SQL> select owner,table_name,column_name from all_lobs where segment_name='SYS_LOB0000091751C00014$$';

OWNER      TABLE_NAME           COLUMN_NAME

---------- -------------------- ---------------

AUDSYS     CLI_SWP$a9b5f52c$1$1 LOG_PIECE

单独看这张表，竟然提示表不存在

SQL> desc audsys.cli_swp$a9b5f52c$1$1

ERROR:

ORA-04043: 对象 audsys.cli_swp$a9b5f52c$1$1 不存在

网络搜索原来是12C的新特性Unified Audit存放的审计数据

参考http://www.orafaq.com/node/2894，即时是sys用户也不能对这张表做DML操作。
而里面的数据应该是通过unified_audit_trail视图也读取的

我在测试环境上使用select count(*) from unified_audit_trail;，过了14分钟也不能获得数据，最后以ORA-01652临时表空间用完退出。
既然不能直接删除表的数据，Oracle提供了存储过程的方式清除这张审计表的数据

SQL> begin

  2  dbms_audit_mgmt.clean_audit_trail(

  3      audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

  4      use_last_arch_timestamp  =>  FALSE);

  5  end;

  6  /

PL/SQL 过程已成功完成。

删除后，SYSAUX的空间使用率一下手降到了12.5%。

进一步操作

按照Oracle文档，我们可以如此手工清除unified audit的数据，也可以定时进行删除，也可以关闭unified audit功能。

具体可以参考此文档

码农公寓

清理AWR统计数据

处理LOB

进一步操作

相关文章