Oracle Database Files

Back to: Oracle DBA Tutorials

Oracle Database Files:

In this article, I am going to discuss Oracle Database Files in detail. Please read our previous article where we discussed Oracle Database Instance Architecture in detail.

Oracle Database Files

A Database is a collection of database files that organize data and are stored in the disk storage (Physical). Physical indicates the files that we can see in our daily tasks. We have 9 types of files in the Oracle Database. They are as follows:

1. Datafiles
2. Control files
3. Redo log files
4. Database Backup files
5. Archived Redo log files
6. Parameter files
7. Password files
8. Alert log files
9. Trace files

For a better understanding, please have a look at the following image.

Data Files:

Data Files contain the actual user data, application data, and metadata. i.e Tables, Rows, indexes procedures, views, etc. The combination of datafiles is represented as tablespaces. The data is stored using Database writer.

Note: If you lose Datafiles, you lose your database. The extension for datafiles is .dbf

Control Files:

Control files stores metadata about the data files and online redo log files like names, locations, timestamp, and status. This information is required by the database instance to start and open the database.

Note: If you lose Control Files, you lose your database. The extension for Control files is .ctl

Redo Log Files:

Redo Log Files stores changes to the database as they occur and are used for data recovery. The redo log files store the changes made to the database like DML and DCL operations. Log writer is used to writing the changes to redo log files.

Note: If you lose Redo log files, you lose your database. The extension for the redo log file is .log

Database Backups Files:

Any backups of your database that you have taken to be stored in any location to recover the database when the database is crashed.

Note: The backup must include the data files, control files, and redo log files to recover because these are the important database files.

Archived Redo Log Files:

This is explained as a group of redo log files. All the redo log files are archived and stored in some other location for backup and recovery purposes. Contains ongoing history of the data changes. Using the backup file and archived redo log files you can recover your Oracle database

Example: let us take an oracle database named dotnet_db1. The backup of this database is initiated at 2 PM. The backup file is stored in some other location. At 4 PM database crashed and all the data is lost. We have to restore the data back to dotnet_db1 which is more important. We need to look into the files and how we can restore the entire database back to normal.

Now we have the backup file which has taken back up at 2 PM. So, the data will be available till 2 PM. What about remaining transactions between 2 Pm to 4 PM? Now the archive redo log files come into place. It stores all the changes that happen to the database. So, we can use the archived redo log files to recover the data from 2 PM to 4 PM, and using these backup files and archived redo log files we can restore the database and put it in the normal situation.

Parameter file:

There are two parameter files.

1. Pfile
2. Spfile

This file defines how the database instance is configured when it starts up. All the configurations of the database are present in the parameter file.

Example: SGA size SGA_TARGET, SGA_MAX_SIZE.

Note: The spfile is a binary file, you cannot edit it directly, it should be done by using oracle commands. Pfile is a parameter file. You can edit again. If you lose the spfile no issue, you can recreate it again. But it is better to take a backup of the spfile in order to restore all the configurations of the database.

Password File:

It stores passwords for users with administrative privileges (sys user) in order to connect remotely. Sys user has all the privileges to the database.

Note: DBA password cannot be stored in the database, because Oracle cannot access the database before the instance is started, Therefore, the authentication of the DBA must happen outside of the database. The password file will be present in the below location:

$ORACLE_HOME \database\ PWD sid. ora(sid is database name)

Alert Log File:

The alert log file is a chronological log of messages and errors written out by an Oracle Database in sequential order. So, this is your go-to file in case you’re trying to troubleshoot a problem with your database. This log file has all the records what are all the changes to files and other errors also. For example, you can see when the database was started and stopped.

Location of Alert log file: /oracle/diag/rdbms/dotnet_db1/dotnet_db1/trace/ the file name would be alert_dotnet_db1.

Trace File:

Each server and background process writes to a trace file. When a process detects an internal error, it writes information about the error to its trace file.

Location of Trace file: /oracle/diag/rdbms/dotnet_db1/dotnet_db1/trace/ and the file name would be *.trc

In the next article, I am going to discuss Oracle Database Storage Structures in detail. Here, in this article, I try to explain Oracle Database Files and I hope you enjoy this Oracle Database Files article.