10: Monitoring Oracle RAC Environments

Optimizing High Availability in Oracle RAC Monitoring

Monitoring Oracle RAC (Real Application Clusters) is crucial for ensuring high availability, optimizing performance, balancing loads, and proactively detecting issues. In this chapter, we will explore different monitoring techniques using Oracle tools and commands.

---

1. Using Oracle Enterprise Manager (OEM) for RAC Monitoring

Oracle Enterprise Manager (OEM) provides a comprehensive graphical interface to monitor and manage Oracle RAC databases. It offers real-time performance metrics, system health indicators, and alert mechanisms to ensure smooth operation.

• Accessing RAC Database in OEM: Once RAC is set up in OEM, you can navigate to the Cluster Database view, where you’ll find performance and workload information for each node.Steps to Access RAC Monitoring:
  • Log in to OEM and go to Targets > Databases > Cluster Database.
  • You can view Performance Overview, which shows:
    • Active sessions per instance.
    • Cache Fusion and global waits.
    • CPU and memory utilization per node.
  • Generating AWR Report from OEM:
    1. In OEM, go to Performance > Automatic Workload Repository.
    2. Select the database and time period for which you want the report.
    3. Generate the AWR report to analyze system performance, wait events, and resource usage.

---

2. Cluster Health Monitor (CHM) and Trace Files

The Cluster Health Monitor (CHM) is a utility that monitors the health of Oracle RAC components. It collects and analyzes system data like CPU, memory, and network performance across the cluster nodes.

Checking CHM Status:

crsctl status resource ora.crf -init

Viewing CHM Logs: CHM trace files are stored in the Grid Infrastructure logs directory:

tail -f $ORACLE_BASE/crf/log/<hostname>/chm.log

• CHM Monitors:
  • CPU and memory utilization across RAC nodes.
  • Network interconnect performance.
  • Disk I/O statistics.
• Diagnosing Issues with CHM:
  Trace files in CHM can help identify bottlenecks in the RAC environment by providing detailed system metrics over time. Issues like network congestion, memory bottlenecks, or high disk I/O can be identified through these logs.

---

3. Monitoring RAC Performance and Load Balancing

Performance Monitoring in RAC involves checking session distribution across nodes, monitoring inter-node data transfers (Cache Fusion), and identifying waits that impact performance.

Query to Monitor Active Sessions Across RAC Nodes:

SELECT inst_id, COUNT(*) AS active_sessions
FROM gv$session
WHERE status = 'ACTIVE'
GROUP BY inst_id;

Monitoring Cache Fusion Latency: Cache Fusion allows RAC nodes to share data blocks between them, and excessive latency in this communication can degrade performance.

SELECT inst_id, event, total_waits, time_waited
FROM gv$system_event
WHERE event LIKE 'gc%';

Look for events like gc current block busy, which indicates contention or slow block transfers between instances.

Monitoring Server-Side Load Balancing: Oracle RAC provides load balancing to distribute workloads across RAC nodes automatically.

Connection Load Balancing is monitored through the listener logs:

tail -f $ORACLE_BASE/diag/tnslsnr/<hostname>/listener/trace/listener.log

Server-Side Load Balancing ensures that incoming connections are distributed evenly across available RAC instances.

---

4. Tools and Scripts for Monitoring RAC (e.g., crs_stat, racdiag.sql)

Several command-line tools are available for monitoring Oracle RAC components and performance.

crs_stat:
This command provides the status of Clusterware resources, such as RAC instances, listeners, and services.

crs_stat -t

Example output displays the status of cluster services (ONLINE/OFFLINE) across RAC nodes.

srvctl:
Use srvctl to check the status and manage Oracle RAC instances and services.

srvctl status database -d <db_name>
srvctl status instance -d <db_name> -i <instance_name>

racdiag.sql:
This diagnostic script provides a report of RAC-specific wait events and performance metrics. It helps identify problems like slow interconnect performance or excessive cache block contention.

Running racdiag.sql:

$ORACLE_HOME/rdbms/admin/racdiag.sql

oswatcher:
This Oracle tool captures OS-level statistics such as CPU, memory, and network usage over time. It can be particularly useful in identifying resource bottlenecks affecting RAC performance.

oswatcher.sh --start

5. Proactive RAC Monitoring and Performance Management

Proactive monitoring involves setting up automated alert systems, configuring monitoring scripts, and regularly reviewing performance reports.

• Set Threshold Alerts in OEM:
  OEM allows you to configure alerts for important RAC metrics such as CPU usage, memory consumption, and cache fusion waits.
  • Go to Monitoring > Metric and Collection Settings.
  • Set thresholds for CPU utilization, session wait events, or global cache coherency metrics.
  Example:
  Set an alert for CPU usage to trigger when utilization exceeds 85%.
  • Navigate to Cluster Database > Monitoring > Alerts.
  • Configure thresholds and set email alerts for warnings and critical conditions.
• Using crsctl for Resource Monitoring:
  Oracle Clusterware can be monitored using crsctl to check the status and availability of resources.

crsctl status resource -t

Restart a Cluster Resource: If a resource goes down, you can restart it

crsctl start resource ora.racdb.db

Monitoring Interconnect Performance:
Regularly review network interconnect performance to prevent communication delays between RAC nodes.

• Use the ifconfig command to check network interface statistics.

ifconfig eth0

Use netstat to check for network errors or dropped packets.

netstat -i

Tuning and Proactive Performance Management:
Regularly run AWR and ADDM reports to identify performance bottlenecks, including long-running SQL queries, excessive wait events, or slow Cache Fusion.

Generate an AWR Report:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Analyze ADDM Findings: ADDM can help you identify tuning recommendations for the RAC system.

@$ORACLE_HOME/rdbms/admin/addmrpt.sql

Conclusion

Monitoring Oracle RAC is crucial for identifying and resolving performance issues, ensuring workload distribution, and maintaining high availability. By using Oracle Enterprise Manager (OEM), Cluster Health Monitor (CHM), and command-line tools like srvctl, crsctl, and racdiag.sql, administrators can proactively manage RAC performance.