Recover lost datafile with/without backup

scenario 1) if for example the "USER" datafile is lost or corrupt and backup is available
scenario 2) if backup is not available but the database in archivelog mode


scenario 1)

solution
=========
bring the backup copy to the same location or to different location.
if copied to different location, than that has to be informed to control file.

how to inform the new location to control file
----------------------------------------------
sql> startup mount
sql> alter database rename file 'oldpath\filename' to 'newpath\filename'
this way new path of the file is registered to control file

now recover the file in any of the 3 ways
------------------------------------------
recover database ;(it will recover complete database)
recover tablespace users; (it will recover only the specified files in that tablespace)
recover datafile 'path/filename' (this recover single datafile)

after media recovery complete

sql> alter database open; (to open database)

Note: for confirmation you can check v$recover_file (for confirmation there are no pending files for recovery)


scenario 2)


Recover a lost/corrupted/deleted datafile without backup
------------------------------------------------------------
we can get the datafile back to its current state if the database is in archive_log mode and all the archive_logs are present from the time the datafile is created.

 try the below example:

1) create  tablespace test datafile 'd:\test1.dbf' size 10m
   alter tablespace test add datafile 'd:\test2.dbf' size 10m

2) datafile are created in test tablespace

create objects in test tablespace
----------------------------------

1) grant connect,resource,select any table to xyz identified by xyz
2) alter user xyz default tablespace test;
3) conn xyz/xyz
3) create table emp as select * from scott.emp

shutdown immediate
delete datafile 'd:\test1.dbf' and 'd:\test2.dbf' with OS commands
sqlplus "/as sysdba"
startup mount
c:\rman target /
rman>restore tablespace "TEST"; (all the tablespace names should be in caps by default)
rman>recover tablespace "TEST";
exit;
sqlplus "/as sysdba"
alter database open;