Monday, March 23, 2009

Offline data files may need recovery after bringing up database

Due to I/O error, one of our production databases was down last week. In the alert log we can see the following error, for example:


Thu Mar 19 20:23:28 2009
KCF: write/open error block=0x96146 online=1
file=150 [filename].dbf
error=27063 txt: 'SVR4 Error: 6: No such device or address
Additional information: -1
Additional information: 16384'
Automatic datafile offline due to write error on
file 150: [filename].dbf


The database was brought up automatically by cluster software possibly. Anyway it was up though I am not sure how. Later on, application recieved error and called DBA for help. We found that there were files in 'recover' status:


select file# from
v$datafile where status='RECOVER';

FILE#
----------
49
144
146
148
150
172



Those files were further identified by:

select file_name from dba_data_files where file_id in (49,144,146,148,150,172) ;

We recovered them one by one and brought them online by following commands:

alter database recover datafile ;

alter database datafile online;

Learned from this incident, I reinforced the following understanding about Oracle behavior:

Oracle will do instance recovery if necessary when the database is brought up, however, if the data files are offline before the crash, after database is up, they will still be offline and may need to be manually recovered and brought online.

No comments: