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了。

--我上次做的测试链接如下：

--通过例子来说明：

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

--嗯！都是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 FFFFFFFFFFFF0000 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 ................................

<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 ................................

<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,现在问题已经解决。