Oracle数据块损坏之10231内部事件不完全恢复

数据库  /  管理员 发布于 6年前   519

什么是块损坏：

所谓损坏的数据块，是指块没有采用可识别的 Oracle 格式，或者其内容在内部不一致。通常情况下，损坏是由硬件故障或操作系统问题引起的。Oracle 数据库将损坏的块标识为“逻辑损坏”或“介质损坏”。如果是逻辑损坏，则是 Oracle 内部错误。Oracle 数据库检测到不一致之后，就将逻辑损坏的块标记为损坏。如果是介质损坏，则是块格式不正确；从磁盘读取的块不包含有意义的信息。实验：某个分区数据块损坏，不完全恢复此分区表数据。

 背景：数据库没有有效备份，某个分区中有数据块损坏。

 要求：最大限度恢复此分区数据。

 环境：RHEL 6.4 + Oracle 11.2.0.4

下面这篇文章主要给大家介绍了关于Oracle数据块损坏之10231内部事件的相关内容，分享出来供大家参考学习，下面来看看详细的介绍：

1. 初始化实验环境

初始化创建模拟实验环境用到的表空间、业务用户、表，并导入测试数据。

本次实验用到表空间DBS_D_JINGYU, 业务用户JINGYU, 分区表T_PART(含两个分区的测试数据)。

