数据块恢复总结
一、说明
1.1、坏块故障现象
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 19846)
ORA-01110: data file 7: '+DATA/orcl/datafile/jxc1.270.1010446343’
1.2、坏块类型
数据坏块
索引坏块
其它损坏
1.3、检验坏块的方式
$dbv file=/opt/oracle/oradata/mycdb/system01.dbf
RMAN> validate database|datefile xx;
1.4、坏块处理
若有有效的rman备份则恢复语句如下：
recover datafile 7 block 19846;
recover corruption list; validate 检测后可用该语句进行恢复
若仅有数据泵备份，则从数据泵恢复该表数据到备份的状态。
impdp directory=dump dumpfile=bfapp20.dump SCHEMAS=BFAPP20 include=table:"\=\'T_OBJ\'"
impdp directory=dump dumpfile=bfapp20.dump tables=BFAPP20.T_OBJ
无备份，屏蔽掉坏块，拯救部分数据。
启用10231内部事件 alter system set events='10231 trace name context forever,level 10’;
关闭10231内部事件 alter system set events='10231 trace name context off’;
二、实战演练
2.1、构造数据损坏坏块
1、创建表插入数据
create table t as select object_id,object_name from dba_objects;
insert into t select * from t;
create index t_obj_id on t (object_id);
SQL> exec dbms_stats.gather_table_stats('ZX1','T',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
2.2、查询表占用块相关信息
SQL> select owner,segment_name,header_file,header_block,blocks from dba_segments where owner='ZX1' and segment_name='T';
OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
--------------- -------------------- ----------- ------------ ----------
ZX1 T 54 130 1024
SQL> select rowid,
dbms_rowid.rowid_relative_fno(rowid) rel_fno,
dbms_rowid.rowid_block_number(rowid) blockno,
dbms_rowid.rowid_row_number(rowid) rowno
from T WHERE ROWNUM<=10;
ROWID REL_FNO BLOCKNO ROWNO
------------------ ---------- ---------- ----------
AAAR88AA2AAAACDAAA 54 131 0
AAAR88AA2AAAACDAAB 54 131 1
AAAR88AA2AAAACDAAC 54 131 2
AAAR88AA2AAAACDAAD 54 131 3
AAAR88AA2AAAACDAAE 54 131 4
AAAR88AA2AAAACDAAF 54 131 5
AAAR88AA2AAAACDAAG 54 131 6
AAAR88AA2AAAACDAAH 54 131 7
AAAR88AA2AAAACDAAI 54 131 8
AAAR88AA2AAAACDAAJ 54 131 9
10 rows selected.
2.3、查询索引所在的块
SQL> select owner,segment_name,header_file,header_block,blocks from dba_segments where owner='ZX1' and segment_name='T_OBJ_ID';
OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
--------------- ------------------------------ ----------- ------------ ----------
ZX1 T_OBJ_ID 54 1154 384
2.4、创建数据坏块
[oracle@oracle18c1 ~]$ rman target sys/[email protected]:1521/mypdb1
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Jun 5 14:19:25 2020
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYCDB:MYPDB1 (DBID=2302914177)
RMAN> blockrecover datafile 54 block 131 clear;
Starting recover at 05-JUN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=209 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=204 device type=DISK
Finished recover at 05-JUN-20
2.5、创建索引坏块
RMAN> blockrecover datafile 54 block 1159 clear;
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_2: SID=147 device type=DISK
2.6、查询触发数据坏块
SQL> select count(*) from zx1.t;
select count(*) from zx1.t
*
ORA-01578: ORACLE data block corrupted (file # 54, block # 131)
ORA-01110: data file 54: '/opt/oracle/oradata/mycdb/mypdb1/jxc.dbf'
2.7、查询触发索引坏块
SQL> select count(*) from zx1.t where object_id<=10000;
select count(*) from zx1.t where object_id<=10000
ORA-01578: ORACLE data block corrupted (file # 54, block # 1159)
2.8、使用dbv工具检验坏块信息
[oracle@oracle18c1 ~]$ dbv file=/opt/oracle/oradata/mycdb/mypdb1/jxc.dbf
DBVERIFY: Release 18.0.0.0.0 - Production on Fri Jun 5 15:48:49 2020
DBVERIFY - Verification starting : FILE = /opt/oracle/oradata/mycdb/mypdb1/jxc.dbf
Page 131 is marked corrupt
Corrupt block relative dba: 0x0d800083 (file 54, block 131)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0d800083
last change scn: 0x0000.0000.003a64ed seq: 0x2 flg: 0x04
spare3: 0x0
consistency value in tail: 0x64ed0602
check value in block header: 0xf584
computed block checksum: 0x6100
Page 1159 is marked corrupt
Corrupt block relative dba: 0x0d800487 (file 54, block 1159)
type: 6 format: 2 rdba: 0x0d800487
last change scn: 0x4700.0000.003a651a seq: 0x2 flg: 0x04
consistency value in tail: 0x651a0602
check value in block header: 0x2fe7
computed block checksum: 0x4700
DBVERIFY - Verification complete
Total Pages Examined : 128000
Total Pages Processed (Data) : 937
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 322
Total Pages Failing (Index): 0
Total Pages Processed (Other): 165
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 126574
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3827002 (0.3827002)
2.9 validate 命令验证
[oracle@oracle18c1 ~]$ rman target sys/[email protected]/mypdb1
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Jun 5 15:50:21 2020
RMAN> validate datafile 54;
Starting validate at 05-JUN-20
channel ORA_DISK_1: SID=213 device type=DISK
channel ORA_DISK_2: SID=10 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00054 name=/opt/oracle/oradata/mycdb/mypdb1/jxc.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
54 FAILED 0 126574 128000 3827002
File Name: /opt/oracle/oradata/mycdb/mypdb1/jxc.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 938
Index 1 323
Other 0 165
validate found one or more corrupt blocks
See trace file /opt/oracle/diag/rdbms/mycdb/mycdb/trace/mycdb_ora_18140.trc for details
Finished validate at 05-JUN-20
注意：该视图的更新需要执行validate命令。
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
54 131 1 3826925 CHECKSUM 3
54 1159 1 5.1161E+18 CHECKSUM 3
2.10、索引坏块修复
SQL> alter index ZX1.T_OBJ_ID rebuild online;
Index altered.
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
54 131 1 3826925 CHECKSUM 3
54 1159 1 5.1161E+18 CHECKSUM 3
SQL> select count(*) from ZX1.T where object_id<=20000;
COUNT(*)
----------
39534
2.11、数据坏块修复
1、无备份时处理
SQL> select count(*) from ZX1.T;
select count(*) from ZX1.T
*
SQL> alter system set events='10231 trace name context forever,level 10';
System altered.
145362
2、有rman备份时处理
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Jun 5 16:19:30 2020
RMAN> recover datafile 54 block 131;
channel ORA_DISK_1: SID=84 device type=DISK
channel ORA_DISK_2: SID=152 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 00054
channel ORA_DISK_1: reading from backup piece /backup/fullbk.1qv1vvtp_1_1
channel ORA_DISK_1: piece handle=/backup/fullbk.1qv1vvtp_1_1 tag=TAG20200605T112752
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
RMAN> select count(*) from ZX1.T;
COUNT(*)
----------
145700
三、只有数据泵备份时处理方式
方式1
[oracle@oracle ~]$ impdp directory=dump dumpfile=bfapp20.dump SCHEMAS=BFAPP20 include=table:"\=\'T_OBJ\'"
Import: Release 11.2.0.3.0 - Production on Wed Mar 25 19:47:03 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": /******** AS SYSDBA directory=dump dumpfile=bfapp20.dump SCHEMAS=BFAPP20 include=table:\=\'T_OBJ\'
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "BFAPP20"."T_OBJ" 2.385 MB 74914 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 19:47:10
方式2
[oracle@oracle ~]$ impdp directory=dump dumpfile=bfapp20.dump tables=BFAPP20.T_OBJ
Import: Release 11.2.0.3.0 - Production on Wed Mar 25 19:47:35 2020
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA directory=dump dumpfile=bfapp20.dump tables=BFAPP20.T_OBJ
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 19:47:42