Monday, 28 November 2016

Emergency Monitoring in Oracle Enterprise Manager Cloud Control 12c

The Emergency Monitoring feature of Enterprise Manager Cloud Control 12c is an evolution of Memory Access Mode from Grid Control 11g, allowing you to access in-memory statistics. In EM12c you no longer need to explicitly enable memory access mode. Just navigate to the page and wait while it makes a diagnostic connection to the database and performs lightweight analysis. The screen contains information that will help analyze performance issues, even if the database is hung and you can't connect to the normal performance pages.

Emergency Monitoring is listed as requirement for the 1Z0-060 "Upgrade to Oracle Database 12c" OCP upgrade exam, hence this short overview for those who don't use Cloud Control.
  • Emergency Monitoring
  • Command Line
Emergency Monitoring

If you are struggling to connect to the main performance pages for the target database, you can use emergency monitoring. The "Emergency Monitoring" menu option is available under the "Performance" section once you've navigated to the target database.

Emergency Monitoring in Oracle Enterprise Manager Cloud Control 12c

The resulting screen shows "Host : Runnable Processes", "Average Active Sessions", "I/O" and "Hang Analysis".

Emergency Monitoring in Oracle Enterprise Manager Cloud Control 12c

Use this information to determine your next course of action.

Command Line

You don't need Cloud Control to perform emergency monitoring. Once connected to the database, you can query views such as V$SESS_TIME_MODEL, V$SESSTAT, V$SQLSTATS, V$ACTIVE_SESSION_HISTORY, DBA_BLOCKERS, DBA_WAITERS etc.

Hang analysis can be performed using oradebug hanganalyze.

$ sqlplus / as sysdba

SQL> oradebug hanganalyze 3

On versions prior to 11g, you can run hanganalyze from a preliminary connection, which may help if you are trying to connect to a hung database, so a normal connection is not possible.

$ sqlplus -prelim / as sysdba

SQL>  oradebug hanganalyze 3

From 11g onward, this method of hanganalyze will still appear to work, but the resulting trace file will include an error message.

$ sqlplus -prelim / as sysdba

SQL> oradebug setospid <os_pid>
SQL> oradebug dump hanganalyze 3