Oracle數(shù)據(jù)塊損壞之10231內(nèi)部事件不完全恢復(fù)
更新時(shí)間：2017年07月11日 08:36:12 作者：AlfredZhao
其實(shí)對(duì)于壞塊來(lái)說(shuō)，修復(fù)的辦法還是很多的，下面這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)塊損壞之10231內(nèi)部事件不完全恢復(fù)的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起看看吧。
什么是塊損壞：
所謂損壞的數(shù)據(jù)塊，是指塊沒(méi)有采用可識(shí)別的 Oracle 格式，或者其內(nèi)容在內(nèi)部不一致。通常情況下，損壞是由硬件故障或操作系統(tǒng)問(wèn)題引起的。Oracle 數(shù)據(jù)庫(kù)將損壞的塊標(biāo)識(shí)為“邏輯損壞”或“介質(zhì)損壞”。如果是邏輯損壞，則是 Oracle 內(nèi)部錯(cuò)誤。Oracle 數(shù)據(jù)庫(kù)檢測(cè)到不一致之后，就將邏輯損壞的塊標(biāo)記為損壞。如果是介質(zhì)損壞，則是塊格式不正確；從磁盤(pán)讀取的塊不包含有意義的信息。實(shí)驗(yàn)：某個(gè)分區(qū)數(shù)據(jù)塊損壞，不完全恢復(fù)此分區(qū)表數(shù)據(jù)。
背景：數(shù)據(jù)庫(kù)沒(méi)有有效備份，某個(gè)分區(qū)中有數(shù)據(jù)塊損壞。
要求：最大限度恢復(fù)此分區(qū)數(shù)據(jù)。
環(huán)境：RHEL 6.4 + Oracle 11.2.0.4
下面這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)塊損壞之10231內(nèi)部事件的相關(guān)內(nèi)容，分享出來(lái)供大家參考學(xué)習(xí)，下面來(lái)看看詳細(xì)的介紹：
1. 初始化實(shí)驗(yàn)環(huán)境
初始化創(chuàng)建模擬實(shí)驗(yàn)環(huán)境用到的表空間、業(yè)務(wù)用戶、表，并導(dǎo)入測(cè)試數(shù)據(jù)。
本次實(shí)驗(yàn)用到表空間DBS_D_JINGYU, 業(yè)務(wù)用戶JINGYU, 分區(qū)表T_PART(含兩個(gè)分區(qū)的測(cè)試數(shù)據(jù))。
-- 數(shù)據(jù)表空間
create tablespace dbs_d_jingyu datafile '/u02/oradata/jingyu/dbs_d_jingyu01.dbf' size 30M autoextend off;
-- 臨時(shí)表空間
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;
-- 假設(shè)創(chuàng)建用戶 jingyu 密碼 jingyu，默認(rèn)臨時(shí)表空間 temp_jingyu， 默認(rèn)數(shù)據(jù)表空間 dbs_d_jingyu。
CREATE USER jingyu IDENTIFIED BY jingyu
TEMPORARY TABLESPACE temp_jingyu
DEFAULT TABLESPACE dbs_d_jingyu
QUOTA UNLIMITED ON dbs_d_jingyu;
-- 賦予普通業(yè)務(wù)用戶權(quán)限
grant resource, connect to jingyu;
-- 賦予DBA用戶權(quán)限
grant dba to jingyu;
-- 業(yè)務(wù)用戶登錄
conn jingyu/jingyu
-- 1.1 創(chuàng)建分區(qū)表
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 插入測(cè)試數(shù)據(jù)
--分區(qū)P20150102插入10000行數(shù)據(jù)
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;
/
--分區(qū)P20150103插入20000行數(shù)據(jù)
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查詢(xún)表數(shù)據(jù)量和大小
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
--普通表/分區(qū)表的每個(gè)分區(qū)大約__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. 模擬分區(qū)中有數(shù)據(jù)塊損壞情景
我這里使用BBED制造壞塊，修改t_part分區(qū)表的分區(qū)P20150103中的某個(gè)塊內(nèi)容，模擬真實(shí)環(huán)境中有數(shù)據(jù)塊損壞的情景。
--查詢(xún)分區(qū)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 --查詢(xún)某一行記錄所在的塊 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號(hào)文件1217塊內(nèi)容，
BBED工具：http://www.dhdzp.com/article/118349.htm
[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號(hào)文件，1217塊。
查詢(xún)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 --此時(shí)查詢(xún)分區(qū)表T_PART alter system flush buffer_cache; select count(1) from t_part; --查詢(xún)報(bào)錯(cuò)ORA-01578 select count(1) from t_part partition(P20150102); --查詢(xún)正常，即分區(qū)P20150102未受影響 select count(1) from t_part partition(P20150103); --查詢(xún)報(bào)錯(cuò)ORA-01578 --嘗試邏輯導(dǎo)出表數(shù)據(jù)失敗 [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內(nèi)部事件10231進(jìn)行不完全恢復(fù)
使用Oracle 10231內(nèi)部事件可以跳過(guò)壞塊
--啟用10231內(nèi)部事件 alter system set events='10231 trace name context forever,level 10'; --關(guān)閉10231內(nèi)部事件 alter system set events='10231 trace name context off';
測(cè)試設(shè)置10231事件后是否可以邏輯導(dǎo)出：
[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. --成功導(dǎo)出后記得要關(guān)閉10231內(nèi)部事件 alter system set events='10231 trace name context off'; 20000 - 19823 = 177行，也就是說(shuō)該數(shù)據(jù)塊損壞直接導(dǎo)致了177行數(shù)據(jù)丟失。不過(guò)還好，保住了大部分?jǐn)?shù)據(jù)。
實(shí)際上設(shè)置10231內(nèi)部事件后，如果上面邏輯導(dǎo)出沒(méi)問(wèn)題，這種情況自然還可以把數(shù)據(jù)直接導(dǎo)出到臨時(shí)表，更加方便。
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
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。
相關(guān)文章
Oracle中查詢(xún)本月星期5的所有日期列表的語(yǔ)句
Oracle中查詢(xún)本月星期5的所有日期列表的語(yǔ)句，需要的朋友可以參考下2012-07-07
文章介紹了SQLNET.ora文件在Oracle數(shù)據(jù)庫(kù)中的作用,包括加密、加密類(lèi)型、校驗(yàn)和以及校驗(yàn)和類(lèi)型等配置選項(xiàng)2024-11-11
Oracle安裝監(jiān)聽(tīng)器錯(cuò)誤的解決方法
這篇文章給大家介紹了oracle安裝監(jiān)聽(tīng)器錯(cuò)誤的解決方法，非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友參考下吧2017-09-09
Oracle基礎(chǔ):通過(guò)sqlplus執(zhí)行sql語(yǔ)句后的結(jié)果進(jìn)行判斷
今天小編就為大家分享一篇關(guān)于Oracle基礎(chǔ):通過(guò)sqlplus執(zhí)行sql語(yǔ)句后的結(jié)果進(jìn)行判斷，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧2018-12-12
Oracle RAC環(huán)境下的阻塞(blocking blocked)介紹和實(shí)例演示
這篇文章主要介紹了Oracle RAC環(huán)境下的阻塞(blocking blocked)介紹和實(shí)例演示本文提供了2個(gè)查詢(xún)腳本,并給出實(shí)例演示那些session為阻塞者,哪些為被阻塞者,需要的朋友可以參考下2014-09-09
Oracle數(shù)據(jù)庫(kù)的備份與恢復(fù)
Oracle數(shù)據(jù)庫(kù)的備份與恢復(fù)...2007-03-03
Oracle數(shù)據(jù)庫(kù)中保留小數(shù)點(diǎn)后兩位的問(wèn)題解讀
在Oracle數(shù)據(jù)庫(kù)中,對(duì)數(shù)字和百分比進(jìn)行格式化,以保留兩位小數(shù),主要使用to_char()函數(shù),對(duì)于大數(shù)字如10000000.12,使用to_char(字段名, 'FM99999999999990.00')可確保保留兩位小數(shù)而無(wú)額外空格,對(duì)于百分比如86.63%2024-09-09
這篇文章主要介紹了Oracle分頁(yè)查詢(xún)的實(shí)例詳解的相關(guān)資料,希望通過(guò)本文能幫助到大家實(shí)現(xiàn)理解這部分內(nèi)容，需要的朋友可以參考下2017-10-10
查找oracle數(shù)據(jù)庫(kù)表中是否存在系統(tǒng)關(guān)鍵字的方法
遇到列說(shuō)明無(wú)效的報(bào)錯(cuò)情況，這是由于數(shù)據(jù)庫(kù)列名起的不好引起的，名字用到了數(shù)據(jù)庫(kù)的關(guān)鍵字2014-07-07
oracle 11g 設(shè)置用戶密碼大小寫(xiě)敏感測(cè)試
這篇文章主要測(cè)試了oracle 11g 設(shè)置用戶密碼大小寫(xiě)敏感,需要的朋友可以參考下2014-08-08