4

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

6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.