RMAN - Recover datafile

Users informed that they are receiving alerts in the log files while accessing the data. I found that one of the datafiles is missing becuase of HUMAN Error. I followed the following procedure to resolve the issue

Restore and Recovery:

Quick test:

SQL> create table ns as select * from user_objects;

create table ns as select * from user_objects

*

ERROR at line 1:
ORA-01116: error in opening database file 67
ORA-01110: data file 67: '/oracle10/oradata/USP/s_ts_ns_data01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

I query v$datafile, dba_data_files and v$recover_file to get the details

SQL> alter database datafile 67 offline;

Database altered.

RMAN> restore datafile 67;

Starting restore at 21-DEC-07

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00067 to /oracle10/oradata/USP/s_ts_ns_data01.dbf
channel ORA_DISK_1: reading from backup piece /data01/app/oracle/product/10.2.0/db/dbs/6ej461co_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data01/app/oracle/product/10.2.0/db/dbs/6ej461co_1_1 tag=TAG20071221T165552
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 21-DEC-07

RMAN> recover datafile 67;

Starting recover at 21-DEC-07

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:06

Finished recover at 21-DEC-07

SQL> alter database datafile 67 online;

Database altered.

SQL> create table ns as select * from user_objects;

Table created.

Download the toolbar and Stay connected with Live DBA