Overview of Oracle Clusterware for Oracle RAC

Oracle Clusterware provides a complete, integrated clusterware management solution on all Oracle AI Database platforms.

Guidelines for Using Oracle Clusterware

The functionality provided by Oracle Clusterware provides all of the features that are required to manage cluster databases, including node membership, group services, global resource management, and high availability functions.

You can install Oracle Grid Infrastructure independently or as a prerequisite to installing Oracle RAC. Oracle AI Database features, such as services, use the underlying Oracle Clusterware mechanisms to provide advanced capabilities. Starting with Oracle Database 21c, third-party clusterware products are no longer supported with Oracle RAC.

Oracle Clusterware is designed for, and tightly integrated with, Oracle RAC. You can use Oracle Clusterware to manage high-availability operations in a cluster. When you create an Oracle RAC database using any of the management tools, the database is registered with and managed by Oracle Clusterware, along with the other required components such as the VIP address, the Single Client Access Name (SCAN) (which includes the SCAN VIPs and the SCAN listener), Oracle Notification Service, and the Oracle Net listeners. These resources automatically start when the node starts and automatically restart if they fail. The Oracle Clusterware daemons run on each node.

Anything that Oracle Clusterware manages is known as a CRS resource. A CRS resource can be a database, an instance, a Pluggable Database (PDB), a service, a listener, a VIP address, or an application process. Oracle Clusterware manages CRS resources based on a resource's configuration information that is stored in the Oracle Cluster Registry (OCR). You can use SRVCTL commands to administer any Oracle-defined CRS resources. Oracle Clusterware provides the framework that enables you to create CRS resources to manage any process running on servers in the cluster which are not predefined by Oracle. Oracle Clusterware stores the information that describes the configuration of these components in OCR that you can administer.

Overview of Temporary Tablespaces

Oracle uses temporary tablespaces to write spill-overs to non-shared tablespaces that are created on local disks.

It is still possible for SQL operations, such as hash aggregations, sorts, hash joins, creations of cursor-duration temporary tables for the WITH clause, and star transformations to spill over to disk. The spill overs go to the tablespaces on shared disks. Management of temporary tablespaces is similar to that of existing tablespaces.

Temporary tablespaces improve tablespace management by:

Storing temp files in private storage to take advantage of local storage I/O benefits.
Avoiding expensive cross-instance temporary tablespace management.
Increased addressability of temporary tablespace.
Improving instance warm-up performance by eliminating on-disk space metadata management.

Note:

You cannot use temporary tablespaces to store database objects, such as tables or indexes.

Parallel Execution Support for Cursor-Duration Temporary Tablespaces

The temporary tablespaces that are created using the WITH clause and star transformation exist in the temporary tablespace on shared disk.

A set of parallel query child processes loads the intermediate query results into these temporary tablespaces, which are then read by a different child processes. There is no restriction on how these child processes reading these results are allocated, because any parallel query child process on any instance can read the temporary tablespaces residing on the shared disk.

For read-write and read-only instance architecture, when the parallel query child processes load the intermediate results to the temporary tablespaces of these instances, the parallel query child processes of the instance where the intermediate results are stored share an affinity with the read operations for the intermediate results and can thus read them.

Temporary Tablespace Organization

Review the following information when organizing the temporary tablespaces.

For example:

CREATE TEMPORARY TABLESPACE temp_ts TEMPFILE\
   '/u01/app/oracle/database/23.4.0/dbs/temp_file'\
   EXTENT MANAGEMENT UNIFORM SIZE 1M AUTOEXTEND ON;

Creation of a temporary tablespace results in the creation of temporary files on every instance and not a single file.

You can create temporary tablespaces for both read-only and read-write instances.

CREATE TEMPORARY TABLESPACE FOR ALL temp_ts TEMPFILE\
  '/u01/app/oracle/database/23.4.0/dbs/temp_file'\
  EXTENT MANAGEMENT UNIFORM SIZE 1M AUTOEXTEND ON;

Temporary Tablespace Hierarchy

When you define temporary tablespaces, there is a hierarchy that determines how the tablespaces are used.

There can be multiple temporary tablespaces in a database, such the default shared temporary tablespace for the database and multiple temporary tablespaces that are assigned to individual users. If a user has a shared temporary tablespace assigned, then that tablespace is used first, otherwise the database default temporary tablespace is used.

