快下班了自己自己测试环境突然报这个错。
SQL> startup
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database Buffers 289406976 bytes
Redo Buffers 2355200 bytes
Database mounted.
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/oradata/node3/users01.dbf'
ORA-01200: actual file size of 533 is smaller than correct size of 640 blocks
尝试recover也报错不行
SQL> revcover datafile 4;
SP2-0734: unknown command beginning "revcover d..." - rest of line ignored.
SQL> recover datafile 4;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 4: '/oradata/node3/users01.dbf'
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/oradata/node3/users01.dbf'
ORA-01200: actual file size of 533 is smaller than correct size of 640 blocks
SQL> revcover datafile 4;
SP2-0734: unknown command beginning "revcover d..." - rest of line ignored.
SQL> recover datafile 4;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 4: '/oradata/node3/users01.dbf'
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/oradata/node3/users01.dbf'
ORA-01200: actual file size of 533 is smaller than correct size of 640 blocks
SQL> select status from v$instance;
STATUS
---------
MOUNTED
查看数据字典记录的文件大小是5M
SQL> select file#,status,bytes/1024/1024 mb,name from v$datafile where file#=4;
FILE# STATUS MB NAME
----------------------------------------------------
4 ONLINE 5 /oradata/node3/users01.dbf
而系统文件大小显示的是4.2M
[root@node3 node3]# ll -sh users01.dbf
788K -rw-r----- 1 oracle dba 4.2M Feb 2 23:08 users01.dbf
[oracle@node3 node3]$ oerr ora 01200
01200, 00000, "actual file size of %s is smaller than correct size of %s blocks"
// *Cause: The size of the file as returned by the operating system is smaller
// than the size of the file as indicated in the file header and the
// control file. Somehow the file has been truncated. Maybe it is the
// result of a half completed copy.
// *Action: Restore a good copy of the data file and do recovery as needed.
可以看出: 是数据文件的实际大小与控制文件和该数据文件的头部所记录的大小不同而引起的。
一般是数据库异常导致的,在数据库的运行过程中,重新启动时,文件resize之后出现异常状况时都可能会遇到。
3、dump数据文件头的信息看看
SQL> oradebug setmypid
Statement processed.
SQL> alter session set events 'immediate trace name FILE_HDRS level 10';
Session altered.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/node3/node3/trace/node3_ora_27311.trc
++++++trace文件
Read of datafile '/oradata/node3/users01.dbf' (fno 4) header failed with ORA-01200
Rereading datafile 4 header failed with ORA-01200
V10 STYLE FILE HEADER:
Compatibility Vsn = 186647552=0xb200400
Db ID=2029768178=0x78fbcdf2, Db Name='NODE3'
Activation ID=0=0x0
Control Seq=915=0x393, File size=640=0x280 +++ 这里是 filesize 640,而报错是说实际大小是533
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS rel_fn:4
Creation at scn: 0x0000.00003f0f 08/24/2013 11:37:49
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x35cee135 scn: 0x0000.000e2006
prev reset logs count:0x3121c97a scn: 0x0000.00000001
recovered at 02/02/2016 12:00:26
status:0x0 root dba:0x00000000 chkpt cnt: 95 ctl cnt:94
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.000f0b9a 02/02/2016 23:08:45
解决方案:
1.如果有备份可以采用备份来恢复。(没备份)
2.通过一些特殊的手段BBED,将该文件恢复
3.搜搜MOS看看有没有好的方法
[oracle@node3 ~]$ bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Wed Feb 3 00:36:31 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 4 block 1
FILE# 4
BLOCK# 1
BBED> p kcvfhhdr
struct kcvfhhdr, 76 bytes @20
ub4 kccfhswv @20 0x00000000
ub4 kccfhcvn @24 0x0b200400
ub4 kccfhdbi @28 0x78fbcdf2
text kccfhdbn[0] @32 N
text kccfhdbn[1] @33 O
text kccfhdbn[2] @34 D
text kccfhdbn[3] @35 E
text kccfhdbn[4] @36 3
text kccfhdbn[5] @37
text kccfhdbn[6] @38
text kccfhdbn[7] @39
ub4 kccfhcsq @40 0x00000393
ub4 kccfhfsz @44 0x00000280 ++这里是280转换成10进制是640,这里要改成实际大小533
s_blkz kccfhbsz @48 0x00
ub2 kccfhfno @52 0x0004
ub2 kccfhtyp @54 0x0003
ub4 kccfhacid @56 0x00000000
ub4 kccfhcks @60 0x00000000
text kccfhtag[0] @64
BBED> modify /x 1502 offset 44
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /oradata/node3/users01.dbf (4)
Block: 1 Offsets: 44 to 555 Dba:0x01000001
------------------------------------------------------------------------
15020000 00200000 04000300 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0f3f0000 00000000
8dc92131 35e1ce35 06200e00 00000000 00000000 00000000 00000000 00000000
5f000000 dae0ce35 5e000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
04000000 05005553 45525300 00000000 00000000 00000000 00000000 00000000
00000000 04000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 7ac92131 01000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 9a0b0f00 00000000
7d7dcf35 01000000 03000000 9b4b0100 10008b91 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 1:
current = 0xbae9, required = 0xbae9
SQL> alter database open;
Database altered.
数据库正常open;
再次查看数据字典记录的user01文件大小
SQL> select file#,status,bytes/1024/1024 mb,name from v$datafile where file#=4;
FILE# STATUS MB NAME
---------- -------------- --------------------------------------
4 ONLINE 4.1640625 /oradata/node3/users01.dbf
[oracle@node3 node3]$ ll -h users01.dbf
-rw-r----- 1 oracle dba 4.2M Feb 3 00:46 users01.dbf
可以看到 现在数据字典里面记录的文件大小和操作系统查看的一样了