Oracle壞塊處理
oracle壞塊問題處理
創(chuàng)新互聯(lián)公司-專業(yè)網站定制、快速模板網站建設、高性價比陳巴爾虎網站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式陳巴爾虎網站制作公司更省心,省錢,快速模板網站建設找我們，業(yè)務覆蓋陳巴爾虎地區(qū)。費用合理售后完善，十載實體公司更值得信賴。
1、說明
壞塊問題是經常出現(xiàn)在數(shù)據(jù)庫系統(tǒng)中的，如果沒有合適的處理方法往往會導致壞塊對象不可用或者數(shù)據(jù)丟失。本文從壞塊產生開始說起，著重說明一但壞塊產生后不同情況的處理方法。
2、壞塊產生的原因：
硬件的I/O錯誤
操作系統(tǒng)的I/O錯誤或緩沖問題
內存或paging問題
磁盤修復工具
一個數(shù)據(jù)文件的一部分正在被覆蓋
Oracle試圖訪問一個未被格式化的系統(tǒng)塊失敗
數(shù)據(jù)文件部分溢出
Oracle或者操作系統(tǒng)的bug
3、壞塊的發(fā)現(xiàn)：
3.1、Alter日志報錯
Tue Aug 17 10:48:07 2010
Corrupt Block Found
TSN = 7, TSNAME = BTEST
RFN 6, BLK = 839, rdba = 25166663
OBJN = 49205, BJD = 49205, BJECT= BOBJ, SUBOBJECT =
Segment wner= AN, Segment Type = Table Segment
其中RFN表示的是relative_fno，6號文件的839塊，段類型為表段，是表bobj出現(xiàn)壞塊。
3.2、查詢報錯
SQL> select count(*) from bobj;
select count(*) from bobj
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 839)
ORA-01110: data file 6: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10\BTEST.DBF'
3.3、分析表報錯
SQL> analyze table bobj validate structure cascade;
analyze table bobj validate structure cascade
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 839)
ORA-01110: data file 6: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10\BTEST.DBF'
3.4、Rman備份報錯
RMAN> backup tablespace btest;
Starting backup at 17-8月 -10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10\BTEST.DBF
channel ORA_DISK_1: starting piece 1 at 17-8月 -10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/17/2010 11:03:09
ORA-19566: exceeded limit of 0 corrupt blocks for file F:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10\BTEST.DBF
3.5、Dbv檢查報錯
F:\oracle\product\10.1.0\oradata\db10>dbv file=BTEST.DBF blocksize=8192
DBVERIFY: Release 10.1.0.2.0 - Production on星期二 8月 17 10:49:02 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = BTEST.DBF
Page 839 is marked corrupt
Corrupt block relative dba: 0x01800347 (file 6, block 839)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01800347
last change scn: 0x0000.0005246f seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x246f0601
check value in block header: 0x50c2
computed block checksum: 0x2751
DBVERIFY - Verification complete
Total Pages Examined : 5376
Total Pages Processed (Data) : 5165
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 9
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 201
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
3.6、查詢壞塊發(fā)生的數(shù)據(jù)文件以及對應表空間
Select file_name,tablespace_name,file_id “AFN”,relative_fno “RFN”
From dba_data_files;
Select file_name,tablespace_name,file_id, relative_fno“RFN”
From dba_temp_files;
3.7、查詢存在壞塊的對象是什么：
SELECT tablespace_name, segment_type, owner,
segment_name, partition_name FROM
dba_extents WHERE file_id =v_file_id and v_block_id
between block_id AND block_id + blocks – 1；
4、出現(xiàn)壞塊的常見對象：
Sys用戶下的對象
回滾段
臨時段
索引或者分區(qū)索引
表
5、問題的處理
5.1、無備份情況下的恢復
5.1.1、Sys用戶下的對象，需要謹慎處理。
5.1.2、回滾段壞塊采用類似ora-600（4000）的處理方法將回滾段刪除即可。會造成事物失敗，數(shù)據(jù)丟失。
5.1.3、索引：重建，期間會鎖表對業(yè)務有影響，數(shù)據(jù)不會丟失。
5.1.4、表：
5.1.4．1、EVENT10231
SQL> select count(*)from bobj;
COUNT(*)
----------
376240
SQL> analyze table bobj validate structure cascade;
analyze table bobj validate structure cascade
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 495)
ORA-01110: data file 6: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10\BTEST.DBF'
SQL> alter session set events ='10231 trace name context forever,level 10';
Session altered.
SQL> select count(*) from bobj;
COUNT(*)
----------
376169
忽略了壞塊進行讀寫，所以數(shù)據(jù)量上有變化，丟失了一部分數(shù)據(jù)。
5.1.4．2、ROWID RANGE SCAN
FUNCTION ROWID_CREATE RETURNS ROWID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_TYPE NUMBER IN
OBJECT_NUMBER NUMBER IN
RELATIVE_FNO NUMBER IN
BLOCK_NUMBER NUMBER IN
ROW_NUMBER NUMBER IN
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
6 839 1 0 CHECKSUM
SQL> SELECT dbms_rowid.rowid_create(1,49205,6,839,0) from DUAL;
DBMS_ROWID.ROWID_C
------------------
AAAMA1AAGAAAANHAAA
SQL> SELECT dbms_rowid.rowid_create(1,49205,6,840,0) from DUAL;
DBMS_ROWID.ROWID_C
------------------
AAAMA1AAGAAAANIAAA
SQL> create table bbobj tablespace btest as select * from bobj where 1=2;
Table created.
SQL> insert into bbobj select /*+ rowid(a) */ * from bobj where rowid<'AAAMA1AAG
AAAANHAAA';
60993 rows created.
SQL> insert into bbobj select /*+ rowid(a) */ * from bobj where rowid>='AAAMA1AAG
AAAANIAAA';
315108 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from bbobj;
COUNT(*)
----------
376101
從v$database_block_corruption中可以查處當前數(shù)據(jù)庫中的壞塊信息
通過調用dbms_rowid.rowid_create確認出壞塊對應的rowid，重新創(chuàng)建表結構相同的表，并以rowid為條件跳過壞塊將好數(shù)據(jù)存入到中間表中，損失一部分數(shù)據(jù)，仍然可以將壞塊的影響忽略掉。
5.1.4.3、Dbms_repair
以sys用戶執(zhí)行
PROCEDURE SKIP_CORRUPT_BLOCKS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
OBJECT_TYPE BINARY_INTEGER IN DEFAULT
FLAGS BINARY_INTEGER IN DEFAULT
SQL> Execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('AN','BOBJ')
PL/SQL procedure successfully completed.
SQL> conn an/an
Connected.
SQL> select count(*) from bobj;
COUNT(*)
----------
376169
試用sys用戶調用dbms_repair中的skip_corrupt_blocks忽略對象中的壞塊，同樣會丟失壞塊中的數(shù)據(jù)。包中還包含過程FIX_CORRUPT_BLOCKS，可以fix掉壞塊。
5.2、有備份情況下的恢復
5.2.1、Blockrecover
RMAN> blockrecover datafile 5 block 425;
Starting blockrecover at 17-8月 -10
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\DB10\BACKUPSET\2010_08_17\O1_MF_NNNDF_TAG20100817T162508_66NKV5B8_.BKP tag=TAG20100817T162508
channel ORA_DISK_1: block restore complete
starting media recovery
media recovery complete
Finished blockrecover at 17-8月 -10
SQL> select count(*) from an.atest;
COUNT(*)
----------
376240
5.2.2、Recover datafile
SQL> select count(*) from an.atest;
select count(*) from an.atest
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 963)
ORA-01110: data file 5: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10\ATEST.DBF'
RMAN> sql " alter database datafile 5 offline";
sql statement: alter database datafile 5 offline
RMAN> restore datafile 5;
Starting restore at 17-8月 -10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to F:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10\ATEST.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\DB10\BACKUPSET\2010_08_17\O1_MF_NNNDF_TAG20100817T162508_66NKV5B8_.BKP tag=TAG20100817T162508
channel ORA_DISK_1: restore complete
Finished restore at 17-8月 -10
RMAN> recover datafile 5;
Starting recover at 17-8月 -10
using channel ORA_DISK_1
starting media recovery
media recovery complete
Finished recover at 17-8月 -10
RMAN> sql " alter database datafile 5 online";
sql statement: alter database datafile 5 online
SQL> select count(*) from an.atest;
COUNT(*)
----------
376240
5.3、Rman備份跳過壞塊
SET MAXCORRUPT FOR DATAFILE filename TO n；
例：
run {
allocate channel node1 type disk;
SET MAXCORRUPT FOR DATAFILE 8 TO 3；
set limit channel node1 kbytes = 1800000;
backup as compressed backupset full database format '$BACKUPDIR/full_%d_%T_%s_%p' plus archivelog format '$BACKUPDIR/arch_%d_%T_%s_%p' delete all input;
backup current controlfile format '$BACKUPDIR/ctl_%d_%T_%s_%p' TAG "control.bak";
release channel node1;
}
新聞標題：Oracle壞塊處理
文章來源：http://muchs.cn/article38/gechsp.html
成都網站建設公司_創(chuàng)新互聯(lián)，為您提供、面包屑導航、外貿網站建設、網站維護、全網營銷推廣、做網站
聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)
- 搜索引擎優(yōu)化的網站導航設計技巧 2015-01-20
- 圖片搜索引擎優(yōu)化 2022-07-29
- 成都網站優(yōu)化分享對搜索引擎優(yōu)化排名概念的再思考 2016-10-31
- 建議初學者SEOER對待搜索引擎優(yōu)化資料不要照本宣讀 2023-04-06
- 整站優(yōu)化:搜索引擎優(yōu)化的五大原則 2014-03-10
- “搜索引擎優(yōu)化”正確的術語 2015-01-04
- cookie為何影響搜索引擎優(yōu)化排名？ 2022-07-11
- 搜索引擎優(yōu)化如何篩選更有效的關鍵詞？ 2021-08-08
- 搜索引擎優(yōu)化方法都有哪些 2021-05-01
- 反向鏈接對搜索引擎優(yōu)化的影響 2021-01-09
- 搜索引擎優(yōu)化新站關鍵詞排名多久能上首頁? 2020-09-22
- 簡單但功能強大的搜索引擎優(yōu)化提示:反向鏈接 2022-05-01