Once a tablespace has been selected for spilling during query processing, there is no switching to another tablespace. For example, if a user has a shared temporary tablespace assigned and during spilling it runs out of space, then there is no switching to an alternative tablespace. The spilling, in that case, results in an error. Additionally, remember that shared temporary tablespaces are shared among instances.

The allocation of temporary space for spilling to a temporary tablespace differs between read-only and read-write instances. For read-only instances, the following is the priority of selecting which temporary location to use for spills:

Allocate from a user's temporary tablespace.
Allocate from the database default temporary tablespace.

Note:

If there is no temporary tablespace in the database, then read-only instances spill to shared temporary tablespace.

For read-write instances, the priority of allocation differs from the preceding allocation order, because shared temporary tablespaces are given priority, allocating as follows from:

A user's shared temporary tablespace.
The database default shared temporary tablespace.

Temporary Tablespace Features

Review the following information when using temporary tablespaces.

Instances cannot share temporary tablespace. Therefore, one instance cannot take temporary tablespace from another. If an instance runs out of temporary tablespace during spilling, then the statement results in an error.

Temporary tablespaces support only one BIGFILE per tablespace.
To address contention issues arising from having only one BIGFILE-based temporary tablespace, you can assign multiple temporary tablespaces to different users as the default.
A database administrator can specify the default temporary tablespace for a user using ALTER USER syntax. For example:
```
ALTER USER MAYNARD TEMPORARY TABLESPACE temp_ts;
```
You can configure a user with two default temporary tablespaces:
- One temporary tablespace when the user is connected to the read-only instance.
- One shared temporary tablespace to be used when the same user is connected on the read-write instances that are running on a node.

Metadata Management of Temporary Files

Instance-specific information, such as bitmap for allocation, current size for a temporary file, and the file status, is stored in the SGA and not in control files because such information can vary across instances.

Currently, temporary file information (such as file name, creation size, creation SCN, temporary block size, and file status) is stored in the control file along with the initial and max files, as well as auto extent attributes. However, information about temporary files in the control file is common to all applicable instances.

When an instance starts, it reads the control file information and creates the temporary files that constitute the temporary tablespace for that instance. If there are two or more instances running on a node, then each instance has its own temporary files.

For temporary tablespaces, there is a separate file for each involved instance. The temporary file names follow a naming convention such that the instance numbers are appended to the temporary file names that are specified while creating the temporary tablespace.

For example, assume that a read-only node, N1, runs two Oracle read-only database instances with numbers 3 and 4. The following DDL command creates two files on node N1-/temp/temp_file_3 and /temp/temp_file_4, for instance 3 and 4 respectively:

CREATE TEMPORARY TABLESPACE temp_ts TEMPFILE  '/temp/temp_file'\
   EXTENT MANAGEMENT UNIFORM SIZE 1M AUTOEXTEND ON;

Assuming that there are two read-write instances, instances 1 and 2, and two read-only instances, instances 3 and 4, the following DDL command creates four files-/temp/temp_file_all_1 and /temp/temp_file_all_2 for instances 1 and 2, respectively, and /temp/temp_file_all_3 and /temp/temp_file_all_4 for instances 3 and 4, respectively:

CREATE TEMPORARY TABLESPACE FOR ALL  temp_ts_for_all TEMPFILE  '/temp/temp_file_all'\
   EXTENT MANAGEMENT UNIFORM SIZE 1M AUTOEXTEND ON;

DDL Support for Temporary Tablespaces

You can manage temporary tablespaces and temporary files with either the DDL command ALTER TABLESPACE, or ALTER DATABASE.

Run all temporary tablespace management and creation DDL commands from the read-write instances. Running all other DDL commands affects all instances in a homogeneous manner.

For example, the following command resizes the temporary files on all read-only instances:

ALTER TABLESPACE temp_ts RESIZE 1G;

For temporary tablespaces, Oracle supports the allocation options and their restrictions that are currently active for temporary files.

To run a DDL command on a temporary tablespace on a read-only instance, there must be at least one read-only instance in the cluster. This restriction is not applicable when creating or altering temporary tablespaces FOR ALL. You can assign a default temporary tablespace to the database with the clause DEFAULT TEMPORARY TABLESPACE appended to the command ALTER DATABASE.

For example:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts;

You can specify default temporary tablespaces when creating the database, as follows:

