[20161111]資料檔案的第0塊2.txt

lfree發表於2016-11-11

[20161111]資料檔案的第0塊2.txt

--如果資料檔案的第0塊是OS塊資訊,以前的測試如果rman做備份集都不會備份。
--如果這塊損壞,裡面講問題不大,你甚至可以不修復,如果線上resize就ok了,當然重建控制檔案就出現問題。

--而且解決也很簡單,就是建立一樣大小的資料檔案,然後copy回去。做一個測試例子:

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;
alter system checkpoint;

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;

--昨天的測試無法在bbed下使用copy file 7 block 0 to file 6 block 0,實際上無法識別資料檔案6的os頭。這樣變成了block=1.

2.重新測試:

SCOTT@book> alter tablespace sugar offline ;
Tablespace altered.

SCOTT@book> alter tablespace tea offline ;
Tablespace altered.

--設定資料檔案6 sugar01.dbf的第0塊全為0.

3.使用bbed修復:
BBED> copy file 7 block 0 to file 6 block 0
BBED-00309: out of range block number (0)

--實際上晚上在回家的路上才想起來問題在那裡。我以前在windows下使用bbed,也遇到修改11g的資料檔案,訪問block必須加1,實際上
--就是無法識別11g資料檔案的塊頭。

BBED> dump /v file 7 block 0  count 256 offset 0
File: /mnt/ramdisk/book/tea01.dbf (7)
Block: 0                                 Offsets:    0 to  255                            Dba:0x01c00000
-----------------------------------------------------------------------------------------------------------
00a20000 0000c0ff 00000000 00000000 66ee0000 00200000 00140000 7d7c7b7a l ................f.... ......}|{z
a0810000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................

<32 bytes per line>

BBED> dump /v file 6 block 1  count 256 offset 0
BBED-00309: out of range block number (0)

BBED> dump /v file 6 block 1  count 256 offset 0
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 1                                 Offsets:    0 to  255                            Dba:0x01800001
-----------------------------------------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................

<32 bytes per line>

-- 這樣要在bbed下執行應該是(不過還是要小心,至少仔細看一下里面的內容,避免錯誤)。
-- 而且我遇到奇怪的問題每次按照如下順序執行:
dump /v file 7 block 0  count 256 offset 0
dump /v file 6 block 1  count 256 offset 0
dump /v file 6 block 1  count 256 offset 0

--第2步總是報錯BBED-00309: out of range block number (0),但是我反過來就沒有問題。

dump /v file 6 block 1  count 256 offset 0
dump /v file 7 block 0  count 256 offset 0

-- 注意一定要在執行copy前看一下里面的內容,避免寫入錯誤的位置,還是不推薦這種操作模式。

copy file 7 block 0 to file 6 block 1

BBED> copy file 7 block 0 to file 6 block 1
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 1                                                    Offsets:    0 to  255                                               Dba:0x01800001
------------------------------------------------------------------------------------------------------------------------------------------------
00a20000 0000c0ff 00000000 00000000 66ee0000 00200000 00140000 7d7c7b7a a0810000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<64 bytes per line>
--補充如果這樣順序執行,到copy是第1次出錯,第2次ok:
dump /v file 6 block 1  count 256 offset 0
dump /v file 7 block 0  count 256 offset 0

BBED> copy file 7 block 0 to file 6 block 1
BBED-00309: out of range block number (0)


BBED> copy file 7 block 0 to file 6 block 1
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 1                                                    Offsets:    0 to  255                                               Dba:0x01800001
------------------------------------------------------------------------------------------------------------------------------------------------
00a20000 0000c0ff 00000000 00000000 66ee0000 00200000 00140000 7d7c7b7a a0810000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<64 bytes per line>

--完成後退出bbed,再看一下,步驟略。

4.檢查是否正常:

SCOTT@book> alter tablespace sugar online ;
Tablespace altered.

SCOTT@book> select count(*) from t1;
  COUNT(*)
----------
    100000


總結:
還是不推薦這樣操作,僅僅為了瞭解學習的必要。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2128281/,如需轉載,請註明出處,否則將追究法律責任。

相關文章