When it comes to managing large databases with high availability and scalability, Oracle Real Application Clusters (RAC) stands out as a powerful solution. At the heart of Oracle RAC’s capability to ensure data consistency across multiple instances lies the Global Cache Service (GCS). But what exactly is GCS, and how does it work? Let’s dive into the details with a practical example.

The Role of Global Cache Service

In an Oracle RAC environment, the GCS is responsible for maintaining cache coherency across all instances. This means that it ensures that when one instance modifies a data block, other instances see the latest version of that block. The GCS coordinates access to data blocks, preventing conflicts and ensuring that every instance has a consistent view of the data.

Scenario: Two-Node Oracle RAC

Imagine a simple Oracle RAC setup with two nodes, Node 1 and Node 2, both accessing the same database. Each node has its own buffer cache where data blocks are temporarily stored during operations.

Example Process

Let’s walk through a typical scenario involving data access and modification across the nodes.

1. Initial State

• A data block, B1, resides on disk.
• Neither Node 1 nor Node 2 initially has B1 in their buffer cache.

2. Accessing Data Block

• Node 1 executes a query:

SELECT * FROM employees WHERE employee_id = 101;

• Node 1 reads B1 from disk into its buffer cache.
• Node 1 now holds an exclusive lock on B1, allowing it to modify the block.

3. Modifying Data Block

• Node 1 updates the data:

UPDATE employees SET salary = salary + 1000 WHERE employee_id = 101;

• The block B1 is modified in Node 1’s cache and marked as dirty (i.e., it has changes not yet written to disk).

4. Another Instance Requests the Same Block

• Node 2 runs a query:

SELECT * FROM employees WHERE employee_id = 101;

• Node 2 requests B1.
• The GCS detects that Node 1 has the latest version of B1.

5. Cache Fusion Operation

• Instead of reading the outdated block from disk, the GCS initiates a cache fusion.
• Node 1 sends the updated B1 directly from its buffer cache to Node 2.
• Node 1’s lock on B1 is downgraded to a shared lock, allowing Node 2 to also access the block.

6. Maintaining Consistency and Coherency

• Node 2 receives the updated B1 and can now read or modify it.
• If Node 2 modifies B1, it gains an exclusive lock, ensuring Node 1 knows it no longer has the latest version.

Detailed Example with SQL Operations

Let’s bring this scenario to life with specific SQL operations on a table named employees.

Node 1:SELECT * FROM employees WHERE employee_id = 101;

• Node 1 fetches B1 from disk.

Node 1:UPDATE employees SET salary = salary + 1000 WHERE employee_id = 101;

• Node 1 modifies B1.

Node 2:SELECT * FROM employees WHERE employee_id = 101;

• Node 2 requests B1.
• GCS initiates cache fusion, and Node 1 sends the updated B1.

Node 2:UPDATE employees SET salary = salary + 500 WHERE employee_id = 101;

• Node 2 modifies B1.

The Magic of GCS: Ensuring High Availability and Scalability

Through the orchestration of the GCS, Oracle RAC ensures that:

• The most recent version of a data block is always accessed.
• Data integrity is maintained across all nodes.
• High availability and scalability are achieved without compromising data consistency.

This seamless coordination provided by the GCS allows Oracle RAC to manage large-scale databases efficiently, making it a robust solution for enterprises that demand high performance and reliability.

Understanding the intricacies of the GCS helps us appreciate the sophisticated mechanisms that keep Oracle RAC systems running smoothly, even under heavy loads and complex transactional requirements.