这里有新鲜出炉的精品教程,程序狗速度看过来!
Oracle Database,又名 Oracle RDBMS,或简称 Oracle。是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。
其实对于坏块来说,修复的办法还是很多的,下面这篇文章主要给大家介绍了关于 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 工具:/article/17/0809/344332.html
- [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
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对 PHPERZ 的支持。
来源: http://www.phperz.com/article/17/0825/344333.html