[20161125]PAGETABLE SEGMENT HEADER损坏恢复.txt
--前面我已经提到数据文件的位图区损坏,修复相对容易。可以参考我前面的blog,这很容易想起成语"留得青山在,不怕没柴烧"。
--可以看相关连接:
http://blog.itpub.net/267265/viewspace-2128290/
http://blog.itpub.net/267265/viewspace-2128290/
--我提到修复机制,是通过重新扫描段头,修复位图区,但是如果表的段头损坏,(索引也许通过删除重建解决,当然是用户的索引)。
--问题就大了。通过例子讲解恢复过程。
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;
SCOTT@book> create table t4 tablespace sugar as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e3;
Table created.
SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where owner=user and SEGMENT_NAME='T4' ;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT T4 TABLE SUGAR 0 6 128 65536 8 6
SCOTT T4 TABLE SUGAR 1 6 136 65536 8 6
SCOTT@book> select segment_name,header_file,header_block from dba_segments where owner='SCOTT' and header_file=6;
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
T4 6 130
SCOTT@book> select rowid,t4.id from t4 where id=1;
ROWID ID
------------------ ----------
AAAVwNAAGAAAACDAAA 1
SCOTT@book> @ &r/rowid AAAVwNAAGAAAACDAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
89101 6 131 0 0x1800083 6,131 alter system dump datafile 6 block 131 ;
2.转储段头看看:
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system dump datafile 6 block 130;
System altered.
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01800089 ext#: 1 blk#: 1 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 9
mapblk 0x00000000 offset: 1
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01800089 ext#: 1 blk#: 1 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 9
mapblk 0x00000000 offset: 1
Level 1 BMB for High HWM block: 0x01800080
Level 1 BMB for Low HWM block: 0x01800080
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01800081
Last Level 1 BMB: 0x01800080
Last Level II BMB: 0x01800081
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 2 obj#: 89101 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01800080 length: 8
0x01800088 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01800080 Data dba: 0x01800083
Extent 1 : L1 dba: 0x01800080 Data dba: 0x01800088
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01800081
--//我建立的表很小,你可以发现段头包含很重要的信息,只要通过它知道使用那些块。
--//以及一级位图块,二级位图块信息。dba =6,128 6,129.
3.现在清空128,129块。
SCOTT@book> alter tablespace sugar offline ;
Tablespace altered.
$ dd if=/dev/zero of=/mnt/ramdisk/book/sugar01.dbf bs=8192 seek=128 count=2 conv=notrunc
2+0 records in
2+0 records out
16384 bytes (16 kB) copied, 4.9188e-05 seconds, 333 MB/s
--//再次强调,不要在生产系统做这样测试,使用dd注意方向,注意加上conv=notrunc,我个人非常害怕这个命令。
SCOTT@book> alter tablespace sugar online ;
Tablespace altered.
SCOTT@book> select rowid,t4.id from t4 where id=1;
ROWID ID
------------------ ----------
AAAVwNAAGAAAACDAAA 1
SCOTT@book> select count(*) from t4 ;
COUNT(*)
----------
1000
--可以发现查询一点问题都没有,如果你增加涉及到问题信息对应块128,129的修改问题就出现了。
SCOTT@book> insert into t4 select rownum+1000 id ,lpad('B',32,'B') name from dual connect by level<=1e3;
insert into t4 select rownum+1000 id ,lpad('B',32,'B') name from dual connect by level<=1e3
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 129)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
$ dbv file=/mnt/ramdisk/book/sugar01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Thu Nov 24 17:16:45 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/sugar01.dbf
Page 128 is marked corrupt
Corrupt block relative dba: 0x01800080 (file 6, block 128)
Completely zero block found during dbv:
Page 129 is marked corrupt
Corrupt block relative dba: 0x01800081 (file 6, block 129)
Completely zero block found during dbv:
--出现这种实际上至少能保证数据不丢失。仅仅需要在别的地方拷贝出来。
SCOTT@book> delete from t4 where id<500 ;
delete from t4 where id<500
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01578: ORACLE data block corrupted (file # 6, block # 128)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
ORA-01578: ORACLE data block corrupted (file # 6, block # 128)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
Process ID: 29783
Session ID: 243 Serial number: 653
--会话被kill掉了,重新登录执行:
SCOTT@book> delete from t4 where id=2 ;
1 row deleted.
SCOTT@book> rollback ;
Rollback complete.
--删除1条可以,删除499条,触发了块状态信息的改写,因为assm使用2bit来表示块的状态(0,25%,50%,75%).
4.如果PAGETABLE SEGMENT HEADER损坏,问题就大了。
SCOTT@book> alter tablespace sugar offline ;
$ dd if=/dev/zero of=/mnt/ramdisk/book/sugar01.dbf bs=8192 seek=128 count=3 conv=notrunc
3+0 records in
3+0 records out
24576 bytes (25 kB) copied, 5.1975e-05 seconds, 473 MB/s
SCOTT@book> alter tablespace sugar online ;
Tablespace altered.
SCOTT@book> select * from t4;
select * from t4
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
--//这回就没有这么幸运了。
SCOTT@book> select * from t4 where rowid='AAAVwNAAGAAAACDAAA';
ID NAME
---------- ----------------------------------------
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
--// 可以看出通过rowid绕过了段头的访问,直接访问数据块,一般应用都有主键索引(注意如果索引字段为NULL,索引不记录)。
--// 在这种情况下通过主键索引,取回全部数据。
5.重新测试。
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 t4 tablespace sugar as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e5;
insert into t4 values (null,'B');
commit ;
create index i_t4_id on t4(id) tablespace sugar ;
SCOTT@book> select count(*) from t4 ;
COUNT(*)
----------
100001
SCOTT@book> alter tablespace sugar offline ;
Tablespace altered.
$ dd if=/dev/zero of=/mnt/ramdisk/book/sugar01.dbf bs=8192 seek=128 count=3 conv=notrunc
3+0 records in
3+0 records out
24576 bytes (25 kB) copied, 5.3083e-05 seconds, 463 MB/s
SCOTT@book> alter tablespace sugar online ;
Tablespace altered.
SCOTT@book> select * from t4;
select * from t4
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
--无法访问。
SCOTT@book> select min(id) from t4;
MIN(ID)
----------
1
SCOTT@book> select max(id) from t4;
MAX(ID)
----------
100000
SCOTT@book> select * from t4 where id =1;
ID NAME
---------- ----------------------------------------
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
--//通过索引可以访问。
SCOTT@book> create table t4_bak tablespace users as select /*+ rowid(t4) */ * from t4 where id between 1 and 1e5;
Table created.
SCOTT@book> select count(*) from t4_bak ;
COUNT(*)
----------
100000
--//可以发现差1条,因为索引不包括全部为null的字段。
--总结:
1.FIRST LEVEL BITMAP BLOCK,SECOND LEVEL BITMAP BLOCK损坏,PAGETABLE SEGMENT HEADER完好,select一般没有问题。
dml 如果不涉及相应FIRST LEVEL BITMAP BLOCK,SECOND LEVEL BITMAP BLOCK的修改(注这些块在许多地方都存在,一个段也许有许多
地方都有),我个人认为不涉及相应的修改应该可以通过。
2.如果存在主键索引(或者非null索引),可以通过索引直接定位取出数据,如果数据量很大,估计有点慢,至少是取出数据的方法。
3.如果这样的索引不存在,实际上理论上讲DBMS_ROWID包的函数ROWID_CREATE构造rowid,写脚本取出数据,不过有点麻烦,我个人比较
推荐odu,dul,gdul之类的抽取工具。
4.补充DBMS_ROWID.ROWID_CREATE测试,需要知道文件号,块号,data_object_id。
SCOTT@book> select * from dba_objects where owner=user and object_name='T4';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ ----------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- -------------
SCOTT T4 89102 89102 TABLE 2016-11-25 08:55:49 2016-11-25 08:58:34 2016-11-25:08:55:49 VALID N N N 1
--select * from t4 where rowid = DBMS_ROWID.ROWID_CREATE(1,89102,6,131,0);
SCOTT@book> select * from t4 where rowid = DBMS_ROWID.ROWID_CREATE(1,89102,6,131,0);
ID NAME
---------- ----------------------------------------
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SCOTT@book> select * from t4 where rowid = DBMS_ROWID.ROWID_CREATE(1,89102,6,131,1);
ID NAME
---------- ----------------------------------------
2 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2129044/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2129044/