MMON slaves fail with ORA-12751: 'Cpu Time Or Run Time Policy Violation'. This may be reported in trace and the alert log. The elapsed time of the query depends on the retention period and the interval settings of the AWR snapshots. In this case, we had a 90 days retention with a 30 minutes interval, which led to over 4000 snapshots.
while checking my database, i noticed there is a performance issue. So, to investigate further i have checked the alert log file.
(Remark: you can check the location of your alert log file using the query: select * from v$diag_info; )
the below is the contents of the log file
****************************************************************************************************
ORA-00604: error occurred at recursive SQL level 4
ORA-12751: cpu time or run time policy violation
Mon Jan 26 02:52:36 2015
Suspending MMON slave action ketbgac2_ for 82800 seconds
Mon Jan 26 03:02:37 2015
Suspending MMON slave action ketbgac1_ for 82800 seconds
Mon Jan 26 05:01:38 2015
Suspending MMON slave action kdilmclnslv_ for 82800 seconds
Mon Jan 26 06:23:43 2015
Errors in file /oraclex/oradbp61/diag/rdbms/oradbp61/oradbp61/trace/oradbp61_m00b_44433466.trc:
ORA-00604: error occurred at recursive SQL level 4
ORA-12751: cpu time or run time policy violation
Mon Jan 26 06:49:41 2015
ORA-12751: cpu time or run time policy violation
Mon Jan 26 02:52:36 2015
Suspending MMON slave action ketbgac2_ for 82800 seconds
Mon Jan 26 03:02:37 2015
Suspending MMON slave action ketbgac1_ for 82800 seconds
Mon Jan 26 05:01:38 2015
Suspending MMON slave action kdilmclnslv_ for 82800 seconds
Mon Jan 26 06:23:43 2015
Errors in file /oraclex/oradbp61/diag/rdbms/oradbp61/oradbp61/trace/oradbp61_m00b_44433466.trc:
ORA-00604: error occurred at recursive SQL level 4
ORA-12751: cpu time or run time policy violation
Mon Jan 26 06:49:41 2015
************************************************************************************************
To Resolve this:
you need to monitor and check the performance of the server where database is hosted in.
also, you can try to figure out the SQL statement that is taking a long time to process.
in my situation, when trying to establish sqlplus from within the server the, sqlplus was in state of “hanging ”
so the only solution was to forcefully shutdown the database by killing the core Oracle Process.
Unix Command:
ps –ef | grep smon