Corrupted or deleted datafile from production - Restoration from Standby dr server
1) Find out the details of the file corrupted or deleted.
2) If corrupted cp and take backup and then delete it.
On standby database server
1) Take backup of the datafile to be restored ;
RMAN>backup as copy datafile 9 format '/u01/datafile3.dbf';
2) Copy the backup to the production server using scp or any other method
1) Take the datafile to be restored offile
SQL> alter database datafile 8 offline;
2) From RMAN catalog and restore the backup
RMAN> catalog datafilecopy '/u06/8.dbf'; cataloged datafile copy datafile copy file name=/u06/8.dbf RECID=40 STAMP=935966739
RMAN> restore datafile 8;
3) recover the datafile
RMAN> recover datafile 8; Starting recover at 14-FEB-17 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 261320 is already on disk as file /u06/p rod_archs/1_261320_834611294.dbf archived log for thread 1 with sequence 261321 is already on disk as file /u06/p rod_archs/1_261321_834611294.dbf archived log for thread 1 with sequence 261322 is already on disk as file /u06/p rod_archs/1_261322_834611294.dbf archived log for thread 1 with sequence 261323 is already on disk as file /u06/p rod_archs/1_261323_834611294.dbf archived log file name=/u06/prod_archs/1_261320_834611294.dbf thread=1 sequence= 261320 media recovery complete, elapsed time: 00:00:00 Finished recover at 14-FEB-17
4) Put the datafile online again.
SQL> alter database datafile 8 online;