【故障分析】通過壞塊提示資訊確定損壞的資料庫物件資訊

secooler發表於2010-04-27
資料庫物件
1.故障場景
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1289)
ORA-01110: data file 5: '/oracle/oradata/ora10g/tbs_sec_d_01.dbf'

此處提示有壞塊，位置是5號資料檔案的第1289號塊。

2.在僅知道檔案號和塊號資訊的情況下查詢對應的資料庫物件
假設我們不知道是在查詢T表的時候報的錯，僅僅通過“ORA-01578: ORACLE data block corrupted (file # 5, block # 1289)”提示資訊可以確認是那個資料庫物件報錯麼？
完全可以，我們可以使用dba_extents檢視來完成這個小任務。

1）有關DBA_EXTENTS的描述可以參考Oracle的官方文件
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3111.htm#REFRN23072

DBA_EXTENTS

DBA_EXTENTS describes the extents comprising the segments in all tablespaces in the database.

Note that if a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.

Related View

USER_EXTENTS describes the extents comprising the segments owned by the current user's objects. This view does not display the OWNER, FILE_ID, BLOCK_ID, or RELATIVE_FNO columns.

Column Datatype NULL Description
OWNER VARCHAR2(30)   Owner of the segment associated with the extent
SEGMENT_NAME VARCHAR2(81)   Name of the segment associated with the extent
PARTITION_NAME VARCHAR2(30)   Object Partition Name (Set to NULL for non-partitioned objects)
SEGMENT_TYPE VARCHAR2(18)   Type of the segment: INDEX PARTITION, TABLE PARTITION
TABLESPACE_NAME VARCHAR2(30)   Name of the tablespace containing the extent
EXTENT_ID NUMBER   Extent number in the segment
FILE_ID NUMBER   File identifier number of the file containing the extent
BLOCK_ID NUMBER   Starting block number of the extent
BYTES NUMBER   Size of the extent in bytes
BLOCKS NUMBER   Size of the extent in Oracle blocks
RELATIVE_FNO NUMBER   Relative file number of the first extent block

2）確定5號資料檔案的第1289號塊對應的資料庫物件
sec@ora10g> col OWNER for a10
sec@ora10g> col SEGMENT_NAME for a10
sec@ora10g> col TABLESPACE_NAME for a15
sec@ora10g> select owner,segment_name,segment_type,tablespace_name from dba_extents where file_id = 5 and block_id = 1289;

OWNER      SEGMENT_NA SEGMENT_TYPE       TABLESPACE_NAME
---------- ---------- ------------------ ---------------
SEC        T          TABLE              TBS_SEC_D

OK，任務完成。

3.小結
在確定了壞塊對應的資料庫物件後，就可以依據這個資訊來判斷待回覆的資料庫物件的重要級別，如果不是重要的生產資料，我們的恢復策略也會更加靈活。

Good luck.

secooler
10.04.27

-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-661519/，如需轉載，請註明出處，否則將追究法律責任。

相關文章