Recovery Steps for Lost of system datafile

Recovery Steps for Lost of system datafile

SQL>shut abort;

SQL> startup
ORACLE instance started.

Total System Global Area  348127232 bytes
Fixed Size                  1248936 bytes
Variable Size             104857944 bytes
Database Buffers          239075328 bytes
Redo Buffers                2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF'

While database open in read write mode, it thrown an error.

We have last Level 0 backup.

Note: DB is already in mount stage


Steps to Restoration

C:\Users\oradb>rman target sys/orcl1@orcl1

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 6 13:08:12 2014

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

connected to target database: ORCL1 (DBID=1225511391, not open)

RMAN> restore datafile 3;

Starting restore at 06-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
channel ORA_DISK_1: reading from backup piece D:\LOCAL\HOTLEVEL0_ORCL1_DB_02P2GDIV_2_1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\LOCAL\HOTLEVEL0_ORCL1_DB_02P2GDIV_2_1 tag=LVL0_ORCL
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 06-MAR-14


RMAN> recover datafile 3;

Starting recover at 06-MAR-14
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 5 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_5_9KJ8S929
_.ARC
archive log thread 1 sequence 6 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_6_9KJ8SB18
_.ARC
archive log thread 1 sequence 7 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_7_9KJ8SGP1
_.ARC
archive log thread 1 sequence 8 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_8_9KJ8SLJY
_.ARC
archive log thread 1 sequence 9 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_9_9KJ8SM47
_.ARC
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_5_9KJ8S929_.ARC thread=1 sequence=5
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_6_9KJ8SB18_.ARC thread=1 sequence=6
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_7_9KJ8SGP1_.ARC thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-MAR-14

RMAN> alter database open;

database opened

Same can be used for system datafile as well.