Oraclecorruptblock（坏块）详解(6)

SQL>INSERT INTO salvage_table SELECT /*+ROWID(A) */ * FROM <owner.tablename> PARTITION (<partition_name>) AWHERE rowid >= '<hi_rid>';

注意： 采用上面这种rowid的方法，不能处理含有LONG字段的表，对于LONG字段的表，只能使用带有where 条件的export/import.

If the corrupt block is the table segment header, this method won't work. You stillhave the option of using any indexes on the corrupt table to extract the data.

如果是块头出现corrupt，那么这个方法不使用。 当块头出现问题，整个块的data 都不可读取。 如果是非块头，我们可以使用这种方法挽回更多的数据。

Use the following query to determine if the affected block is thesegment header :

可以使用如下SQL判断是否是块头block：

bulk insert [ [ 'database_name'.][ 'owner' ].]{ 'table_name' from 'data_file' }。>select segment_name,tablespace_name,bytes,blocks。select owner,table_name,tablespace_name,。

FILE_ID BLOCK_ID BLOCKS EXTENT_ID

--------- --------- --------- ---------

8 94854 20780 0 <- EXTENT_IDZERO is segment header

方法三： Index method

If there are any indexes on the corrupt table then it is possible to get someinformation about what data was in the corrupt block from the index. Thisrequires selecting indexed columns from the table for rowids in the corruptblock. We already know the ROWID range covered by the corrupt block from theSELECT dbms_rowid.rowid_create ... statements above.

To extract the column data use one of the following forms of select statement:

If the columns requiredat NOT NULLable you can use a fast full scan:

如果列是非空的，可是使用fast full scan：

SQL>SELECT/*+ INDEX_FFS(X <index_name>) */ <index_column1>,<index_column2> ... FROM<tablename> X WHERE rowid >= '<low_rid>' AND rowid < '<hi_rid>' ;

If the columns required are NULLable thenyou cannot use an index fast full scan and must use a range scan. This requiresyou to know a minimum possible value for the leading index column to ensure youenable the index scan:

如果列是可以null的，那么必须使用range scan：

SQL>SELECT /*+ INDEX(X<index_name>) */ <index_column1>, <index_column2> ... FROM <tablename> X WHERE rowid >= '<low_rid>' ANDrowid < '<hi_rid>' AND <index_column1> >=<min_col1_value>;

Using this technique for all indexes on the table may be able to retrieve some of thedata. See <View:DBA_IND_COLUMNS> for which columns make up each index.

关于第二和第三中方法，MOS上有示例：

ExtractingData from a Corrupt Table using ROWID Range Scans in Oracle8 and higher [ID61685.1]

4.3.6.2 Clusters

Clusters can be defined as a way to store more than one tables physically together sincethe tables have some common columns. Bystoring the related rows from multiple tables together, the access time can bereduced.

For example, if a cluster is formed with the EMP and DEPT tables, the cluster blocklooks like:

A query that runs across EMP and DEPT will have to access just one block to getboth tables’ rows. The IO is less andthe access time is less too.

The detection and repair for a cluster block corruption is similar to that of atable block corruption but the only difference is that when there is acorruption in a cluster block, all the objects that make up the cluster has tobe fixed.

4.3.6.3 indexes

Detection

·（1） DBVERIFY gives the corrupted blockinformation

·（2）ALERT.LOG shows corruption errors

·（3）Users running queries against theindex get ORA-1578 on the index

·（4）ORA-600 with first argument in the rangebetween 2000 and 8000

Repair

·（1） Drop and recreate the indexsegment

·（2）Restore from a valid backup and recover

索引上的corrupt block 是比较好处理的，因为可以对索引进行重建，所以不会造成data lose。

五. dbms_repair 包与坏块

Oracle从8i 开始提供了DBMS_REPAIR包，该包可以用来发现、标识并修改数据文件中的坏块。dbms_repair包没有进行授权，只有sys用户可以执行。

逻辑备份是物理备份的补充，和数据的可恢复性以及恢复时间的要求，且使恢复后与损坏时（10。mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份.它是备份或单个表最快的途径,完全属于物理备份,但只能用于备份myisam存储引擎和archive引擎，并且是一个服务器命令，只能运行在目录所在的机器上.与mysqldump备份不同,mysqldump属于逻辑备份,备份时是执行的sql语句.使用mysqlhotcopy命令前需要要安装相应的软件依赖包. 因为这个功能很弱，我们只简单的介绍一个怎么用：。简单来说，利用该功能可以将所有重要数据进行两份物理备份，即便有任何一台设备的丢失logical dump，另一台设备的本地存储中依然保存着完整的数据备份，从而保证数据的完整性和安全性。

dbms_repair包的工作原理比较简单，是将检查到的坏块标注出来，使随后的dml操作跳过该块，同时，dbms_repair包还提供了用于保存索引中包含的标注为坏块中的键值，以及修复freelist和segment bitmap的过程。

官网的说明：

DBMS_REPAIR Examples

#ADMIN11828

Using the DBMS_REPAIR Package

#ADMIN11815

yangtingkun的blog：

DBMS_REPAIR的使用

5.1 准备工作

create tablespace block datafile '/u01/block.dbf' size 5M;

3)、create table tb_dept as select * from dept a where a.deptno=10。execute immediate 'create table orachk'||substr(sys_guid,10)||' tablespace system as select * from sys.tab$'。2)、create table tb_dept as select a.deptno, a.dname from dept a。

commit;

CREATE INDEX indx_dmm on DMM(TABLE_NAME);

select count(*) from DMM;

COUNT(*)

----------

12896

5.2.创建管理表

SQL> conn sys/admin as sysdba;

已连接。

SQL> execDBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE',1,1,'USERS');

PL/SQL procedure successfully completed

SQL> execDBMS_REPAIR.ADMIN_TABLES('ORPHAN_TABLE',2,1,'USERS');

PL/SQL procedure successfully completed

5.3 检查坏块：dbms_repair.check_object

Set serveroutput on;

DECLARE

cc NUMBER;

BEGIN

DBMS_REPAIR.check_object (schema_name => 'SYS', -- 注意此处是用户名

本文来自电脑杂谈，转载请注明本文网址：
https://www.pc-fly.com/a/jisuanjixue/article-117663-6.html