Database Monitoring   «Prev  Next»
Lesson 1

Routine database monitoring

Once you have a database running and in production, one of the best things you can do for yourself is to monitor proactively and routinely for potential problems. This is like doing preventative maintenance on a car. You can chose just to drive your car, never checking anything, until you find yourself stalled by the side of the road because something stopped working, or you can check under the hood every so often to see if something's about to break. It's obviously easier and less disruptive to detect and prevent problems than it is to experience them. That is true with both cars and databases. In this module, you will learn how to:
  • Learning Objectives
    1. Monitor and rename the alert log
    2. Check license compliance
    3. Monitor free space
    4. Check for objects that have no room left for growth
    5. Monitor the efficiency of the buffer cache
    6. Monitor the efficiency of the shared pool
    7. Collect statistics for your database
    8. Monitor input and output (I/O)
    9. Check for redo log buffer contention

Oracle Database Monitoring in Oracle 19c

Oracle Database Monitoring in Oracle 19c involves a combination of tools, features, and best practices to ensure the database is operating optimally and to detect potential issues before they escalate. Here are the best ways to monitor Oracle 19c databases:
  1. Use Oracle Enterprise Manager (OEM)

    Oracle Enterprise Manager is the most comprehensive tool for monitoring Oracle databases. It provides a centralized interface to monitor performance, storage, and availability.

    • Performance Hub: Offers real-time and historical performance analysis.
    • Automatic Database Diagnostics Monitor (ADDM): Identifies performance bottlenecks and provides actionable insights.
    • Alerts and Notifications: Automatically sends alerts for potential issues like tablespace usage, high CPU consumption, etc.
    • SQL Monitoring: Tracks SQL execution performance.
  2. Monitor with Automatic Workload Repository (AWR)

    AWR is an Oracle-built repository that collects, processes, and maintains performance statistics.

    • Use the DBA_HIST views to query historical data.
    • Generate AWR reports with: 
      @$ORACLE_HOME/rdbms/admin/awrrpt.sql
    • Compare reports from different time frames for trend analysis.
  3. Use Active Session History (ASH)

    ASH helps monitor active sessions in near real-time.

    • Identifies the sessions contributing to database load.
    • Diagnoses blocking sessions or long-running queries.
    • Use queries like: 
      SELECT session_id, sql_id, event, state 
FROM v$active_session_history 
WHERE sample_time > SYSDATE - INTERVAL '5' MINUTE;
  4. Leverage Oracle Cloud Infrastructure (OCI) Monitoring

    If using Oracle 19c in the cloud, OCI provides built-in monitoring capabilities.

    • Infrastructure metrics like CPU, memory, and IOPS.
    • Integration with Oracle Management Cloud (OMC) for deeper analytics.
    • Set up alarms and notifications for predefined thresholds.
  5. Enable and Monitor Real-Time SQL Monitoring

    This feature helps track and diagnose SQL execution in real-time.

    • Query views such as V$SQL_MONITOR and V$SQL_PLAN_MONITOR.
    • Enable SQL Monitoring for long-running queries by setting: 
      ALTER SYSTEM SET STATISTICS_LEVEL = ALL;
  6. Proactive Monitoring with Alerts and Thresholds

    Set thresholds for critical metrics and monitor them using views like DBA_OUTSTANDING_ALERTS.

    • Define warning and critical thresholds for metrics like tablespace usage, session limits, etc.
    • Use this query to review outstanding alerts: 
      SELECT message_type, reason, object_name, suggested_action 
FROM dba_outstanding_alerts;
  7. Monitor Using Oracle Data Guard (if applicable)

    For disaster recovery configurations, monitor Data Guard to ensure synchronization and readiness.

    • Use DGMGRL commands to monitor health: 
      SHOW CONFIGURATION;
SHOW DATABASE VERBOSE ;
  8. Monitor OS and Hardware Metrics

    Use tools like Oracle Automatic Storage Management (ASM) and Operating System Monitoring tools for disk I/O, memory, and CPU usage.

    • Use vmstat, iostat, and top for Linux environments.
    • Query database views like V$OSSTAT and V$RESOURCE_LIMIT.
  9. Log File Monitoring

    Regularly review alert logs, listener logs, and redo log statistics.

    • Check the alert log for errors: 
      tail -f $ORACLE_BASE/diag/rdbms///trace/alert_.log
    • Monitor redo logs for excessive log switches.
  10. Third-Party Tools

    If needed, integrate third-party monitoring tools for extended functionality and cross-platform environments.

    • SolarWinds Database Performance Analyzer
    • Quest Foglight for Oracle
    • Dynatrace or AppDynamics
  11. Monitor User Sessions and Locks

    Monitor user activity and troubleshoot locks or contention issues.

    • Identify blocking sessions: 
      SELECT blocking_session, session_id, sql_id, event 
FROM v$session 
WHERE blocking_session IS NOT NULL;
    • Check for long-running queries: 
      SELECT sql_id, elapsed_time, executions 
FROM v$sql 
WHERE elapsed_time > 1000000000; -- greater than 1000 seconds

Best Practices:
  • Schedule regular AWR and ADDM analysis.
  • Use database auditing to monitor changes.
  • Set up backup monitoring to ensure recovery plans are in place.
  • Ensure the database is patched with the latest updates.

The lessons in this module will highlight the most important metrics that you should be looking at regularly. Don't be the database administrator (DBA) who gets stranded by the side of the road. Monitor your database regularly. Don't let problems happen--prevent them.
In the next lesson, you will learn about items to monitor on a regular basis.