修復資料庫壞塊之二

===========================================================

修復資料庫壞塊之二

===========================================================

壞塊有兩種： 物理壞塊和邏輯壞塊。
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 = and 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,,,,0) from DUAL;
HI_RID AFTER the corrupt block:
dbms_rowid.rowid_create(1,,,+1,0) from DUAL;
建一個臨時表
CREATE TABLE salvage_table AS SELECT * FROM corrupt_tab Where 1=2;
儲存未損壞的資料
INSERT INTO salvage_table SELECT /*+ ROWID(A) */ * FROM A WHERE rowid < '';
INSERT INTO salvage_table SELECT /*+ ROWID(A) */ * FROM A WHERE rowid >= '';
重建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('','');
儲存表中資料
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 wner='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='';

如何預先發現壞塊：
(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