Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 6 down vote accepted

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

share|improve this answer
    
Thank you very much –  Hari Nov 21 '12 at 11:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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