Oracle OS Block Header

oracle檔案的第一個塊（block 0）是OS block header，在資料庫中查詢不到資訊，記錄的是OS資訊，以及檔案大小的等資訊：

SQL> select file_name,bytes from dba_data_files; FILE_NAME BYTES-------------------------------------------------- ----------/u01/app/oracle/oradata/PROD/user01.dbf 67108864 $ls -lrttotal 1390268-rw-r----- 1 oracle oinstall 67117056 Apr 12 09:31 user01.dbf

從上面可以看出，OS上的大小比資料庫裡的大小多了一個BLOCK。

如果OS block header損壞，並不影響資料庫開啟、使用，但重建控制檔案時會報錯，用dbverify/rman也檢測不到壞塊，不過可以使用dbfsize來查看：
正常狀態：

$dbfsize user01.dbf Database file: user01.dbfDatabase file type: file systemDatabase file size: 8192 8192 byte blocks

損壞：

$dbfsize user01.dbfuser01.dbf: Header block magic number is bad

編緝BLOCK 0，類比損壞，可以正常啟動、使用：

SQL> startup;ORACLE instance started. Total System Global Area 184549376 bytesFixed Size 1266488 bytesVariable Size 100666568 bytesDatabase Buffers 79691776 bytesRedo Buffers 2924544 bytesDatabase mounted.ORA-01113: file 4 needs media recoveryORA-01110: data file 4: '/u01/app/oracle/oradata/PROD/user01.dbf'  SQL> recover datafile 4; Media recovery complete.SQL> alter database open; Database altered. SQL> create table test01 tablespace USERS as select * from dba_objects; Table created.

用dbv檢查，未發現壞塊：

$dbv file=user01.dbf DBVERIFY: Release 10.2.0.4.0 - Production on Mon Apr 16 16:38:33 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = user01.dbf  DBVERIFY - Verification complete Total Pages Examined : 8192Total Pages Processed (Data) : 357Total Pages Failing (Data) : 0Total Pages Processed (Index): 0Total Pages Failing (Index): 0Total Pages Processed (Other): 11Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 7824Total Pages Marked Corrupt : 0Total Pages Influx : 0Highest block SCN : 336969 (0.336969)

用dbfsize檢查，報錯：

$dbfsize user01.dbfuser01.dbf: Header block magic number is bad

如果重建控制檔案，則會報錯：

SQL> startup nomount;ORACLE instance started. Total System Global Area 184549376 bytesFixed Size 1266488 bytesVariable Size 100666568 bytesDatabase Buffers 79691776 bytesRedo Buffers 2924544 bytesSQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG 2 MAXLOGFILES 5 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 2 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 ( 9 '/u01/app/oracle/oradata/PROD/REDO1_1.log', 10 '/u01/app/oracle/oradata/PROD/REDO1_2.log', 11 '/u01/app/oracle/oradata/PROD/REDO1_3.log' 12 ) SIZE 100M, 13 GROUP 2 ( 14 '/u01/app/oracle/oradata/PROD/REDO2_1.log', 15 '/u01/app/oracle/oradata/PROD/REDO2_2.log', 16 '/u01/app/oracle/oradata/PROD/REDO2_3.log' 17 ) SIZE 100M 18 -- STANDBY LOGFILE 19 DATAFILE 20 '/u01/app/oracle/oradata/PROD/SYSTEM01.dbf', 21 '/u01/app/oracle/oradata/PROD/undotbs01.dbf', 22 '/u01/app/oracle/oradata/PROD/SYSAUX01.dbf', 23 '/u01/app/oracle/oradata/PROD/user01.dbf' 24 CHARACTER SET AL32UTF8 25 ;CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG*ERROR at line 1:ORA-01503: CREATE CONTROLFILE failedORA-01565: error in identifying file '/u01/app/oracle/oradata/PROD/user01.dbf'ORA-27047: unable to read the header block of fileAdditional information: 2

報ORA-27047錯誤。這裡可以在資料庫開啟狀態下，resize datafile，這樣就可以重寫OS block header資訊：

SQL> alter database open; Database altered. SQL> alter database datafile '/u01/app/oracle/oradata/PROD/user01.dbf' resize 65M; Database altered. SQL> select file_name,bytes from dba_data_files; FILE_NAME BYTES-------------------------------------------------- ----------/u01/app/oracle/oradata/PROD/user01.dbf 68157440

dbfsize檢查正常，重建控制檔案正常：

$dbfsize user01.dbf Database file: user01.dbfDatabase file type: file systemDatabase file size: 8320 8192 byte blocks SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG 2 MAXLOGFILES 5 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 2 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 ( 9 '/u01/app/oracle/oradata/PROD/REDO1_1.log', 10 '/u01/app/oracle/oradata/PROD/REDO1_2.log', 11 '/u01/app/oracle/oradata/PROD/REDO1_3.log' 12 ) SIZE 100M, 13 GROUP 2 ( 14 '/u01/app/oracle/oradata/PROD/REDO2_1.log', 15 '/u01/app/oracle/oradata/PROD/REDO2_2.log', 16 '/u01/app/oracle/oradata/PROD/REDO2_3.log' 17 ) SIZE 100M 18 -- STANDBY LOGFILE 19 DATAFILE 20 '/u01/app/oracle/oradata/PROD/SYSTEM01.dbf', 21 '/u01/app/oracle/oradata/PROD/undotbs01.dbf', 22 '/u01/app/oracle/oradata/PROD/SYSAUX01.dbf', 23 '/u01/app/oracle/oradata/PROD/user01.dbf' 24 CHARACTER SET AL32UTF8 25 ; Control file created. SQL> alter database open; Database altered.

註：resize 原大小，重建還是報錯，需要resize一個不同的大小。