Recovering missing system datafile in oracle

Prerequisite: need to take full RMAN backup of the database plus archivelogs. 


Check the datafiles location.

SQL> select file_name from dba_data_files;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/sample01.dbf

remove the system datafile from physical location using os commands like rm

[oracle@localhost ~]$ cd /u01/app/oracle/oradata/orcl

[oracle@localhost orcl]$ rm system01.dbf 

[oracle@localhost orcl]$ ls -lrth

total 2.3G
-rw-r----- 1 oracle oinstall 5.1M Apr  3 00:42 users01.dbf
-rw-r----- 1 oracle oinstall  51M Apr  8 03:56 redo02.log
-rw-r----- 1 oracle oinstall  21M Apr  8 03:58 temp01.dbf
-rw-r----- 1 oracle oinstall 249M Apr  8 03:59 example01.dbf
-rw-r----- 1 oracle oinstall 641M Apr  8 04:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 571M Apr  8 04:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall  51M Apr  8 04:06 redo01.log
-rw-r----- 1 oracle oinstall 9.3M Apr  8 04:06 control01.ctl


If system datafile is missed or corrupted, database will not shut down, but no operations permissible

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 8 04:06:38 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE


SQL> create table rr(name varchar(10));
create table rr(name varchar(10))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

In order to restore system datafile we need to bring down the database using shut abort, bcz shut immediate will not work

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 8 04:17:27 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

shutdown database by aborting the instance.

SQL> shut abort
ORACLE instance shut down.


connect to rman and start up instance in mount stage.

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 8 04:18:18 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area    1033670656 bytes

Fixed Size                     1341056 bytes
Variable Size                704645504 bytes
Database Buffers             322961408 bytes
Redo Buffers                   4722688 bytes

Now restore datafile.

RMAN> restore datafile 1;

Starting restore at 08-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_04_08/o1_mf_nnndf_TAG20150408T035923_bl8pl3p8_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_04_08/o1_mf_nnndf_TAG20150408T035923_bl8pl3p8_.bkp tag=TAG20150408T035923
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 08-APR-15


recover datafile.

RMAN> recover datafile 1;

Starting recover at 08-APR-15
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 08-APR-15

open the database.

RMAN> alter database open;

database opened