檢測ORACLE資料區塊損壞的方法匯總,oracle匯總
1:使用初始化參數
使用初始化參數db_block_checksum\db_block_checking可以設定資料庫對塊的物理一致性和邏輯一致性檢查。
Db_block_checksum:物理一致性檢查,預設開啟。始終對system資料表空間資料進行檢查,如果開啟還會對日誌進行檢查,開啟後會有1~2%的效能影響,建議開啟。
Db_block_checking:邏輯一致性檢查,預設未開啟。始終對system資料表空間進行檢查,如果開啟會有1~10%的效能影響。DML越頻繁,效能影響越大。不建議開啟。
有關該參數的詳細內容可以參考:
http://blog.csdn.net/yidian815/article/details/39932903
2:使用dbv工具
使用dbv可以進行物理和邏輯一致性檢查,但是不會檢測表資料和索引資料的匹配關係。使用該工具有以下特點:
唯讀方式開啟資料檔案
可以線上檢查資料檔案,資料庫無需關閉
不可以檢測控制檔案和記錄檔
可以檢測asm檔案
有時檔案名稱需要有尾碼名,並且有時不可以檢測超過2G大小的檔案
3:使用analyze命令
文法格式:analyze table validate structure cascade online (offline).
該命令會同時進行物理和邏輯檢查,同時還可以進行表資料和索引資料的匹配性檢查,檢查分區表的記錄是否在正確的分區中,檢測結果會儲存在trace檔案中。
如果沒有使用cascade關鍵字,則只會進行表資料的檢測,使用cascade後會進行表資料和索引資料的檢測。
如果使用了online關鍵字,則可以對錶資料進行線上檢查,此時不會對錶添加鎖,DML語句可以繼續進行,如果使用了offline關鍵字,則會對錶加鎖,此時使用者不能對該表進行修改操作。
如果需要檢測分區表記錄是否在正確的分區內,可以使用如下語句:
Anlyze table table_name validte structure into invalid_rows.
使用該命令前,請使用utlvalid.sql 指令碼建立相應的invalid_rows表。
4:使用rman工具
使用rman備份工具時,oracle會將資料先讀入讀緩衝區,然後將資料寫入寫緩衝區,最後寫入磁碟,在從讀緩衝區轉移到寫緩衝區時,rman工具會進行資料一致性檢測。
文法格式:backup validate database.
使用backup validate 命令會對資料檔案進行物理一致性檢測(前提是db_block_checksum啟用), 此時並不會產生備份檔案。如:
RMAN> backup validate database; Starting backup at 2014-11-05 15:44:21using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/home/app/oraten/oradata/oraten/system01.dbfinput datafile fno=00003 name=/home/app/oraten/oradata/oraten/sysaux01.dbfinput datafile fno=00002 name=/home/app/oraten/oradata/oraten/undotbs01.dbfinput datafile fno=00005 name=/home/app/oraten/oradata/oraten/test01.dbfinput datafile fno=00004 name=/home/app/oraten/oradata/oraten/users01.dbfchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 2014-11-05 15:44:23 RMAN> list backup; RMAN>
我們也可以添加check logical選項,以便rman進行邏輯一致性檢測。注意:check logical是命令選項,如果單獨使用check logical會產生備份:
RMAN> backup check logical validate database; Starting backup at 2014-11-05 15:45:59using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/home/app/oraten/oradata/oraten/system01.dbfinput datafile fno=00003 name=/home/app/oraten/oradata/oraten/sysaux01.dbfinput datafile fno=00002 name=/home/app/oraten/oradata/oraten/undotbs01.dbfinput datafile fno=00005 name=/home/app/oraten/oradata/oraten/test01.dbfinput datafile fno=00004 name=/home/app/oraten/oradata/oraten/users01.dbfchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 2014-11-05 15:46:04 RMAN> list backup; RMAN> backup check logical database; Starting backup at 2014-11-05 15:46:14using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/home/app/oraten/oradata/oraten/system01.dbfinput datafile fno=00003 name=/home/app/oraten/oradata/oraten/sysaux01.dbfinput datafile fno=00002 name=/home/app/oraten/oradata/oraten/undotbs01.dbfinput datafile fno=00005 name=/home/app/oraten/oradata/oraten/test01.dbfinput datafile fno=00004 name=/home/app/oraten/oradata/oraten/users01.dbfchannel ORA_DISK_1: starting piece 1 at 2014-11-05 15:46:14channel ORA_DISK_1: finished piece 1 at 2014-11-05 15:46:39piece handle=/home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_05/o1_mf_nnndf_TAG20141105T154614_b5mog6kr_.bkp tag=TAG20141105T154614 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 2014-11-05 15:46:40channel ORA_DISK_1: finished piece 1 at 2014-11-05 15:46:41piece handle=/home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_05/o1_mf_ncsnf_TAG20141105T154614_b5moh030_.bkp tag=TAG20141105T154614 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 2014-11-05 15:46:41 List of Backup Sets=================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------59 Full 545.84M DISK 00:00:17 2014-11-05 15:46:31 BP Key: 41 Status: AVAILABLE Compressed: NO Tag: TAG20141105T154614 Piece Name: /home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_05/o1_mf_nnndf_TAG20141105T154614_b5mog6kr_.bkp List of Datafiles in backup set 59 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 1 Full 870906 2014-11-05 15:46:14 /home/app/oraten/oradata/oraten/system01.dbf 2 Full 870906 2014-11-05 15:46:14 /home/app/oraten/oradata/oraten/undotbs01.dbf 3 Full 870906 2014-11-05 15:46:14 /home/app/oraten/oradata/oraten/sysaux01.dbf 4 Full 870906 2014-11-05 15:46:14 /home/app/oraten/oradata/oraten/users01.dbf 5 Full 870906 2014-11-05 15:46:14 /home/app/oraten/oradata/oraten/test01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------60 Full 6.80M DISK 00:00:01 2014-11-05 15:46:40 BP Key: 42 Status: AVAILABLE Compressed: NO Tag: TAG20141105T154614 Piece Name: /home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_05/o1_mf_ncsnf_TAG20141105T154614_b5moh030_.bkp Control File Included: Ckp SCN: 870915 Ckp time: 2014-11-05 15:46:39 SPFILE Included: Modification time: 2014-11-05 15:16:33<span style="font-size: 12px; font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"> </span>
關於rman備份與恢複的驗證,可以參照:
http://blog.csdn.net/yidian815/article/details/40824689
5:使用dbms_repair包
關於dbms_repaire包的使用,請參見:
http://blog.csdn.net/yidian815/article/details/40825511