Real Time ADDM (Automatic Database Diagnostic Monitoring) in Oracle 12c

Automatic Database Diagnostic Monitoring (ADDM) is one of the ways to use if you are facing performance problems with your database to gather information. Moreover, in previous releases the ADDM is generated based on 2 AWR (Automatic Workload Repository) Report snapshots which is taken on hourly basis and retained for 8 days by default. In oracle 12c, a new enhanced feature is introduced which is Real Time-ADDM. The real time ADDM is based on information fetched from memory triggered by manageability monitor process (MMON) especially when the database is in “hanging” state.

Database statistics gathering requires that STATISTICS_LEVEL parameter is set to TYPICAL or ALL. Also, check CONTROL_MANAGEMENT_PACK_ACCESS parameter.

ADDM DB Parameters

Let us assume a scenario that your database is in “hanging” state:

sqlplus ‘/ as sysdba’  (if you can’t access then use: sqlplus -prelim “/as sysdba”)
SQL>@$ORACLE_HOME/rdbms/admin/rtaddmrpt.sql

SQL>exit

Now you have successfully generated a Real-Time ADDM, check it and see what is causing the problem (the file will be in HTML format).

Another important report you can generated is “Performance Hub Active Report” which is a comprehensive HTML report that will show different tab information such as:

Activity

Workload

ADDM findings

Resources (CPU and Memory)

I/O

This can be generated using the following query:

SQL>@$ORACLE_HOME/rdbms/admin/perfhubrpt.sql

perf hub

ORA-00604: error occurred at recursive SQL level 4 ORA-12751: cpu time or run time policy violation

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