1108File Space Bitmap Block損壞能修復嗎2
[20161108]File Space Bitmap Block損壞能修復嗎？
--這陣子做了資料檔案的一些探究，還是回到File Space Bitmap Block損壞修復的問題。
--連結http://www.itpub.net/thread-2071023-1-1.html提到File Space Bitmap Block損壞，問能修復嗎?
--透過我前面的測試，可以把空間設定為1.這樣就可以解決這個問題，問題在在於相應的資料塊如何構造。
--實際上很簡單我僅僅建立1個相同大小的資料檔案，在copy回去，再修改一些基本資訊就ok了。
--我上次做的測試連結如下：http://blog.itpub.net/267265/viewspace-2127826/
--透過例子來說明：
1.環境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table t1 tablespace sugar as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e5;
--建立大小5M的表。
create table t2 tablespace sugar as select rownum id ,lpad('B',32,'B') name from dual connect by level<=2e5;
create table t3 tablespace sugar as select rownum id ,lpad('C',32,'C') name from dual connect by level<=2e5;
alter system checkpoint;
2.破壞點陣圖區。
SYS@book> alter tablespace sugar offline ;
Tablespace altered.
--安全期間，做一個備份：
$ cp /mnt/ramdisk/book/sugar01.dbf /u01/backup/sugar01.dbf_20161104
--正常資料檔案第0塊OS。第1塊是檔案頭，第2塊是點陣圖頭塊，第3-127塊是點陣圖區。
--我檔案很小，估計在3塊裡面：8192*3=24576
$ bvi -b 24576 -s 8192 /mnt/ramdisk/book/sugar01.dbf
--應該能看到許多F，表示已經使用，隨便輸入一些垃圾資料。我的測試全部設定為0。
RMAN> validate datafile 6;
Starting validate at 2016-11-08 16:25:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=90 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 FAILED 0 1945 5120 6167853
File Name: /mnt/ramdisk/book/sugar01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 2974
Index 0 0
Other 1 201
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_56225.trc for details
Finished validate at 2016-11-08 16:25:21
3.繼續測試：
SYS@book> alter tablespace sugar online ;
Tablespace altered.
SYS@book> select * from t1 where rownum=1;
ID NAME
--- --------------------------------
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SYS@book> select * from t2 where rownum=1;
ID NAME
--- --------------------------------
1 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
SYS@book> select * from t3 where rownum=1;
ID NAME
--- --------------------------------
1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
--訪問存在的物件沒有問題。
SYS@book> update t1 set name=lpad('B',32,'B') where rownum=1;
1 row updated.
SYS@book> commit ;
Commit complete.
SYS@book> create table t4 tablespace sugar as select rownum id ,lpad('D',32,'D') name from dual connect by level<=10;
create table t4 tablespace sugar as select rownum id ,lpad('D',32,'D') name from dual connect by level<=10
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [KSLFDNG2], [0x08591B490], [0], [0x084B84748], [0x000000000], [], [], [], [], [], [], []
--建立新表插入資料失敗，既然能online，測試execute dbms_space_admin.tablespace_dump_bitmaps('SUGAR');。
SYS@book> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
1 3345 1 0 CORRUPT
1 91041 1 0 CORRUPT
6 3 1 0 ALL ZERO
SYS@book> execute dbms_space_admin.tablespace_dump_bitmaps('SUGAR');
BEGIN dbms_space_admin.tablespace_dump_bitmaps('SUGAR'); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [KSLFDNG2], [0x08591B490], [32767], [0x084B84748], [0x000000000], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 322
ORA-06512: at line 1
--這樣直接操作不行。
SYS@book> alter database datafile 6 offline ;
Database altered.
SYS@book> recover datafile 6;
Media recovery complete.
4.修復看看：
--建立一個相同大小的資料檔案對應的表空間：
CREATE TABLESPACE TEA DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SYS@book> execute dbms_space_admin.tablespace_dump_bitmaps('TEA');
PL/SQL procedure successfully completed.
SYS@book> alter system checkpoint;
System altered.
SYS@book> alter system dump datafile 7 block 3;
System altered.
Block dump from disk:
buffer tsn: 8 rdba: 0x01c00003 (7/3)
scn: 0x0000.005e2005 seq: 0x01 flg: 0x04 tail: 0x20051e01
frmt: 0x02 chkval: 0x4104 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F1BB5E4D200 to 0x00007F1BB5E4F200
7F1BB5E4D200 0000A21E 01C00003 005E2005 04010000 [......... ^.....]
7F1BB5E4D210 00004104 00000007 00000080 00000000 [.A..............]
7F1BB5E4D220 00000000 0000F800 00000000 00000000 [................]
7F1BB5E4D230 00000000 00000000 00000000 00000000 [................]
Repeat 507 times
7F1BB5E4F1F0 00000000 00000000 00000000 20051E01 [............... ]
File Space Bitmap Block:
BitMap Control:
RelFno: 7, BeginBlock: 128, Flag: 0, First: 0, Free: 63488
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
--嗯！ 都是0，不行要都是F才行。
SCOTT@book> create table t4 tablespace tea as select rownum id from dual connect by level<=2;
Table created.
SYS@book> execute dbms_space_admin.tablespace_dump_bitmaps('TEA');
PL/SQL procedure successfully completed.
--檢查發現還是0.不知道為什麼，難道要寫滿在執行才可那個出現這個情況嗎？
SYS@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/tea01.dbf' AUTOEXTEND OFF;
Database altered.
SCOTT@book> ALTER TABLE t4 MINIMIZE RECORDS_PER_BLOCK ;
Table altered.
SCOTT@book> insert into t4 select rownum id from dual connect by level<=2e5;
insert into t4 select rownum id from dual connect by level<=2e5
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.T4 by 128 in tablespace TEA
SCOTT@book> rollback;
Rollback complete.
SYS@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/tea01.dbf' AUTOEXTEND ON;
Database altered.
SYS@book> alter system checkpoint ;
System altered.
SYS@book> alter system dump datafile 7 block 3;
System altered.
--檢查轉儲：
File Space Bitmap Block:
BitMap Control:
RelFno: 7, BeginBlock: 128, Flag: 0, First: 624, Free: 62864
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFF0000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
--現在玩一把偷樑換柱的把戲，利用bbed。
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /mnt/ramdisk/book/system01.dbf 0
2 /mnt/ramdisk/book/sysaux01.dbf 0
3 /mnt/ramdisk/book/undotbs01.dbf 0
4 /mnt/ramdisk/book/users01.dbf 0
5 /mnt/ramdisk/book/example01.dbf 0
6 /mnt/ramdisk/book/sugar01.dbf 0
7 /mnt/ramdisk/book/tea01.dbf 0
BBED> copy dba 7,3 to dba 6,3
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 3 Offsets: 0 to 63 Dba:0x01800003
------------------------------------------------------------------------------------------------------------------------------------------------
1ea20000 0300c001 97235e00 00000104 1bb10000 07000000 80000000 00000000 70020000 90f50000 00000000 00000000 00000000 00000000 ffffffff ffffffff
<64 bytes per line>
--千萬注意方向不要錯!!
BBED> set dba 6,3
DBA 0x01800003 (25165827 6,3)
BBED> dump /v count 128
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 3 Offsets: 0 to 127 Dba:0x01800003
-----------------------------------------------------------------------------------------------------------
1ea20000 0300c001 97235e00 00000104 1bb10000 07000000 80000000 00000000 l .........#^.....................
~~~~~~~~ ~~~~~~~~
70020000 90f50000 00000000 00000000 00000000 00000000 ffffffff ffffffff l p...............................
ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff l ................................
ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff l ................................
<32 bytes per line>
--修改~ 的兩處應該ok。 第1處是dba地址，大小頭對調就是 03008010, 第2處我推測是檔案號對應的就是06000000
--我個人喜歡bvi修改，修改後如下：
BBED> dump /v dba 6,3 count 128
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 3 Offsets: 0 to 127 Dba:0x01800003
-----------------------------------------------------------------------------------------------------------
1ea20000 03008001 97235e00 00000104 1bb10000 06000000 80000000 00000000 l .........#^.....................
~~~~~~~~ ~~~~~~~~
70020000 90f50000 00000000 00000000 00000000 00000000 ffffffff ffffffff l p...............................
ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff l ................................
ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff ffffffff l ................................
<32 bytes per line>
BBED> sum apply dba 6,3
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 6, Block 3:
current = 0xb15a, required = 0xb15a
SYS@book> create table t5 tablespace sugar as select rownum id ,lpad('D',32,'D') name from dual connect by level<=10;
Table created.
--OK現在沒有問題了。
5.再來執行如下命令看看：
SYS@book> execute dbms_space_admin.tablespace_dump_bitmaps('TEA');
PL/SQL procedure successfully completed.
--奇怪無法再現前面的測試。
RMAN> validate datafile 1;
Starting validate at 2016-11-08 17:19:58
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/mnt/ramdisk/book/system01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 15314 96071 6170653
File Name: /mnt/ramdisk/book/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 63529
Index 0 13184
Other 0 3973
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 618
Finished validate at 2016-11-08 17:20:00
RMAN> validate datafile 6;
Starting validate at 2016-11-08 17:20:06
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 3989 7168 6170478
File Name: /mnt/ramdisk/book/sugar01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 2975
Index 0 0
Other 0 204
Finished validate at 2016-11-08 17:20:07
SYS@book> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
--OK,現在問題已經解決。
--我覺得奇怪的是全是0，並不影響存在資料的讀取。理論講想辦法備份出來應該也可以解決問題。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2128025/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 1104File Space Bitmap Block損壞能修復嗎BloC
- 1110File Space Bitmap Block損壞能修復嗎3BloC
- 20161114File Space Bitmap Block修復機制2BloC
- [20161111File Space Bitmap Block修復機制BloC
- 不重灌也能修復損壞的 Ubuntu 系統Ubuntu
- 伺服器資料庫損壞能修復嘛伺服器資料庫
- 伺服器資料損壞有辦法修復嗎？伺服器
- linux下修復磁碟損壞Linux
- 修復損壞的資料塊
- SQL Server 資料頁損壞修復SQLServer
- ORACLE中修復資料塊損壞Oracle
- 使用dbms_repair修復塊損壞AI
- 電腦硬碟分割槽表損壞怎麼修復？電腦硬碟分割槽表損壞的修復方法硬碟
- system資料檔案頭損壞修復
- SQLite資料庫損壞及其修復探究SQLite資料庫
- INACTIVE日誌組損壞的修復
- pg 檔案塊損壞的修復措施。
- 磁頭損壞的修復方法有哪些
- SQL Anywhere db檔案損壞修復 DB檔案修復 DB資料庫修復SQL資料庫
- ASM磁碟頭資訊損壞和修復（kfed/dd）ASM
- Oracle中匯出修復資料塊損壞Oracle
- Oracle中模擬修復資料塊損壞Oracle
- 伺服器Oracle資料庫損壞修復伺服器Oracle資料庫
- linux檔案系統損壞如何修復Linux
- win7修復系統損壞 解除安裝軟體損壞win7系統修復教程（圖文詳解）Win7
- redo損壞修復啟動資料庫辦法資料庫
- MySQL資料庫表損壞後的修復方法MySql資料庫
- InterBase資料庫檔案損壞的修復方法資料庫
- RAID10磁碟陣列損壞修復操作AI陣列
- 資料恢復工具Recoverit使用教程:如何修復損壞的影片資料恢復
- index損壞恢復Index
- 檢查資料塊損壞(Block Corruption)BloC
- ORA-01578 data block corrupted 資料檔案損壞 與 修復 (多為借鑑 linux)BloCLinux
- 一個簡單的方法修復ubuntu引導損壞Ubuntu
- MYSQL資料表損壞的原因分析和修復方法MySql
- Linux技巧：使用Fsck命令修復損壞的分割槽Linux
- redhat8 rhel8 啟動grub損壞修復Redhat
- windows10應用商店損壞怎麼修復_win10應用商店損壞處理方法WindowsWin10