DBMS_REPAIR处理坏块

最新推荐文章于 2024-07-26 09:40:31 发布

cnqmkwvbv44393245

最新推荐文章于 2024-07-26 09:40:31 发布

阅读量137

点赞数

文章标签：数据库

DBMS_REPAIR

1> Oracle DB出现块坏情况下,假如DB没有备份,可以使用Oracle包dbms_repair, 对受损的数据块进行标识.在DB进行查询备份等时, 不在对其标识的数据块进行访问. 如受损的数据块为索引index,删除索引,重新建立即可. 如果受损数据块上有数据,易引起数据丢失.
Oracle坏块查询工具,比如dbv命令; 或用analyze table table_name validate structure cascade 对数据库中比较重要的表进行坏块检查, 不会标记坏块为corrupt,检测的结果保存在USER_DUMP_DEST目录下用户trace文件中;
或查询视图v$copy_corrution,v$backup_corruption,v$database_block_corruption, 也可以查询出坏块的信息,3个视图oracle联机文档解释如下.

V$COPY_CORRUPTION displays information about datafile copy corruptions from the control file.
V$BACKUP_CORRUPTION displays information about corrupt block ranges in datafile backups from the control file. Note that corruptions are not tolerated in the control file and archived redo log backups.
V$DATABASE_BLOCK_CORRUPTION displays information about database blocks that were corrupted after the last backup.

DBMS_REPAIR Procedures

---各procedure name解释引自oracle联机文档

The following table lists the procedures included in the DBMS_REPAIR package.

Procedure Name	Description
ADMIN_TABLES	Provides administrative functions (create, drop, purge) for repair or orphan key tables. Note: These tables are always created in the SYS schema.
CHECK_OBJECT	Detects and reports corruptions in a table or index
DUMP_ORPHAN_KEYS	Reports on index entries that point to rows in corrupt data blocks
FIX_CORRUPT_BLOCKS	Marks blocks as software corrupt that have been previously identified as corrupt by the CHECK_OBJECTprocedure
REBUILD_FREELISTS	Rebuilds the free lists of the object
SEGMENT_FIX_STATUS	Provides the capability to fix the corrupted state of a bitmap entry when segment space management isAUTO
SKIP_CORRUPT_BLOCKS	When used, ignores blocks marked corrupt during table and index scans. If not used, you get error ORA-01578 when encountering blocks marked corrupt.

Limitations and Restrictions

DBMS_REPAIR procedures have the following limitations:

Tables with LOB data types, nested tables, and varrays are supported, but the out-of-line columns are ignored.
Clusters are supported in the SKIP_CORRUPT_BLOCKS and REBUILD_FREELISTS procedures, but not in the CHECK_OBJECT procedure.
Index-organized tables and LOB indexes are not supported.
The DUMP_ORPHAN_KEYS procedure does not operate on bitmap indexes or function-based indexes.
The DUMP_ORPHAN_KEYS procedure processes keys that are no more than 3,950 bytes long.

~~~ 创建实验环境.
2> 创建测试表空间,用户,表,索引等.
2.1 建测试表空间
SYS> create tablespace ts_corruptblock datafile '/data/test11g/ts_corruptblock01.dbf' size 100m autoextend on maxsize 1000M;
Tablespace created.

2.2 创建测试用户,并付相应权限.
SYS> create user jieyu119
2 identified by jieyu119
3 default tablespace ts_corruptblock
4 account unlock
5 temporary tablespace temp
6 profile default;
User created.

SYS> grant create session,connect,dba to jieyu119;
Grant succeeded.

SYS> alter user jieyu119 default role all;
User altered.

2.3 建立测试表并插入数据.
sqlplus jieyu119/jieyu119@test11g

JIEYU119> select * from tab;
no rows selected

JIEYU119> create table tb_corruptblock tablespace ts_corruptblock
2 as select * from dba_objects;
Table created.

JIEYU119> select count(*) from tb_corruptblock;
COUNT(*)
----------
74872

