Category: database

 

How Cache Fusion Works

In a typical Oracle RAC setup, each instance maintains its own local buffer cache, which stores recently accessed data blocks. However, data blocks in the buffer cache of one instance might need to be accessed or modified by other instances. This could lead to data inconsistency if not managed properly.

Cache Fusion solves this problem by allowing instances in the RAC environment to communicate and share data blocks directly through the Global Cache Service (GCS), which manages the distribution of data across all instances.

When one instance modifies a data block, the change must be propagated to other instances that might have the same data block in their buffer cache. Oracle RAC uses Cache Fusion to allow these instances to send and receive blocks from each other’s memory, rather than from disk. This significantly improves performance compared to traditional disk I/O operations.

Key Components of Cache Fusion

1. Global Cache Service (GCS):
   GCS is responsible for managing the access and consistency of shared data blocks between instances. It ensures that when one instance changes a block, other instances can either receive the latest version of the block or be notified to take appropriate action (e.g., invalidating or updating their copies).

2. Global Enqueue Service (GES):
   GES is responsible for managing locks and ensuring that data consistency is maintained when multiple instances try to access or modify the same data block. It coordinates the cache and ensures that operations such as reads, writes, and cache transfers are serialized properly.

3. Global Cache Interconnect (GCI):
   This is the physical communication mechanism that connects instances in a RAC cluster. Data blocks are transferred across this interconnect between instances. The Cache Fusion process relies on this interconnect for high-speed data sharing and consistency enforcement.

Cache Fusion Operation Flow

Here is a simplified overview of how Cache Fusion works in Oracle RAC:

1. Block Request:
   Suppose instance 1 has a data block in its buffer cache, and instance 2 requests that block. Instead of fetching the block from disk, instance 1 sends the block over the interconnect to instance 2.

2. Block Modification:
   If instance 1 modifies the data block, it must notify other instances that have a copy of the block. If instance 2 already has the block in its cache, it will receive the updated block or be invalidated and will need to refetch the updated data.

3. Consistency Maintenance:
   GCS ensures that any change to a block in one instance is visible to all other instances that have cached that block. This ensures data consistency and prevents issues like “dirty reads” across the cluster.

Types of Cache Fusion Communication

Cache Fusion uses a variety of communication methods to ensure data consistency across RAC nodes:

1. Read Request: When an instance requests a block, if the block is available in the buffer cache of another instance, it is transferred to the requesting instance through Cache Fusion. This is more efficient than reading from disk.

2. Write Request: If an instance modifies a block, the change must be propagated to all other instances that hold a copy of the same block, to maintain consistency across the cluster.

3. Invalidate: If an instance writes to a block, it must ensure that other instances that have cached the block are notified to invalidate their copies. This prevents other instances from using stale data.

4. Transfer: If an instance has a block that another instance needs, the block is transferred from the source instance’s buffer cache to the requesting instance’s buffer cache.

Advantages of Cache Fusion

• Improved Performance:
  By sharing data blocks directly between instances in memory (via Cache Fusion), Oracle RAC reduces the need for disk I/O, which improves overall performance and responsiveness in multi-instance environments.

• High Availability:
  Cache Fusion ensures that each node can access the latest data without having to wait for disk I/O, which helps in maintaining high availability, especially in the event of instance failures or high-load scenarios.

• Reduced Contention:
  Cache Fusion helps reduce contention for disk-based resources by allowing data sharing between instances at the memory level. This helps to avoid bottlenecks that might occur when multiple instances need to read or write the same data blocks from disk.

Cache Fusion and Global Cache

In Oracle RAC, the Global Cache mechanism works hand in hand with Cache Fusion. The Global Cache Service (GCS) manages the access and consistency of data blocks across instances. Whenever an instance modifies a block in its buffer cache, the change must be reflected across all instances that hold a copy of the block, and Cache Fusion is the mechanism that facilitates this communication.

Challenges in Cache Fusion

1. Network Traffic:
   Cache Fusion relies heavily on the interconnect (the network connection between RAC nodes) for block transfers. High network latency or bandwidth constraints could negatively impact performance, especially in large-scale RAC systems.

2. Cache Coherency Overhead:
   Maintaining cache coherency in a large RAC environment requires significant communication between instances, which can lead to overhead, particularly in high-transaction environments.

3. Locking and Contention:
   In cases of high contention for the same data blocks (e.g., when multiple instances frequently modify the same block), the overhead of locking and cache synchronization could degrade performance.

