Shulou(Shulou.com)06/01 Report--

What is block damage:

The so-called corrupted data block means that the block does not use a recognizable Oracle format, or its contents are inconsistent internally. Usually, the damage is caused by a hardware failure or operating system problem. The Oracle database identifies damaged blocks as "logically corrupted" or "media corrupted". If it is a logical corruption, it is an internal error in Oracle. After the Oracle database detects inconsistencies, it marks logically corrupted blocks as corrupted. If the media is damaged, the block format is incorrect; the block read from disk does not contain meaningful information. Lab: a partition data block is damaged and the partition table data is not fully recovered.

Background: there is no valid backup of the database and a block in a partition is corrupted.

Requirements: maximum recovery of this partition data.

Environment: RHEL 6.4 + Oracle 11.2.0.4

The following article mainly introduces you about the 10231 internal events of Oracle block corruption, which are shared for your reference. Let's take a look at the detailed introduction:

1. Initialize the experimental environment

Initialize the tablespaces, business users, tables used to create a simulated experimental environment, and import test data.

This experiment uses tablespace DBS_D_JINGYU, business user JINGYU, partition table T_PART (test data with two partitions).

-- data tablespace create tablespace dbs_d_jingyu datafile'/ u02oradata size create tablespace dbs_i_jingyu datafile'/ u02 oradata autoextend off;-- temporary tablespace create temporary tablespace temp_jingyu tempfile'/ u02oradataUniverse jingyu01.dbf' oradata 30m autoextend off temporary table space create tablespace dbs_i_jingyu datafile'/ u02aporadataAccordjingyu01.tmp' oradata 30m autoextend off;-- index tablespace (optional) create tablespace dbs_i_jingyu datafile'/ u02aporadataAccording jingyu01.dbf' oradata size 30m -- suppose you create the user jingyu password jingyu, the default temporary tablespace temp_jingyu and the default data tablespace dbs_d_jingyu. CREATE USER jingyu IDENTIFIED BY jingyu TEMPORARY TABLESPACE temp_jingyu DEFAULT TABLESPACE dbs_d_jingyu QUOTA UNLIMITED ON dbs_d_jingyu;-- gives ordinary business users the right to grant resource, and connect to jingyu;-- gives the DBA user the right to grant dba to jingyu -- Business user logs in to conn jingyu/jingyu-- 1.1.Create partition table 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 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace dbs_d_jingyu Partition P20150102 values less than (TO_DATE ('2015-01-02 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 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace dbs_d_jingyu) -- 1.2 insert test data-- Partition P20150102 inserts 10000 rows of data begin for i in 1.. 10000 loop insert into t_part values (I, to_date ('2015-01-01-01)),' AAAAAAAAAA'); end loop; commit;end /-- Partition P20150103 inserts 20000 rows of data begin for i in 10001.. 30000 loop insert into t_part values (I, to_date ('2015-01-02), AAAAAAAAAA'); end loop; commit;end;/-- 1.3 query table data volume and size select count (1) from tasking part;-- result: 30000select count (1) from t_part partition (P20150102);-- result: 10000select count (1) from t_part partition (P20150103) -- result: 20000-each partition of a regular table / partitioned table is about _ _ 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 = 'reserved 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

two。 There is a block corruption scenario in the simulated partition

I use BBED here to create bad blocks and modify the contents of a block in the partition P20150103 of the t_part partition table to simulate the situation of data block corruption in the real world.

-- query 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' of partition P20150103 HEADER_FILE HEADER_BLOCK--5 1169 color-query the block select rowid of a row of records, 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 dbms_rowid.rowid_row_number Rowid REL_FNO BLOCKNO ROWNO--AAAVveAAFAAAATBABX 5 1217 87

Use bbed tool to destroy 1217 pieces of document 5.

BBED tool: https://www.jb51.net/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 DBA 0x014004c1 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 @ 118ub1 freespace [6901] @ 1287 ub4 tailchk @ 8188 BBED > d / v offset 0 count 128File: / u02/oradata/jingyu/dbs_d_jingyu01.dbf (5) Block: 1217 Offsets: 0 to 127Dba:0x014004c1 -06a20000 c1044001 52733100 00000106 l. @ .Rs1. 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 l. 00000000 0001b100 ffff7401 a3042f03 l .t... /. 2f030000 b100711f 4a1f231f fc1ed51e l / .q.J. #. 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 000000000000000000000000000000000000000000 0001b100 ffff7401 a3042f03 2f030000 b100711f 4a1f231f fc1ed51e BBED > sum applyCheck value for File 5 Block 1217:current = 0xa9ae, required = 0xa9aeBBED >

So far, document No. 5 has been destroyed, 1217 yuan.

Query v$database_block_corruption

Select * from vested databaseblockblockblockblock from t_part; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO- 5 1217 10 CORRUPT-- query partition table T_PARTalter system flush buffer_cache;select count (1) from t_part at this time -query error ORA-01578select count (1) from t_part partition (P20150102);-query is normal, that is, partition P20150102 is not affected select count (1) from t_part partition (P20150103) Failed to query error reporting ORA-01578-- attempt logic export table data [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. Attempt to use Oracle internal event 10231 for incomplete recovery

Use Oracle 10231 internal events to skip bad blocks

-- enable 10231 internal event alter system set events='10231 trace name context forever,level 10 internal event alter system set events='10231 trace name context off'-- close 10231 internal event alter system set events='10231 trace name context off'

Test whether you can logically export after setting the 10231 event:

[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. . After the exporting partition P20150103 19823 rows exportedExport terminated successfully without warnings.-- is successfully exported, remember to close the 10231 internal event alter system set events='10231 trace name context off';20000-19823 = 177line, which means that the data block corruption directly caused the 177line data loss. Fortunately, however, most of the data was preserved.

In fact, after setting 10231 internal events, if the above logic export is not a problem, this situation can also export the data directly to the temporary table, which is more convenient.

SQL > select count (1) from tasking partnering 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 partners 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

Summary

The above is the whole content of this article, I hope that the content of this article can bring some help to your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.