ENABLE MMON AUTO PURGE TRACE IN ORACLE
In order to troubleshoot sysaux tablespace growth issues, MMON auto purging activity should be monitored and traced to further identify the root cause of auto purge fault.
To enable trace for MMON process for monitoring the auto purging mechanism of cleaning up SYSAUX tablespace, first execute dbms_monitor package
kish@exdbx<>BEGIN
dbms_monitor.serv_mod_act_trace_enable
(service_name=>'SYS$BACKGROUND',
module_name=>'MMON_SLAVE',
action_name=>'AWR Auto-Purge Slave Action',
waits => true,
binds => true);
END;
/
PL/SQL procedure successfully completed.
Check if the trace is enabled
kish@exdbx<>col PRIMARY_ID for a20
kish@exdbx<>col QUALIFIER_ID1 for a20
kish@exdbx<>col QUALIFIER_ID2 for a20
kish@exdbx<>select * from dba_enabled_traces;
TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS PLAN_STATS INSTANCE_NAME
--------------------- -------------------- -------------------- -------------------- ----- ----- ---------- ----------------
SERVICE_MODULE_ACTION SYS$BACKGROUND MMON_SLAVE AWR Auto-Purge Slave TRUE TRUE FIRST_EXEC NULL
Action
To disable MMON trace
kish@exdbx<>
BEGIN
dbms_monitor.serv_mod_act_trace_disable
(service_name=>'SYS$BACKGROUND',
module_name=>'MMON_SLAVE',
action_name=>'AWR Auto-Purge Slave Action'
);
END;
/
kish@exdbx<> 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
Trace file name should be looking similar to the below for MMON slaves.
-rw-r-----. 1 oracle oinstall 4370 Oct 9 16:28 sqldb_m003_30865.trm
-rw-r-----. 1 oracle oinstall 43373 Oct 9 16:28 sqldb_m003_30865.trc
-rw-r-----. 1 oracle oinstall 2167 Oct 9 16:29 sqldb_m006_39528.trm
-rw-r-----. 1 oracle oinstall 17322 Oct 9 16:29 sqldb_m006_39528.trc
-rw-r-----. 1 oracle oinstall 1565 Oct 9 16:29 sqldb_m005_42308.trm
-rw-r-----. 1 oracle oinstall 8958 Oct 9 16:29 sqldb_m005_42308.trc
Most probably, errors like ORA-32751 occur in the trace files which happen due to high CPU usage in the database.