使用存储过程DBMS_REPAIR.SKIP_CORRUPT_BLOCKS恢复模拟验证

2020-09-09 10:32:18

recover corruption block in which segment is created with nologging

1 环境准备

创建表空间

SYS@orcl2>create tablespace nologging_tbs datafile '+asmvg1' size 10M;

Tablespace created.

Elapsed: 00:00:01.04

创建表带nologging选项，此时日志记录很少的信息，不足以恢复该数据块中的逻辑记录

SYS@orcl2>create table scott.nologging_tab nologging tablespace nologging_tbs as select * from all_objects;

Table created.

Elapsed: 00:00:00.43

SYS@orcl2>select logging from dba_tables where table_name='NOLOGGING_TAB';

LOG

---

我们看该表段的数据块分布

SYS@orcl2>select dbms_rowid.rowid_block_number(rowid),count(*) from scott.nologging_tab group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)

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

138 80

151 82

152 77

……………

320 73

324 67

185 rows selected.

Elapsed: 00:00:00.12

2 数据破坏模拟

RMAN> recover datafile 12 block 320 clear;

Starting recover at 09-SEP-20

using channel ORA_DISK_1

Finished recover at 09-SEP-20

下面我们做一个全表扫描，使得Oracle可以读到这个坏块，从而报错

SYS@orcl2>select count(*) from scott.nologging_tab;

select count(*) from scott.nologging_tab

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 12, block # 320) <<<<<<<跟我们破坏的块是一致的

ORA-01110: data file 12: '+ASMVG1/orcl/datafile/nologging_tbs.273.1050656631'

Elapsed: 00:00:01.58

3 恢复

这里我们需要再重复下环境，我们在一个表空间中创建了一个表，这个表没有使用logging选项,后续该表的320号数据块坏掉了，我们从之前查询知道

该表记录了73行数据，如果没有备份那这73行记录（常规手段）就无法恢复了。

我们使用之前的脚本检查收到影响对的对象信息。

SYS@orcl2>select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

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

12 320 1 5260417 CHECKSUM

Elapsed: 00:00:00.00

RMAN的validate可以识别NOLOGGING 坏块计入动态视图v$database_block_corruption

RMAN> validate datafile 12;

Starting validate at 09-SEP-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00012 name=+ASMVG1/orcl/datafile/nologging_tbs.273.1050656631

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles

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

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

12 FAILED 0 956 1280 5260421

File Name: +ASMVG1/orcl/datafile/nologging_tbs.273.1050656631

Block Type Blocks Failing Blocks Processed

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

Data 1 185 <<<<<<<处理了185个数据块，有一个验证失败，这个块涉及的对象为Data（用户数据）

Index 0 0

Other 0 139

validate found one or more corrupt blocks

See trace file /oracle/db/diag/rdbms/orcl/orcl2/trace/orcl2_ora_8174.trc for details

Finished validate at 09-SEP-20

下面我们通过脚本获取收到影响的对象的更详细信息

SYS@orcl2>SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#

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

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

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

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

6 , corruption_type description

7 FROM dba_extents e, v$database_block_corruption c

8 WHERE e.file_id = c.file#

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

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

11 UNION

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

13 , header_block corr_start_block#

14 , header_block corr_end_block#

15 , 1 blocks_corrupted

16 , corruption_type||' Segment Header' description

17 FROM dba_segments s, v$database_block_corruption c

18 WHERE s.header_file = c.file#

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

20 UNION

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

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

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

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

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

26 , 'Free Block' description

27 FROM dba_free_space f, v$database_block_corruption c

28 WHERE f.file_id = c.file#

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

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

31* 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 NOLOGGING_TAB 12 320 320 1 CHECKSUM

Elapsed: 00:00:00.06

由此我们知道用户scott的表nologging_tabde 320号数据块损坏，由于该表是nologging的，与没有备份效果类似，我们需要使用存储过程DBMS_REPAIR.SKIP_CORRUPT_BLOCKS跳过坏块，进而通过重建的方式恢复表的可用性。

1 创建repair表

SYS@orcl2>BEGIN

