使用存储过程DBMS_REPAIR.SKIP_CORRUPT_BLOCKS恢复模拟验证
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条数据，需要业务补录了。
相关文章