Retrieving data if control file is lost in oracle Database

Question

Suppose if the databases control file is lost permanently (no backup). Can we retrieve the data from data file in Oracle database?

Answer 1

You may create new controlfiles with a CREATE CONTROLFILE statement including a list of data files and redo logs.

startup nomount

CREATE CONTROLFILE REUSE DATABASE TEST RESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 1
    MAXLOGHISTORY 449
        LOGFILE
          GROUP 1 '/u01/oradata/test/redo01a.rdo', '/u02/oradata/test/redo01b.rdo'  SIZE 500M
        DATAFILE
          '/u01/oradata/system01.dbf',
          '/u01/oradata/undotbs01.dbf'
        CHARACTER SET WE8ISO8859P1
        ;
recover database;
alter database open;

You need to list your actual database files and come up with sane parameters for your environment, including character set, the size of the redo logs and the name of the database.

If you are unable to RECOVER DATABASE you may instead use ALTER DATABASE OPEN RESETLOGS;

Full documentation for CREATE CONTROLFILE can be found in the Oracle Database SQL Reference