Oracle-通过DBMS_REPAIR处理表坏块

最新推荐文章于 2024-08-18 10:54:35 发布

牛牛的笔记

最新推荐文章于 2024-08-18 10:54:35 发布

阅读量439

点赞数 1

分类专栏： Oracle 文章标签： oracle 数据库 dba 运维 mysql

本文链接：https://blog.csdn.net/sinat_36757755/article/details/126257100

版权

Oracle 专栏收录该内容

105 篇文章 19 订阅

订阅专栏

前言：

数据库可能由于存储磁盘问题，bug，参数配置不合理等意外情况导致出现表坏块问题，如何最大化的减少数据丢失以及恢复表数据访问成为重中之重，Oracle数据库的DBMS_REPAIR包可以对表坏块进行检查，并对坏块进行标记跳过，可以减少数据丢失以及快速恢复表数据访问。

模拟表坏块场景

创建test表

create tablespace tbs_test datafile '/u01/app/oracle/oradata/ORCL/datafile/tbs_test.dbf' size 100M;
create table test.test tablespace tbs_test as select * from dba_objects where object_id is not null;
alter table test.test modify object_id not null;
alter table test.test add primary key (object_id);
create index test.ind_owner_test on test.test(owner) tablespace tbs_test;

SQL> select count(*) from test.test;

  COUNT(*)
----------
     72764

查看数据块的分布

select * 
from (select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid),count(*)
from test.test
group by dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid)
order by 1,2) a
where rownum<11; 

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ------------------------------------ ----------
           2                           131        68
           2                            132        65
           2                            133        63
           2                            134        62
           2                            135        63
           2                            136        63
           2                            137        60
           2                            138        58
           2                            139        61
           2                            140        63

通过bbed修改表数据块131，模拟坏块产生

[oracle@ol7db1 tmp]$ cat /tmp/listfile.par 
1 /u01/app/oracle/oradata/ORCL/datafile/tbs_test.dbf 104857600

[oracle@ol7db1 tmp]$ bbed password=blockedit mode=edit blocksize=8192 listfile=/tmp/listfile.par
************* !!! For Oracle Internal Use only !!! ***************
#确认数据文件加载
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/app/oracle/oradata/ORCL/datafile/tbs_test.dbf               12800
#移动到数据文件1，块131
BBED> set dba 1,131
  DBA              0x00400083 (4194435 1,131)

BBED> dump /v dba 1,131 offset 0
 File: /u01/app/oracle/oradata/ORCL/datafile/tbs_test.dbf (1)
 Block: 131     Offsets:    0 to  511  Dba:0x00400083
-------------------------------------------------------
 06a20000 83008000 37205400 00000204 l ........7 T.....
 4bd20000 01000000 cb210100 30205400 l K........!..0 T.
 00800000 03003200 80008000 ffff0000 l ......2.........
 00000000 00000000 00000000 00800080 l ................
 30205400 00000000 00000000 00000000 l 0 T.............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00014400 l ..............D.
 ffff9a00 ca033003 30030000 44000e1f l ......0.0...D...
 9a1e291e cb1d571d f91c9b1c 271cc91b l ..)...W........
 6b1b031b a51a461a e7197d19 1f19ab18 l k.....F...}.....
 3818be17 5f170017 a1162e16 cf156315 l 8..._.........c.
 f0149114 1e14c013 4713e912 75120b12 l ........G...u...
 98112f11 bc104910 dd0f780f 1b0fa80e l ../...I...x.....
 350ec10d 620d030d a40c3e0c df0b810b l 5...b.....>.....
 020ba30a 450ae709 88092b09 cd086e08 l ....E.....+...n.
 0f089707 2007be06 5c06eb05 85050e05 l .... ...\.......
 a9043004 ca030000 00000000 00000000 l ..0.............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................

 <16 bytes per line>
#修改块第一个偏移字节，模拟坏块
BBED> modify /x 8888888 dba 1,131 offset 0
 File: /u01/app/oracle/oradata/ORCL/datafile/tbs_test.dbf (1)
 Block: 131              Offsets:    0 to  511           Dba:0x00400083
------------------------------------------------------------------------
 08888888 83008000 37205400 00000204 4bd20000 01000000 cb210100 30205400 
 00800000 03003200 80008000 ffff0000 00000000 00000000 00000000 00800080 
 30205400 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00014400 
 ffff9a00 ca033003 30030000 44000e1f 9a1e291e cb1d571d f91c9b1c 271cc91b 
 6b1b031b a51a461a e7197d19 1f19ab18 3818be17 5f170017 a1162e16 cf156315 
 f0149114 1e14c013 4713e912 75120b12 98112f11 bc104910 dd0f780f 1b0fa80e 
 350ec10d 620d030d a40c3e0c df0b810b 020ba30a 450ae709 88092b09 cd086e08 
 0f089707 2007be06 5c06eb05 85050e05 a9043004 ca030000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>
#保留应用修改
BBED> sum dba 1,131 apply
Check value for File 1, Block 131:
current = 0x70cd, required = 0x70cd

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ORCL/datafile/tbs_test.dbf
BLOCK = 131

Block 131 is corrupt
Corrupt block relative dba: 0x00800083 (file 0, block 131)
Bad header found during verification
Data in bad block:
 type: 8 format: 0 rdba: 0x00800083
 last change scn: 0x8888.0000.00542037 seq: 0x2 flg: 0x04
 spare3: 0x0
 consistency value in tail: 0x20370602
 check value in block header: 0x70cd
 computed block checksum: 0x0

#检查数据块，发现有一个坏块
DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1   ---有一个坏块
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED


BBED>

全表查询表数据，出现ORA-01578报错

