Oracle数据块损坏之10231内部事件不完全恢复
什么是块损坏:
所谓损坏的数据块,是指块没有采用可识别的 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: 30000
select count(1) from t_part partition(P20150102);
result: 10000
select count(1) from t_part partition(P20150103);
result: 20000
普通表/分区表的每个分区大约__G大小
set linesize 160
col segment_name for a30
select (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_BLOCK
select 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工具:[oracle@JY-DB01 ~]$ bbed parfile=/tmp/bbed.par
Password:
BBED: Release 2.0.0.0.0 Limited Production on Tue Jan 19 11:37:59 2016
Copyright (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 @.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 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 apply
Check value for File 5, Block 1217:
current = 0xa9ae, required = 0xa9ae
BBED
至此破坏了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_PART
alter system flush buffer_cache;
select count(1) from t_part;
查询报错ORA-01578
select 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.log
Export: Release 11.2.0.4.0 Production on Tue Jan 19 11:52:21 2016
Copyright (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 Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About 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
EXP-00056: ORACLE error 1578 encountered
ORA-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 sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 14:01:43 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL alter system set events= 10231 trace name context forever,level 10
System altered.
SQL exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@JY-DB01 ~]$ exp jingyu/jingyu tables=t_part file=t_part.dmp log=exp_t_part.log
Export: Release 11.2.0.4.0 Production on Tue Jan 19 14:01:57 2016
Copyright (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 Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About 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 exported
Export 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)
-
29823
SQL 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 技术文档 数据库我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 Oracle数据块损坏之10231内部事件不完全恢复
相关文章
- 解决Oracle数据表自动增长(oracle插数据自增)
- Oracle更改联合主键的最佳实践(oracle修改联合主键)
- 实现Oracle数据库最佳性能优化(oracle数据优化)
- 掌握Oracle触发器类型,提升数据库运行能力(oracle触发器类型)
- Oracle自增主键ID的分配策略(oracle主键id)
- Oracle实现单元测试深度剖析(oracle单元测试)
- Oracle收费模式:寻求财富增值之路(oracle 收费方式)
- Oracle 关闭回收站功能,数据效率提高(oracle回收站关闭)
- Oracle给用户授予权限:让用户实现更多功能(oracle 给用户授权)
- Oracle数据库中CASE函数的多功能表现(oracle函数case)
- Oracle 共享游标精准控制数据处理(oracle 共享游标)
- Oracle公共表数据导出实践(oracle公共表到处)
- Oracle数据库中的几个重要池(oracle 几个池)
- Oracle数据库中的联立表更新助力数据同步(oracle中联立表更新)
- Oracle自动增长助力数据库管理的有效解决方案(oracle中的自动增长)
- 利用Oracle云数据库授权,你可以提升数据运算能力(oracle云数据库授权)
- Oracle事件回滚技术 维护数据的安全性(oracle事件回滚)
- 更新处理方法Oracle中超长数据更新的有效解决方案(oracle中数据超长)
- Oracle中借助SQL找寻重复数据的方法(oracle中找重复数据)
- 数据如何在Oracle中插入数据(oracle中 如何插入)
- 揭露Oracle数据下载过程中的缺失(oracle下载数据缺失)
- housing战胜数据挑战Oracle数据仓库解决方案(oracle ware)
- 使用Oracle SQL处理文本数据的方法(oracle sql文本)
- 鉴Oracle SQL迎接新的一年(oracle sql 年)
- 探索Oracle数据库之精髓Oracle原理(oracle or原理)
- 掌握Oracle OGG最新版本,实现数据同步之旅(oracle ogg版本)