CREATE DATABASE .. DEFAULT TEMPORARY TABLESPACE temp_ts_for_dbtemp_ts TEMPFILE\
   '/temp/temp_file_for_db' EXTENT MANAGEMENT UNIFORM SIZE 1M AUTOEXTEND ON;

You cannot specify default temporary tablespaces using the command CREATE DATABASE. When you create a database, its default temporary tablespace points to the default shared temporary tablespace. You must run the command ALTER DATABASE to assign an existing temporary tablespace as the default for the database.

Temporary Tablespace for Users

When you create a user without explicitly specifying shared or temporary tablespace, the user inherits shared and temporary tablespace from the corresponding default database tablespaces.

You can specify default temporary tablespace for a user, as follows:

CREATE USER new_user IDENTIFIED BY new_user TEMPORARY TABLESPACE temp_ts_for_all;

You can change the temporary tablespace for a user using the ALTER USER command, as follows:

ALTER USER maynard TEMPORARY TABLESPACE temp_ts;

As previously mentioned, default user temporary tablespace can be shared temporary space. Consider the following items for the ALTER USER...TEMPORARY TABLESPACE command:

You can change the user default temporary tablespace to any existing temporary tablespace.
If you want to set the user default temporary tablespace to a shared temporary tablespace, T, then T must be the same as the default shared temporary tablespace.
If a default user temporary tablespace points to a shared temporary tablespace, then, when you change the default shared temporary tablespace of the user, you also change the default temporary tablespace to that tablespace.

Following are some examples of temporary space management using the command ALTER:

To take a temporary tablespace offline:

ALTER DATABASE TEMPFILE '/temp/temp_file' OFFLINE;

To decrease the size of a temporary tablespace:
```
ALTER TABLESPACE temp_ts SHRINK SPACE KEEP 20M
```
To change the auto-extension attributes of a temporary file:
```
ALTER TABLESPACE temp_ts AUTOEXTEND ON NEXT 20G
```
To resize a temporary file:
```
ALTER TABLESPACE temp_ts RESIZE 10G
```
Note:
When you resize a temporary file, it applies to individual files.

Some read-only instances may be down when you run the preceding commands. This does not prevent the commands from succeeding because when a read-only instance starts up later, it creates the temporary files based on information in the control file. Creation is fast because Oracle AI Database reformats only the header block of the temporary file, recording information about the file size, among other things. If you cannot create the temporary files, then the read-only instance stays down. Commands that you submitted from read-write instances are replayed immediately on all open, read-only instances.

Atomicity Requirement for Commands

All of the commands that you run from read-write instances are performed in an atomic manner.

This means that the command succeeds only when it succeeds on all live instances.

Temporary Tablespace and Dictionary Views

Oracle extended dictionary views can display information about temporary tablespaces.

Note the following changes to data dictionary views:

All of the diagnosibility information that is related to temporary tablespaces and temporary files that are exposed through AWR, SQL monitor, and other utilities, is also available for temporary tablespaces and temporary files. This information is available with the existing dictionary views for temporary tablespaces and temporary files: DBA_TEMP_FILES, DBA_TEMP_FREE_SPACE.
The USER_TABLESPACES and DBA_TABLESPACES dictionary view are extended by a column called SHARED, that indicates if the temporary file is shared.
The DBA_TEMP_FILES dictionary view is extended by two columns: SHARED and INST_ID. The SHARED column indicates if the temp file is shared. The INST_ID column contains the instance number. For shared temporary files, there is a single row per file and the INST_ID is null. For temporary files, this column contains information about temporary files for each instance, such as the size of the file in bytes ( BYTES column).
The DBA_TEMP_FREE_SPACE dictionary view is extended by two columns: SHARED and INST_ID. The SHARED column indicates of the temporary file is shared. The INST_ID column contains the instance number. For shared temporary files, there is a single row for each file and the INST_ID is null. For temporary files, this column contains information about temporary files for each instance, such as the total free space available ( FREE_SPACE column).
In the dictionary views, such as DBA_TABLESPACES, Oracle distinguishes the type of the tablespace using the SHARED column with the following values:
SHARED: for shared temporary tablespace
LOCAL_ON_ALL: for temporary tablespace on all of the instances

Note:
Currently, spills onto temporary tablespace for
queries, such as sort and hash join spills, are automatically encrypted. This is
also true for spills to temporary tablespace.