OGG相關的CPATURE導致SYSAUX表空間異常暴增處理

背景：某客戶反應其核心庫SYSAUX突然暴增，每小時30g左右

環境：oracle 19.8 RAC  CentOS 7

1.檢視sysaux佔用空間分佈

SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES 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';