Oracle Data Block Corruption Chapter 10231 internal events

Experiment: A partition data block is corrupted and the partition table data is not fully recovered
Background: The database does not have a valid backup and a partition has corrupted data blocks.
Requirement: Maximum recovery of this partition data.
Environment: RHEL 6.4 + Oracle 11.2.0.4

• 1. Initializing the experimental environment
• 2. Data block corruption scenario in the simulation partition
• 3. Attempt to use Oracle Internal event 10231 for incomplete recovery
• Reference

1. Initializing the experimental environment

Initializes the tablespace, business user, table, and import test data used to create the simulated lab environment.
This experiment is used to table space Dbs_d_jingyu, business user Jingyu, partition table T_part (with two partitions of test data).

--Data table space Create tablespace dbs_d_jingyu datafile '/u02/oradata/jingyu/dbs_d_jingyu01.dbf ' size 30M autoextend off;-- Temp table Space Create temporary tablespace Temp_jingyu tempfile '/u02/oradata/jingyu/temp_jingyu01.tmp ' size 30M autoextend off;- -Index Table space (optional) Create tablespace dbs_i_jingyu datafile '/u02/oradata/jingyu/dbs_i_jingyu01.dbf ' size 30M autoextend off;-- Assume that you create a user Jingyu password Jingyu, the default temporary tablespace Temp_jingyu, and the default data table space Dbs_d_jingyu. CREATE USER Jingyu identified by Jingyu temporary tablespace Temp_jingyu DEFAULT tablespace Dbs_d_jingyu QUOTA unlimite D on dbs_d_jingyu;--grants General Service user Rights grant resource, connect to jingyu;--grants DBA user Rights Grant DBA to jingyu;--business user Login Conn jingyu/jing Yu--1.1 Creating a partitioned Table CREATE TABLE T_part (ID number, name varchar2 (), start_time date, content Varchar2) partition by rang E (start_time) (partition P20150101 values less than (To_date (' 2015-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calen Dar=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 P20150 103 Values less than (To_date (' 2015-01-03 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')) tablespace D Bs_d_jingyu);---1.2 insert test data--partition P20150102 insert 10000 rows of data begin for me 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;/--partition P20150103 Insert 20000 rows of data 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 Query table data volume and size 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--Normal table/partition table for each partition approximately __g size 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_jin          Gyu 8 Jingyu T_part P20150103 Dbs_d_jingyu

2. Data block corruption scenario in the simulation partition

I use bbed to create a bad block, modify a block content in the partition P20150103 of the T_part partition table, and simulate a scenario in which the data block is corrupted in the real world.

 --Query partition 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--Query The block where a row of records is located select rowID, Dbms_rowid.rowid_relati VE_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) bloc Kno, 5 dbms_rowid.rowid_row_number (ROWID) Rowno 6 from T_part where id = 20000;          ROWID rel_fno blockno ROWNO------------------------------------------------AAAVVEAAFAAAATBABX 5 1217  

Use the Bbed tool to destroy 1217 blocks of file 5th,
bbed tool: http://www.cnblogs.com/jyzhao/p/5139584.html

[[email protected] ~]$ bbed parfile=/tmp/bbed.parpassword:bbed:release 2.0.0.0.0-limited Production on Tue Jan 19  11:37:59 2016Copyright (c) 1982, Oracle and/or its affiliates. All Rights reserved.*************!!! For Oracle Internal with 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, bytes @0 struct KTB                    BH, Bytes @20 struct kdbh, bytes @100 struct kdbt[1], 4 bytes                         @114 SB2 kdbr[177] @118 ub1 freespace[815] @472 ub1 rowdata[6901] @1287 ub4 tailchk @8188 BBED&G T D /V Offset 0 Count of File:/U02/ORADATA/JINGYU/DBS_D_JINGYU01.DBF (5) block:1217 offsets:0 to 127 dba:0x014004 C1-------------------------------------------------------06a20000 c1044001 52733100 00000106 l [email  Protected] a18b0000 01000c00 de5b0100 4d733100 l ..... [.. Ms1.0000e81f 021f3200 81044001 02001b00 l [email protected] 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 5d0b0fc0fc000 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>

At this point, 5th files were destroyed, 1217 pieces.

Query 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--this time query partition table T_partalter system flush Buffer_cache;select count (1) from t_part;--Query Error Ora-01578select count (1) from T_part partition (P20150102);--query normal, that is, partition P20150102 unaffected Select COUNT (1) from T_ Part partition (P20150103);--Query error ora-01578--attempt to export table data logically failed [[email protected] ~]$ 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 11:52:21 2016Copyright (c) 1982, 2  011, 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 Al16ut F16 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 Cor rupted (File # 5, Block # 1217) ora-01110:data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf ' Export terminated successfu Lly with warnings.  [[email protected] ~]$

3. Attempt to use Oracle Internal event 10231 for incomplete recovery

Use Oracle 10231 internal events to skip bad blocks

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

Whether you can logically export after you test the Setup 10231 event:

[[email protected] ~]$ sqlplus/as sysdbasql*plus:release 11.2.0.4.0 Production on Tue Jan 14:01:43 2016Copyrigh  T (c) 1982, 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 nam E context Forever,level 10 '; System altered. Sql> exitdisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith the Partiti Oning, Automatic Storage Management, OLAP, Data miningand Real application testing options[[email protected] ~]$ 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:  2016Copyright (c) 1982, 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 ZHS16 GBK 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 .--After successful export, remember to close the 10231 internal event alter system set events= ' 10231 Trace name context off '; 20000-19823 = 177 rows, which means that the data block corruption directly leads to 177 rows of data loss 。 But fortunately, most of the data was saved.

In fact, after the 10231 internal events, if the above logical export is not a problem, this situation will naturally be able to export data directly to the temporary table, more convenient.

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

Oracle Data Block Corruption Chapter 10231 internal events