【故障分析】通过坏块提示信息确定损坏的数据库对象信息

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-661519/