Chapter 9. Creating a Database
A four-node Oracle RAC database is actually only one database, but has four instances, one running on each node. An Oracle instance consists of shared memory (Shared Global Area or SGA) and Oracle background processes. Oracle functions like an operating system that has a transactional file system with a buffer cache and journaling (redo logs). Each instance shares access to the database files, but maintains an instance-specific set of redo logs. Although these logs are instance-private, they must also be shared and visible so that any other node can perform RAC instance recovery.
Oracle file I/O falls into two usage categories: database files and transaction files. Database files (and the sparse TEMP files) contain database blocks, which hold the user’s data. The I/O profile of these files is either small random reads and writes or large sequential reads and writes, depending on the SQL application.
Transaction files (redo and undo) are typically small, very low-latency sequential writes and reads. The transaction files are instance-specific and have an I/O profile distinct from the datafiles.
The largest GFS volume in this configuration is dedicated to the main portion of the database for both datafiles and indexes. This volume can hold a single tablespace for everything, several small tablespaces, or whatever arrangement meets your needs.
All volumes in our sample cluster are evenly stripped across all spindles. This is called the SAME (Stripe and Mirror Everything) strategy, which avoids most I/O tuning problems. The problem you are going to have is the problem everyone faces and that is not enough IOPs or spindles. If you have expand the array by adding more spindles, then it must be capable of adding the performance capacity of these new spindles to existing GFS volumes. Most modern storage arrays do this, but it is usually considered an advanced activity for the storage administrator.
The redo logs and undo tablespaces for each instance are contained on separate GFS volumes. The I/O to this volume is almost always from one instance, and this strategy minimizes cluster-wide contention for these GFS volumes. Each instance has three 512MB redo logs and their size remains static for the lifetime of the database (unless manually altered by a DBA). These logs are large enough and numerous enough to ensure sufficient committed transaction throughput while avoiding processing stalls due to hard checkpoints.
There is also an undo tablespace for each instance as well. For example, the undo tablespace holds the entire encoded contents of a table whose zip code column is being updated. All the values as they existed prior to the update statement are stored in the undo tablespace. If the transaction issues a commit, the undo contents are discarded. If the transaction issues a rollback, the undo contents are retrieved and the table is put back to its original pre-update state. The undo tablespaces are initially 64MB, but are allowed to expand up to 2GB. In our example, a single UPDATE statement of 100s of millions of rows of zip codes could be supported with 2GB worth of available undo. It is unlikely that the redo logs would ever be expanded, but undo requirements may exceed 2GB. If that is the case, then redo/undo volumes could be created to be 8GB instead of 4GB.