物理一致性:硬件故障檢查,不關心內容正確與否。Checksum
邏輯一致性:內容檢查,比checksum複雜的多。如:檢查記錄是否被不存在的事務鎖定、記錄和索引是否對應,檢查內容比物理一致性要多
db_block_checksum : 通過對數據塊校驗和 物理一致性檢查
9i,10G中
TRUE 啟用,默認參數:
1、SGA數據塊寫入硬盤前,先計算數據塊校驗和,填寫數據塊頭的對應字段,再把數據塊寫入硬盤。
2、讀取數據塊時重新計算校驗和,和數據塊頭字段比較,如不一致則物理介質故障。
3、除會對所有tablespace block進行checksum,還會對redo log block做校驗和。
FALSE :
支對system tablespace block進行checksum
11G中
TYPICAL ,默認參數:
10.2.0.3后就有引入,和9i,10G 設置為TRUE時作用一樣,官方文檔:會增加1%~2%的負載
FULL,
Oracle also verifies the checksum before a change application from update/delete statements and recomputes it after the change is applied.
Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. If set toFULL,DB_BLOCK_CHECKSUMalso catches in-memory corruptions and stops them from making it to the disk.
Oracle還會當塊要發生變化應用前對該塊驗證checksum,並會在DML update/insert/delete語句引起變化被應用到塊後再次計算該checksum。
checksum讓Oracle具備檢測由底層磁片、存儲子系統、IO子系統引起的壞塊。
若設置為FULL, 則DB_BLOCK_CHECKSUM還會捕捉記憶體訛誤並避免將存在邏輯訛誤的塊被寫入到磁片上。
官方文檔:會增加4%~5%的負載
但MACLEAN LIU 實際測試發現在 Linux +版本11.2.0.3上DB_BLOCK_CHECKSUM=FULL對普通的記憶體訛誤(memory corruption)幾乎無效。
官方建議還是使用:TYPICAL
OFF,FALSE:和9i,10G 設置為FALSE時作用一樣
11G還引入參數:db_ultra_safe 但是默認沒有設置,為OFF
sets the default values for other parameters that control protection levels. Values:
OFF
When any of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT are explicitly set, no changes are made.
DATA_ONLY
DB_BLOCK_CHECKING will be set to MEDIUM.
DB_LOST_WRITE_PROTECT will be set to TYPICAL.
DB_BLOCK_CHECKSUM will be set to FULL.
DATA_AND_INDEX
DB_BLOCK_CHECKING will be set to FULL.
DB_LOST_WRITE_PROTECT will be set to TYPICAL.
DB_BLOCK_CHECKSUM will be set to FULL.
db_block_checking: 邏輯一致性檢查
FALSE: 不啟用,默認
TURE: 啟用,
當每次數據塊被修改時,先對數據塊內容做一致性檢查。對DML操作密集的DB性能影響大。通常增加1%~10%負載
DBV
DBVERIFY(DBV):
1、可對datafile物理和邏輯一致性檢查
2、可對ASM文件檢查,如file=+DG1/ORCL/datafile/system01.dbf
3、不能檢查index記錄和data記錄的匹配關係
4、不能檢查control file和log file
5、可open在線檢查datafile,不需要關閉db
6、不會關心data block是否屬於哪個對象
例檢查users01.dbf文件:
emd<*emd*/data/ysdcss>$dbv file=users01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Wed Jan 28 15:26:18 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/ysdcss/users01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 19200
Total Pages Processed (Data) : 15233
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 360
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 3607
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2830216082 (2769.2830216082)
注:page就是data block
例:檢查指定segment Segment ID (tsn.relfile.block) (NONE)
select ts#,header_file,header_block from v$tablespace t ,dba_segments s
where t.name=s.tablespace_name and segment_name='SYSTEM_OPERATION_LOG' and owner='PV2W'
或
select tablespace_id,header_file, header_block from sys_dba_segs
where segment_name='SYSTEM_OPERATION_LOG' and owner='PV2W'
TS#, HEADER_FILE, HEADER_BLOCK
4,4,73362
$dbv userid=system/oracle segment_id=4.4.73362
DBVERIFY: Release 11.2.0.3.0 - Production on Wed Jan 28 15:49:00 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : SEGMENT_ID = 4.4.73362
DBVERIFY - Verification complete
Total Pages Examined : 131072
Total Pages Processed (Data) : 130412
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 611
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 48
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2846677509 (2769.2846677509)
Analyze
1、檢查物理和邏輯一致性
2、檢查table和index內容是否匹配,index時加casecade參數
3、檢查partition table的記錄是否在正確partition中
4、收集統計信息
5、檢查出問題放在user_dump_dest下nrow的文件
6、Online可以指定在DML操作時在線檢查一致性,如使用offline表會被鎖
7、對性能有一定影響
analyze table PV2W.SYSTEM_OPERATION_LOG validate structure cascade offline
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25583515/viewspace-2146422/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25583515/viewspace-2146422/