Recover datafile (ORA-00376)

Errors in file /oracle/log/diag/rdbms/prod/trace/prod_smon_28281.trc:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/oracle/data/undotbs01.dbf'


SQL>  select * from v$recover_file
  2  /

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------------
         2 OFFLINE OFFLINE                                                                     45342425 31-AUG-11
         3 OFFLINE OFFLINE                                                                     45342425 31-AUG-11
        20 OFFLINE OFFLINE                                                                     45342425 31-AUG-11


SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id);
Enter value for give_file_id: 2
old   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id)
new   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =2)

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSAUX                         ONLINE

SQL> /

Enter value for give_file_id: 3

old   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id)

new   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =3)

TABLESPACE_NAME                STATUS

------------------------------ ---------

UNDOTBS1                       ONLINE

SQL> /

Enter value for give_file_id: 20

old   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id)

new   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =20)

TABLESPACE_NAME                STATUS

------------------------------ ---------

SNPM                           ONLINE

SQL>  select FILE# from v$datafile where status in ('OFFLINE','RECOVER');

     FILE#

----------

         2

         3

        20

SQL>  select status, enabled from v$datafile where file#=&give_file_id;

Enter value for give_file_id: 2

old   1:  select status, enabled from v$datafile where file#=&give_file_id

new   1:  select status, enabled from v$datafile where file#=2

STATUS  ENABLED

------- ----------

RECOVER READ WRITE

SQL> /

Enter value for give_file_id: 3

old   1:  select status, enabled from v$datafile where file#=&give_file_id

new   1:  select status, enabled from v$datafile where file#=3

STATUS  ENABLED

------- ----------

RECOVER READ WRITE

SQL> /

Enter value for give_file_id: 20

old   1:  select status, enabled from v$datafile where file#=&give_file_id

new   1:  select status, enabled from v$datafile where file#=20

STATUS  ENABLED

------- ----------

RECOVER READ WRITE

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME

 FROM V$DATAFILE_HEADER

 WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

SQL>  alter database datafile '/oracle/data/snpm01.dbf' online;

Database altered.

SQL>  alter database datafile /oracle/data/undotbs01.dbf' online;

Database altered.

SQL>  select FILE# from v$datafile where status in ('OFFLINE','RECOVER');

no rows selected

SQL> alter database open;