Cache Fusion vs. Traditional Database Clusters

In traditional database clusters (pre-RAC), each instance had to access the shared data stored on disk. In contrast, Oracle RAC’s Cache Fusion technology minimizes disk I/O by transferring data blocks across instances directly in memory, making it much faster and more efficient.

Cache Fusion in Oracle RAC is a key component of Oracle’s ability to offer high performance, high availability, and scalability in a clustered database environment. By allowing instances to share and synchronize their buffer caches over the interconnect, Cache Fusion improves the efficiency of data access and modification across multiple instances in a RAC cluster. This technology enables Oracle RAC to provide a seamless, high-performance solution for mission-critical applications running in a multi-instance cluster.

 In Oracle Real Application Clusters (RAC), several background processes are responsible for managing cluster-related tasks, ensuring high availability, and coordinating the activities of multiple instances running in the cluster. These processes are similar to the background processes in a single-instance Oracle Database, but with additional processes to handle the complexities of a clustered environment.

Here’s a list of key background processes in Oracle RAC:

1. Global Cache Service (GCS) and Global Enqueue Service (GES) Processes

• LGWR (Log Writer): As in a single-instance Oracle Database, LGWR is responsible for writing redo log entries to disk. In RAC, LGWR writes to all instances, coordinating with the Global Cache Service (GCS) to manage redo for all instances.

• LMSn (Lock Manager Slave Processes): These processes coordinate the global cache and manage locking of data blocks across all nodes in the RAC environment. For each instance, there are one or more LMS processes. They help in ensuring that the data is consistent across the cluster.

• LMD (Lock Manager Daemon): The LMD process manages the global enqueues and global locks within Oracle RAC. It ensures that all nodes in the cluster can access and update shared data blocks in a consistent manner.

• LCKn (Lock Controller Processes): These processes manage the global cache and enforce locking rules at the block level. Each node can have one or more LCK processes.

2. Oracle Clusterware (Grid Infrastructure) Processes

These are part of the Oracle Clusterware software and are involved in managing the cluster environment:

• CSS (Cluster Synchronization Service): The CSS process ensures that the various nodes in the cluster can communicate with each other and synchronize the status of all nodes. It is responsible for cluster membership and node fencing.

• CRS (Cluster Resource Services): CRS is responsible for the management of cluster resources, such as Oracle Database instances, listeners, etc. It monitors and restarts failed resources to ensure high availability.

• EVN (Event Notification): The EVN process is used for inter-node communication in Oracle RAC. It handles events related to cluster management, especially in failover or failback scenarios.

• CTSS (Cluster Time Synchronization Service): This ensures that all nodes in the RAC environment are synchronized to the same time, which is crucial for maintaining consistency in the cluster.

3. Database Instance Processes

Oracle RAC instances will also run the following standard Oracle background processes:

• DBWn (Database Writer): As in single-instance Oracle Database, the DBWn process writes dirty buffers from the buffer cache to disk. In RAC, DBWn works on the instance’s local buffer cache but coordinates with other DBWn processes in the cluster for efficient buffer management.

• PMON (Process Monitor): PMON is responsible for cleaning up resources (e.g., orphaned sessions, locks) and recovering resources for terminated sessions. In RAC, PMON ensures that failed or terminated instances are cleaned up from the cluster.

• SMON (System Monitor): SMON is responsible for performing recovery operations during instance startup (e.g., recovering from an instance crash), cleaning up temporary segments, and managing space in the database. SMON in RAC works in coordination with other instances to handle instance-level recovery.

• CKPT (Checkpoint): CKPT signals the DBWn process to perform a checkpoint, ensuring that all changes in the buffer cache are written to disk and that the database’s checkpoint position is updated.

• RECO (Recoverer): The RECO process is responsible for performing automatic recovery for distributed transactions that have failed or been interrupted. In RAC, RECO works across instances and is crucial for maintaining data integrity in distributed environments.

• ARCH (Archiver): The ARCH process is responsible for archiving redo logs to the archive destination. In RAC, each instance has its own ARCH process, which may archive redo logs to the same or different destinations.

4. Other RAC-Specific Processes

• OPN (Oracle Parallel Server) Process: In older versions of Oracle RAC (pre-10g), OPN processes were used to manage the communication between multiple instances. However, these processes have been largely replaced by the GCS and GES mechanisms in modern versions.

