Oracle Physical Storage

 

The physical database structures of an Oracle database are viewable at the operating system level.

Data Files

At the operating system level, Oracle Database stores database data in structures called data files. Every Oracle database must have at least one data file.

Use of Data Files

Oracle Database physically stores tablespace data in data files. Each non-partitioned schema object and each partition of an object is stored in its own segment, which belongs to only one tablespace.

Tablespaces and data files are closely related, but have important differences:

• Each tablespace consists of one or more data files, which conform to the operating system in which Oracle Database is running.
• The data for a database is collectively stored in the data files located in each tablespace of the database.
• A segment can span one or more data files, but it cannot span multiple tablespaces.
• A database must have the SYSTEM and SYSAUX tablespaces. Oracle Database automatically allocates the first data files of any database for the SYSTEM tablespace during database creation.

The SYSTEM tablespace contains the data dictionary, a set of tables that contains database metadata. Typically, a database also has an undo tablespace and a temporary tablespace (usually named TEMP).

Permanent and Temporary Data Files

A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.

A temporary tablespace contains schema objects only for the duration of a session. Locally managed temporary tablespaces have temporary files (temp files), which are special files designed to store data in hash, sort, and other operations. 

Temp files are similar to permanent data files, with the following exceptions:

• Permanent database objects such as tables are never stored in temp files.
• Temp files are always set to NOLOGGING mode, which means that they never have redo generated for them. Media recovery does not recognize temp files.
• You cannot make a temp file read-only.
• You cannot create a temp file with the ALTER DATABASE statement.
• When you create or resize temp files, they are not always guaranteed allocation of disk space for the file size specified.
• Temp file information is shown in the data dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view.

Online and Offline Data Files

Every data file is either online (available) or offline (unavailable). You can alter the availability of individual data files or temp files by taking them offline or bringing them online. The database cannot access offline data files until they are brought online.

You may take data files offline for many reasons, including performing offline backups or block corruption. The database takes a data file offline automatically if the database cannot write to it.

Like a data file, a tablespace itself is offline or online. When you take a data file offline in an online tablespace, the tablespace itself remains online. You can make all data files of a tablespace temporarily unavailable by taking the tablespace itself offline.

Starting in Oracle Database 12c, you can use the ALTER DATABASE MOVE DATAFILE statement to move an online data file from one physical file to another while the database is open and accessing the file. You can use this technique to achieve the following goals:

• Move a tablespace from one kind of storage to another
• Move data files that are accessed infrequently to lower cost storage
• Make a tablespace read-only and move its data files to write-once storage, such as a write once read many (WORM) drive
• Move a database into Oracle ASM

Data File Structure

Oracle Database creates a data file for a tablespace by allocating the specified amount of disk space and the overhead for the data file header. The data file header contains metadata about the data file such as its size and checkpoint SCN.

Control Files

The database control file is a small binary file associated with only one database. Each database has one unique control file, although multiple identical copies are permitted.

Use of Control Files

Oracle Database uses the control file to locate database files and to manage the state of the database generally.

A control file contains information such as the following:

• The database name and database unique identifier (DBID)
• The time stamp of database creation
• Information about data files, online redo log files, and archived redo log files
• Tablespace information
• RMAN backups

The control file serves the following purposes:

• It contains information about data files, online redo log files, and so on that are required to open the database.
• The control file tracks structural changes to the database. For example, when an administrator adds, renames, or drops a data file or online redo log file, the database updates the control file to reflect this change.
• It contains metadata that must be accessible when the database is not open. For example, the control file contains information required to recover the database, including checkpoints. A checkpoint indicates the SCN in the redo stream where instance recovery would be required to begin. Every committed change before a checkpoint SCN is guaranteed to be saved on disk in the data files. At least every three seconds the checkpoint process records information in the control file about the checkpoint position in the online redo log.
• Oracle Database reads and writes to the control file continuously during database use and must be available for writing whenever the database is open. For example, recovering a database involves reading from the control file the names of all the data files contained in the database. Other operations, such as adding a data file, update the information stored in the control file.

Multiple Control Files

Oracle Database enables multiple, identical control files to be open concurrently and written to the same database. By multiplexing a control file on different disks, the database can achieve redundancy and thereby avoid a single point of failure.

Note: Oracle recommends that you maintain multiple control file copies, each on a different disk.

If a control file becomes unusable, then the database instance fails when it attempts to access the damaged control file. When other current control file copies exist, then you can remount the database and open it without media recovery. If all control files of a database are lost, however, then the database instance fails and media recovery is required. Media recovery is not straightforward if an older backup of a control file must be used because a current copy is not available.

Online Redo Log File

The database maintains online redo log files to protect against data loss. Specifically, after an instance failure, the online redo log files enable Oracle Database to recover committed data that it has not yet written to the data files.

Server processes write every transaction synchronously to the redo log buffer, which the LGWR process then writes to the online redo log. Contents of the online redo log include uncommitted transactions, and schema and object management statements.

Oracle Database uses the online redo log only for recovery. Redo log files are a useful source of historical information about database activity.

The redo records have all relevant metadata for the change, including the following:

• SCN and time stamp of the change
• Transaction ID of the transaction that generated the change
• SCN and time stamp when the transaction committed (if it committed)
• Type of operation that made the change
• Name and type of the modified data segment

Archived Redo Log Files

An archived redo log file is a copy of a filled member of an online redo log group. The file is not considered part of the database, but is an offline copy of an online redo log file created by the database and written to a user-specified location.

Archived redo log files are a crucial part of a backup and recovery strategy. You can use archived redo log files to:

• Recover a database backup
• Update a standby database
• Obtain information about the history of a database using the Oracle LogMiner utility.

The operation of generating an archived redo log file is known as archiving. This operation is either automatic or manual. It is only possible when the database is in ARCHIVELOG mode.