Datafile Recovery

Database Oracle 10gr1
Backup Method "user managed"
Archivelog "ENABLE"

When Any datafile is lost due to media failure then through below procedure we can perform media recovery.


Requirements
Recent Valid Database Backup
All archivelog after backup.


Due to Media failure database goes down and everything stop then after fix media problem and trying to open Oracle Database

SQL> conn sys as sysdba
Enter password:
Connected.

SQL> shutdown abort; ---Dummy purpose use ABORT option.
ORACLE instance shut down.

Trying to open database

SQL> startup

But suddenly get below errors message.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF'

Check v$recover_file for more infomation

SQL> column error format a25
SQL> /

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------------- ---------- ---------
4 ONLINE ONLINE FILE NOT FOUND 0

Note: Error column indicate "file not found" means due to media failure we lost our user datafile.


Restore User Datafile from BACKUP LOCATION.



SQL> set autorecovery on

Note: Oracle Corp. recommended set autorecovery on before perform recovery.

SQL> recover tablespace users;
ORA-00279: change 1308298 generated at 09/10/2007 11:19:22 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00140_0632336321.001
ORA-00280: change 1308298 for thread 1 is in sequence #140
[output cut]
Log applied.
Media recovery complete.

Note: All neccessary ARCHIVELOG files applied automatically and media recovery is complete.

SQL> alter database open;

Now open database for normal use.