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

2024-02-07 17:40:06 267

什么是块损坏：

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

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

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

环境：RHEL6.4+Oracle11.2.0.4

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

1.初始化实验环境

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

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

--数据表空间
createtablespacedbs_d_jingyudatafile'/u02/oradata/jingyu/dbs_d_jingyu01.dbf'size30Mautoextendoff;
--临时表空间
createtemporarytablespacetemp_jingyutempfile'/u02/oradata/jingyu/temp_jingyu01.tmp'size30Mautoextendoff;
--索引表空间(可选)
createtablespacedbs_i_jingyudatafile'/u02/oradata/jingyu/dbs_i_jingyu01.dbf'size30Mautoextendoff;
--假设创建用户jingyu密码jingyu，默认临时表空间temp_jingyu，默认数据表空间dbs_d_jingyu。
CREATEUSERjingyuIDENTIFIEDBYjingyu
TEMPORARYTABLESPACEtemp_jingyu
DEFAULTTABLESPACEdbs_d_jingyu
QUOTAUNLIMITEDONdbs_d_jingyu;
--赋予普通业务用户权限
grantresource,connecttojingyu;
--赋予DBA用户权限
grantdbatojingyu;
--业务用户登录
connjingyu/jingyu
--1.1创建分区表
createtablet_part(
idnumber,
namevarchar2(20),
start_timedate,
contentvarchar2(200)
)partitionbyrange(start_time)
(
partitionP20150101valueslessthan(TO_DATE('2015-01-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
tablespacedbs_d_jingyu,
partitionP20150102valueslessthan(TO_DATE('2015-01-0200:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
tablespacedbs_d_jingyu,
partitionP20150103valueslessthan(TO_DATE('2015-01-0300:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
tablespacedbs_d_jingyu
);

--1.2插入测试数据
--分区P20150102插入10000行数据
begin
foriin1..10000loop
insertintot_partvalues(i,'alfred'||i,to_date('2015-01-01','yyyy-mm-dd'),'AAAAAAAAAA');
endloop;
commit;
end;
/
--分区P20150103插入20000行数据
begin
foriin10001..30000loop
insertintot_partvalues(i,'alfred'||i,to_date('2015-01-02','yyyy-mm-dd'),'AAAAAAAAAA');
endloop;
commit;
end;
/

--1.3查询表数据量和大小
selectcount(1)fromt_part;
--result:30000
selectcount(1)fromt_partpartition(P20150102);
--result:10000
selectcount(1)fromt_partpartition(P20150103);
--result:20000
--普通表/分区表的每个分区大约__G大小
setlinesize160
colsegment_namefora30
select(t.bytes/1024/1024)"MB",t.owner,t.segment_name,t.partition_name,t.tablespace_namefromdba_segmentstwheresegment_name='T_PART';
MBOWNERSEGMENT_NAMEPARTITION_NAMETABLESPACE_NAME
----------------------------------------------------------------------------------------------------------------------------------
8JINGYUT_PARTP20150102DBS_D_JINGYU
8JINGYUT_PARTP20150103DBS_D_JINGYU

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

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

--查询分区P20150103的HEADER_BLOCK
selectheader_file,header_blockfromdba_segmentswheresegment_name='T_PART'andpartition_name='P20150103'andowner='JINGYU';
SQL>selectheader_file,header_blockfromdba_segmentswheresegment_name='T_PART'andpartition_name='P20150103'andowner='JINGYU';

HEADER_FILEHEADER_BLOCK
-----------------------
51169

--查询某一行记录所在的块
select
rowid,
dbms_rowid.rowid_relative_fno(rowid)rel_fno,
dbms_rowid.rowid_block_number(rowid)blockno,
dbms_rowid.rowid_row_number(rowid)rowno
fromt_partwhereid=20000;

SQL>select
2rowid,
3dbms_rowid.rowid_relative_fno(rowid)rel_fno,
4dbms_rowid.rowid_block_number(rowid)blockno,
5dbms_rowid.rowid_row_number(rowid)rowno
6fromt_partwhereid=20000;

ROWIDREL_FNOBLOCKNOROWNO
------------------------------------------------
AAAVveAAFAAAATBABX5121787

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

BBED工具：https://www.nhooo.com/article/118349.htm

[oracle@JY-DB01~]$bbedparfile=/tmp/bbed.par
Password:

BBED:Release2.0.0.0.0-LimitedProductiononTueJan1911:37:592016

Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.

*************!!!ForOracleInternalUseonly!!!***************

BBED>setdba5,1217
DBA0x014004c1(209727375,1217)

BBED>map
File:/u02/oradata/jingyu/dbs_d_jingyu01.dbf(5)
Block:1217Dba:0x014004c1
------------------------------------------------------------
KTBDataBlock(Table/Cluster)

structkcbh,20bytes@0

structktbbh,72bytes@20

structkdbh,14bytes@100

structkdbt[1],4bytes@114

sb2kdbr[177]@118

ub1freespace[815]@472

ub1rowdata[6901]@1287

ub4tailchk@8188


BBED>d/voffset0count128
File:/u02/oradata/jingyu/dbs_d_jingyu01.dbf(5)
Block:1217Offsets:0to127Dba:0x014004c1
-------------------------------------------------------
06a20000c10440015273310000000106l......@.Rs1.....
a18b000001000c00de5b01004d733100l.........[..Ms1.
0000e81f021f32008104400102001b00l......2...@.....
5d0b0000fc0fc000df030600b1200000l]..............
52733100000000000000000000000000lRs1.............
00000000000000000000000000000000l................
000000000001b100ffff7401a3042f03l..........t.../.
2f030000b100711f4a1f231ffc1ed51el/.....q.J.#.....

<16bytesperline>

BBED>modify/x19901010offset0
File:/u02/oradata/jingyu/dbs_d_jingyu01.dbf(5)
Block:1217Offsets:0to127Dba:0x014004c1
------------------------------------------------------------------------
19901010c10440015273310000000106a18b000001000c00de5b01004d733100
0000e81f021f32008104400102001b005d0b0000fc0fc000df030600b1200000
5273310000000000000000000000000000000000000000000000000000000000
000000000001b100ffff7401a3042f032f030000b100711f4a1f231ffc1ed51e

<32bytesperline>

BBED>sumapply
CheckvalueforFile5,Block1217:
current=0xa9ae,required=0xa9ae

BBED>

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

查询v$database_block_corruption

select*fromv$database_block_corruption;

SQL>select*fromv$database_block_corruption;

FILE#BLOCK#BLOCKSCORRUPTION_CHANGE#CORRUPTIO
---------------------------------------------------------
5121710CORRUPT

--此时查询分区表T_PART
altersystemflushbuffer_cache;
selectcount(1)fromt_part;
--查询报错ORA-01578
selectcount(1)fromt_partpartition(P20150102);
--查询正常，即分区P20150102未受影响
selectcount(1)fromt_partpartition(P20150103);
--查询报错ORA-01578

--尝试逻辑导出表数据失败
[oracle@JY-DB01~]$expjingyu/jingyutables=t_partfile=t_part.dmplog=exp_t_part.log

Export:Release11.2.0.4.0-ProductiononTueJan1911:52:212016

Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.


Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction
WiththePartitioning,AutomaticStorageManagement,OLAP,DataMining
andRealApplicationTestingoptions
ExportdoneinZHS16GBKcharactersetandAL16UTF16NCHARcharacterset

AbouttoexportspecifiedtablesviaConventionalPath...
..exportingtableT_PART
..exportingpartitionP201501010rowsexported
..exportingpartitionP2015010210000rowsexported
..exportingpartitionP20150103
EXP-00056:ORACLEerror1578encountered
ORA-01578:ORACLEdatablockcorrupted(file#5,block#1217)
ORA-01110:datafile5:'/u02/oradata/jingyu/dbs_d_jingyu01.dbf'
Exportterminatedsuccessfullywithwarnings.
[oracle@JY-DB01~]$

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

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

--启用10231内部事件
altersystemsetevents='10231tracenamecontextforever,level10';
--关闭10231内部事件
altersystemsetevents='10231tracenamecontextoff';

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

[oracle@JY-DB01~]$sqlplus/assysdba

SQL*Plus:Release11.2.0.4.0ProductiononTueJan1914:01:432016

Copyright(c)1982,2013,Oracle.Allrightsreserved.


Connectedto:
OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction
WiththePartitioning,AutomaticStorageManagement,OLAP,DataMining
andRealApplicationTestingoptions

SQL>altersystemsetevents='10231tracenamecontextforever,level10';

Systemaltered.

SQL>exit
DisconnectedfromOracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction
WiththePartitioning,AutomaticStorageManagement,OLAP,DataMining
andRealApplicationTestingoptions
[oracle@JY-DB01~]$expjingyu/jingyutables=t_partfile=t_part.dmplog=exp_t_part.log

Export:Release11.2.0.4.0-ProductiononTueJan1914:01:572016

Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.


Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction
WiththePartitioning,AutomaticStorageManagement,OLAP,DataMining
andRealApplicationTestingoptions
ExportdoneinZHS16GBKcharactersetandAL16UTF16NCHARcharacterset

AbouttoexportspecifiedtablesviaConventionalPath...
..exportingtableT_PART
..exportingpartitionP201501010rowsexported
..exportingpartitionP2015010210000rowsexported
..exportingpartitionP2015010319823rowsexported
Exportterminatedsuccessfullywithoutwarnings.

--成功导出后记得要关闭10231内部事件
altersystemsetevents='10231tracenamecontextoff';

20000-19823=177行，也就是说该数据块损坏直接导致了177行数据丢失。不过还好，保住了大部分数据。

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

SQL>selectcount(1)fromt_part;
selectcount(1)fromt_part
*
ERRORatline1:
ORA-01578:ORACLEdatablockcorrupted(file#5,block#1217)
ORA-01110:datafile5:'/u02/oradata/jingyu/dbs_d_jingyu01.dbf'

SQL>altersystemsetevents='10231tracenamecontextforever,level10';

Systemaltered.

SQL>selectcount(1)fromt_part;

COUNT(1)
----------
29823

SQL>createtabletemp_t_part_20150103asselect*fromt_partpartition(P20150103);

Tablecreated.

SQL>altersystemsetevents='10231tracenamecontextoff';

Systemaltered.

SQL>selectcount(1)fromt_partpartition(P20150103);
selectcount(1)fromt_partpartition(P20150103)
*
ERRORatline1:
ORA-01578:ORACLEdatablockcorrupted(file#5,block#1217)
ORA-01110:datafile5:'/u02/oradata/jingyu/dbs_d_jingyu01.dbf'

SQL>selectcount(1)fromtemp_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数据块损坏之10231内部事件不完全恢复

热门推荐

随机推荐