背景：某客户反应其核心库SYSAUX突然暴增，每小时30g左右

环境：oracle 19.8 RAC  CentOS 7

1.查看sysaux占用空间分布

SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES,SCHEMA_NAME FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC;

OCCUPANT_NAME                  SPACE_USAGE_KBYTES

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

LOGMNR                                  316323072

SM/AWR                                     24678208

SM/OPTSTAT                               1696960

SM/ADVISOR                                 274816

SDO                                        198848

SM/OTHER                                   123200

XDB                                         70016

AO                                          42432

JOB_SCHEDULER                               21376

ORDIM/ORDDATA                               16640

WM                                           6720

看出占用最大空间的是LOGMNR，而且一直在快速增长

2.再继续查看是哪个段

select owner,SEGMENT_NAME,BYTES/1024/1024 size_M from dba_segments where OWNER='SYSTEM' ORDER BY 3

OWNER      SEGMENT_NAME                       SIZE_M

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

SYSTEM     LOGMNR_LOG$_FIRST_CHANGE#              19

SYSTEM     LOGMNR_LOG$_PURGE_IDX2                 31

SYSTEM     LOGMNR_LOG$_PK                         48

SYSTEM     LOGMNR_LOG$_PURGE_IDX1                 80

SYSTEM     LOGMNR_LOG$                           120

SYSTEM     SYS_IL0000001454C00009$$              165

SYSTEM     LOGMNR_RESTART_CKPT$_PK             13002

SYSTEM     SYS_LOB0000001454C00009$$           27668

SYSTEM     LOGMNR_RESTART_CKPT$               258691

可以看出LOGMNR_RESTART_CKPT$这个占了258个g，而这个是记录capture相关东西的

3.查看capture的保留时间

select CAPTURE_NAME,CHECKPOINT_RETENTION_TIME from dba_capture;

这里查出来retention_time是7天

4.查看记录频率参数

select name ,value from sys.streams$_process_params where name='_CHECKPOINT_FREQUENCY';

查出来是默认的10，也就是说redo每产生10m的活动就记录一次logminer

查看mos，有如下说明，详细看Doc ID 735071.1

SYMPTOMS

Streams Capture setup is defined and two symptoms have been observed:

1) system.logmnr_restart_ckpt$ table grows daily and the number of rows in the table just grow

2) Excessive archive log generated in this database, when starting Capture

CAUSE

Observe if the value of capture parameter _checkpoint_frequency is below 10, which is the default value for this parameter.

This parameter control how often the logminer session associated to the capture is going to do a logminer checkpoint, so having a value of 10 means that after 10Mb of redo activity we do a logminer checkpoint.

This causes lots of entries on table system.logmnr_restart_ckpt$ and causes that the streams tables grows, also this causes delays on the streams performance.

SOLUTION

In order to conduct this database to a normal situation, please do:

1) Increase Capture parameter _CHECKPOINT_FREQUENCY to 1000 on EMISDC database, by doing:

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

处理过程：如上说明先考虑将CHECKPOINT_RETENTION_TIME改为3天，_CHECKPOINT_FREQUENCY改为500

设置保留时间

exec dbms_capture_adm.alter_capture(capture_name=>'OGG$CAP_XXX_XXXX',CHECKPOINT_RETENTION_TIME=>3);

设置检查点频率

exec dbms_capture_adm.set_parameter('OGG$CAP_RR_RRRX','_CHECKPOINT_FREQUENCY','500');

设置完成后，SYSAUX表空间的增长显著下降，统计了两小时在0.25g左右。

处理过程遇到问题：

1.OGG$CAP_ZZZ_ZZZX停不了

ERROR at line 1:

ORA-26672: timeout occurred while stopping GoldenGate process OGG$CAP_ZZZ_ZZZX

ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 1399

ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 106

ORA-06512: at line 1

解决办法：不需要停止capture，直接可在线设置检查点频率

补充：

1.查看某capture的检查点频率值

select p.name parameter,

    p.value,

    p.user_changed_flag,

    p.internal_flag

from sys.streams$_process_params p,

    sys.streams$_capture_process c

where p.process# = c.capture#

and c.capture_name = 'OGG$CAP_WWW_WWWX'

and p.name like '\_%' escape '\'                

order by parameter;

PARAMETER            VALUE      USER_CHANGED_FLAG INTERNAL_FLAG

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

_ACK_INTERVAL        5                          0             1

_APPLY_BUFFER_ENTRIES 1000                       0             1

_APPLY_UNRESPONSIVE_ 300                        0             1

SECS

_CHECKPOINTS_PER_DAY 4                          0             1

_CHECKPOINT_FORCE    N                          0             1

_CHECKPOINT_FREQUENCY 500                        1             1

2.查看一般隐含参数

SELECT ksppinm, ksppstvl, ksppdesc

  FROM x$ksppi x, x$ksppcv y

 WHERE x.indx = y.indx

   AND ksppinm = '_db_block_hash_buckets';

3.drop capture

可以查看 Doc ID 787832.1

4.删除某表空间没有数据的数据文件

-----查看哪些表空间基本没有数据文件

SELECT

  　　b.file_name ,

  　　b.tablespace_name ,

  　　b.bytes         / 1024 / 1024 total_size ,

  　　(b.bytes        - SUM(NVL(a.bytes, 0))) / 1024 / 1024 use_size,

  　　substr((b.bytes - SUM(NVL(a.bytes, 0))) / (b.bytes) * 100, 1, 5) use_per

  　　from dba_free_space a,

  dba_data_files b 　　where a.file_id = b.file_id 　and b.tablespace_name=

  'SYSAUX'　group BY b.tablespace_name,

  b.file_name,

  b.bytes 　　order BY b.tablespace_name;

  ----删除表空间的没有数据的数据文件，其实在删除的时候如果有文件的话会提示不能删

  alter tablespace SYSAUX drop datafile '/u01/app/oracle/oradata/xmc/sysaux02.DBF';