Datafile Needs Recovery

When there is a disk read/write problem, such as adapter issues connecting SAN luns to server or other server IO problems, Oracle data files may fail to get checkpoint. In this case, Oracle may place the datafile offline and write errors to the alert log:

Errors in file /u01/app/oracle/admin/dw/bdump/dw_ckpt_1081360.trc:
ORA-01171: datafile 7 going offline due to error advancing checkpoint
ORA-01110: data file 7 : '/u06/app/oracle/oradata/dw/dw_idx01.dbf'
ORA-01115: IO error reading block from file 7 (block # 1)
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 5: I/O error
Additional information: -1
Additional information: 16384

Database view DBA_DATA_FILES will confirm that the datafile needs recovery:

SQL> select * from dba_data_files
where online_status != 'ONLINE';

FILE_NAME ONLINE_STATUS
/u06/app/oracle/oradata/dw/dw_idx01.dbf RECOVER
/u02/app/oracle/oradata/dw/system01.dbf SYSTEM

The fix to the problem is to run two "alter database ..." commands to bring the data file back online, after the disk IO issue has been firmly resolved. Here is the alert log when the two commands ran:

Fri May 7 23:25:30 2010
alter database recover datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf'
Fri May 7 23:25:30 2010
Media Recovery Start
parallel recovery started with 7 processes
Fri May 7 23:25:31 2010
Recovery of Online Redo Log: Thread 1 Group 2 Seq 70541 Reading mem 0
Mem# 0: /u03/app/oracle/oradata/dw/redo02a.log
Mem# 1: /u03/app/oracle/oradata/dw/redo02b.log
Fri May 7 23:25:34 2010
Recovery of Online Redo Log: Thread 1 Group 3 Seq 70542 Reading mem 0
Mem# 0: /u04/app/oracle/oradata/dw/redo03a.log
Mem# 1: /u04/app/oracle/oradata/dw/redo03b.log
Fri May 7 23:25:36 2010
Media Recovery Complete (dw)
Completed: alter database recover datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf'

Fri May 7 23:26:07 2010
alter database datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf' online
Fri May 7 23:26:07 2010
Starting control autobackup
Control autobackup written to DISK device
handle '/u01/app/oracle/product/10.2.0/db_1/dbs/c-1219972149-20100507-02'
Completed: alter database datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf' online