alter system flush buffer_cache
SQL> select /*+full(test ) */ count(*) from test.test;

ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 2, block # 131)
ORA-01110: data file 2: '/u01/app/oracle/oradata/ORCL/datafile/tbs_test.dbf'

其他方式查询表数据

#通过索引查询，不访问数据块,没有报错

SQL> select /*+index(SYS_C007700 ) */ count(*) from test.test;

  COUNT(*)
----------
     72764


#查询不包含坏块的数据，没有报错
SQL> select dbms_rowid.ROWID_BLOCK_NUMBER('AAASHLAACAAAACIAAA') from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAASEOAACAAAACIAAA')
---------------------------------------------------
                                                136

SQL> select count(*) from test.test where rowid>'AAASHLAACAAAACIAAA';

  COUNT(*)
----------
     72442

使用DBMS_REPAIR跳过表坏块

#创建REPAIR_TABLE，用来存储坏块信息
BEGIN
   DBMS_REPAIR.ADMIN_TABLES (
   TABLE_NAME => 'REPAIR_TABLE',
   TABLE_TYPE => dbms_repair.repair_table,
   ACTION => dbms_repair.create_action,
   TABLESPACE => 'TBS_TEST');
 END;
 /

PL/SQL procedure successfully completed.
#检查表对象，发现一个坏块
 set serveroutput on
 DECLARE num_corrupt INT;
 BEGIN
   num_corrupt := 0;
   DBMS_REPAIR.CHECK_OBJECT (
   SCHEMA_NAME => 'TEST',
   OBJECT_NAME => 'TEST',
   REPAIR_TABLE_NAME => 'REPAIR_TABLE',
   corrupt_count => num_corrupt);
   DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
 END;
 /
number corrupt: 1

PL/SQL procedure successfully completed.

SQL> 

REM Optionally display any corrupted block identified by check_object:

set linesize 400
set pagesize 400
col SCHEMA_NAME for a10
col OBJECT_NAME for a20
 select object_id,RELATIVE_FILE_ID,BLOCK_ID,CORRUPT_TYPE,SCHEMA_NAME,OBJECT_NAME,MARKED_CORRUPT,REPAIR_DESCRIPTION
 from REPAIR_TABLE

 OBJECT_ID RELATIVE_FILE_ID   BLOCK_ID CORRUPT_TYPE SCHEMA_NAM OBJECT_NAM MARKED_CORRUPT       REPAIR_DESCRIPTION
---------- ---------------- ---------- ------------ ---------- ---------- -------------------- --------------------
     74024                2        131         6148 TEST       TEST       TRUE                 mark block software
                                                                                               corrupt
#对坏块进行标记
DECLARE num_fix INT;
BEGIN
  num_fix := 0;
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
  SCHEMA_NAME => 'TEST',
  OBJECT_NAME=> 'TEST',
  OBJECT_TYPE => dbms_repair.table_object,
  REPAIR_TABLE_NAME => 'REPAIR_TABLE',
  FIX_COUNT=> num_fix);
  DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
END;
/

num fix: 0

PL/SQL procedure successfully completed.

#跳过坏块
REM Allow future DML statements to skip the corrupted blocks:

BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
  SCHEMA_NAME => 'TEST',
  OBJECT_NAME => 'TEST',
  OBJECT_TYPE => dbms_repair.table_object,
  FLAGS => dbms_repair.SKIP_FLAG);
END;
/

验证跳过坏块之后的表数据

全表扫描，没有发出现报错，由于出现坏块的数据已经被跳过，所以会出现少量的数据丢失

alter system flush buffer_cache;
SQL> select /*+full(test ) */ count(*) from test.test;

  COUNT(*)
----------
     72696

通过主键索引扫描，没有报错，但是发出现数据不一致的情况，并且还未出现唯一值冲突的情况，主键索引与表数据不一致，需要进行重建

#通过主键索引扫描
SQL>  select /*+index(SYS_C007700 ) */ count(*) from test.test;

  COUNT(*)
----------
     72764

#主键插入，唯一值冲突
SQL> select * from test.test where object_id=2;

no rows selected



SQL> insert into test.test(object_id) values(2);
insert into test.test(object_id) values(2)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.SYS_C007700) violated

重建索引用drop+create的方式，不要用rebuild

alter table test.test drop primary key;
alter table test.test add primary key(object_id);
drop index TEST.ind_owner_test;
create index test.ind_owner_test on test.test(owner) tablespace tbs_test;

重建索引后，表数据和索引一致

SQL>  select /*+index(SYS_C007700 ) */ count(*) from test.test;

  COUNT(*)
----------
     72696

SQL>
 select /*+full(test ) */ count(*) from test.test;

  COUNT(*)
----------
     72696

重新导入表数据

对于修复后的表，建议重新导出再导入数据库，以确保表相关对象的一致性。

#导出test表
expdp \" / as sysdba \" directory=dir_test tables=test.test dumpfile=test.dmp logfile=test.log

Export: Release 19.0.0.0.0 - Production on Tue Aug 9 16:50:29 2022
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=dir_test tables=test.test dumpfile=test.dmp logfile=test.log 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."TEST"                               9.553 MB   72696 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /tmp/test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Aug 9 16:50:43 2022 elapsed 0 00:00:12
#导入test表
impdp \" / as sysdba \" directory=dir_test tables=test.test dumpfile=test.dmp logfile=test.log

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=dir_test tables=test.test dumpfile=test.dmp logfile=imp_test.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."TEST"                               9.553 MB   72696 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Aug 9 16:51:53 2022 elapsed 0 00:00:19

bbed重新验证没有发现坏块

BBED> VERIFY
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_test_kh262zch_.dbf
BLOCK = 1


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0 --->没有坏块
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED


BBED>