How to recreate a control file in Oracle Database

An Oracle control file is a small binary file that serves as a critical component of an Oracle database. It’s responsible for maintaining metadata about the physical structure of the database, such as datafile and redo log file locations, database name, timestamp of database creation, and checkpoints for database recovery.

Devamını oku: How to recreate a control file in Oracle Database

The control file is crucial for database startup and recovery operations. Without a valid control file, the database cannot be opened. In case of database failure, the control file helps in database recovery by providing information about the database structure and recent transactions.

Oracle recommends having multiple copies of the control file to ensure database reliability and to guard against control file corruption. It’s also worth noting that the control file can be backed up and restored like other database files to prevent data loss.

To recreate a control file in Oracle Database follow the steps given here.

We should only recreate our control file when we are in certain circumstances :-

• All copies of control files present in database are lost or corrupted.
• We are restoring a backup in which control file is corrupted or missing.
• We need to change a hard limit database parameter in the control file.
• If we are moving our database to another server and files are present in different location.

Recreating a control file of that database which is able to mount or open.

1. First we have to generate dump of the control file.

When database is mounted or open :
SQL> alter database backup controlfile to trace;
Database altered

or if you need to make it specific path follow
SQL> alter database backup controlfile to trace as ‘/xxx/;
Database altered

Trace file will be generated in User_dump directory.

SQL> show parameter user_dump_dest
NAME TYPE VALUE
———————— —— —————————-
user_dump_dest string /xxx/diag/rdbms/orcl/orcl/trace

Modify the trace file, delete everything above the “CREATE CONTROLFILE” and after the “CHARACTER SET” option.

2. Shut down your database with immediate option.
   SQL> shutdown immediate;
3. Startup the database in nomount mode.

SQL> startup nomount;
ORACLE instance started.

4. Take the control file script and use it to create the control file of the database.
   SQL> CREATE CONTROLFILE REUSE DATABASE “SID” RESETLOGS NOARCHIVELOG
   MAXLOGFILES 100
   MAXLOGMEMBERS 3
   MAXDATAFILES 400
   MAXINSTANCES 4
   MAXLOGHISTORY 449
   LOGFILE
   GROUP 1 ‘/xxx/redo1.log’ SIZE 50M,
   GROUP 2 ‘/xxx/redo2.log’ SIZE 50M,
   GROUP 3 ‘/xxx/redo3.log’
   DATAFILE
   ‘/u01/data/datafile/1.dbf’ ,
   ‘/u01/data/datafile/2.DBF ‘ ,
   ‘/u01/data/datafile/3.dbf’,
   ‘/u01/data/datafile/4.DBF ‘ ,
   ‘/u01/data/datafile/5.dbf’,
   ‘/u01/data/datafile/6.dbf’ ;

Control file created.
Once the control file successfully created database is automatically mounted.

5. Once the database is mounted open the database with reset logs option.
   SQL> alter database open resetlogs;

Database altered.

6. After the database is open add the existing temp file
   SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ”/u01/data/datafile/temp_01” size 250M REUSE;

Tablespace altered.