数据文件 failed check 的处理 - ldquo;ora-01122database file 201 failedrdquo;
今天data从mounted 到open 的时候，，报：
sql> alter database open;
alter database open
*
error at line 1:
ora-01092: oracle instance terminated. disconnection forced
检查alert日志：
errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_dbw0_6672.trc:
ora-01186: file 201 failed verification tests
ora-01122: database file 201 failed verification check
ora-01110: data file 201: '/u02/ezhou/temp01.dbf'
ora-01203: wrong incarnation of this file - wrong creation scn
fri dec  9 11:14:17 2011
file 201 not verified due to error ora-01122
报我的test01.dbf 有问题。
我看一下：
sql> select * from v$tempfile;
     file# creation_change# creation_time              ts#     rfile# status
---------- ---------------- ------------------- ---------- ---------- -------
enabled         bytes     blocks create_bytes block_size
---------- ---------- ---------- ------------ ----------
name
--------------------------------------------------------------------------------
         1           446161 2011-08-27 21:25:00          3          1 online
read write   52428800       6400     20971520       8192
/u02/ezhou/temp01.dbf
是online 的。
思路：先offline 再online:
发现： offline 容易，online 难：
sql> alter database tempfile '/u02/ezhou/temp01.dbf' offline;
database altered.
sql> alter database tempfile '/u02/ezhou/temp01.dbf' online;
alter database tempfile '/u02/ezhou/temp01.dbf' online
*
error at line 1:
ora-01122: database file 201 failed verification check
ora-01110: data file 201: '/u02/ezhou/temp01.dbf'
ora-01203: wrong incarnation of this file - wrong creation scn
好，下面试试删除重建：
sql> alter database tempfile '/u02/ezhou/temp01.dbf' drop;
database altered.
sql> alter tablespace temp add tempfile '/u02/ezhou/temp01.dbf' size 100m;
alter tablespace temp add tempfile '/u02/ezhou/temp01.dbf' size 100m
*
error at line 1:
ora-01109: database not open
没有办法。
在看一下log:
ora-01122: database file 201 failed verification check
还是做一下datafile 的check 吧：
sql> alter system check datafiles;
system altered.
sql> alter database open resetlogs;
alter database open resetlogs
*
error at line 1:
ora-01139: resetlogs option only valid after an incomplete database recovery
sql> alter database open;
database altered.
sql> select * from v$tempfile;
no rows selected
把原来的存在的文件：temp01.dbf 删除，重建：
sql> alter tablespace temp add tempfile '/u02/ezhou/temp01.dbf' size 100m;
tablespace altered.
好，现在database open， temp文件也有了。