2.4 创建索引.
JIEYU119> create index idx_corruptblock_name on tb_corruptblock(object_name);
Index created.

3> 制造数据文件有坏块
3.1 $dd of=ts_corruptblock01.dbf bs=8192 conv=notrunc seek=1005 < > Corruption block!
> EOF
0+1 records in
0+1 records out

SYS> alter system flush buffer_cache; --从SGA data buffer cache中清空数据.
System altered.

JIEYU119> select count(*) from tb_corruptblock;
select count(*) from tb_corruptblock   *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 1005) ---比较显著的oracle坏块报错
ORA-01110: data file 7: '/data/test11g/ts_corruptblock01.dbf'
如上访问表tb_corruptblock报错, 因为数据文件ts_corruptblock, 我们已经制造出坏块,且表tb_corruptblock存在于表空间上.
查询视图vdatabase_block_corruption也可以查询出坏块信息如下.
SYS> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
7 1005 1 0 CORRUPT

3.2 如果在db重新启动或查询某一个表时,报如上错误.
  可以用dbv查询异常的数据文件,或根据报错信息找相应的对象.
3.2.1 dbv工具查询数据文件ts_corruptblock 有无坏块

jietestdb$dbv file=/data/test11g/ts_corruptblock01.dbf blocksize=8192;
DBVERIFY: Release 11.2.0.3.0 - Production on Mon Jan 6 17:25:17 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/test11g/ts_corruptblock01.dbf
Page 1005 is marked corrupt
Corrupt block relative dba: 0x01c003ed (file 7, block 1005)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x69747075
last change scn: 0x6f6c.62206e6f seq: 0x63 flg: 0x6b
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x59440601
check value in block header: 0xa21
block checksum disabled
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 1065
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 374
Total Pages Failing (Index): 0
Total Pages Processed (Other): 169
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 11191
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1923629 (0.1923629)
如上dbv查询数据文件时,可以查询此数据文件上的坏块信息.

3.2.2 根据alert预警文件块坏报错信息或dbv工具,查询出坏块信息 (file # 7, block # 1005),可查询出哪个对象出现异常.

SYS> select owner,
2 segment_name,
3 tablespace_name,
4 segment_type
5 from dba_extents
6 where file_id=&fileid and &blockid
7 between block_id and block_id + blocks;
Enter value for fileid: 7
Enter value for blockid: 1005
old 6: where file_id=&fileid and &blockid
new 6: where file_id=7 and 1005
OWNER   SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE
------------------------------ ------------------------------   ------------------------------ ------------------
JIEYU119   TB_CORRUPTBLOCK TS_CORRUPTBLOCK TABLE
如上查询出对象表ts_corruptblock有坏块.

3.2.3  如上文所说检查表坏块还可以用如下命令,特别是重要的表,可以用如下命令进行查询有没有坏块.
SYS> analyze table jieyu119.tb_corruptblock validate structure cascade;
analyze table jieyu119.tb_corruptblock validate structure cascade
* ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 1005)
ORA-01110: data file 7: '/data/test11g/ts_corruptblock01.dbf'
如下为运行analyze table jieyu119.tb_corruptblock validate structure cascade命令后,oracle alert预警文件中的报错信息如下.

