Oracle RAC Administration Guide — Scripts & Monitoring for Real Application Clusters

Oracle RAC Administration Guide

Essential scripts and monitoring practices for Oracle Real Application Clusters (RAC) environments. RAC scripts use GV$ (Global V$) views to query across all instances.

---

RAC Instance Monitoring

Instance Status and Health

Script	Purpose
gvinst.sql	RAC instance status across all nodes
db.sql	Database overview and status
health.sql	Comprehensive health check suite

Session Monitoring Across Nodes

Script	Purpose
gvsess.sql	Active sessions across all RAC instances
gvsessa.sql	Active sessions with SQL and wait events
gvsessab.sql	Session activity analysis
gvsessactive.sql	Session count summary by machine and user
gvsessw.sql	Current session waits excluding idle events
gvuserall.sql	All user sessions across instances

Connection Distribution

Script	Purpose
pgmcnt.sql	Program connection count
pgmcnts.sql	Program count by instance
pgmcntu.sql	Program count by username
pgmcntm.sql	Program count by module

---

RAC Performance Analysis

Cross-Instance Performance

Script	Purpose
gvcpu.sql	CPU usage across all RAC instances
gvio.sql	I/O statistics across RAC nodes
gvlatch.sql	Latch statistics across instances
gvbp.sql	Buffer pool statistics per instance
gvpga.sql	PGA memory across all instances
SGAComponentsRAC.sql	SGA component sizes per instance

RAC-Specific Wait Events

Key wait events in RAC environments:

Wait Event	Description	Investigation
gc buffer busy acquire	Block transfer between instances	Check global cache transfer rates
gc buffer busy release	Waiting for block transfer to complete	Check interconnect bandwidth
gc cr/current block busy	Remote instance holding block	Reduce cross-instance block requests
gc cr/current grant 2-way	Block grant from remote instance	Normal RAC overhead

Scripts for interconnect analysis:

• gvcrrec.sql — Current block receive analysis
• gvsessev9_bo.sql — Session events analysis

SQL Analysis Across Instances

Script	Purpose
gvsqltop.sql	Top SQL statements across all instances
gvsqltopunb.sql	Top unbound SQL statements
gvsqlplanfind.sql	SQL plan finder across instances
gvplanid.sql	Execution plan analysis
gvbindcap.sql	Bind variable capture

---

RAC Lock Management

Locks in RAC span all instances, making diagnosis more complex.

Script	Purpose
gvlock.sql	Lock analysis across all RAC instances
gvlockb.sql	Blocking locks across instances
vlockobj.sql	Locked objects identification
vlockb2.sql	Blocking and waiting locks
gvtrans.sql	Transaction analysis across nodes

Key considerations:

• A session on Instance 1 can block a session on Instance 2
• Use gvlock.sql instead of vlock.sql to see the full picture
• Include INST_ID in lock queries to identify which node holds locks

Related errors:

• ORA-00054: Resource Busy
• ORA-00060: Deadlock Detected

---

RAC Diagnostics

Comprehensive RAC Diagnostic

• racdiag_9i.sql — RAC comprehensive diagnostic report

This script collects:

• Instance status and configuration
• Interconnect statistics
• Global cache performance
• Cluster wait events
• Resource usage per node

Streams and Replication

Script	Purpose
gvscapture.sql	Streams capture status
streams_apply_lag.sql	Streams apply lag monitor
streams_capture_lag.sql	Capture lag history
capture_latency.sql	Capture latency analysis
apply_lag.sql	Apply lag monitoring

Log and Archive Management

Script	Purpose
gvlogfreq.sql	Redo log switch frequency per instance
vlog.sql	Redo log file status
varchlog.sql	Archive log gap analysis
vthread.sql	Redo log thread information

---

RAC Storage (ASM)

Most RAC environments use ASM for shared storage.

Script	Purpose
gvasmdsk.sql	ASM disk performance across nodes
vasmdsk.sql	ASM disk information
vasmdskg.sql	ASM disk group information
vasm_disk.sql	ASM disk performance metrics
vasmdgrp.sql	ASM disk group capacity analysis
vasmdsumsz.sql	ASM disk group summary

---

RAC Monitoring Best Practices

Daily Checks

1. Instance status — Run gvinst.sql to verify all nodes are up
2. Session balance — Run pgmcnts.sql to check even distribution
3. Wait events — Run gvsessw.sql for wait event spikes
4. Lock contention — Run gvlockb.sql for blocking chains
5. Space — Run ddbspacea.sql for tablespace usage

Weekly Checks

1. Performance trends — Compare AWR reports across nodes
2. Interconnect — Check global cache statistics
3. Log switches — Run gvlogfreq.sql for redo log patterns
4. ASM capacity — Run vasmdgrp.sql for disk group usage

Key RAC Parameters to Monitor

Parameter	Description	Check With
CLUSTER_DATABASE	RAC enabled	SHOW PARAMETER cluster
INSTANCE_NUMBER	Node identifier	SHOW PARAMETER instance
CLUSTER_INTERCONNECTS	Private interconnect	SHOW PARAMETER interconnect
REMOTE_LOGIN_PASSWORDFILE	Password file sharing	SHOW PARAMETER remote

---

Related Resources

• Performance Tuning Checklist — Systematic tuning methodology
• Oracle Troubleshooting Guide — Diagnostic approach by problem type
• Performance Analysis Scripts (171 tools) — Complete script library
• ASM Analysis Scripts — ASM storage scripts
• Oracle Database Health Check — Comprehensive health assessment