ORA-01200: actual file size of 533 is smaller than correct size of 640 blocks

来源：互联网发布：天狼星期货软件编辑：程序博客网时间：2024/04/30 08:47

快下班了自己自己测试环境突然报这个错。

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> 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

************* !!! 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

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

可以看到现在数据字典里面记录的文件大小和操作系统查看的一样了

0 0