• GSN (Global Services Network): This is a process used in some versions to coordinate messaging and shared services across RAC instances.

5. Additional Oracle RAC Background Processes

• RMS (RAC Managed Services): These processes are responsible for the managed services that allow nodes and resources in the cluster to failover and be restarted appropriately.

• FAN (Fast Application Notification): FAN processes are used for notifications when a failure occurs in the Oracle Cluster, such as node or instance failures, and notifying client applications about these failures in real-time.

• RAC Listener Process: In RAC environments, the Oracle listener (with TNSLSNR process) can be configured for multiple instances and dynamically distribute client connections across instances.

Summary of Common RAC Background Processes:

• Global Cache Services (GCS) & Global Enqueue Services (GES) Processes: LMSn, LMD, LCKn, etc.
• Clusterware Processes: CSS, CRS, EVN, CTSS
• Instance Processes: DBWn, PMON, SMON, CKPT, RECO, ARCH
• Additional Processes: FAN, RMS, RAC Listener

Each of these processes ensures that Oracle RAC remains a highly available, scalable, and fault-tolerant system by managing resources, communication, synchronization, and instance coordination within the cluster.

 To enable Flashback in an Oracle Database, you need to perform several key steps. Flashback allows you to retrieve data that was modified or deleted and to perform time-based queries, which can be helpful for recovery, auditing, and historical analysis.

Here’s a step-by-step guide to enable Flashback in Oracle:

### 1. **Check Database Compatibility**

Flashback features require your database to be running in a compatible mode, typically Oracle 9i or later. Ensure your database is using **ARCHIVELOG** mode (Flashback won’t work if the database is running in **NOARCHIVELOG** mode).

SELECT LOG_MODE FROM V$DATABASE;

If the database is not in ARCHIVELOG mode, you need to enable it.

### 2. **Enable ARCHIVELOG Mode (if not already enabled)**

If the database is not in ARCHIVELOG mode, follow these steps to enable it:

1. **Shutdown the database**:

   SHUTDOWN IMMEDIATE;

2. **Mount the database**:

   STARTUP MOUNT;

3. **Enable ARCHIVELOG mode**:

   ALTER DATABASE ARCHIVELOG;

4. **Open the database**:

   ALTER DATABASE OPEN;

5. **Check ARCHIVELOG mode**:

   SELECT LOG_MODE FROM V$DATABASE;

Now, the database should be in ARCHIVELOG mode.

### 3. **Enable Flashback Logging**

Flashback features require Flashback Logging to be enabled in the database. To enable Flashback, ensure that the database has sufficient undo tablespace and the **flashback retention target** is set appropriately.

#### a. **Enable Flashback Logging**

Flashback logging is enabled by default in recent Oracle versions, but you can manually ensure it’s enabled by running:

ALTER SYSTEM SET UNDO_RETENTION = <time_in_seconds>;

ALTER SYSTEM SET FLASHBACK_RETENTION_TARGET = <time_in_minutes>;

For example, to set the Flashback retention to 24 hours:

ALTER SYSTEM SET FLASHBACK_RETENTION_TARGET = 1440;

#### b. **Check Flashback Status**

You can check whether Flashback is enabled using the following query:

SELECT FLASHBACK_ON FROM V$DATABASE;

If the result is `YES`, Flashback is enabled.

### 4. **Create a Flash Recovery Area (FRA)**

To enable Flashback, you typically need a Flash Recovery Area (FRA) where Oracle stores archived logs, backups, and Flashback logs. Set up the FRA by running the following command (adjust the path and size according to your requirements):

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=’/path/to/flash_recovery_area’;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=10G;

To check the FRA status:

SELECT * FROM V$RECOVERY_FILE_DEST;

### 5. **Enable Flashback on Tablespaces (Optional)**

Flashback operations may require certain tablespaces to be Flashback-enabled. You can enable Flashback on a tablespace using the following command:

If you want to disable Flashback on a tablespace, use:

ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;

### 6. **Perform Flashback Operations**

Once Flashback is enabled, you can perform various Flashback operations, such as:

– **Flashback Query** to see data as of a specific time or SCN:

  SELECT * FROM my_table AS OF TIMESTAMP TO_TIMESTAMP(‘2024-11-01 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);

– **Flashback Table** to restore a table to a previous state:

  FLASHBACK TABLE my_table TO TIMESTAMP TO_TIMESTAMP(‘2024-11-01 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);

