The files that comprise an Oracle database are as follows:
- Control files: Each database has one unique control file that contains data about the database itself (that is, physical database structure information). Multiple copies may be maintained to protect against total loss. It can also contain metadata related to backups. The control file is critical to the database. Without the control file, the database cannot be mounted or opened.
- Data files: Contain the user or application data of the database, as well as metadata and the data dictionary
- Online redo log files: Allow for instance recovery of the database. If the database server crashes and does not lose any data files, the instance can recover the database with the information in these files.
The following additional files are used during the operation of the database:
- Parameter file: Is used to define how the instance is configured when it starts up
- Password file: Allows users using the SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM, and SYSASM roles to connect remotely to the instance and perform administrative tasks.
- Backup files: Are used for database recovery. You typically restore a backup file when a media failure or user error has damaged or deleted the original file.
- Archived redo log files: Contain an ongoing history of the data changes (redo) that are generated by the instance. Using these files and a backup of the database, you can recover a lost data file. That is, archive logs enable the recovery of restored data files.
- Trace files: Each server and background process can write to an associated trace file. When an internal error is detected by a process, the process dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, whereas other information is for Oracle Support Services.
- Alert log file: These are special trace entries. The alert log of a database is a chronological log of messages and errors. It is recommended that you review the alert log periodically.
Logical and Physical Database Structures
The database has logical structures and physical structures as shown in the figure.
Databases, Tablespaces, and Data Files
The relationship among databases tablespaces and data files is illustrated in the figure above. The database is logically divided into two or more tablespaces. One or more data files are explicitly created for each tablespace to physically store the data of all segments in a tablespace. If it is a TEMPORARY tablespace, it has a temporary file instead of a data file. A tablespace’s data file can be physically stored on any supported storage technology.
A database is divided into logical storage units called tablespaces, which group related logical structures or data files together. For example, tablespaces commonly group all of an application’s segments to simplify some administrative operations.
At the finest level of granularity, an Oracle database’s data is stored in data blocks. One data block corresponds to a specific number of bytes of physical space on the disk. A data block size is specified for each tablespace when it is created. A database uses and allocates free database space in Oracle data blocks.
The next level of logical database space is an extent. An extent is a specific number of contiguous Oracle data blocks (obtained in a single allocation) that are used to store a specific type of information. Oracle data blocks in an extent are logically contiguous but can be physically spread out on disk because of RAID striping and file system implementations.
The level of logical database storage above an extent is called a segment. A segment is a set of extents allocated for a certain logical structure. Example:
- Data segments: Each nonclustered, non-index-organized table has a data segment, with the exception of external tables, global temporary tables, and partitioned tables (in which each table has one or more segments). All of the table’s data is stored in the extents of its data segment. For a partitioned table, each partition has a data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster’s data segment.
- Index segments: Each index has an index segment that stores all of its data. For a partitioned index, each partition has an index segment.
- Undo segments: One UNDO tablespace is created for each database instance. This tablespace contains numerous undo segments to temporarily store undo information. The information in an undo segment is used to generate read-consistent database information and, during database recovery, to roll back uncommitted transactions for users.
- Temporary segments: Temporary segments are created by the Oracle database when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment’s extents are returned to the database for future use. Specify either a default temporary tablespace for every user, or a default temporary tablespace that is used database-wide.
Note: There are other types of segments not listed here. There are also schema objects such as views, packages, triggers, and so on that are not considered segments even though they are database objects. A segment owns its respective disk space allocation. The other objects exist as rows stored in a system metadata segment.
The Oracle Database server dynamically allocates space. When the existing extents of a segment are full, additional extents are added. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on the disk, and they can come from different data files belonging to the same tablespace.
Tablespaces and Data Files
A database is divided into tablespaces, which are logical storage units that can be used to group related logical structures. One or more data files are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace.
Note: You can also create bigfile tablespaces, which have only one file that is often very large. The file may be any size up to the maximum that the row ID architecture permits. The maximum size is the block size for the tablespace multiplied by 236, or 128 TB for a 32 KB block size. Traditional smallfile tablespaces (which are the default) may contain multiple data files, but the files cannot be as large.
SYSTEM and SYSAUX Tablespaces
Each Oracle database must contain a SYSTEM tablespace and a SYSAUX tablespace. They are automatically created when the database is created. The system default is to create a smallfile tablespace. You can also create bigfile tablespaces, which enable the Oracle database to manage ultra-large files.
A tablespace can be online (accessible) or offline (not accessible). The SYSTEM tablespace is always online when the database is open. It stores tables that support the core functionality of the database, such as the data dictionary tables.
The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. The SYSAUX tablespace stores many database components, and it must be online for the correct functioning of all database components. The SYSTEM and SYSAUX tablespaces are not recommended for storing an application’s data. Additional tablespaces can be created for this purpose.