Monday, July 7, 2014

ORA-00376: "file#" cannot be read at this time.

I have experienced this error message (message contains file# of the file which has problems) several times, but only on Windows platform (But this may come on any platform). After this error, all data in the mentioned datafile is no longer available and status of this datafile become "Recover" if we run query:

select name,status from v$datafile where file#=<file# in error message>; 

I am not able to find the any cause of why file automatically gone into "RECOVER" state, but solution for this problem is quite simple.

Solution
If you take immediate action when error comes, you can run "RECOVER" command right away to solve the problem. Oracle would perform  recovery using either online redo log files or available archived log files (if needed) to move forward SCN of this datafile to the current SCN to make datafile consistent. After recovery, bring this datafile online.

SQL> RECOVER DATAFILE <file#>;
SQL> ALTER DATABASE DATAFILE  <file#> ONLINE;

Note: If you delayed the recovery for some time, the information in redo logs may be over written and recovery command may suggest you the archived log which is required for this recovery and you would need to restore that archived log file for recovery to proceed.
Moreover, if database is in NOARCHVELOG mode, and that redo log group is over written which is required for this recovery, you would not be able to recover this datafile.



No comments: