[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，并不影响存在数据的读取。理论讲想办法备份出来应该也可以解决问题。