Tue Jan 07 09:55:15 2014
Hex dump of (file 7, block 1005) in trace file /u01/product/diag/rdbms/test11g/test11g/trace/test11g_ora_7809.trc
Corrupt block relative dba: 0x01c003ed (file 7, block 1005)
Bad header found during multiblock buffer read
Data in bad block:
type: 67 format: 7 rdba: 0x69747075
last change scn: 0x6f6c.62206e6f seq: 0x63 flg: 0x6b
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x59440601
check value in block header: 0xa21
block checksum disabled
Reading datafile '/data/test11g/ts_corruptblock01.dbf' for corruption at rdba: 0x01c003ed (file 7, block 1005)
Reread (file 7, block 1005) found same corrupt data (no logical check)
Tue Jan 07 09:55:15 2014
Corrupt Block Found
TSN = 8, TSNAME = TS_CORRUPTBLOCK
RFN = 7, BLK = 1005, RDBA = 29361133
OBJN = 76652, OBJD = 76652, OBJECT = TB_CORRUPTBLOCK, SUBOBJECT =
SEGMENT OWNER = JIEYU119, SEGMENT TYPE = Table Segment
Errors in file /u01/product/diag/rdbms/test11g/test11g/trace/test11g_ora_7809.trc (incident=424299):
ORA-01578: ORACLE data block corrupted (file # 7, block # 1005)
ORA-01110: data file 7: '/data/test11g/ts_corruptblock01.dbf'
Incident details in: /u01/product/diag/rdbms/test11g/test11g/incident/incdir_424299/test11g_ora_7809_i424299.trc
Tue Jan 07 09:55:17 2014
Sweep [inc][424299]: completed
Sweep [inc2][424299]: completed
Tue Jan 07 09:55:18 2014
Dumping diagnostic data in directory=[cdmp_20140107095518], requested by (instance=1, osid=7809), summary=[incident=424299].

4> 用DBMS_REPAIR包标识坏块.
4.1 Building a Repair Table or Orphan Key Table

The ADMIN_TABLE procedure is used to create, purge, or drop a repair table or an orphan key table.

A repair table provides information about the corruptions that were found by the CHECK_OBJECT procedure and how these will be addressed if the FIX_CORRUPT_BLOCKS procedure is run. Further, it is used to drive the execution of the FIX_CORRUPT_BLOCKS procedure.

An orphan key table is used when the DUMP_ORPHAN_KEYS procedure is executed and it discovers index entries that point to corrupt rows. The DUMP_ORPHAN_KEYSprocedure populates the orphan key table by logging its activity and providing the index information in a usable manner.
REPAIR_TABLE与ORPHAN_KEY_TABLE解释,摘自oracle联机文档.

4.1.1 创建repair_table
SYS> declare
2 begin
3 dbms_repair.admin_tables(
4 table_name=>'REPAIR_TABLE',
5 table_type=>dbms_repair.repair_table,
6 action=>dbms_repair.create_action,
7 tablespace=>'ts_corruptblock');
8 end;
9 /
PL/SQL procedure successfully completed.

SYS> desc repair_table;
Name Null?     Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
OBJECT_ID    NOT NULL    NUMBER
TABLESPACE_ID   NOT NULL     NUMBER
RELATIVE_FILE_ID NOT NULL   NUMBER
BLOCK_ID NOT NULL   NUMBER
CORRUPT_TYPE NOT NULL   NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL   VARCHAR2(30)
BASEOBJECT_NAME   VARCHAR2(30)
PARTITION_NAME    VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT   NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE

SYS> set long 100000
SYS> set line 150
SYS> col owner for a10
SYS> col object_name for a30
SYS>
SYS> select owner,object_name,object_id,object_type,status from dba_objects where object_name like '%REPAIR_TABLE%';

OWNER OBJECT_NAME OBJECT_ID    OBJECT_TYPE    STATUS
----------   ------------------------------    ----------   -------------------    -------
SYS REPAIR_TABLE   76666      TABLE     VALID
SYS DBA_REPAIR_TABLE   76667   VIEW       VALID

4.1.2 创建orphan_key_table
SYS> declare
2 begin
3 dbms_repair.admin_tables(
4 table_name=>'ORPHAN_KEY_TABLE',
5 table_type=>dbms_repair.orphan_table,
6 action=>dbms_repair.create_action,
7 tablespace=>'ts_corruptblock');
8 end;
9 /
PL/SQL procedure successfully completed.

SYS> desc orphan_key_table;
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
SCHEMA_NAME NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
IPART_NAME VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(30)
PART_NAME VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE

SYS> select owner,object_name,object_id,object_type,status from dba_objects where object_name like '%ORPHAN_KEY_TABLE%';
OWNER   OBJECT_NAME OBJECT_ID    OBJECT_TYPE     STATUS
---------- ------------------------------   ----------   -------------------    -------
SYS ORPHAN_KEY_TABLE 76668   TABLE   VALID
SYS DBA_ORPHAN_KEY_TABLE 76669   VIEW VALID

4.2 Detecting Corruption
check_object会检查DB坏块信息,且把信息写到表REPAIR_TABLE & 视图DBA_REPAIR_TABLE中.

The CHECK_OBJECT procedure checks the specified object, and populates the repair table with information about corruptions and repair directives. You can optionally specify a range, partition name, or subpartition name when you want to check a portion of an object.

Validation consists of checking all blocks in the object that have not previously been marked corrupt. For each block, the transaction and data layer portions are checked for self consistency. During CHECK_OBJECT, if a block is encountered that has a corrupt buffer cache header, then that block is skipped.

The following is an example of executing the CHECK_OBJECT procedure for the scott.dept table..

SYS> set serveroutput on;
SYS> declare
2 num_corrupt int;
3 begin
4 num_corrupt :=0;
5 dbms_repair.check_object(
6 schema_name=>'JIEYU119',
7 object_name=>'TB_CORRUPTBLOCK',
8 object_type=>DBMS_REPAIR.TABLE_OBJECT,
9 repair_table_name=>'REPAIR_TABLE',
10 corrupt_count=>num_corrupt);
11
12 dbms_output.put_line('Number block corrupt: '||to_char(num_corrupt));
13 end;
14 /
Number block corrupt: 1
PL/SQL procedure successfully completed.

SYS> select object_id,tablespace_id,relative_file_id,block_id,schema_name,object_name,marked_corrupt,repair_description from repair_table;

OBJECT_ID TABLESPACE_ID RELATIVE_FILE_ID BLOCK_ID SCHEMA_NAME    OBJECT_NAME MARKED_CORRUPT REPAIR_DESCRIPTION
  ---------- -------------   ---------------- ---------- ---------------   ------------------------- ---------- ----------------------------------------
76652 8 7 1005 JIEYU119 TB_CORRUPTBLOCK    TRUE mark block software corrupt

注:查询repair_table 栏位marked_corrupt为TRUE. 已经标识坏块.

4.3 Fixing Corrupt Blocks

Use the FIX_CORRUPT_BLOCKS procedure to fix the corrupt blocks in specified objects based on information in the repair table that was generated bytheCHECK_OBJECT procedure.
Before changing a block, the block is checked to ensure that the block is still corrupt. Corrupt blocks are repaired by marking the block software corrupt. When a repair is performed,
the associated row in the repair table is updated with a timestamp.

SYS> set serveroutput on;

SYS> declare

2 num_fix int;

3 begin

4 dbms_repair.fix_corrupt_blocks(

5 schema_name=>'JIEYU119',

6 object_name=>'TB_CORRUPTBLOCK',

7 object_type=>dbms_repair.table_object,

8 repair_table_name=>'REPAIR_TABLE',

9 fix_count=>num_fix);

11 dbms_output.put_line('Number fix: ' ||to_char(num_fix));

12 end;

13 /

Number fix: 0

PL/SQL procedure successfully completed.

注：由于check_object已经标识坏块了,所以fix_corrupt_blocks现在显示为0. Oracle官方文档中解释在check_object步骤时,repair_table中栏位MARKED_CORRUPT为false, 在fix_corupt_blocks中会把MARKED_CORRUPT 更新为true.

以上试验中,check_object步骤中,MARKED_CORRUPT 已经被置为true了.

4.4 Finding Index Entries Pointing to Corrupt Data Blocks

The DUMP_ORPHAN_KEYS procedure reports on index entries that point to rows in corrupt data blocks. For each index entry, a row is inserted into the specified orphan key table. The orphan key table must have been previously created.

This information can be useful for rebuilding lost rows in the table and for diagnostic purposes.

Note:

This should be run for every index associated with a table identified in the repair table.

SYS> set serveroutput on;
SYS> declare
2 num_orphan int;
3 begin
4 dbms_repair.dump_orphan_keys(
5 schema_name=> 'JIEYU119',
6 object_name=>'IDX_CORRUPTBLOCK_NAME',
7 object_type=>dbms_repair.index_object,
8 repair_table_name=>'REPAIR_TABLE',
9 orphan_table_name=>'ORPHAN_KEY_TABLE',
10 key_count=>num_orphan);
11
12 dbms_output.put_line('Number orphan count: ' || to_char(num_orphan));
13 end;
14 /
Number orphan count: 68

PL/SQL procedure successfully completed.

此时,sys.orphan_key_table中被插入值,数据值与如上dump_orphan_keys返回值相同.
SYS> SELECT COUNT(*)
2 FROM orphan_key_table;

COUNT(*)
----------
68

4.5 Skipping Corrupt Blocks

The SKIP_CORRUPT_BLOCKS procedure enables or disables the skipping of corrupt blocks during index and table scans of the specified object. When the object is a table, skipping applies to the table and its indexes. When the object is a cluster, it applies to all of the tables in the cluster, and their respective indexes. 启用或禁止跳过索引或表的坏块.

SYS> declare
2 begin
3 dbms_repair.skip_corrupt_blocks(
4 schema_name=>'JIEYU119',
5 object_name=>'TB_CORRUPTBLOCK',
6 object_type=>dbms_repair.table_object,
7 flags=>dbms_repair.skip_flag);
8 end;
9 /

PL/SQL procedure successfully completed.

查询dba_tables中表tb_corrupt_block的skip_corrupt变为enabled.

SYS> select owner,table_name,tablespace_name,skip_corrupt from dba_tables where owner='JIEYU119';

OWNER    TABLE_NAME TABLESPACE_NAME SKIP_CORRUPT
------------------------------   ------------------------------   ------------------------------   --------
JIEYU119    TB_CORRUPTBLOCK TS_CORRUPTBLOCK ENABLED

SYS> select count(*) from jieyu119.tb_corruptblock;

COUNT(*)
----------
74804
表jieyu119.tb_corruptblock现在可以查询了,查询表数据值如上.

SYS> select count(owner) from jieyu119.tb_corruptblock;

COUNT(OWNER)
------------
74804

查询栏位owner值如上.

SYS> select count(object_name) from jieyu119.tb_corruptblock;

COUNT(OBJECT_NAME)
------------------
74872

查询索引值如上. 如上三条记录数值不相等. 下面我们把索引重新REBUILD一下,观察三个值有无变化.

SYS> alter index jieyu119.idx_corruptblock_name rebuild;
Index altered.
SYS>
SYS> select count(*) from jieyu119.tb_corruptblock;

COUNT(*)
----------
74804

SYS> select count(owner) from jieyu119.tb_corruptblock;

COUNT(OWNER)
------------
74804
SYS>
SYS> select count(object_name) from jieyu119.tb_corruptblock;

COUNT(OBJECT_NAME)
------------------
74872

如上可以观察出三个值未有变化,下面我们把索引DROP掉,在观察一下数值变化.

SYS> drop index jieyu119.idx_corruptblock_name;

Index dropped.

SYS> create index jieyu119.idx_corruptblock_name on jieyu119.tb_corruptblock(object_name);

Index created.

SYS> select count(*) from jieyu119.tb_corruptblock;

COUNT(*)
----------
74804
SYS>
SYS> select count(owner) from jieyu119.tb_corruptblock;

COUNT(OWNER)
------------
74804
SYS>
SYS> select count(object_name) from jieyu119.tb_corruptblock;

COUNT(OBJECT_NAME)
------------------
74804

索引重新DROP掉,重新建立后,如上三个数据最后相同.
在上文步骤2.3, 表jieyu119.tb_corruptblock刚建立时, 表的数值为74872, 现在查询表的数值为74804 数据有丢失.
写在最后, DB的有效备份重于一切, 如果对象为表,用DBMS_REPAIR进行坏块标识, 会丢失数据. 所以对于重要的表, 数据丢失, 我们是不允许的.