10 Oracle RAC Interview Questions and Answers

Oracle Real Application Clusters (RAC) is a critical technology for businesses that require high availability, scalability, and robust database performance. By allowing multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, Oracle RAC ensures that applications can continue to run even if one server fails. This makes it an essential skill for database administrators and IT professionals working in environments where downtime is not an option.

This article provides a curated selection of interview questions designed to test your knowledge and understanding of Oracle RAC. Reviewing these questions will help you prepare effectively for your interview, ensuring you can demonstrate your expertise and problem-solving abilities in this specialized area.

Oracle RAC Interview Questions and Answers

1. Describe the role of the Clusterware.

Clusterware is a component in Oracle Real Application Clusters (RAC) that manages nodes and resources within the cluster, ensuring availability and scalability. It provides the infrastructure for node communication, resource management, and cluster health maintenance.

Key roles of Clusterware include:

• Node Membership: Tracks and manages nodes in the cluster.
• Resource Management: Allocates and deallocates resources like virtual IPs and database instances.
• Failure Detection and Recovery: Monitors node health and initiates recovery to minimize downtime.
• Inter-node Communication: Facilitates efficient communication between nodes.
• Load Balancing: Distributes workload across nodes for optimized performance.

2. What are the different types of interconnects used?

In Oracle RAC, interconnects facilitate node communication. Types include:

• Private Interconnect: Dedicated network for node communication, ensuring fast and reliable data transfer.
• Public Interconnect: Used for client connections and general network traffic.
• Virtual Interconnect: Simulates private interconnects in virtualized environments.

3. Write a query to check the status of all instances.

To check the status of all instances in an Oracle RAC environment, use the following SQL query:

SELECT INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME, STATUS
FROM GV$INSTANCE;

This query returns the instance number, name, host, and status for each instance in the cluster.

4. How do you handle split-brain scenarios?

Split-brain scenarios in Oracle RAC occur when nodes lose communication, leading to potential data inconsistency. Oracle RAC handles these scenarios using:

• Voting Disk: Determines active nodes by requiring access to more than half of the voting disks.
• Clusterware: Monitors node health and takes corrective actions if needed.
• Network Heartbeat: Monitors node communication; nodes failing to send heartbeats are assumed down.
• Disk Heartbeat: Ensures nodes access shared storage correctly, adding protection against split-brain.

5. Explain how Cache Fusion works.

Cache Fusion in Oracle RAC maintains data consistency across nodes by transferring data blocks directly between node memories, bypassing disk writes. Managed by the Global Cache Service (GCS) and Global Enqueue Service (GES), it involves:

• GCS checks if a requested data block is held by another node.
• If found, GCS coordinates direct memory transfer to the requesting node.
• GES maintains locks and latches to ensure data consistency.

This reduces latency associated with disk I/O, enhancing RAC performance.

6. What are the steps to perform rolling patch upgrades?

Rolling patch upgrades in Oracle RAC allow patch application with minimal downtime. Steps include:

1. Pre-Upgrade Preparation:

• Review patch documentation and prerequisites.
• Backup the database and configuration files.
• Verify current environment and patch levels.

2. Patch Application:

• Apply the patch to one node at a time.
• Stop the Oracle instance on the node to be patched.
• Apply the patch using the appropriate tool (e.g., OPatch).
• Restart the Oracle instance on the patched node.
• Verify node functionality and rejoin it to the cluster.

3. Rolling Through Nodes:

• Repeat the process for each remaining node.
• Ensure at least one node remains operational.

4. Post-Upgrade Tasks:

• Verify patch application on all nodes.
• Perform required post-patch steps.
• Conduct testing to ensure cluster functionality.

7. Write a query to find the top 5 resource-consuming sessions.

To find the top 5 resource-consuming sessions in an Oracle RAC environment, use the following SQL query:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    SUM(ss.value) AS total_resource_consumption
FROM
    v$session s
JOIN
    v$sesstat ss ON s.sid = ss.sid
JOIN
    v$statname sn ON ss.statistic# = sn.statistic#
WHERE
    sn.name IN ('CPU used by this session', 'physical reads', 'logical reads')
GROUP BY
    s.sid, s.serial#, s.username, s.program
ORDER BY
    total_resource_consumption DESC
FETCH FIRST 5 ROWS ONLY;

8. Explain the concept of SCAN (Single Client Access Name).

SCAN (Single Client Access Name) in Oracle RAC provides a single name for clients to access a database in a cluster, simplifying client configuration and management. It offers:

• Simplified Client Configuration: Clients use a single name for database connections.
• High Availability: Distributes client connections across nodes, redirecting if a node fails.
• Load Balancing: Evenly distributes client connections for optimal resource use.
• DNS Resolution: Typically resolved through DNS to multiple IP addresses for SCAN listeners.

9. How do you troubleshoot node evictions?

Node evictions in Oracle RAC can result from network issues, hardware failures, or misconfigurations. Troubleshooting involves:

• Check the Clusterware Logs: Examine alert.log and crsd.log for details on node eviction events.
• Network Configuration: Verify interconnects, IP addresses, and check for network partitioning or latency.
• Disk and I/O Subsystem: Ensure shared storage accessibility and check for I/O bottlenecks or disk failures.
• Cluster Health Monitor (CHM): Use CHM for real-time cluster performance data and anomaly detection.
• Operating System Logs: Check OS logs for hardware or software errors contributing to eviction.
• Resource Limits: Ensure CPU, memory, and swap space are not exhausted.
• Patch Levels: Confirm all nodes run the same Oracle software version and patch level.

10. What is the role of Oracle ASM (Automatic Storage Management)?

Oracle ASM (Automatic Storage Management) simplifies database storage management by automating file placement and managing storage configuration dynamically. Key roles include:

• Storage Virtualization: Abstracts physical storage into logical units for easier management.
• Striping and Mirroring: Automatically stripes and mirrors data for performance and redundancy.
• Dynamic Rebalancing: Rebalances data across disks when disks are added or removed.
• Automatic File Management: Manages creation, deletion, and resizing of database files.
• Scalability: Supports large databases and scales for growing data volumes.