Recover Controlfile without Backup

Hello Friends, In this article we are going to discuss how to recover our controlfile without backup. Suppose if your controlfile is deleted accidentally and you don't have any backup then what is the way to recover them.

How do you recover a lost control file?

So here you can find out the manual re-creation method to your deleted controlfiles. No need to do anything just use the below code and make some changes according to your environment.

How to create controlfile?

In the Below code, you need to change your database name, logfile group location, and datafile with the location.

1. Check your Database name

select name from v$database;

NAME
---------
OCPTECH

2. Check your Datafile Location

select name from v$datafile; 

NAME
--------------------------------------------------------------------------------
/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

3. Check your redo location and size

col member for a55

select l.group#,l.member,f.bytes/1024/1024 "size in MB" 
from v$logfile l, v$log f  where l.group#=f.group#;

    GROUP# MEMBER                                                  size in MB
---------- ------------------------------------------------------- ----------
         1 /u01/app/oracle/oradata/ocptech/redo01.log                      50
         3 /u01/app/oracle/oradata/ocptech/redo03.log                      50
         4 /u01/app/oracle/oradata/ocptech/redo04.log                      50

4. How to check your database characterset

col PROPERTY_VALUE for a20
col DESCRIPTION for a20
set line 120 

select * from database_properties where PROPERTY_NAME='NLS_CHARACTERSET';


PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- --------------------
NLS_CHARACTERSET               WE8MSWIN1252         Character set

What is backup control file in Oracle?

After finding all the above details you can edit the below code and run it on SQL prompt.

Note: You can get this code from your database trace file also just using the following command.

alter database backup controlfile to trace;

or 

alter database backup controlfile to trace as '/u01/controlfile.bkp';

Final code to restrore contorlfile

STARTUP NOMOUNT
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
-- STANDBY LOGFILE
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
;

Now your controlfile is created successfully. Open your database and enjoyed.

Read - How to Restore RMAN Backup with a Different Database Name