-- 数据表空间create tablespace dbs_d_jingyu datafile '/u02/oradata/jingyu/dbs_d_jingyu01.dbf' size 30M autoextend off;-- 临时表空间create temporary tablespace temp_jingyu tempfile '/u02/oradata/jingyu/temp_jingyu01.tmp' size 30M autoextend off;-- 索引表空间(可选)create tablespace dbs_i_jingyu datafile '/u02/oradata/jingyu/dbs_i_jingyu01.dbf' size 30M autoextend off;-- 假设创建用户 jingyu 密码 jingyu，默认临时表空间 temp_jingyu， 默认数据表空间 dbs_d_jingyu。CREATE USER jingyu IDENTIFIED BY jingyu TEMPORARY TABLESPACE temp_jingyu DEFAULT TABLESPACE dbs_d_jingyu QUOTA UNLIMITED ON dbs_d_jingyu;-- 赋予普通业务用户权限grant resource, connect to jingyu;-- 赋予DBA用户权限grant dba to jingyu;-- 业务用户登录conn jingyu/jingyu -- 1.1 创建分区表create table t_part(id number, name varchar2(20), start_time date, content varchar2(200))partition by range(start_time)( partition P20150101 values less than (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace dbs_d_jingyu, partition P20150102 values less than (TO_DATE(' 2015-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace dbs_d_jingyu, partition P20150103 values less than (TO_DATE(' 2015-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace dbs_d_jingyu);-- 1.2 插入测试数据--分区P20150102插入10000行数据begin for i in 1..10000 loop insert into t_part values (i,'alfred'||i, to_date('2015-01-01','yyyy-mm-dd'), 'AAAAAAAAAA'); end loop; commit;end;/--分区P20150103插入20000行数据begin for i in 10001..30000 loop insert into t_part values (i,'alfred'||i, to_date('2015-01-02','yyyy-mm-dd'), 'AAAAAAAAAA'); end loop; commit;end;/-- 1.3查询表数据量和大小select count(1) from t_part; --result: 30000select count(1) from t_part partition(P20150102); --result: 10000select count(1) from t_part partition(P20150103); --result: 20000--普通表/分区表的每个分区大约__G大小set linesize 160col segment_name for a30select (t.bytes/1024/1024) "MB", t.owner, t.segment_name, t.partition_name, t.tablespace_name from dba_segments t where segment_name = 'T_PART'; MB OWNER  SEGMENT_NAME  PARTITION_NAME  TABLESPACE_NAME---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ 8 JINGYU  T_PART  P20150102  DBS_D_JINGYU 8 JINGYU  T_PART  P20150103  DBS_D_JINGYU

2. 模拟分区中有数据块损坏情景

我这里使用BBED制造坏块，修改t_part分区表的分区P20150103中的某个块内容，模拟真实环境中有数据块损坏的情景。

--查询分区P20150103的HEADER_BLOCKselect header_file,header_block from dba_segments where segment_name='T_PART' and partition_name='P20150103' and owner='JINGYU';SQL> select header_file,header_block from dba_segments where segment_name='T_PART' and partition_name='P20150103' and owner='JINGYU';HEADER_FILE HEADER_BLOCK----------- ------------  5  1169--查询某一行记录所在的块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_part where id = 20000; SQL> select 2 rowid, 3 dbms_rowid.rowid_relative_fno(rowid)rel_fno, 4 dbms_rowid.rowid_block_number(rowid)blockno, 5 dbms_rowid.rowid_row_number(rowid) rowno 6 from t_part where id = 20000;ROWID   REL_FNO BLOCKNO ROWNO------------------ ---------- ---------- ----------AAAVveAAFAAAATBABX  5 1217  87

使用bbed工具破坏5号文件1217块内容，

BBED工具：///article/118349.htm

[oracle@JY-DB01 ~]$ bbed parfile=/tmp/bbed.parPassword:BBED: Release 2.0.0.0.0 - Limited Production on Tue Jan 19 11:37:59 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.************* !!! For Oracle Internal Use only !!! ***************BBED> set dba 5,1217 DBA  0x014004c1 (20972737 5,1217)BBED> map File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf (5) Block: 1217     Dba:0x014004c1------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes   @0  struct ktbbh, 72 bytes   @20  struct kdbh, 14 bytes   @100  struct kdbt[1], 4 bytes   @114  sb2 kdbr[177]    @118  ub1 freespace[815]    @472  ub1 rowdata[6901]    @1287  ub4 tailchk    @8188 BBED> d /v offset 0 count 128 File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf (5) Block: 1217 Offsets: 0 to 127 Dba:0x014004c1------------------------------------------------------- 06a20000 c1044001 52733100 00000106 l [email protected]..... a18b0000 01000c00 de5b0100 4d733100 l .........[..Ms1. 0000e81f 021f3200 81044001 02001b00 l ......2...@..... 5d0b0000 fc0fc000 df030600 b1200000 l ]............ .. 52733100 00000000 00000000 00000000 l Rs1............. 00000000 00000000 00000000 00000000 l ................ 00000000 0001b100 ffff7401 a3042f03 l ..........t.../. 2f030000 b100711f 4a1f231f fc1ed51e l /.....q.J.#..... <16 bytes per line>BBED> modify /x 19901010 offset 0 File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf (5) Block: 1217  Offsets: 0 to 127  Dba:0x014004c1------------------------------------------------------------------------ 19901010 c1044001 52733100 00000106 a18b0000 01000c00 de5b0100 4d733100  0000e81f 021f3200 81044001 02001b00 5d0b0000 fc0fc000 df030600 b1200000  52733100 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 0001b100 ffff7401 a3042f03 2f030000 b100711f 4a1f231f fc1ed51e <32 bytes per line>BBED> sum applyCheck value for File 5, Block 1217:current = 0xa9ae, required = 0xa9aeBBED>

至此破坏了5号文件，1217块。

查询v$database_block_corruption

select * from v$database_block_corruption;SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO---------- ---------- ---------- ------------------ ---------  5 1217  1   0 CORRUPT--此时查询分区表T_PARTalter system flush buffer_cache;select count(1) from t_part;--查询报错ORA-01578select count(1) from t_part partition(P20150102);--查询正常，即分区P20150102未受影响select count(1) from t_part partition(P20150103);--查询报错ORA-01578--尝试逻辑导出表数据失败[oracle@JY-DB01 ~]$ exp jingyu/jingyu tables=t_part file=t_part.dmp log=exp_t_part.logExport: Release 11.2.0.4.0 - Production on Tue Jan 19 11:52:21 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table    T_PART. . exporting partition   P20150101  0 rows exported. . exporting partition   P20150102 10000 rows exported. . exporting partition   P20150103EXP-00056: ORACLE error 1578 encounteredORA-01578: ORACLE data block corrupted (file # 5, block # 1217)ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'Export terminated successfully with warnings.[oracle@JY-DB01 ~]$

3. 尝试使用Oracle内部事件10231进行不完全恢复

使用Oracle 10231内部事件可以跳过坏块

--启用10231内部事件alter system set events='10231 trace name context forever,level 10';--关闭10231内部事件alter system set events='10231 trace name context off';

测试设置10231事件后是否可以逻辑导出：

[oracle@JY-DB01 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 14:01:43 2016Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> alter system set events='10231 trace name context forever,level 10';System altered.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing options[oracle@JY-DB01 ~]$ exp jingyu/jingyu tables=t_part file=t_part.dmp log=exp_t_part.logExport: Release 11.2.0.4.0 - Production on Tue Jan 19 14:01:57 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table    T_PART. . exporting partition   P20150101  0 rows exported. . exporting partition   P20150102 10000 rows exported. . exporting partition   P20150103 19823 rows exportedExport terminated successfully without warnings.--成功导出后记得要关闭10231内部事件alter system set events='10231 trace name context off';20000 - 19823 = 177行，也就是说该数据块损坏直接导致了177行数据丢失。不过还好，保住了大部分数据。

实际上设置10231内部事件后，如果上面逻辑导出没问题，这种情况自然还可以把数据直接导出到临时表，更加方便。

SQL> select count(1) from t_part;select count(1) from t_part*ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 5, block # 1217)ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'SQL> alter system set events='10231 trace name context forever,level 10';System altered.SQL> select count(1) from t_part; COUNT(1)---------- 29823SQL> create table temp_t_part_20150103 as select * from t_part partition(P20150103);Table created.SQL> alter system set events='10231 trace name context off';System altered.SQL> select count(1) from t_part partition(P20150103);select count(1) from t_part partition(P20150103)*ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 5, block # 1217)ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'SQL> select count(1) from temp_t_part_20150103; COUNT(1)---------- 19823

Reference

•http://blog.csdn.net/tianlesoftware/article/details/5024966

•http://blog.csdn.net/seertan/article/details/8507045

•http://blog.csdn.net/coolyl/article/details/195919

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对AIDI的支持。