What is control file and datafile in Oracle?

In this article, we are going to learn what is control files and datafiles in Oracle. In the Oracle database environment, each and every database has its own separate control files. The control files also called binary files which record the physical structure of the oracle database.

What is in Controlfile

The controlfile also include:

• Name of the Database.
• Datafile and Redo log files name and location.
• The timestamp of database creation.
• Current log sequence.
• Checkpoint information.

What is the importance of a control file?

The controlfile records the physical files' locations and other details. The controlfile is the main root file for the database which finds all other files used by the oracle database. Due to the importance of the controlfile, the oracle recommends multiplexing of the controlfile. During database creation using DBCA, the oracle by default creates two copies of controlfiles and keeps synchronized with each.

If any controlfile is missing then the database becomes unavailable. But don't worry if you have a copy of the controlfile then just shut down the database and recreate the missing controlfile from the copy of the controlfile, then start the database. Another option is that you just remove the entry of missing controlfile from spfile or pfile then restart the database using the remaining controlfile.

File name for the Contorlfile

You can specify the controlfile name using CONTROL_FILES initialize parameter in the init files (pfile or spfile). During the instance startup procedure recognize and open all the listed control files in the initialization parameter file. Once the oracle database instance is started the instance writes and maintains all controlfiles during database operation.

If you create a manual database and forget to specify the CONTROL_FILES parameter value before database creation, and you are not using the OMF feature. The oracle will create a controlfile automatically with the default name and the default name is OS-specific.

Multiplex Control Files

Oracle always recommends the multiplexing of the controlfile for database security.

What is control file multiplexing?

As per oracle's recommendation, each and every oracle database should have at least two controlfiles and each controlfile stored on a separate disk. In the future, if the controlfile is lost or damaged due to disk failure or any other reason, then the associated database instance must be shut down. Once that disk is repaired then just make a copy of the existing controlfile from another disk then you can start your database instance. In this type of case, no media recovery is required.

Creating New Control Files

Generally, in the running database, we don't require to create a controlfile because if the database is in a running state that means your controlfile is exist on the location.

Case 1. If you lose all the controlfiles and you don't have the backup.

Case 2. You want to change the database name.

How do I create a control file without backup?

We can create a new controlfile using CREATE CONTROLFILE command. The below statement will create a new controlfile for your database.

CREATE CONTROLFILE REUSE DATABASE "OCPTECH" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ocptech/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 4 '/u01/app/oracle/oradata/ocptech/redo04.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ocptech/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/ocptech/system01.dbf',
  '/u01/app/oracle/oradata/ocptech/sysaux01.dbf',
  '/u01/app/oracle/oradata/ocptech/undotbs01.dbf',
  '/u01/app/oracle/oradata/ocptech/users01.dbf',
  '/u01/app/oracle/oradata/ocptech/example01.dbf'
CHARACTER SET WE8MSWIN1252;

Steps to Create new controlfiles

Step 1. Create a list of data files and online redo log files of the database.

Using the below commands you can create the list:

SQL> SELECT MEMBER FROM V$LOGFILE;
SQL> SELECT NAME FROM V$DATAFILE; 
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';

Step 2. Shut down the database.

Shut down the database if it is still in open mode. Try to shutdown immediately or with abort.

SQL> shu immediate

or

SQL> shu abort

Step 3. Take Backup

Take a backup of the data file and online redo logs file.

Step 4. Startup Database

Start database till nomount.

SQL> startup nomount

Step 5. Create a new control file using CREATE CONTROLFILE statement.

Step 6. Open the database after creating the controlfile using one of the below methods.

SQL> ALTER DATABASE OPEN;

or 

SQL> ALTER DATABASE OPEN RESETLOGS; 

What is oracle datafile?

In the Oracle database, data stored in data files that are physically available on disk, and each data file is associated with a separate tablespace and each tablespace could be associated with multiple data files. In Oracle database has three types of tablespaces like Parmanant, undo, and temporary tablespace.

You can check the information about tablespace and data files using the below views:

v$datafile
v$dba_data_files
v$tempfile
v$dba_temp_files

Reference article from What is control file and datafile in Oracle?