如果處理Oracle資料庫中的壞塊問題

來源:互聯網
上載者:User
oracle|資料|資料庫|問題
Oracle的資料區塊有固定的格式和結構,分三層: Cache layer、Transaction layer和Data layer.
對資料區塊進行讀寫操作時,做一致性檢查:
–Block type
–DBA
–Scn
–Header and tail
發現不一致,標記為壞塊。

壞塊有兩種: 物理壞塊和邏輯壞塊。

壞塊產生的影響:資料字典表、復原段表、臨時段和使用者資料表和索引。
應用報錯:
–Ora-1578
–Ora-600 and trace file in bdump directory
第一個參數[2000]-[8000]
Range                            block layer
 -------------------------------------------
Cache layer                 2000 – 4000
Transaction layer       4000 – 6000
Data layer                   6000 - 8000

壞塊產生的原因:
Oracle調用標準C的系統函數,對資料區塊進行讀寫操作:
- Bad I/O, H/W, Firmware.
- Operating System I/O or caching problems.
- Memory or paging problems.
- Disk repair utilities.
- Part of a datafile being overwritten.
- Third part software incorrectly attempting to access oracle used heap.
- Oracle or operating system bug.

表中壞塊的處理方法:
(1).收集相關資訊:
Ora-1578  file#  (RFN)  block#
Ora-1110  file#  (AFN)  block#
ora-600   file#  (AFN)  block#
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;
9i tempfiles AFN=file_id+value of db_files
(2).確定受影響的對象:
SELECT tablespace_name, segment_type, owner, segment_name, partition_name FROM dba_extents WHERE file_id = <AFN> and <BL> between block_id AND block_id + blocks - 1;
If on tempfile, no data return;
(3).根據物件類型,確定處理方法:
Objects of sys
rollback
Temporary segment
Index and index partition
Cluster |
Partition | ===>表中壞塊的處理
Table |
(4).選擇合適的方法搶救表中的資料:
Recover datafile
Recover block only (9i)
通過ROWID RANGE SCAN 儲存資料
使用DBMS_REPAIR
使用EVENT

表中壞塊的處理方法一:恢複資料檔案
資料庫為歸檔方式,有完整的物理備份  
OFFLINE the affected data file
ALTER DATABASE DATAFILE 'name_file' OFFLINE;
儲存有壞塊的檔案,RESTORE 備份。
if different from the old location
ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';
Recover the datafile
RECOVER DATAFILE 'name_of_file';
Online the file/s
ALTER DATABASE DATAFILE 'name_of_file' ONLINE;

表中壞塊的處理方法二:block recover
要求
(1).資料庫9.2
(2).catalog 和rman
(3).資料庫為歸檔方式,有完整的物理備份
(4).使用RMAN的BLOCKRECOVER命令
Rman>run{blockrecover
                     datafile 3 block 4,5;}
可以強制使用某個SCN號之前的備份,恢複資料區塊。
Rman>run{blockrecover
                     datafile 3 block 4,5 restore until sequence 7402;}

表中壞塊的處理方法三:ROWID RANGE SCAN
使用DBMS_ROWID 確定壞塊的ROWID RANGE
LOW_RID INSIDE the corrupt block:
SELECT dbms_rowid.rowid_create(1,<OBJ_ID>,<RFN>,<BL>,0) from DUAL;
HI_RID AFTER the corrupt block:
dbms_rowid.rowid_create(1,<OBJ_ID>,<RFN>,<BL>+1,0) from DUAL;
建一個暫存資料表
CREATE TABLE salvage_table AS SELECT * FROM corrupt_tab Where 1=2;
儲存未損壞的資料
INSERT INTO salvage_table SELECT /*+ ROWID(A) */ * FROM <owner.tablename> A WHERE rowid < '<low_rid>';
INSERT INTO salvage_table SELECT /*+ ROWID(A) */ * FROM <owner.tablename> A WHERE rowid >= '<hi_rid>';
重建table,index,foreign constrain table.

表中壞塊的處理方法四:add 10231 event
在session 或database級設10231 event,做全表掃描時,可以跳過壞塊.
Session level:
ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER,LEVEL 10';
CREATE TABLE salvage_emp AS SELECT * FROM corrupt_emp;
database level:
event="10231 trace name context forever, level 10"

表中壞塊的處理方法五:dbms_repair
標記有壞塊的表,做全表掃描時,可以跳過壞塊.
Execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('<schema>','<tablename>');
儲存表中資料
EXPORT the table.
CREATE TABLE salvage_emp AS SELECT * FROM corrupt_emp;

表中壞塊的處理方法六:檢查索引
檢查表上的索引和primary key foreign key約束
SELECT owner,index_name, index_type FROM dba_indexes WHERE table_owner=‘xxxx' AND table_name='xxxx';
SELECT owner,constraint_name,constraint_type,table_name FROM dba_constraints WHERE owner='xxx' AND table_name='xxx' AND
constraint_type='P';
SELECT owner,constraint_name,constraint_type,table_name FROM dba_constraints WHERE r_owner='xxxx' AND r_constraint_name='<CONSTRAINT-NAME>';

如何預先發現壞塊:
(1).Export utility
exp system/manager full=y log=exp_db_chk.log file=/dev/null volsize=100g
does not detect disk corruptions above the high water mark
does not detect corruptions in indexes
does not detect all corruptions in the data dictionary
ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE
performs the block checks ,but does NOT mark blocks as corrupt.
It also checks that table and index entries match.
Any problems found are reported into the user session trace file in USER_DUMP_DEST.
可以定期對一些重要的表作檢查.
(2).DBV檢查資料檔案
show parameter  db_block_size
select BYTES/2048 from v$datafile where FILE#=5;
dbv file=/dev/rdsk/r1.dbf blocksize=2048 END=5120
DBV expects a filename extension. If on raw dev
ln -s /dev/rdsk/mydevice /tmp/mydevice.dbf
Now use DBV against /tmp/mydevice.dbf


相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.