绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
数据库block corruption的检测和恢复模拟
2020-09-08 23:24:54

在生产库中一旦出现坏块就需要及时处理,比如需要确认坏块影响对象范围,对应数据文件是否备份,是否有足够旧的归档,针对不同对象如何恢复,本文给出一个完整的模拟过程,后续讨论了几种坏块恢复方式,以及文件是否备份,归档等的处理方法。我们做过这样的验证,手里有对应的脚本,再遇到类似问题就心里不慌了。

1 模拟环境准备

创建表空间

SYS@orcl2>create tablespace corp_tbs datafile '/oracle/db/corp_tbs.dbf' size 10m;

Tablespace created.

 创建表

SYS@orcl2>create table scott.corp_tab tablespace corp_tbs as select * from scott.dept;

Table created.

Elapsed: 00:00:00.19

检查表段块分配情况

SYS@orcl2>select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from scott.corp_tab;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)

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

                                 139                                   11

                                 139                                   11

                                 139                                   11

                                 139                                   11

说明该表仅仅使用了一块,就是11号文件的139号数据块

SYS@orcl2> select file_name,tablespace_name from dba_data_files where file_id=11

FILE_NAME                                  TABLESPACE_NAME

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

/oracle/db/corp_tbs.dbf              CORP_TBS

2 模拟破坏数据块,我们使用vim编辑数据文件,随机插入几个字符

RMAN> recover datafile 11 block 139 clear;

Starting recover at 08-SEP-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=30 instance=orcl2 device type=DISK

Finished recover at 08-SEP-20

 3 对块中的表数据做一个全表扫描,触发错误

测试库为11.2.0.4该版本数据库在告警日志中已经有非常丰富的信息

Hex dump of (file 11, block 139) in trace file /oracle/db/diag/rdbms/orcl/orcl2/trace/orcl2_ora_27567.trc

Corrupt block relative dba: 0x02c0008b (file 11, block 139)

Bad header found during buffer read

Data in bad block:

 type: 6 format: 2 rdba: 0x02c0008b

 last change scn: 0x0000.0050188a seq: 0x1 flg: 0x04

 spare1: 0x91 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x188a0601

 check value in block header: 0xf626

 computed block checksum: 0x5891

Reading datafile '/oracle/db/corp_tbs.dbf' for corruption at rdba: 0x02c0008b (file 11, block 139)

Reread (file 11, block 139) found same corrupt data (no logical check)

Tue Sep 08 22:50:57 2020

Corrupt Block Found

         TSN = 10, TSNAME = CORP_TBS

         RFN = 11, BLK = 139, RDBA = 46137483

         OBJN = 16186, OBJD = 16187, OBJECT = CORP_TAB, SUBOBJECT = 

Errors in file /oracle/db/diag/rdbms/orcl/orcl2/trace/orcl2_ora_27567.trc  (incident=2140394):

