[20160329]bbed修復offline的資料檔案.txt

lfree發表於2016-03-29

[20160329]bbed修復offline的資料檔案.txt

--測試資料庫,不小心將一個資料檔案offline了,archivelog也刪除了(主要磁碟空間緊張,做了一次整理)。
--自己測試修復看看,順便做一個記錄。

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

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                             TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ----------------
    1        13227498080 2016-03-29 11:44:35                7       13227286650 ONLINE               999 NO  /mnt/ramdisk/book/system01.dbf   SYSTEM
    2        13227498080 2016-03-29 11:44:35             1834       13227286650 ONLINE               995 NO  /mnt/ramdisk/book/sysaux01.dbf   SYSAUX
    3        13227498080 2016-03-29 11:44:35           923328       13227286650 ONLINE               915 NO  /mnt/ramdisk/book/undotbs01.dbf  UNDOTBS1
    4        13227498080 2016-03-29 11:44:35            16143       13227286650 ONLINE               999 NO  /mnt/ramdisk/book/users01.dbf    USERS
    5        13227498080 2016-03-29 11:44:35           952916       13227286650 ONLINE               912 NO  /mnt/ramdisk/book/example01.dbf  EXAMPLE
    6        13227498080 2016-03-29 11:44:35          1314508       13227286650 ONLINE               928 NO  /mnt/ramdisk/book/sugar01.dbf    SUGAR
    7        13227287392 2016-03-25 17:13:07      13227207527       13227286650 OFFLINE               20 YES /mnt/ramdisk/book/tea01.dbf      TEA
7 rows selected.

--我估計CHECKPOINT_CHANGE# 修改為13227498080 就ok了。測試看看。安全起見做一個備份:
$ cp tea01.dbf_ORG tea01.dbf

SYS@book> @ &r/10to16 13227287392

10 to 16 HEX   REVERSE16
-------------- -----------------------------------
0000314686360 0x60636814-03000000

SYS@book> @ &r/10to16 13227498080

10 to 16 HEX   REVERSE16
-------------- -----------------------------------
00003146b9a60 0x609a6b14-03000000

SCOTT@book> @ &r/bbvi 7 1
BVI_COMMAND
--------------------------------------------------
bvi -b 8192 -s 8192 /mnt/ramdisk/book/tea01.dbf

$ cp tea01.dbf tea01.dbf_ORG

2.使用bvi開啟,修改0x60636814=>0x609a6b14

BBED> set dba 7,1
        DBA             0x01c00001 (29360129 7,1)

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      0x14686360

BBED> set dba 1,1
        DBA             0x00400001 (4194305 1,1)

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      0x146b9a60

--換1種修改模式算是學習。使用bbed的assign命令。
BBED> assign file 7 block 1 offset 484= file 1 block 1 offset 484;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub1 pad                                     @484      0x60

BBED> set dba 1,1
        DBA             0x00400001 (4194305 1,1)

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      0x146b9a60

BBED> set dba 7,1
        DBA             0x01c00001 (29360129 7,1)

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      0x14686360

--仔細檢查發現沒改,僅僅修改最後1個位元組,實際上這裡正好一樣(看不出來)。
BBED> help assign
ASSIGN[/x|d|u|o] <target spec>=<source spec>
<target spec> : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
<source spec> : [ value | <target spec options> ]

BBED> assign dba 7,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas = dba 1,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas;
ub4 kscnbas                                 @484      0x146b9a60

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 1,1
ub4 kscnbas                                 @484      0x146b9a60

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 7,1
ub4 kscnbas                                 @484      0x146b9a60

--ok現在一樣了。
BBED> sum apply dba 7,1
Check value for File 7, Block 1:
current = 0xdedb, required = 0xdedb

BBED> verify  dba 7,1
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/tea01.dbf
BLOCK = 1


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

3.重啟到mount狀態:
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                            TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ----------------
    1        13227498080 2016-03-29 11:44:35                7       13227286650 ONLINE               999 NO  /mnt/ramdisk/book/system01.dbf  SYSTEM
    2        13227498080 2016-03-29 11:44:35             1834       13227286650 ONLINE               995 NO  /mnt/ramdisk/book/sysaux01.dbf  SYSAUX
    3        13227498080 2016-03-29 11:44:35           923328       13227286650 ONLINE               915 NO  /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
    4        13227498080 2016-03-29 11:44:35            16143       13227286650 ONLINE               999 NO  /mnt/ramdisk/book/users01.dbf   USERS
    5        13227498080 2016-03-29 11:44:35           952916       13227286650 ONLINE               912 NO  /mnt/ramdisk/book/example01.dbf EXAMPLE
    6        13227498080 2016-03-29 11:44:35          1314508       13227286650 ONLINE               928 NO  /mnt/ramdisk/book/sugar01.dbf   SUGAR
    7        13227498080 2016-03-25 17:13:07      13227207527       13227286650 OFFLINE               20 YES /mnt/ramdisk/book/tea01.dbf     TEA
7 rows selected.
--現在一致了,recover看看。

SYS@book> recover datafile 7;
Media recovery complete.

alter database open read only ;

SYS@book> select owner,segment_name from dba_segments where tablespace_name='TEA';
OWNER  SEGMENT_NAME
------ --------------------
SCOTT  EMPX

SYS@book> select * from scott.empx ;
select * from scott.empx
                    *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

SYS@book> alter database datafile 7 online ;
Database altered.

SYS@book> select * from scott.empx where rownum<=1;
       EMPNO ENAME      JOB                MGR HIREDATE                     SAL         COMM       DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
        7369 YYYY       CLERK             7902 1980-12-17 00:00:00          800                        20

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

相關文章