Physical Structure of Oracle Database

Physical Structure of Oracle Database Contains:

Datafiles: 

As it suggests, datafiles contain database data. Every Oracle database has one or more datafiles. The datafiles allocated for a database physically store information on logical structures of database, such as tables and indexes. The datafiles possess some characteristics such as:

• A datafile can be associated with only one database.
• Data files can automatically extend when the database runs out of space.
• One or more datafiles form a logical unit of database storage, called a tablespace.

Data-files provide data to users when requested, and save it in cache memory of Oracle. Whenever user needs data from table of a database, it fetches from the cache memory if already exists. If not. then it is read from the data files and saved in the memory.

By saving data in cache memory, Oracle reduces I/O operations and thereby improves performance. Oracle also improves performance by reducing the number of disk access when user application inserts new data or modifies data. The data are not written immediately to data-files but saved in a memory, and then all the data are written at one time to appropriate data-files.

Control Files: 

Every Oracle database uses set of files called, control files, to store information on the physical structure and state of the database. Control files, for example, contain the information like name of database, names of data-files and locations of data-files and redo log files, and time stamp of database creation. Control files are stored in the directory Oracle_Base\oradata\SID. At least one control file must be present for the database to start.

Control file is also used in database recovery. Every time an instance of an Oracle database is started, its control files identify the database and redo log files that must be opened for database operation to proceed. Further, Oracle can maintain a number of identical control files simultaneously. This is done to prevent database failure due to control files.

The control files are only updated by the database processes, they should never be directly modified. When physical structure of the database changes by way of adding new data-files or redo log files, the Oracle database makes necessary changes in the control files to reflect those changes.

Redo Log Files: 

Every Oracle database has a set of two or more redo log files. This file has a .log extension and is stored in the folder Oracle_Base\oradata\SID. Based on the type of action (INSERT, UPDATE, or DELETE), the information is stored in the redo log. ROWID of the new row is stored for the INSERT query for an update action query, old values before the change in the record are saved for a delete query, the entire record is stored before it is deleted.

This information is used when the user issues a ROLLBACK command to undo changes. When changed data are not written to the database because of failure, such as unexpected power outage terminating database operation, then the data in memory cannot be written to the data-files.  

The changed data are saved into redo log files so that the user work is not lost. The information in redo log file is used only to recover the database from a system or media failure that prevents database data from being written to the data-files. When database restarts next time, the changed data can be obtained from the redo log files to update the data-files.

Oracle maintains multiple sets of redo log files on different disks so that it can get the changed data from exact copy of the redo log file saved elsewhere. Hence, the users’ data are safe even when redo log file itself fails.

Parameter Files:

Parameter files contain a list of configuration parameters for that instance and database. There is only one parameter filename: init.ora. By default, the parameter file for each database instance is stored in the Oracle_Baseladmin\SID\pfile folder, where SID is the system identifier for the instance. 

The parameter file is a text file and can be edited with any text editor. In order for the changes in the parameter file to take effect, the database must be restarted. If the file is missing, a new parameter file with default options will be created before the database starts.

Alert and Trace Log Files: 

The Oracle database is very complex in its architecture. When database instance is started, it starts many background processes and works simultaneously. For server and all background processes, there is an associated trace file. Each server and background process can write to an associated trace file.

When an internal error is detected by a process, it dumps information about the error to its trace file so that the database administrator (DBA) can review it when needed. Some information may be useful to the Oraele Support Services too. The alert file, or alert log, is a special trace file. The alert log of a database is a chronological log of messages and errors.

Backup Files: 

Backup file is used to restore data when a media failure or user error has damaged or deleted the original file. Backups can be set up and managed by either users or by server. User managed backup and recovery requires you to actually restore backup files before you can perform a trial recovery of the backups. Server managed backup and recovery manages the backup process, such as scheduling of backups, as well as the recovery process, such as applying the correct backup file when recovery is needed.