How to recover database if system datafile lost

Backup and Recovery

If system datafile removed from disk then you can recover system datafile only in mount state of database.

SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

Shutdown database

SQL> shu immedaite;
SP2-0717: illegal SHUTDOWN option
SQL> shu immediate
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/home/oracle/10.2.0/product/oradata/orcl/system01.dbf’
ORA-01208: data file is an old version – not accessing current version

SQL> shu abort
ORACLE instance shut down.

Startup database mount stage and bring system datafile offline. And start recovery

SQL> startup mount
ORACLE instance started.

Total System Global Area 910163968 bytes
Fixed Size 2024976 bytes
Variable Size 260049392 bytes
Database Buffers 641728512 bytes
Redo Buffers 6361088 bytes
Database mounted.

SQL> alter database datafile 1 offline
2 ;

Database altered.

QL> recover datafile 1;
ORA-00279: change 1730264 generated at 04/08/2013 10:54:45 needed for thread 1
ORA-00289: suggestion :
/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_09/o1_mf
1_46%u_.arc
ORA-00280: change 1730264 for thread 1 is in sequence #46

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1732717 generated at 04/08/2013 11:01:00 needed for thread 1
ORA-00289: suggestion :
/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_09/o1_mf
1_47%u_.arc
ORA-00280: change 1732717 for thread 1 is in sequence #47
ORA-00278: log file ‘/home/oracle/arch/1_46_810903019.dbf’ no longer needed for
this recovery

ORA-00279: change 1733385 generated at 04/08/2013 11:01:48 needed for thread 1
ORA-00289: suggestion :
/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_09/o1_mf
1_48%u_.arc
ORA-00280: change 1733385 for thread 1 is in sequence #48
ORA-00278: log file ‘/home/oracle/arch/1_47_810903019.dbf’ no longer needed for
this recovery

Log applied.
Media recovery complete.

Bring datafile open and bring database in open mode

SQL> alter database datafile 1 online;

Database altered.

SQL> alter database open;

Database altered.