Database files play an essential role in the Oracle architecture. They physically store all data necessary to run the database successfully.
Every tablespace consists of one or more data files but only one tablespace might be associated with a data file. Data files are managed by data blocks. The blocks are defined by data block size and these blocks define the actual size of a data file.
Main 4 types are:
- System Data files
- User Data Files
- Temp files
Control files store the information about the structure of a database (such as database name, names, and location of data and redo log files, current log sequence number, ….)
REDO log files
REDO log files are files which store all operations (DML) performed in the database. This is a very important file! In case of a database crash, you can use this file for a recovery (more info in the article DML processing). If you need to replicate data from database A to database B you can easily use REDO log files for replication (via Oracle Golden Gate or Oracle DataGuard).
In order to start up a database, you have to provide the password for SYS user. This password is stored here in this file, unlike general users’ passwords which are stored in Data Files.
PFILE – parameter file, it is a text file which can be edited by admin in a text editor. Whenever you need to do any change in configuration, you (as a DBA) have to do it in this file so when you restart the database the changes will be loaded from PFILE into SPFILE and become permanent.
SPFILE – server parameter file; it is a binary file which cannot be manipulated by any user but Oracle Instance.