3

In my production server I have mistakenly deleted datafiles. When I am trying to open the database I am getting an error. The files are not important but the database is not opening up.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL>  alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/mars/oradata/MARS/system01.dbf'

I have tried plenty of ways to recover but it is not opening. LIKE

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/u01/mars/oradata/MARS/example01.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/mars/oradata/MARS/example01.dbf'

migrated from stackoverflow.com Mar 3 '14 at 12:51

This question came from our site for professional and enthusiast programmers.

8

Since you don't care about the data, you can try the following which will offline and drop the datafile in question allowing you to open the database.

SQL> startup
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2255904 bytes
Variable Size            1275069408 bytes
Database Buffers          436207616 bytes
Redo Buffers                6795264 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'C:\ORACLE\ORADATA\ORCL\TRASHED01.DBF'

SQL> alter database datafile 6 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL>

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.