ORA-01578: ORACLE data block corrupted (file # 11, block # 139)                                              <<<<<<<<<<ORA-1578错误出现了

ORA-01110: data file 11: '/oracle/db/corp_tbs.dbf'

         SEGMENT OWNER = SCOTT, SEGMENT TYPE = Table Segment

Incident details in: /oracle/db/diag/rdbms/orcl/orcl2/incident/incdir_2140394/orcl2_ora_27567_i2140394.trc

Tue Sep 08 22:51:01 2020

Sweep [inc][2140394]: completed

Tue Sep 08 22:51:01 2020

Dumping diagnostic data in directory=[cdmp_20200908225101], requested by (instance=2, osid=27567), summary=[incident=2140394].

Hex dump of (file 11, block 139) in trace file /oracle/db/diag/rdbms/orcl/orcl2/incident/incdir_2140394/orcl2_m000_29080_i2140394_a.trc

Corrupt block relative dba: 0x02c0008b (file 11, block 139)

Bad header found during validation

Data in bad block:

 type: 6 format: 2 rdba: 0x02c0008b

 last change scn: 0x0000.0050188a seq: 0x1 flg: 0x04

 spare1: 0x91 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x188a0601

 check value in block header: 0xf626

 computed block checksum: 0x5891

Reread of blocknum=139, file=/oracle/db/corp_tbs.dbf. found same corrupt data

Reread of blocknum=139, file=/oracle/db/corp_tbs.dbf. found same corrupt data

Reread of blocknum=139, file=/oracle/db/corp_tbs.dbf. found same corrupt data

Reread of blocknum=139, file=/oracle/db/corp_tbs.dbf. found same corrupt data

Reread of blocknum=139, file=/oracle/db/corp_tbs.dbf. found same corrupt data

涉及文件号,块号、坏块中涉及的对象

SYS@orcl2>select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

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

        11          139                   1                                  5249162 CORRUPT

Elapsed: 00:00:00.03

通过如下脚本检查更详细的坏块涉及的对象信息

set pagesize 2000

set linesize 280

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#

     , greatest(e.block_id, c.block#) corr_start_block#

     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#

     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)

       - greatest(e.block_id, c.block#) + 1 blocks_corrupted

     , corruption_type description

  FROM dba_extents e, v$database_block_corruption c

 WHERE e.file_id = c.file#

   AND e.block_id <= c.block# + c.blocks - 1

   AND e.block_id + e.blocks - 1 >= c.block#

UNION

SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#

     , header_block corr_start_block#

     , header_block corr_end_block#

     , 1 blocks_corrupted

     , corruption_type||' Segment Header' description

  FROM dba_segments s, v$database_block_corruption c

 WHERE s.header_file = c.file#

   AND s.header_block between c.block# and c.block# + c.blocks - 1

UNION

SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#

     , greatest(f.block_id, c.block#) corr_start_block#

     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#

     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)

       - greatest(f.block_id, c.block#) + 1 blocks_corrupted

     , 'Free Block' description

  FROM dba_free_space f, v$database_block_corruption c

 WHERE f.file_id = c.file#

   AND f.block_id <= c.block# + c.blocks - 1

   AND f.block_id + f.blocks - 1 >= c.block#

order by file#, corr_start_block#;

OWNER                          SEGMENT_TYPE       SEGMENT_NAME         PARTITION_NAME                      FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION

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

SCOTT                                TABLE                           CORP_TAB                                                                   11                   139                             139                       1                CORRUPT

这个信息最详细,用户,段名,段类型,文件号,起始块号,终止块号,一共坏了几个块

4 恢复坏块

1 RMAN块恢复(基于备份,效果最好)

2 标记坏块做逻辑导出导入(该表变更时间不可用,使用DBMS_REPAIR跳过坏块)

3 恢复整个数据文件(基于备份restore,recover业务影响整个数据文件涉及表对象)

4 如果是其他对象如索引,约束等可以重建解决,索引的重建要充分考虑业务需求,防止由于索引失效引起的性能问题

5 验证我们是否有该文件的备份

RMAN> list backup of datafile 11;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

40      Full    83.96M     DISK        00:00:37     08-SEP-20      

        BP Key: 40   Status: AVAILABLE  Compressed: YES  Tag: TAG20200908T221449

        Piece Name: /oracle/db/product/11.2/dbs/1av9u9u3_1_1

  List of Datafiles in backup set 40

  File LV Type Ckp SCN    Ckp Time  Name

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

  11      Full 5245565    08-SEP-20 /oracle/db/corp_tbs.dbf

发现有备份,我可以尝试直接恢复该数据块,如果该数据文件中没有,比如这是个较早的备份就需要更旧归档文件,可以参考下面注意部分

注意:

如果没有需要事先catalog该文件的映像

RMAN>catalog datafilecopy ‘/oracle/db/corp_tab.dbf’;

如果相关归档不存在(老化,挪走等原因),需要catalog归档

RMAN>catalog archivelog ‘/uo1/backup/archivelog/arch_01.dbf’

或者catalog一个目录下所有归档文件。

下面我们尝试做RMAN block recover

RMAN> blockrecover datafile 11 block 139;

Starting recover at 08-SEP-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1914 instance=orcl2 device type=DISK

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00011

channel ORA_DISK_1: reading from backup piece /oracle/db/product/11.2/dbs/1av9u9u3_1_1

channel ORA_DISK_1: piece handle=/oracle/db/product/11.2/dbs/1av9u9u3_1_1 tag=TAG20200908T221449

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 08-SEP-20

RMAN恢复成功,我们后续做业务验证,查询该坏块中的表

5 业务验证

SYS@orcl2>select * from scott.corp_tab

    DEPTNO DNAME          LOC

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

        20 RESEARCH       DALLAS

        10 ACCOUNTING     NEW YORK

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

Elapsed: 00:00:00.01

模拟验证完毕!

分享好友

分享这个小栈给你的朋友们,一起进步吧。

Oracle运维新鲜事-技术与管理各占半边天
创建时间:2020-08-04 11:34:57
本技术栈旨在分享技术心得,运维趣事,故障处理经验,调优案例,故障处理涉及集群,DG,OGG,大家生产中遇到的问题基本都会囊括了,我会发布生产库遇到的故障,希望在交流中互助互益,共同提高,也希望大家讨论,如果您有生产中遇到的集群问题,也可以在这里提出来,一起讨论,现实中也帮助不少同学解决了生产库的故障。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • Abraham林老师
    栈主
  • 小雨滴
    嘉宾
  • hawkliu
    嘉宾
  • u_97a59a25246404
    嘉宾

小栈成员

查看更多
  • 栈栈
  • dapan
  • 小菜鸟___
  • hwayw
戳我,来吐槽~