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.
查閱文檔:
[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
[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
BBED> sum apply
Check value for File 1, Block 1:
current = 0x1459, required = 0x1459
BBED> quit
試着打開資料庫:
SQL> alter database open;
Database altered.
1 SYSTEM 300 /u01/u01d/system01.dbf ---控制檔案裡記錄是300M,而且資料庫已經成功打開
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Database opened.
ok, 成功打開資料庫。
總結:
ORA-01200錯誤的具體原因已經清楚,但是是什麼導緻的這次資料庫故障,已無從考究。
但是應該可以肯定的是,此時的資料庫應該立即做個導出重建導入操作。