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
************************************************************************************************
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