How to Recover Controlfile without Backup?

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

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

How to create controlfile?

In Below code you need to change your database name, logfile group location and datafile with 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

After finding all above details you can edit 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

1 thought on “How to Recover Controlfile without Backup?”

Leave a Comment

%d bloggers like this:
Enable Notifications    OK No thanks