[20150811]模擬壞塊處理.txt
[20150811]模擬壞塊處理.txt
--如果存在備份,修復壞塊還是相對簡單的.在11g下:
select * from V$DATABASE_BLOCK_CORRUPTION;
--在rman下執行:
blockrecover corruption list;
--如果資料塊沒有使用,沒有分配data_object_id而出現壞塊,如何恢復呢?一般採用的方法建立新物件的方法,格式化這個資料塊.
--具體測試如下:
1.建立測試環境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
CREATE TABLESPACE MSSM DATAFILE
'/mnt/ramdisk/test/mssm01.dbf' SIZE 64M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
SCOTT@test> create table t tablespace mssm as select rownum id ,cast('testtesttesttest' as varchar2(20)) name from xmltable('1 to 400000');
Table created.
--這樣建立檔案大小12M。
--建立備份:
backup database format '/home/oracle/backup/full_%u';
backup datafile 6 format '/home/oracle/backup/DATAFILE6_%u' ;
backup as copy datafile 6 format '/home/oracle/backup/mssm01.dbf_copy' ;
$ cd /home/oracle/backup
$ mv mssm01.dbf_copy mssm01.dbf_copy_org
--delete force copy of datafile 6;
--修改block＝3000的資訊：
--3000*8192/1024/1024=23.4375，這樣沒有資訊寫在該塊。
2.關閉資料庫,破壞資料塊:
SCOTT@test> @bbvi 6 3000
BVI_COMMAND
------------------------------------------------------
bvi -b 24576000 -s 8192 /mnt/ramdisk/test/mssm01.dbf
SCOTT@test> @convrdba.sql 6 3000
RDBA16 RDBA
-------------- ------------
1800bb8 25168824
$ bvi -b 24576000 -s 8192 /mnt/ramdisk/test/mssm01.dbf
01770000 00 A2 00 00 B8 0B 00 00 00 00 00 00 00 00 01 05 B8 AC 0
--將這些資訊清零。
--我使用bvi,使用dd也可以.注意要加conv=notrunc引數.方向不要搞錯。
--dd if=/dev/zero of=/mnt/ramdisk/test/mssm01.dbf bs=8192 seek=3000 conv=notrunc count=1
$ dbv file=/mnt/ramdisk/test/mssm01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 11 08:25:08 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/test/mssm01.dbf
Page 3000 is influx - most likely media corrupt
Corrupt block relative dba: 0x01800bb8 (file 6, block 3000)
Fractured block found during dbv:
Data in bad block:
type: 0 format: 0 rdba: 0x00000000
last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000001
check value in block header: 0x0
block checksum disabled
DBVERIFY - Verification complete
Total Pages Examined : 8192
Total Pages Processed (Data) : 1492
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 : 6690
Total Pages Marked Corrupt : 1
Total Pages Influx : 1
Highest block SCN : 4147024117 (2.4147024117)
--可以發現Corrupt block relative dba: 0x01800bb8 (file 6, block 3000)資訊，表示存在壞塊。
SCOTT@test> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
--可以發現檢視V$DATABASE_BLOCK_CORRUPTION沒有記錄。
RMAN> backup validate datafile 6;
Starting backup at 2015-08-11 08:26:44
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/mnt/ramdisk/test/mssm01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-08-11 08:26:45
SCOTT@test> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
--10g下對這種情況沒有記錄。使用blockrecover corruption list;應該沒用。使用dbv檢查依舊。
RMAN> blockrecover corruption list;
Starting blockrecover at 2015-08-11 08:27:55
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished blockrecover at 2015-08-11 08:27:55
--直接指定資料檔案以及對應塊。
RMAN> blockrecover datafile 6 block 3000 ;
Starting blockrecover at 2015-08-11 08:29:32
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 00006
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/DATAFILE6_10qeakdl
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/home/oracle/backup/DATAFILE6_10qeakdl tag=TAG20150811T081133
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 2015-08-11 08:29:36
$ dbv file=/mnt/ramdisk/test/mssm01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 11 08:30:21 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/test/mssm01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 8192
Total Pages Processed (Data) : 1492
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 10
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6690
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 4147024117 (2.4147024117)
--可以發現在有備份的情況下恢復實際上還是很簡單的，因為V$DATABASE_BLOCK_CORRUPTION沒有記錄，使用blockrecover corruption list;不行。
--但是直接執行blockrecover datafile 6 block 3000 ;，還是能修復問題的。
3.重複採用別的方式：
--採用rman的方式破壞。這種方式的破壞是往塊裡面寫入1堆垃圾(透過bvi觀察)，注意意後面的引數clear：
RMAN> blockrecover datafile 6 block 3000 clear ;
Starting blockrecover at 2015-08-11 08:37:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
Finished blockrecover at 2015-08-11 08:37:51
$ dbv file=/mnt/ramdisk/test/mssm01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 11 08:39:02 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/test/mssm01.dbf
Page 3000 is marked corrupt
Corrupt block relative dba: 0x01800bb8 (file 6, block 3000)
Bad check value found during dbv:
Data in bad block:
type: 58 format: 2 rdba: 0x01800bb8
last change scn: 0x0002.f72e9086 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x90863a01
check value in block header: 0x612e
computed block checksum: 0xef7c
-這種情況下，如果做如下操作會報錯：
RMAN> backup as copy datafile 6 format '/home/oracle/backup/mssm01.dbf_copy' ;
Starting backup at 2015-08-11 08:40:27
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/mnt/ramdisk/test/mssm01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/11/2015 08:40:28
ORA-19566: exceeded limit of 0 corrupt blocks for file /mnt/ramdisk/test/mssm01.dbf
SCOTT@test> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
--依舊沒有記錄。因為在備份資料檔案時使用copy方式要檢查每個塊，而backup datafile 6 僅僅備份有資訊的塊，這樣使用copy方式會
--報錯。
SCOTT@test> create table tx tablespace mssm as select * from t where 1=2;
Table created.
SCOTT@test> alter table tx allocate extent (size 20M);
Table altered.
$ dbv file=/mnt/ramdisk/test/mssm01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 11 08:48:25 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/test/mssm01.dbf
Page 3000 is marked corrupt
Corrupt block relative dba: 0x01800bb8 (file 6, block 3000)
Bad check value found during dbv:
Data in bad block:
type: 58 format: 2 rdba: 0x01800bb8
last change scn: 0x0002.f72e9086 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x90863a01
check value in block header: 0x612e
computed block checksum: 0xef7c
RMAN> backup datafile 6 format '/home/oracle/backup/DATAFILE6_%u' ;
Starting backup at 2015-08-11 08:51:53
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=/mnt/ramdisk/test/mssm01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-08-11 08:51:53
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/11/2015 08:51:54
ORA-19566: exceeded limit of 0 corrupt blocks for file /mnt/ramdisk/test/mssm01.dbf
--這次報錯。因為該塊已經被tx佔用。
SCOTT@test> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
--可以發現這樣檢視V$DATABASE_BLOCK_CORRUPTION沒有記錄。
RMAN> backup validate datafile 6;
Starting backup at 2015-08-11 08:52: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=/mnt/ramdisk/test/mssm01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-08-11 08:52:11
SCOTT@test> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
------------ ------------ ------------ ------------------ ---------
6 3000 1 0 CHECKSUM
--這次有記錄了。
4.選擇生成新資料覆蓋壞塊：
SCOTT@test> insert into tx values (null,null);
1 row created.
SCOTT@test> insert into tx values (null,null);
1 row created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> ALTER TABLE tx MINIMIZE RECORDS_PER_BLOCK;
Table altered.
--3000*8192/1024/1024=23.4375, 24M位置。前面已經佔用12M。
--(24-12)*1024*1024/8192=1536.
--這樣寫1536*2=3072條記錄基本就覆蓋有問題的資料塊。而且這樣寫日誌相對較小。
SCOTT@test> insert into tx select null,null from dual connect by level<=3100;
3100 rows created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter system checkpoint;
System altered.
$ dbv file=/mnt/ramdisk/test/mssm01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 11 09:01:10 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/test/mssm01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 8192
Total Pages Processed (Data) : 3045
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 10
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 5137
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 4147026312 (2.4147026312)
SCOTT@test> set null NULL
SCOTT@test> select rowid,tx.* from tx where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=3000;
ROWID ID NAME
------------------ ------------ --------------------
AAAQCjAAGAAAAu4AAA NULL NULL
AAAQCjAAGAAAAu4AAB NULL NULL
--可以發現已經修復。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1767800/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190718]12c壞塊處理一例.txt
- Oracle壞塊處理Oracle
- 【BLOCK】Oracle壞塊處理命令參考BloCOracle
- 一次壞塊的處理過程(一)
- 一次壞塊的處理過程(二)
- 一次ORACLE資料庫undo壞塊處理Oracle資料庫
- 一個簡單易用的資料庫壞塊處理方案資料庫
- Oracle資料庫處理壞塊問題常用命令Oracle資料庫
- 如何處理Oracle資料庫中的壞塊問題(轉)Oracle資料庫
- [20190311]關於oracle物理與邏輯壞塊.txtOracle
- Maxwell 磁場模擬場計算後處理
- 控制檔案損壞處理
- MATLAB數字訊號處理（2）LFM脈衝雷達回波處理模擬Matlab
- [20181123]模擬ora-01555.txt
- python處理txt檔案Python
- 模擬一則ORA-600 [4194][][]故障並處理
- Oracle資料庫出現ORA-19566 LOB壞塊的處理記錄Oracle資料庫
- [20181031]模擬網路問題.txt
- [20220531]模擬inactive session等待事件.txtSession事件
- RAC磁碟頭損壞問題處理
- 【CONNECT】ORA-00020錯誤模擬及處理方法實驗
- [20181031]模擬ora-01591錯誤.txt
- [20181122]模擬ORA-08103錯誤.txt
- 影像處理演算法的模擬平臺之VGA時序演算法
- [20181106]模擬ora-00600[4194]錯誤.txt
- [20181204]模擬ora-00600[4194]錯誤.txt
- [20181204]模擬ora-00600[4193]錯誤.txt
- [20180529]模擬會話引數變化.txt會話
- MySQL資料庫InnoDB壞頁處理修復MySql資料庫
- oracle壞塊(二)Oracle
- [20201209]模擬ora-04031的測試例子.txt
- RMAN修復壞塊
- [20181105]ORA-00600[4000] 模擬故障(10g).txt
- win10執行mumu模擬器當機如何處理_win10開啟mumu模擬器卡頓解決方法Win10
- 【Python】模擬windows檔名排序（自動處理檔名中有數字型別排序）PythonWindows排序型別
- 【freertos】002-posix模擬器設計與cortex m3異常處理
- 模擬退火演算法Python程式設計（2）約束條件的處理演算法Python程式設計
- [20180319]windows批處理檔案大小比較.txtWindows
- [20190219]變態的windows批處理6.txtWindows