Cluster Health Monitor ( CHM) tables could be fragmented in MGMTDB database.
1) Check the datafile of tablespace SYSMGMTDATA in PDB:
SQL> select PDB_NAME from dba_pdbs;
PDB_NAME
-------------
RACTEST
PDB$SEED
SQL> alter session set container=RACTEST;
Session altered.
SQL> select TABLESPACE_NAME,FILE_NAME,AUTOEXTENSIBLE,
BYTES/1024/1024,MAXBYTES/1024/1024
from dba_data_files
where TABLESPACE_NAME='SYSMGMTDATA';
TABLESPACE_NAME
---------------
FILE_NAME
----------------------------------------------------------------------
AUT BYTES/1024/1024 MAXBYTES/1024/1024
--- --------------- -------------------
SYSMGMTDATA +OCR_VOTE/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysmgmtdata.269.922884445
NO 2048 2048
2) Check CHM data in repository, and we can see the purging is working:
SQL> select trunc(BEGINTIME), count(*)
from chm.CHMOS_PROCESS_INT_TBL
group by trunc(BEGINTIME)
order by trunc(BEGINTIME);
TRUNC(BEG COUNT(*)
--------- ---------
21-FEB-17 1222178
22-FEB-17 3265667
23-FEB-17 1327167
3) Check the current CHM repository retention in seconds :
$ oclumon manage -get reppath
CHM Repository Path = +OCR_VOTE/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysmgmtdata.269.922884445
$ oclumon manage -get repsize
CHM Repository Size = 136320 seconds
4) Check the big objects in repository, they are all from two partition tables CHMOS_PROCESS_INT_TBL and CHMOS_DEVICE_INT_TBL.
SQL> select owner, segment_name,segment_type , bytes/1024/1024
from dba_segments
where owner='CHM'
and bytes/1024/1024>10;
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
---------- ---------------------- ------------------ ---------------
CHM CHMOS_PROCESS_INT_TBL TABLE PARTITION 18
CHM CHMOS_DEVICE_INT_TBL TABLE PARTITION 18
CHM CHMOS_PROCESS_INT_TBL TABLE PARTITION 18
CHM CHMOS_DEVICE_INT_TBL TABLE PARTITION 18
Solution 1 : Move GIMR repository onto to a different disk group as per “How to Move 12c Grid Infrastructure Management Repository ( GIMR ) to Another Diskgroup“.
Solution 2: Manually truncate the big objects owned by CHM as per Doc ID 2177879.1. Normally the following two tables can be truncated :
SQL> truncate table CHM.CHMOS_PROCESS_INT_TBL;
SQL> truncate table CHM.CHMOS_DEVICE_INT_TBL;
Solution 3: Resize datafile bigger. For example, increases the size from 2048M to 2560M
SQL> alter database datafile '+OCR_VOTE/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysmgmtdata.269.922884445' resize 2560m;
Database altered.
Make DBA Life Easy 