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');

logmnr_restart_ckpt$表太大

在oracle的Streams服务中，mining程序会定期检查自身，以便更快地重新启动，这些检查点信息默认保存在 SYSAUX 表空间中。从 Oracle 10.2 开始，logmnr_restart_ckpt$ 的清除工作将由oracle自动完成，有一个capture参数 checkpoint_retention_time（检查点保留时间）可以决定清洗发生的频率。

减小增加频率
观察capture参数 _checkpoint_frequency 的值是否低于 10（这是该参数的默认值）。该参数控制与capture相关联的日志分析器会话多久进行一次日志分析器检查点，因此值为 10 意味着在 10Mb 的重做活动后进行一次日志分析器检查点。这将导致 system.logmnr_restart_ckpt$ 表中出现大量条目，并导致数据流表增长，同时也会导致数据流性能延迟。

增加_CHECKPOINT_FREQUENCY 参数至1000，

1
2
3
4
5
6
7
8
9
10
11
12
13
exec dbms_capture_adm.stop_capture ('CAPTURE');
begin

    dbms_capture_adm.set_parameter

         ('CAPTURE','_CHECKPOINT_FREQUENCY','1000');

end;

/


exec dbms_capture_adm.start_capture ('CAPTURE');

减小高水位

将检查点保留时间改为较小的值，以清除元数据：

1
2
3
4
5
6
7
exec dbms_capture_adm.alter_capture(capture_name =>'<name> ',CHECKPOINT_RETENTION_TIME=><days>);
exec dbms_capture_adm.alter_capture(capture_name => ' ',CHECKPOINT_RETENTION_TIME=>)；

For example :-

exec dbms_capture_adm.alter_capture(capture_name =>'STRMADMIN_CAPTURE ',CHECKPOINT_RETENTION_TIME=>7);
exec dbms_capture_adm.alter_capture(capture_name =>'STRMADMIN_CAPTURE ',CHECKPOINT_RETENTION_TIME=>7)；

这里我们将保留时间设置为 7 天。 表 logmnr_restart_ckpt$ 中的任何数据超过 7 天的将被自动清除。

然后，你可以使用 shrink 命令手动释放闲置空间。

1
2
3
4
5
6
alter table system.LOGMNR_RESTART_CKPT$ enable row movement;
alter table system.LOGMNR_RESTART_CKPT$ shrink space ;
alter table system.LOGMNR_RESTART_CKPT$ modify lob (CKPT_INFO) (shrink space);
alter table system.LOGMNR_RESTART_CKPT$ disable row movement;

alter index <index name> shrink space;

可能会导致部分对象失效，需要重新编译

1
SQL> @?/rdbms/admin/utlrp.sql

重新查看 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日
许可协议