– **Flashback Database** to restore the entire database to a previous point in time (requires the database to be in ARCHIVELOG mode and Flashback logging enabled):

  FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP(‘2024-11-01 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);

– Flashback operations require undo tablespace and the **Flashback logs** to be retained for the duration of your retention period.

– Ensure your FRA has sufficient space to store Flashback logs; otherwise, Oracle will automatically purge them as needed.

  

By following these steps, Flashback will be enabled, and you’ll be able to take advantage of time-based recovery and auditing features in Oracle.

 

The V$DATABASE view contains information about the database, including whether Flashback is enabled.

SELECT flashback_on
FROM v$database;

• If Flashback is enabled, the result will show:

  
  YES
  

• If Flashback is not enabled, it will show:

  
  NO

 

Prerequisites

Before creating a tablespace, ensure you have the following:

• Oracle Database Installed: This guide assumes Oracle Database 11g or later.
• DBA (Database Administrator) Privileges: You need to have sufficient privileges to create a tablespace.
• Sufficient Disk Space: Ensure there is enough disk space for the new tablespace.

Step-by-Step Guide to Creating a Tablespace

1. Connect to Oracle Database

First, connect to your Oracle Database using SQL*Plus or another SQL client (like Oracle SQL Developer).

sqlplus sys as sysdba

Enter your password to log in.

2. Check Existing Tablespaces

Before creating a new tablespace, it’s useful to check the existing ones. Run the following query to list the current tablespaces in your Oracle database.

SELECT tablespace_name FROM dba_tablespaces;

This will return a list of all the tablespaces in the database.

3. Create the Tablespace

To create a tablespace, use the CREATE TABLESPACE command. Below is the syntax to create a basic tablespace.

CREATE TABLESPACE <tablespace_name>
DATAFILE '<file_path>/<filename>.dbf'
SIZE <size> [K | M | G]
AUTOEXTEND ON NEXT <increment> [K | M | G]
MAXSIZE <max_size> [K | M | G];

Example:

Let’s create a tablespace called USER_DATA with a 500MB data file and enable auto-extension to handle growth:

CREATE TABLESPACE USER_DATA
DATAFILE '/u01/app/oracle/oradata/mydb/user_data01.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

• DATAFILE: Specifies the path where the tablespace data file will be stored.
• SIZE: Initial size of the data file (in this case, 500MB).
• AUTOEXTEND: Allows the data file to automatically grow when more space is needed.
• MAXSIZE: The maximum size the data file can grow to. In this example, it is unlimited.

4. Verify the Tablespace Creation

Once the tablespace is created, you can verify its existence by querying the dba_tablespaces table.

SELECT tablespace_name FROM dba_tablespaces WHERE tablespace_name = 'USER_DATA';

If the tablespace is created successfully, it will appear in the results.

5. Assign a Default Storage Location

You may want to assign the newly created tablespace as the default tablespace for users. Use the following command:

ALTER USER <username> DEFAULT TABLESPACE USER_DATA;

This command sets USER_DATA as the default tablespace for the specified user.

6. Add Datafiles (Optional)

In some cases, you may want to add additional data files to the tablespace to increase storage capacity. To do this, you can use the ALTER TABLESPACE command:

ALTER TABLESPACE USER_DATA
ADD DATAFILE '/u01/app/oracle/oradata/mydb/user_data02.dbf'
SIZE 500M;

This adds a second datafile to the USER_DATA tablespace.

Managing Tablespaces in Oracle

1. Altering a Tablespace

If you need to resize or change the properties of an existing tablespace, you can use the ALTER TABLESPACE command.

ALTER TABLESPACE USER_DATA
RESIZE 1G;

This resizes the tablespace to 1GB.

2. Dropping a Tablespace

To drop a tablespace, use the DROP TABLESPACE command. Be cautious, as this operation will remove all data associated with the tablespace.

DROP TABLESPACE USER_DATA INCLUDING CONTENTS AND DATAFILES;

• INCLUDING CONTENTS: Removes all objects within the tablespace.
• AND DATAFILES: Deletes the data files associated with the tablespace.

3. Checking Tablespace Usage

To monitor the usage of a tablespace, you can run a query on the dba_data_files and v$tablespace views:

SELECT tablespace_name, file_name, bytes/1024/1024 "Size (MB)"
FROM dba_data_files;

This query will return the current size of each tablespace in MB.