Oracle Physical Files

Oracle Physical Files

Controlfiles: Locations of other physical files, database name, database block size, database character set, and recovery information. These files are required to open the database.

Datafile: All application data and internal metadata.

Redo log files: Record of all changes made to the database; used for recovery.

Parameter (PFILE or SPFILE): Configuration parameters for the SGA, optional Oracle features,and background processes.

Archived logfiles: Copy of the contents of previous online redo logs, used for recovery.

Password Optional file : used to store names of users who have been
granted the SYSDBA and SYSOPER privileges.

Control Files

Control files are critical components of the database because they store important information
that is not available anywhere else. This information includes the following:

1.The name of the database
2.The names, locations, and sizes of the datafiles and redo log files
3.Information used to recover the database in the case of a disk failure or user error

The control files are created when the database is created in the locations specified in the
control_files parameter in the parameter file. Because loss of the control files negatively
impacts the ability to recover the database, most production databases multiplex their control files to multiple locations. Oracle uses the CKPT background process to automatically update each of these files as needed, keeping the contents of all copies of the control synchronized. You can use the dynamic performance view V$CONTROLFILE to display the names and locations of all the database’s control files.
A sample query of V$CONTROLFILE on a Unix system is shown here:

SQL> select name from v$controlfile;

NAME
------------------------------------
/orcl/MARS/control/control01.ctl
/orcl/MARS/control/control02.ctl
/orcl/MARS/control/control03.ctl


One thing that the control files keep track of in the database are the names, locations, and
sizes of the database datafiles. Datafiles, and their relationship to another database structure
called a tablespace.


Datafiles
Datafiles are the physical files that actually store the data that has been inserted into each table in the database. The size of the datafiles is directly related to the amount of table data that they store. Datafiles are the physical structure behind another database storage area called a tablespace. A tablespace is a logical storage area within the database. Tablespaces group logically related segments.

Common tablespaces are : SYSTEM,SYSAUX, TEMP, TOOLS,USER,UNDOTBS1


SQL> select tablespace_name from dba_tablespaces order by tablespace_name;
TABLESPACE_NAME
------------------------------
APPL_IDX

APPL_TAB
EXAMPLE
SYSAUX
SYSTEM
TEMP
UNDOTBS1
7 rows selected.

or you can try following sql command for more info.

SQL> select tablespace_name, file_name from dba_data_files order by tablespace_name;

Redo Log Files
Whenever a user performs a transaction in the database, the information needed to reproduce
this transaction in the event of a database failure is automatically recorded in the Redo Log
Buffer. The contents of the Redo Log Buffer are ultimately written to the redo logs by the
LGWR background process. Because of the important role that redo logs play in Oracle’s recovery mechanism, they are usually multiplexed, or copied. This means that each redo log contains one or more copies of itself in case one of the copies becomes corrupt or is lost due to a hardware failure. Collectively, these sets of redo logs are referred to as redo log groups. Each multiplexed file within the group is called a redo log group member. Oracle automatically writes to all members of the redo log group to keep the files in sync. Each redo log group must be composed of one or more members. Each database must have a minimum
of two redo log groups because redo logs are used in a circular fashion.

SQL> select * from v$logfile ;

SQL> select * from v$log ;