Basics of Data and Control Files in Oracle 12c - dummies

Basics of Data and Control Files in Oracle 12c

By Chris Ruel, Michael Wessler

Many types of files are created with your Oracle 12c database. Some of these files are for storing raw data. Some are used for recovery. Some are used for housekeeping or maintenance of the database itself. Let’s take a look at the various file types and what they’re responsible for storing.

Data files in Oracle 12c

Data files are the largest file types in an Oracle database. They store all the actual data you put into your database as well as the data Oracle requires to manage the database. Data files are a physical structure: They exist whether the database is open or closed.

Data files are also binary in nature. You can’t read them yourself without starting an instance and executing queries. The data is stored in an organized format broken up into Oracle blocks.

Whenever a server process reads from a data file, it does so by reading at the very least one complete block. It puts that block into the buffer cache so that data can be accessed, modified, and so on.

It’s also worth noting that the data file is physically created using OS blocks. OS blocks are different from Oracle blocks. OS blocks are physical, and their size is determined when you initially format the hard drive.

You should know the size of your OS block. Make sure that it’s equal to, or evenly divisible into, your Oracle block.

Most of the time Oracle data files have an extension of .DBF (short for database file). But the fact of the matter is that file extensions in Oracle don’t matter. You could name it .XYZ, and it would function just fine.

We feel it is best practice to stick with .DBF because that extension is used in 95 percent of databases.

In every data file, the very first block stores the block header. To be specific, depending on your Oracle block size, the data file header block may be several blocks. By default, the header block is 64k. Therefore, if your Oracle block size is 4k, then 16 header blocks are at the beginning of the file. These header blocks are for managing the data file’s internal workings. They contain

  • Backup and recovery information

  • Free space information

  • File status details

Lastly, a tempfile is a special type of database file. Physically, it’s just like a regular data file, but it holds only temporary information. For example, a tempfile is used if you perform sorts on disk or if you’re using temporary tables. The space is then freed to the file either immediately after your operation is done or as soon as you log out of the system.

You can see that by executing a simple query against V$TEMPFILE and V$DATAFILE you can see a listing of the data files in your database.


Control files in Oracle 12c

The control file is a very important file in the database — so important that you have several copies of it. These copies are placed so that losing a disk on your system doesn’t result in losing all of your control files.

Typically, control files are named with the extension .CTL or .CON. Any extension will work, but if you want to follow best practice, those two are the most popular.

Control files contain the following information:

  • Names and locations of your data files and redo log files

  • Recovery information

  • Backup information

  • Checkpoint information

  • Archiving information

  • Database name

  • Log history

  • Current logging information

Control files contain a host of other internal information as well. Typically, control files are some of the smaller files in the database. It’s difficult to tell you how big they are because it varies depending on the following:

  • How many files your database has

  • How much backup information you’re storing in them

  • What OS you’re using

As mentioned earlier, it’s important that you have several copies of your control files. If you were to lose all of your control files in an unfortunate failure, it is a real pain to fix.