1110File Space Bitmap Block損壞能修復嗎3
[20161110]File Space Bitmap Block損壞能修復嗎3.txt
--今天仔細檢查才發現我原來的連結存在問題，http://blog.itpub.net/267265/viewspace-2128025/
--我使用錯函式了，
execute dbms_space_admin.tablespace_dump_bitmaps('TEA');
--應該使用：
execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('TEA');
--都是copy and paste的錯，也怪自己不仔細看。重新測試看看。
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_20161110
--正常資料檔案第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-10 11:27:40
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=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 6239125
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_65118.trc for details
Finished validate at 2016-11-10 11:27:42
3.繼續測試：
SCOTT@book> alter tablespace sugar online ;
Tablespace altered.
--居然可以online。
SCOTT@book> select * from t1 where rownum=1;
ID NAME
--- --------------------------------
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SCOTT@book> select * from t2 where rownum=1;
ID NAME
-- --------------------------------
1 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
SCOTT@book> select * from t3 where rownum=1;
ID NAME
-- --------------------------------
1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
--DML沒有問題，增加新表報錯。
SCOTT@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], [0x0859218E0], [0], [0x084B84748], [0x000000000], [], [], [], [], [], [], []
--既然可以online，執行如下看看。
SYS@book> execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR');
PL/SQL procedure successfully completed.
--BTW,offline不行：
SYS@book> alter tablespace sugar offline ;
Tablespace altered.
SYS@book> execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR');
BEGIN dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR'); END;
*
ERROR at line 1:
ORA-03219: Tablespace 'SUGAR' is dictionary-managed, offline or temporary
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 120
ORA-06512: at line 1
$ bvi -b 24576 -s 8192 /mnt/ramdisk/book/sugar01.dbf
00006000 1E A2 00 00 03 00 80 01 06 35 5F 00 00 00 02 04 44 41 00 00 06 00 00 00 80 00 00 00 00 00 00 00 .........5_.....DA..............
00006020 00 F8 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 FF FF FF FF FF FF FF FF ................................
00006040 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006060 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006080 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
000060A0 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
000060C0 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
000060E0 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006100 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006120 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006140 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006160 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006180 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
000061A0 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
000061C0 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
000061E0 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006200 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006220 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006240 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006260 FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
3.再測試是否可以建立表：
SCOTT@book> create table t4 tablespace sugar as select rownum id ,lpad('D',32,'D') name from dual connect by level<=10;
Table created.
SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where owner=user and segment_name in ('T3','T4');
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------ --------------- ---------- ---------- ---------- ---------- ------ ------------
SCOTT T3 TABLE SUGAR 0 6 2048 65536 8 6
SCOTT T3 TABLE SUGAR 1 6 2056 65536 8 6
SCOTT T3 TABLE SUGAR 2 6 2064 65536 8 6
SCOTT T3 TABLE SUGAR 3 6 2072 65536 8 6
SCOTT T3 TABLE SUGAR 4 6 2080 65536 8 6
SCOTT T3 TABLE SUGAR 5 6 2088 65536 8 6
SCOTT T3 TABLE SUGAR 6 6 2096 65536 8 6
SCOTT T3 TABLE SUGAR 7 6 2104 65536 8 6
SCOTT T3 TABLE SUGAR 8 6 2112 65536 8 6
SCOTT T3 TABLE SUGAR 9 6 2120 65536 8 6
SCOTT T3 TABLE SUGAR 10 6 2128 65536 8 6
SCOTT T3 TABLE SUGAR 11 6 2136 65536 8 6
SCOTT T3 TABLE SUGAR 12 6 2144 65536 8 6
SCOTT T3 TABLE SUGAR 13 6 2152 65536 8 6
SCOTT T3 TABLE SUGAR 14 6 2160 65536 8 6
SCOTT T3 TABLE SUGAR 15 6 2168 65536 8 6
SCOTT T3 TABLE SUGAR 16 6 2176 1048576 128 6
SCOTT T3 TABLE SUGAR 17 6 2304 1048576 128 6
SCOTT T3 TABLE SUGAR 18 6 2432 1048576 128 6
SCOTT T3 TABLE SUGAR 19 6 2560 1048576 128 6
SCOTT T3 TABLE SUGAR 20 6 2688 1048576 128 6
SCOTT T3 TABLE SUGAR 21 6 2816 1048576 128 6
SCOTT T3 TABLE SUGAR 22 6 2944 1048576 128 6
SCOTT T3 TABLE SUGAR 23 6 3072 1048576 128 6
SCOTT T3 TABLE SUGAR 24 6 3200 1048576 128 6
SCOTT T4 TABLE SUGAR 0 6 3328 65536 8 6
26 rows selected.
--你可以發現空間並沒有浪費。這樣看來File Space Bitmap Block損壞很好修復。
RMAN> validate datafile 6;
Starting validate at 2016-11-10 11:38:36
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 1941 5120 6239531
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-10 11:38:37
--一切OK。
4.補充看看其他引數：
SYS@book> @ &r/desc_proc sys dbms_space_admin TABLESPACE_REBUILD_BITMAPS
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE DEFAULTED
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- -------------------- ----------
SYS DBMS_SPACE_ADMIN TABLESPACE_REBUILD_BITMAPS 1 TABLESPACE_NAME VARCHAR2 IN VARCHAR2 N
2 BITMAP_RELATIVE_FILE BINARY_INTEGER IN BINARY_INTEGER Y
3 BITMAP_BLOCK BINARY_INTEGER IN BINARY_INTEGER Y
--還可以指定特定的點陣圖塊，看看。
SYS@book> execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR',BITMAP_RELATIVE_FILE=>6,BITMAP_BLOCK=>4);
BEGIN dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR',BITMAP_RELATIVE_FILE=>6,BITMAP_BLOCK=>4); END;
*
ERROR at line 1:
ORA-03220: DBMS_ADMIN_PACKAGE required parameter is NULL or missing
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 120
ORA-06512: at line 1
--參考官方文件：http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_spadmn.htm#i1003484
TABLESPACE_REBUILD_BITMAPS Procedure
This procedure rebuilds the appropriate bitmaps. If no bitmap block DBA is specified, then it rebuilds all bitmaps for
the given tablespace.
The procedure cannot be used on the SYSTEM tablespace.
Syntax
DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS (
tablespace_name IN VARCHAR2,
bitmap_relative_file IN POSITIVE DEFAULT NULL,
bitmap_block IN POSITIVE DEFAULT NULL);
Parameters
Table 99-13 TABLESPACE_REBUILD_BITMAPS Procedure Parameters
Parameter Description
----------------------------------------------------------
tablespace_name Name of tablespace.
bitmap_relative_file Relative file number of bitmap block to rebuild.
bitmap_block Block number of bitmap block to rebuild.
Usage Notes
Note:
Only full rebuild is supported.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Examples
The following example rebuilds bitmaps for all the files in the USERS tablespace.
EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('USERS');
--我的測試視乎不能使用後面的引數。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2128232/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 1104File Space Bitmap Block損壞能修復嗎BloC
- 1108File Space Bitmap Block損壞能修復嗎2BloC
- [20161111File Space Bitmap Block修復機制BloC
- 20161114File Space Bitmap Block修復機制2BloC
- 不重灌也能修復損壞的 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
- win7修復系統損壞 解除安裝軟體損壞win7系統修復教程（圖文詳解）Win7
- redo損壞修復啟動資料庫辦法資料庫
- MySQL資料庫表損壞後的修復方法MySql資料庫
- 資料恢復工具Recoverit使用教程:如何修復損壞的影片資料恢復
- index損壞恢復Index
- 檢查資料塊損壞(Block Corruption)BloC
- ORA-01578 data block corrupted 資料檔案損壞 與 修復 (多為借鑑 linux)BloCLinux
- 一個簡單的方法修復ubuntu引導損壞Ubuntu
- MYSQL資料表損壞的原因分析和修復方法MySql
- Linux技巧：使用Fsck命令修復損壞的分割槽Linux
- [轉] Linux Ext3檔案系統超級塊損壞後的修復Linux
- windows10應用商店損壞怎麼修復_win10應用商店損壞處理方法WindowsWin10
- 修復損壞的gzip壓縮檔案之原理篇
- 【MySQL】表索引損壞致Crash及修復過程一則MySql索引
- 妙用安裝盤修復損壞的系統檔案(轉)
- 電腦硬碟壞了能修復嗎 電腦硬碟壞了無法進入系統的解決教程硬碟
- RMAN修復壞塊