[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

20161125PAGETABLE SEGMENT HEADER損壞恢復

相關文章