Tablespace SYSMGMTDATA Is Full in GIMR Database (-MGMTDB)

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.

One thought on “Tablespace SYSMGMTDATA Is Full in GIMR Database (-MGMTDB)”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.