2 DBMS_REPAIR.ADMIN_TABLES (

3 TABLE_NAME => 'REPAIR_TABLE',

4 TABLE_TYPE => dbms_repair.repair_table,

5 ACTION => dbms_repair.create_action,

6 TABLESPACE => '&tablespace_name');

7 END;

8 /

Enter value for tablespace_name: users

old 6: TABLESPACE => '&tablespace_name');

new 6: TABLESPACE => 'users');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17

表结构

SYS@orcl2>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

在执行如下步骤后，会识别坏块并填充表REPAIR_TABLE

2 识别对象的坏块

SYS@orcl2>DECLARE num_corrupt INT;

2 BEGIN

3 num_corrupt := 0;

4 DBMS_REPAIR.CHECK_OBJECT (

5 SCHEMA_NAME => '&schema_name',

6 OBJECT_NAME => '&object_name',

7 REPAIR_TABLE_NAME => 'REPAIR_TABLE',

8 corrupt_count => num_corrupt);

9 DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

10 END;

11 /

Enter value for schema_name: SCOTT

old 5: SCHEMA_NAME => '&schema_name',

new 5: SCHEMA_NAME => 'SCOTT',

Enter value for object_name: NOLOGGING_TAB

old 6: OBJECT_NAME => '&object_name',

new 6: OBJECT_NAME => 'NOLOGGING_TAB',

number corrupt: 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

3 将识别的坏块标记为corrupted

DECLARE num_fix INT;

BEGIN

num_fix := 0;

DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

SCHEMA_NAME => '&schema_name',

OBJECT_NAME=> '&object_name',

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;

Enter value for schema_name: SCOTT

old 5: SCHEMA_NAME => '&schema_name',

new 5: SCHEMA_NAME => 'SCOTT',

Enter value for object_name: NOLOGGING_TAB

old 6: OBJECT_NAME=> '&object_name',

new 6: OBJECT_NAME=> 'NOLOGGING_TAB',

num fix: 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

4 允许DML操作跳过坏块访问数据

BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME => '&schema_name',

OBJECT_NAME => '&object_name',

OBJECT_TYPE => dbms_repair.table_object,

FLAGS => dbms_repair.SKIP_FLAG);

END;

Enter value for schema_name: SCOTT

old 3: SCHEMA_NAME => '&schema_name',

new 3: SCHEMA_NAME => 'SCOTT',

Enter value for object_name: NOLOGGING_TAB

old 4: OBJECT_NAME => '&object_name',

new 4: OBJECT_NAME => 'NOLOGGING_TAB',

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

5 验证查询

SYS@orcl2>select count(*) from scott.NOLOGGING_TAB;

COUNT(*)

----------

14099

Elapsed: 00:00:00.01

现在可以查询该表,该表比之前少了73条数据，现在我们可以跟业务协商，停止数据操作，我们做数据抽取重建该表

create table scott.NOLOGGING_TAB_new as select * from scott.NOLOGGING_TAB;

aalter table scott.NOLOGGING_TAB rename to NOLOGGING_TAB_old;

alter table scott.NOLOGGING_TAB_new rename to NOLOGGING_TAB;

系统运行一段时间可以删除该表

drop table NOLOGGING_TAB_OLD purge;

至此恢复，但是丢失了73条数据，需要业务补录了。

分享好友

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

Oracle运维新鲜事-技术与管理各占半边天

创建时间：2020-08-04 11:34:57

本技术栈旨在分享技术心得，运维趣事，故障处理经验，调优案例，故障处理涉及集群，DG，OGG，大家生产中遇到的问题基本都会囊括了，我会发布生产库遇到的故障，希望在交流中互助互益，共同提高，也希望大家讨论，如果您有生产中遇到的集群问题，也可以在这里提出来，一起讨论，现实中也帮助不少同学解决了生产库的故障。

展开

订阅须知

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

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

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

栈主、嘉宾

查看更多

Abraham林老师
栈主
小雨滴

嘉宾
hawkliu

嘉宾
u_97a59a25246404

嘉宾

小栈成员

查看更多

栈栈
dapan
小菜鸟___
hwayw