Oracle 11g 清理SYSAUX表空间

收到告警一个11g的库sysaux表空间不足，上去检查看到使用率达到了95%，所以接下来就说下如何处理这种情况。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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_BYTES
            FROM  SYS.DBA_FREE_SPACE
            GROUP  BY TABLESPACE_NAME) F,
            (SELECT DD.TABLESPACE_NAME,
                    Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
            FROM  SYS.DBA_DATA_FILES DD
            GROUP  BY DD.TABLESPACE_NAME) D
    WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME
    and D.tablespace_name = 'SYSAUX';

表空间名    表空间大小(M)    已使用空间(M)    使用比 空闲空间(M) 最大块(M)
SYSAUX  32751   31388.75      95.84%    1362.25 1006

在oracle数据库中SYSAUX被认为是SYSTEM表空间的辅助表空间，作为oracle的默认表空间来支持oracle的各种产品和特性，从而降低对system表空间的压力。

SYSAUX表空间的限制

  • SYSAUX只能由一个数据文件组成
  • 无法对SYSAUX表空间采用类似(PERMANENT, READ WRITE,EXTENT MANAGMENT LOCAL,SEGMENT SPACE MANAGMENT AUTO)的ALTER TABLESPACE操作
  • SYSAUX表空间不能被RENAME或DROP

什么占用了SYSAUX表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
sys@> select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants;

OCCUPANT_NAME                                                    OCCUPANT_DESC                                                    SPACE_USAGE_KBYTES
---------------------------------------------------------------- ---------------------------------------------------------------- ------------------
LOGMNR                                                           LogMiner                                                                      13696
LOGSTDBY                                                         Logical Standby                                                                1408
SMON_SCN_TIME                                                    Transaction Layer - SCN to TIME mapping                                        3328
PL/SCOPE                                                         PL/SQL Identifier Collection                                                   1600
STREAMS                                                          Oracle Streams                                                                 1024
AUDIT_TABLES                                                     DB audit tables                                                                 320
XDB                                                              XDB                                                                          129984
AO                                                               Analytical Workspace Object Table                                             39104
XSOQHIST                                                         OLAP API History Tables                                                       39104
XSAMD                                                            OLAP Catalog                                                                   5248
SM/AWR                                                           Server Manageability - Automatic Workload Repository                       30022848
SM/ADVISOR                                                       Server Manageability - Advisor Framework                                     189312
SM/OPTSTAT                                                       Server Manageability - Optimizer Statistics History                         1424768
SM/OTHER                                                         Server Manageability - Other Components                                       12416
STATSPACK                                                        Statspack Repository                                                              0
SDO                                                              Oracle Spatial                                                                76032
WM                                                               Workspace Manager                                                              3584
ORDIM                                                            Oracle Multimedia ORDSYS Components                                             448
ORDIM/ORDDATA                                                    Oracle Multimedia ORDDATA Components                                          13888
ORDIM/ORDPLUGINS                                                 Oracle Multimedia ORDPLUGINS Components                                           0
ORDIM/SI_INFORMTN_SCHEMA                                         Oracle Multimedia SI_INFORMTN_SCHEMA Components                                   0
EM                                                               Enterprise Manager Repository                                                 47168
TEXT                                                             Oracle Text                                                                    3712
ULTRASEARCH                                                      Oracle Ultra Search                                                               0
ULTRASEARCH_DEMO_USER                                            Oracle Ultra Search Demo User                                                     0
EXPRESSION_FILTER                                                Expression Filter System                                                       3712
EM_MONITORING_USER                                               Enterprise Manager Monitoring User                                             1408
TSM                                                              Oracle Transparent Session Migration User                                         0
SQL_MANAGEMENT_BASE                                              SQL Management Base Schema                                                     1728
AUTO_TASK                                                        Automated Maintenance Tasks                                                     384
JOB_SCHEDULER                                                    Unified Job Scheduler                                                         14528

很明显的看到绝大部分是AWR占用了

通过语句可以查看是哪些表占用的表空间最大

1
2
3
4
col segment_name for a50
col partition_name for a30
col segment_type for a30
select * from (select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum>=10;

查看awr的保存期限

1
2
3
4
5
sys@> select retention from dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0

也就8天时间，已经比较短了，如果时间太长可以调整时间

1
execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);  >>>>==== 间隔60分钟，保存10080分钟，也就是7*24*60

这里要注意，上面这个语句设置的时间要配合MOVING_WINDOW_SIZE参数

1
2
3
4
5
exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);

SELECT moving_window_size
FROM dba_hist_baseline
WHERE baseline_type = 'MOVING_WINDOW';

清除awr历史数据

既然设置的时间是合理的，那就只能清除历史数据了，查看最近和最久的时间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
sys@TFDW1> SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
UNION
SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)
/

   SNAP_ID BEGIN_INTERVAL_TIME                                                         END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
     16873 13-2月 -19 10.00.11.981 下午                                                13-2月 -19 11.00.15.959 下午
     16873 13-2月 -19 10.00.12.767 下午                                                13-2月 -19 11.00.16.763 下午
     17083 22-2月 -19 04.00.35.330 下午                                                22-2月 -19 05.00.41.087 下午
     17083 22-2月 -19 04.00.35.988 下午                                                22-2月 -19 05.00.41.686 下午

清除snap_id介于16873和17083的数据

1
2
3
4
BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id => 16873, high_snap_id=>17083);
END;
/

这语句执行了很长时间都没反应，最终放弃。采用另外一个办法

1
2
3
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql

其实就是把awr相关的对象重建了一遍，如果觉得风险大可以通过truncate的方式

1
2
3
4
5
6
select distinct 'truncate  table  '||segment_name||';',s.bytes/1024/1024
  from dba_segments s
  where s.segment_name like 'WRH$%'
    and segment_type in ('TABLE PARTITION', 'TABLE')
    and s.bytes/1024/1024>100
    order by s.bytes/1024/1024/1024 desc;

清除logminer数据

还有一种情况是因为logminer数据占用的sysaux表空间，一般是由于执行了一些大的dml操作，导致产生了大量的日志。目前没有一个好的办法去直接收回这些空间，但是可以将这些数据迁移到别的表空间。

1
2
SQL> connect / as sysdba
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE ('LOGMNRTS');

重新查看SYSAUX表空间使用情况

1
2
3
4
5
sys@> select * from dba_tablespace_usage_metrics where tablespace_name='SYSAUX';

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX                             267848         4194302     6.385997
Troubleshooting
Oracle 11g 清理SYSAUX表空间
https://www.xbdba.com/2019/02/22/oracle-11g-clean-sysaux-tablespace/
作者
xbdba
发布于
2019年2月22日
许可协议