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

2020-09-09 00:00:00 创建 数据 对象 识别 号数

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

---

NO

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

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条数据,需要业务补录了。

相关文章