ORA-01200原因及解决方法

2021-08-26 23:38:12

ITPUB 论坛上的一个帖子，分享一下，谢谢LZ oracle_小秋

在数据库startup时，报错：
[oracle@Oracle9i:~]$sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Sat May 22 11:50:27 2010
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  202445884 bytes
Fixed Size                451644 bytes
Variable Size          167772160 bytes
Database Buffers          33554432 bytes
Redo Buffers                667648 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/u01d/system01.dbf'
ORA-01200: actual file size of 38400 is smaller than correct size of 51200 blocks

SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>
报错说，数据文件的实际大小与正确的大小较小。
SQL> col name for a50
SQL> select file#,status,bytes/1024/1024 mb,name from v$datafile;
   FILE# STATUS       MB NAME
---------- ------- ---------- --------------------------------------------------
      1 SYSTEM       400 /u01/u01d/system01.dbf  ---可以看出控制文件记录的是400M，而du -skh system01.dbf结果是301M
      2 ONLINE       100 /u01/u01d/undotbs01.dbf
      3 ONLINE       25 /u01/u01d/users01.dbf
      4 ONLINE       25 /u01/u01d/indx01.dbf
      5 ONLINE       100 /u01/u01d/perfstat.dbf
      6 ONLINE       10 /u01/u01d/timi01.dbf
6 rows selected.
SQL>
查阅文档：
[oracle@Oracle9i:/u01/temp]$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
//       controlfile. 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.
[oracle@Oracle9i:/u01/temp]$
可以看出，是数据文件的实际大小与控制文件和该数据文件的头部所记录的大小不同而引起的。为什么会有这个变化，具体原因无法再现。
来看看怎么恢复，以打开数据库：
首先转储数据文件头部看看：
SQL> alter session set events 'immediate trace name FILE_HDRS level 10';
Session altered.
SQL> @/u01/admin/mytools/myscripts/gettrcname.sql
TRACE_FILE_NAME
--------------------------------------------------------------------------------------------------------------
/u01/admin/denver/udump/denver_ora_4669.trc
SQL>
[oracle@Oracle9i:~]$more /u01/admin/denver/udump/denver_ora_5349.trc
.....
FILE HEADER:
      Software vsn=153092096=0x9200000, Compatibility Vsn=134217728=0x8000000
      Db ID=4004057640=0xeea91228, Db Name='DENVER'
      Activation ID=0=0x0
      Control Seq=1908=0x774, File size=51200=0xc800  -----可见此处的大小是51200, 而ORA-01200报告说实际大小是38400
      File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM  rel_fn:1
.....
可以看出此时必须用备份来恢复，但是发现根本没有任何的备份。没办法，只有使用bbed工具来非常规恢复了。
BBED> modify /x 00960000 offset 44  ---用find /x c8 curr 找出51200所在之处, 因为38400的十六进制为9600，在根据倒位法则就是0096
File: /u01/u01d/system01.dbf (1)
Block: 1             Offsets: 44 to  555          Dba:0x00400001
------------------------------------------------------------------------
00960000 00200000 01000300 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 a1014000 07000000 00000000
4bbaa02a 9aabd02a 0e3a0f00 00000000 00000000 00000000 00000000 00000400
7a061000 00000000 86a7dd2a 01005162 01000000 8d660000 1000ffbf 02000000
00000000 6c010000 54abd02a 6b010000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 06005359 5354454d 00000000 00000000 00000000
00000000 00000000 00000000 01000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0a000a00 9cefc52a
5c0a0d00 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 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

BBED> sum apply
Check value for File 1, Block 1:
current = 0x1459, required = 0x1459
BBED> quit
试着打开数据库：
SQL> alter database open;
Database altered.
SQL> col name for a50
SQL> select file#,status,bytes/1024/1024 mb,name from v$datafile;
   FILE# STATUS       MB NAME
---------- ------- ---------- --------------------------------------------------
      1 SYSTEM       300 /u01/u01d/system01.dbf  ---控制文件里记录是300M，而且数据库已经成功打开
      2 ONLINE       100 /u01/u01d/undotbs01.dbf
      3 ONLINE       25 /u01/u01d/users01.dbf
      4 ONLINE       25 /u01/u01d/indx01.dbf
      5 ONLINE       100 /u01/u01d/perfstat.dbf
      6 ONLINE       10 /u01/u01d/timi01.dbf
6 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  202445884 bytes
Fixed Size                451644 bytes
Variable Size          167772160 bytes
Database Buffers          33554432 bytes
Redo Buffers                667648 bytes
Database mounted.
Database opened.
ok, 成功打开数据库。
总结：
ORA-01200错误的具体原因已经清楚，但是是什么导致的这次数据库故障，已无从考究。
但是应该可以肯定的是，此时的数据库应该立即做个导出重建导入操作。

码农公寓

相关文章