Different files involved in Oracle database

As Oracle DBA, we all know Oracle database server is combination of physical files, processes and memory, in this article we are going to discuss about different files involved in oracle database architecture

There are 3 most important files

·       Data files

·       Control files

·       Redo logs file

There are other supplementary files as well - parameter files, password files, archive log files

 

Let’s discuss some more details 

Datafiles 

Data files contains actual data of the database, data files are formatted as per block size designed during database setup.

There are few types of datafiles

 

1.    SYSTEM - this includes system datafile and is critical to database, it includes dictionary of the database

2.    User datafile - this includes application specific datafiles and contains application data and can be customized as per requirement

3.    UNDO datafile - It holds old data while performing activity in database like select, insert, update, delete, this old data can be used to revert the changes in case of recovery

4.    TEMP datafile - In case user orders data based on select query, it needs space to sort the data before returning back to user, first sorting operation is performed in memory and if memory is not sufficient then it moves to temp tablespace.

 

Control Files

Control file contains structure of the database like location of all datafiles, redo logfiles.

This is Critical file to operation of database, and it is one of first file read by instance at startup.

 

Redo logfile

This file contains changes made to the database and can be used for recovery of processes. 

copies of redo logfile are called as archived log files.

 

Other files in oracle database 

Password file - this file authenticates the use who is capable to startup the database.

parameter files- 

    This file contains list of parameters used by instance.  typically, there are two types of parameter files

PFILE - text based parameter file, change to parameter needs to be modified/added manually in file and restart is required to get it into effect. 

SPFILE - binary parameter file, change to the parameter can be carried out on the go.