Oracle File types

Database files are important to a storage administrator, because you normally do not want to backup the database files with your standard utilities, so you need to know what they are, and also some small database administration files are very important, so you need to know what they are and how to handle them.

In Oracle file terminology, data is stored logically in tablespaces and physically in datafiles. A tablespace is a logical partition that contains both a table and its indexes. Each datafile belongs to only one tablespace but a tablespace can consist of more than one datafile. Database file backups are usually managed by RMAN and so the files should be excluded from standard file system backups. Database file names usually look like sid.DBS.CONTROL.DBF, sid.DBS.DATABASE1.DBF, or sid.DBS.LOG1.DBF. Note that by convention, datafile extensions are .dbf, but this is a convention and can be changed so it is worth checking the file names with your DBA before adding the excludes.

The control file is a binary file that contains the database and log file names. It is impossible to start an Oracle database without a control file, so they are usually replicated - preferably on different disks. If you use Netbackup to backup your databases, it is essential that you know where the current control file is. Control files are usually called controlnn.ctl where n is an integer. You could search for files that follow that pattern, but the only reliable way to identify them is with an SQL query within Oracle. In other words, ask your DBA.

The redo logs contain changed data for wind-forward purposes and the undo logs contain the original data before changes were applied for wind-back purposes. Log file extensions are usually .rdo or .log, Redo and undo log backups are usually managed by RMAN and should be excluded from standard file system backups.

Temporary files are used to hold in flight work that is too big to be held in memory. These files cannot be recovered and so do not require backup.

A couple of important system files are:

  • The parameter file which contains start up information and is usually called init.ora or initdatabasename.ora. The parameter contains things like how much RAM the database is going to use, where to find the control files and where to write trace files
  • The System tablespace which is part of the database and contains the data dictionary. This contains database descriptor tables like column names, data types, user privileges, constraints and space allocations and is very frequently referred to during database processing, so it can be a performance bottleneck. It could be worth while identifying the underlying database files for this tablespace to see if they can be placed for better performance.

The Oratab is a colon-delimited text file on Unix and Linux systems that defines the association between ORACLE_SID and ORACLE_HOME variables. This file is held in /etc/oratab
Entries are of the form: $ORACLE_SID:$ORACLE_HOME:<N|Y>:
The first and second fields are the system identifier and home directory of the database respectively. The third field indicates if the database should be brought up at system boot time. For example,
agent10g:/u01/app/oracle/product/10.2.0/agent10g:N