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

lfree發表於2016-11-10

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

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

--而且解決也很簡單,就是建立一樣大小的資料檔案,然後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;

2.看看第0塊內容,注意實際上建立的資料檔案大小是40M+8K。

$ bvi -b 0 -s 8192 /mnt/ramdisk/book/sugar01.dbf
00000000  00 A2 00 00 00 00 C0 FF 00 00 00 00 00 00 00 00 ................
00000010  66 EE 00 00 00 20 00 00 00 14 00 00 7D 7C 7B 7A f.... ......}|{z
00000020  A0 81 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00000030  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00000040  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00000050  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
...
00001FF0  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00002000
}

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

--安全期間做1個備份。
$ cp /mnt/ramdisk/book/sugar01.dbf /u01/backup/sugar01.dbf_20161110

--全部清零。
SCOTT@book> alter tablespace sugar online ;
alter tablespace sugar online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'

--實際上如果線上resize會重寫第0塊OS塊。

3.建立一樣大小的資料檔案。

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

--注意不能這樣執行,這個不屬於dba地址。
BBED> set dba 7,0
BBED-00205: illegal or out of range DBA (File 7, Block 0)

BBED> help copy
COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ]

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

--問題在於清0後,無法訪問
BBED> set file 6 block 0
        FILE#           6
BBED-00309: out of range block number (0)

BBED> dump /v file 7 block 0  count 128 offset 0
File: /mnt/ramdisk/book/tea01.dbf (7)
Block: 0                                 Offsets:    0 to  127                            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 ................................
<32 bytes per line>
}

--直接編輯資料檔案6就ok了。

$ bvi -b 0 -s 8192 /mnt/ramdisk/book/sugar01.dbf

:set cm=32

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

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

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

相關文章