Chapter 25 – Diagnosing Oracle Database Issues

Intro

• There are two distinct sources for alert info in Oracle
  • Background processes – these create trace files and add data to alert logs. This cannot be configured.
  • Database alert system – runs from within the database and is completely configurable

The Alert Log

• As soon as an instance starts, critical messages are written to the alert log (and trace files) in the BACKGROUND_DUMP_DEST for which the OS directory must exist and the oracle user must have write permissions.
  • The format of the file is alert_<SID>.log
  • The directory can’t be the root directory
• Messages included in alert log
  • Startup and shutdown – moves from nomount to mount to open, etc.
  • All changes to physical structure of db – creating, dropping, renaming datafiles, logfiles, resizing & taking datafiles offline or online
  • Tablespace operations like DROP and CREATE; putting tablespaces into and out of hot backup mode for user backups.
  • Log switches and archives
  • Nondefault instance parameters used to start instance; alter system commands
  • There are no DML or DDL commands in the alert log.
• Common messages:
  • Checkpoint incomplete – when a checkpoint occurs and a previous checkpoint has not yet completed.
  • Unable to open file – when a problem exists with a controlfile or controlfile copy (-> MOUNT) or a problem exists with a redo log or datafile (-> OPEN)
  • Corrupt block – address of a corrupt datafile block
  • Problems with archiving – archivelog destination full
  • Deadlocks – sessions are blocking each other

Background Trace Files

• Background processes will write out data to the BACKGROUND_DUMP_DEST when they encounter problems.
• The size of the trace file is limited by MAX_DUMP_FILE_SIZE.
• All trace files write out their own file name, the version of Oracle running on the server and operating system information to the beginning of the file before writing out the trace info.

Server Generated Alerts

• Completely configurable alert system that monitors db, instance, user sessions and issues warnings when limits are reached and events occur.
• MMON (Manageablility Monitor) and MMNL (Manageablility Monitor Light) send out alerts based on configured metrics.
• Different metrics are calculated with different frequency.
  • Disk space is calculated every 10 mins
  • CPU usage is calculated every second

Alert Types

• threshold / stateful – alerts that build up over time and persist until cleared (disk space)
• nonthreshold / stateless – real-time alerts that don’t persist (snapshot too old)

The Alert System Architecture

• The instance accumulates statistics used by the alert system and writes them to disk (AWR) every hour.
• MMON issues alerts to the ALERT_QUE which is visible by many sessions via subscription.
  • The main subscriber to the ALERT_QUE is the DB EM Daemon which displays the alerts and can send out emails, if configured.
• The alert log file is completely separate from the Alert System

Viewing Alert Information

• Alert information can be viewed by querying:
  • DBA_OUTSTANDING_ALERTS
  • DBA_ALERT_HISTORY

Setting Up Notification Rules

• Can be configured through DB Control

Editing Thresholds

• Can be done via DB Control in the Manage Metrics link from the Related Links section or the dbms_server_alert package.
• Examples:
  • dbms_server_alert.tablespace_pct_full
  • dbms_server_alert.operator_ge – greater or equal
  • dbms_server_alert.object_type_tablespace

User Trace Files

• Background trace files are created automatically when a problem occurs while user trace files are created on demand.
• User tracing can be used to detect what SQL a user is running for situations where his session may be running slow.
• Shared servers and web applications may cause tracing problems because sessions are the same for many users. However, it is still possible to trace a session in Shared Server Mode by tracing the SID and SERIAL# that uniquely identify the session.

Instance Level SQL Tracing

• Can be enabled by setting the parameter SQL_TRACE to true and will be output in the USER_DUMP_DEST.
• The files will be named: <SID>_ora_<process_id>.trc
• To find the process id / username of the session that produced a trace file, you can join the V$SESSION & V$PROCESS views.
• SQL> alter system set sql_trace=true;

Session Level SQL Tracing

• Output in USER_DUMP_DEST.
• SQL> execute dbms_monitor.session_trace_enable(session_id=>162,serial_num=>14);
  • To get session_id:
    • SQL> select sid, serial# from v$session where username=’JOHN’;
• SQL> alter session set sql_trace=true; // only works if user is in SQL*Plus

Tracing with DB Control

• From the db control home page, Performance tab -> Top Consumers. You will see activity by session, module, service name, web client ID, or PL/SQL procedure.

Parameters

• BACKGROUND_DUMP_DEST [dyn] – location of the alert log
• USER_DUMP_DEST [dyn] – location of user trace files
• MAX_DUMP_FILE_SIZE [dyn] – defaults to UNLIMITED and is the max size for a dump file (does not apply to alert log)
• SQL_TRACE [dyn] – defaults to FALSE

Tables & Views

• DBA_OUTSTANDING_ALERTS – alerts yet to be cleared
• DBA_ALERT_HISTORY – alerts that have been cleared or were stateless
• DBA_EXTENTS – helps in determining block corruption
• V$ALERT_TYPES – configurable alerts
• V$SESSION – session info that can be used for determining who created user trace files
• V$PROCESS – session info that can be used for determining who created user trace files