Oracle_UNDO壞塊測試和修復(BBED)

chenoracle發表於2016-11-12

Oracle_UNDO壞塊測試和修復

  

UNDO段頭塊損壞測試與修復

 

本次案例通過BBED工具模擬UNDO段頭壞塊,並在沒有備份情況下啟動資料庫;

 

1 檢視UNDO段頭塊位置

select header_file, header_block

  from dba_segments

 where segment_name like '_SYSSMU%'

 order by 2;

 

2 通過BBED工具,破壞UNDO某一段的段頭塊(file=3 block=280)

破壞的方式是直接將其他的資料塊覆蓋段頭塊

 

[oracle11@primary ~]$ bbed parfile=bbed.par

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Sat Jul 30 18:00:26 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> copy dba 1,1 to dba 3,280

BBED> sum apply;

Check value for File 3, Block 280:

current = 0x599e, required = 0x599e

BBED> verify

DBVERIFY - Verification starting

FILE = /u02/app/oracle/oradata/orcl11/undotbs01.dbf

BLOCK = 280

 

Block 280 is corrupt

Corrupt block relative dba: 0x00400118 (file 0, block 280)

Bad header found during verification

Data in bad block:

 type: 11 format: 2 rdba: 0x00400001

 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x00000b01

 check value in block header: 0xc8c7

 computed block checksum: 0x0

 

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

Total Blocks Influx           : 0

Message 531 not found;  product=RDBMS; facility=BBED

 

---通過BBEDDBV檢查結果都是file3,block 280損壞

 

[oracle11@primary orcl11]$ dbv file=undotbs01.dbf

 

DBVERIFY: Release 11.2.0.4.0 - Production on Sat Jul 30 18:01:38 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

DBVERIFY - Verification starting : FILE = /u02/app/oracle/oradata/orcl11/undotbs01.dbf

Page 280 is marked corrupt

Corrupt block relative dba: 0x00c00118 (file 3, block 280)

Bad header found during dbv:

Data in bad block:

 type: 11 format: 2 rdba: 0x00400001

 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x00000b01

 check value in block header: 0xc8c7

 computed block checksum: 0x0

 

DBVERIFY - Verification complete

 

Total Pages Examined         : 392

Total Pages Processed (Data) : 0

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 45

Total Pages Processed (Seg)  : 23

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 346

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 1283208 (0.1283208)

 

---模擬異常斷電

SQL> shutdown abort

ORACLE instance shut down.

 

---啟動資料庫,報錯ORA-01578

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             515903032 bytes

Database Buffers          264241152 bytes

Redo Buffers                2596864 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01578: ORACLE data block corrupted (file # 3, block # 280)

ORA-01110: data file 3: '/u02/app/oracle/oradata/orcl11/undotbs01.dbf'

Process ID: 8265

Session ID: 1 Serial number: 5

 

通常UNDO損壞,在沒有備份的情況下,可以通過以下方式啟動資料庫

#*.undo_tablespace='UNDOTBS1'       ----註釋原UNDO表空間

#*.undo_management=AUTO            ----UNDO管理方式改為手動

*.undo_management='MANUAL'

*.undo_tablespace='SYSTEM'            ---將UNDO表空間改成SYSTEM

*._corrupted_rollback_segments=損壞的回滾段    ---遮蔽損壞的UNDO

建立新的回滾段:

create undo tablespace UNDOTBS2 datafile '/u02/app/oracle/oradata/orcl11/undotbs02.dbf' size 1M autoextend on;

刪除舊的回滾段:

drop tablespace UNDOTBS1 including contents and datafiles;

*.undo_tablespace='UNDOTBS2'

*.undo_management=AUTO

 

但是在mount狀態下無法查詢(建立或刪除)回滾段

SQL> select * from v$rollname;

select * from v$rollname

              *

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

 

無法建立新的UNDO表空間

SQL> create undo tablespace UNDOTBS2 datafile '/u02/app/oracle/oradata/orcl11/undotbs02.dbf' size 1M autoextend on;

create undo tablespace UNDOTBS2 datafile '/u02/app/oracle/oradata/orcl11/undotbs02.dbf' size 1M autoextend on

*

ERROR at line 1:

ORA-01109: database not open

 

無法刪除舊的UNDO表空間

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

drop tablespace UNDOTBS1 including contents and datafiles

*

ERROR at line 1:

ORA-01109: database not open

 

在資料庫不能OPEN情況下,有兩種方式可以查詢資料庫部分資訊;

 

1strings命令可以查詢所有的UNDO回滾段名,包括已經刪除的回滾段;

[oracle11@primary orcl11]$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u > listSMU

 

[oracle11@primary orcl11]$ vim listSMU

_SYSSMU20_3293637928$

_SYSSMU20_379396250$

_SYSSMU20_379396250$

_SYSSMU13_811223436$

........

 

2BBED工具也可以查詢UNDO段名;

BBED> set file 1 block 225  -----Oracle 11g版本,undo$表資訊一般位於1號檔案第225個資料塊中

        FILE#           1

        BLOCK#         225

 

BBED> map

 File: /u02/app/oracle/oradata/orcl11/system01.dbf (1)

 Block: 225                                   Dba:0x004000e1

------------------------------------------------------------

 KTB Data Block (Table/Cluster)

 

 struct kcbh, 20 bytes                      @0      

 

 struct ktbbh, 48 bytes                     @20     

 

 struct kdbh, 14 bytes                      @68     

 

 struct kdbt[1], 4 bytes                    @82     

 

 sb2 kdbr[25]                               @86      -------含有25個UNDO

 

 ub1 freespace[6402]                        @136    

 

 ub1 rowdata[1650]                          @6538   

 

 ub4 tailchk                                @8188   

 

BBED> p kdbr

sb2 kdbr[0]                                 @86       8078

sb2 kdbr[1]                                 @88       8011

sb2 kdbr[2]                                 @90       7944

......

sb2 kdbr[22]                                @130      6603

sb2 kdbr[23]                                @132      6537

sb2 kdbr[24]                                @134      6470

 

BBED> x /rnc *kdbr[0]        ----檢視0號UNDO段名稱

col    1[6] @8151: SYSTEM

 

BBED> x /rnc *kdbr[1]        ----檢視1號UNDO段名稱

col   1[20] @8085: _SYSSMU1_4115952380$


如果UNDO段特別多,可以通過EXECL,自動生成多個x /rnc *kdbr[0]......*kdbr[n]命令,再將命令複製貼上到BBED中,同時獲取多個UNDO段名;

 x /rnc *kdbr[0]

 x /rnc *kdbr[1]

 x /rnc *kdbr[2]

 x /rnc *kdbr[3]

......

 x /rnc *kdbr[24]

 

如果不能判斷具體哪個回滾段出現問題,可以跳過所有的回滾段

*._corrupted_rollback_segments='_SYSSMU1_4115952380$','_SYSSMU2_3882698531$','_SYSSMU3_1780844141$','_SYSSMU4_1137450214$','_SYSSMU5_2972601029$','_SYSSMU6_2318781079$','_SYSSMU7_1865616030$','_SYSSMU8_4279519761$','_SYSSMU9_1551968587$','_SYSSMU10_2324134815$','_SYSSMU11_2069826877$','_SYSSMU12_2242918609$','_SYSSMU13_811223436$','_SYSSMU14_1093125402$','_SYSSMU15_2825991097$','_SYSSMU16_252471872$','_SYSSMU17_3347133763$','_SYSSMU18_1765883319$','_SYSSMU19_1005333767$','_SYSSMU20_3293637928$','_SYSSMU21_3641740596$','_SYSSMU22_3421614834$','_SYSSMU23_138031739$'

 

引數檔案:

#*.undo_tablespace='UNDOTBS1'

#*.undo_management=AUTO

*.undo_tablespace='SYSTEM'

*.undo_management='MANUAL'

*._corrupted_rollback_segments='_SYSSMU1_4115952380$','_SYSSMU2_3882698531$','_SYSSMU3_1780844141$','_SYSSMU4_1137450214$','_SYSSMU5_2972601029$','_SYSSMU6_2318781079$','_SYSSMU7_1865616030$','_SYSSMU8_4279519761$','_SYSSMU9_1551968587$','_SYSSMU10_2324134815$','_SYSSMU11_2069826877$','_SYSSMU12_2242918609$','_SYSSMU13_811223436$','_SYSSMU14_1093125402$','_SYSSMU15_2825991097$','_SYSSMU16_252471872$','_SYSSMU17_3347133763$','_SYSSMU18_1765883319$','_SYSSMU19_1005333767$','_SYSSMU20_3293637928$','_SYSSMU21_3641740596$','_SYSSMU22_3421614834$','_SYSSMU23_138031739$'                     

SQL> shutdown immediate

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             515903032 bytes

Database Buffers          264241152 bytes

Redo Buffers                2596864 bytes

Database mounted.

Database opened.

 

建立新的UNDO表空間

create undo tablespace UNDOTBS2 datafile '/u02/app/oracle/oradata/orcl11/undotbs02.dbf' size 1M autoextend on;

 

刪除舊的UNDO表空間

drop tablespace UNDOTBS1 including contents and datafiles;

 

修改引數檔案

*.undo_tablespace='UNDOTBS2'

*.undo_management=AUTO

#*.undo_tablespace='SYSTEM'

#*.undo_management='MANUAL'

#*._corrupted_rollback_segments='_SYSSMU1_4115952380$','_SYSSMU2_3882698531$','_SYSSMU3_1780844141$','_SYSSMU4_1137450214$','_SYSSMU5_2972601029$','_SYSSMU6_2318781079$','_SYSSMU7_1865616030$','_SYSSMU8_4279519761$','_SYSSMU9_1551968587$','_SYSSMU10_2324134815$','_SYSSMU11_2069826877$','_SYSSMU12_2242918609$','_SYSSMU13_811223436$','_SYSSMU14_1093125402$','_SYSSMU15_2825991097$','_SYSSMU16_252471872$','_SYSSMU17_3347133763$','_SYSSMU18_1765883319$','_SYSSMU19_1005333767$','_SYSSMU20_3293637928$','_SYSSMU21_3641740596$','_SYSSMU22_3421614834$','_SYSSMU23_138031739$'

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             515903032 bytes

Database Buffers          264241152 bytes

Redo Buffers                2596864 bytes

Database mounted.

Database opened.

 

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS2

 

BBED修改資料塊是比較危險的操作,如果某個修改操作有誤,可以通過revertundo命令回退BBED的修改操作;

 

例如:BBED回退3,280塊上所有修改

BBED> revert dba 3,280

All changes made to this block will be rolled back. Proceed? (Y/N) y

Reverted file '/u02/app/oracle/oradata/orcl11/undotbs01.dbf', block 280

 

BBED> sum apply;

Check value for File 3, Block 280:

current = 0x3f90, required = 0x3f90

 

UNDO非段頭(檔案頭)塊損壞測試與修復

undo非段頭(檔案頭)損壞,資料庫可以正常啟動,在沒有備份的情況下,可以通過alert報錯資訊,找到並刪除受損的回滾段

 

SQL> insert into t values(1);      -----插入一條資料,不提交

 

SQL> select usn,status,xacts from v$rollstat;

 

       USN STATUS               XACTS

     ---------- ---------------           ----------

         0 ONLINE                   0

         8 ONLINE                   0

         9 ONLINE                   1           ----9號回滾段存在活動事物

        10 ONLINE                   0

        11 ONLINE                   0

        12 ONLINE                   0

        24 ONLINE                   0

        25 ONLINE                   0

        26 ONLINE                   0

        27 ONLINE                   0

        28 ONLINE                   0

 

11 rows selected.

 

---檢視回滾段頭塊位置

SQL> SET LINE 100

SQL> col segment_name for a30

SQL> select segment_name,header_file,header_block from dba_segments where segment_name like '_SYSSMU%' order by 3;

 

SEGMENT_NAME                   HEADER_FILE HEADER_BLOCK

------------------------------ ----------- ------------

_SYSSMU8_4161384913$                     3            8

_SYSSMU9_1458183674$                     3           24

_SYSSMU10_2644453179$                    3           40

_SYSSMU11_4737420$                       3           56

_SYSSMU12_392022772$                     3           72

_SYSSMU24_4044825012$                    3           88

_SYSSMU25_2098992521$                    3          104

_SYSSMU26_2158116475$                    3          120

_SYSSMU27_4048022843$                    3          136

_SYSSMU28_1413754230$                    3          152

 

10 rows selected.

 

通過BBED工具,手動破壞9號回滾段非頭塊;

[oracle11@primary ~]$ bbed parfile=bbed.par

Password:

 

BBED: Release 2.0.0.0.0 - Limited Production on Sat Aug 13 22:35:38 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> copy dba 1,1 to dba 3,25

 

BBED> sum apply;

Check value for File 3, Block 25:

current = 0xae9a, required = 0xae9a

 

BBED> verify

DBVERIFY - Verification starting

FILE = /u02/app/oracle/oradata/orcl11/undotbs01.dbf

BLOCK = 25

 

Block 25 is corrupt

Corrupt block relative dba: 0x00400019 (file 3, block 25)

Bad header found during verification

Data in bad block:

 type: 11 format: 2 rdba: 0x00400001

 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x00000b01

 check value in block header: 0xae9a

 computed block checksum: 0x0

 

 

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

Total Blocks Influx           : 0

Message 531 not found;  product=RDBMS; facility=BBED

 

[oracle11@primary orcl11]$ dbv file=undotbs01.dbf

 

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Aug 17 11:39:35 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

DBVERIFY - Verification starting : FILE = /u02/app/oracle/oradata/orcl11/undotbs01.dbf

Page 25 is marked corrupt

Corrupt block relative dba: 0x00c00019 (file 3, block 25)

Bad header found during dbv:

Data in bad block:

 type: 11 format: 2 rdba: 0x00400001

 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x00000b01

 check value in block header: 0xae9a

 computed block checksum: 0x0

 

DBVERIFY - Verification complete

 

Total Pages Examined         : 208

Total Pages Processed (Data) : 0

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 88

Total Pages Processed (Seg)  : 10

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 119

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 1570655 (0.1570655)

 

SQL> shutdown abort

ORACLE instance shut down.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             515903032 bytes

Database Buffers          264241152 bytes

Redo Buffers                2596864 bytes

Database mounted.

Database opened.

 

資料庫可以正常啟動,後臺alert日誌也沒有報錯,通過dbv或者bbed工具檢查出壞塊後,可以手動刪除壞塊對應的undo段:

(1)select * from dba_extents where file_id=xx and xxx between block_id and block_id+blocks-1;

(2)DROP ROLLBACK SEGMENT rollback_segment; 

或者直接新建UNDO表空間:

(1):建立新的UNDO表空間

create undo tablespace UNDOTBS2 datafile '/u02/app/oracle/oradata/orcl11/undotbs02.dbf' size 1M autoextend on;

(2):刪除舊的UNDO表空間

drop tablespace UNDOTBS1 including contents and datafiles;

 

UNDO檔案頭塊損壞測試與修復

UNDO檔案頭損壞,無法正常open資料庫;

SQL> shutdown abort

ORACLE instance shut down.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             515903032 bytes

Database Buffers          264241152 bytes

Redo Buffers                2596864 bytes

Database mounted.

ORA-01122: database file 3 failed verification check

ORA-01110: data file 3: '/u02/app/oracle/oradata/orcl11/undotbs01.dbf'

ORA-01210: data file header is media corrupt


在沒有備份的情況下,需要通過BBED工具進行修復損壞的檔案頭;

修復的方式是通過複製其他資料檔案頭,並手動修改檔案頭中相關資訊;

 

1、修改資料的DBA,rdba_kcbh

 2、修改檔案的大小,kccfhfsz

 3、修改檔案號,kccfhfno

 4、修改檔案建立時SCNkcvfhcrs

 5、修改檔案建立時間,kcvfhcrt

 6、修改表空間號,kcvfhtsn 

 7、修改相對檔案號,kcvfhrfn  

 8、修改表空間的名稱, kcvfhtnm

 9、修改表空間的長度,kcvfhtln     

 10、修改檢查點的SCNkcvfhckp

 11、修改檢查點的時間,kcvcptim 

 12、修改檢查點的計數器,kcvfhcpc

 13、修改檢查點的控制檔案備份的計數器, kcvfhccc

 14、如果你修改是1號檔案的1號塊,他的root rdba的地針是指向了bootstrap$

 

通過BBED修復UNDO檔案頭壞塊過程比較複製,並且BBED工具並不對外公開,也不提供技術支援,使用過程中很容易出現問題,建議在正式環境儘量避免使用BBED工具;

 

可以通過下面網站檢視具體修改過程;

http://blog.csdn.net/guoyjoe/article/details/31018075

 

BBED工具的安裝

 

Oracle 11g版本和以後的版本已經不提供bbed工具了,11g資料庫如果需要使用bbed工具,可以拷貝10g或之前版本資料庫上的三個檔案:

 

[oracle11@primary ~]$ ll -rth bbed_install/

total 20K

-rw-r--r-- 1 root root 8.5K Sep  8  2012 bbedus.msb

-rw-r--r-- 1 root root 1.9K Sep  8  2012 sbbdpt.o

-rw-r--r-- 1 root root 1.2K Sep  8  2012 ssbbded.o

 

將檔案拷貝到指定目錄

[oracle11@primary ~]$ cp /home/oracle11/bbed_install/bbedus.msb /u02/app/oracle/

product/11.2.0/rdbms/mesg/

 

[oracle11@primary ~]$ cp /home/oracle11/bbed_install/ssbbded.o /u02/app/oracle/product/11.2.0/rdbms/lib/

 

[oracle11@primary ~]$ cp /home/oracle11/bbed_install/sbbdpt.o /u02/app/oracle/product/11.2.0/rdbms/lib/

編譯

[oracle11@primary ~]$ make -f /u02/app/oracle/product/11.2.0/rdbms/lib/ins_rdbms

.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed

 

bbed預設密碼"blockedit"

[oracle11@primary ~]$ bbed

Password:

 

BBED: Release 2.0.0.0.0 - Limited Production on Sat Jul 30 14:22:17 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED>

 

使用BBED工具之前需要建立filelist檔案

SQL> set linesize 100

SQL> col name for a45

SQL> spool /home/oracle11/filelist.txt

SQL> select file#,name,bytes from v$datafile order by 1;

 

     FILE# NAME                                               BYTES

---------- --------------------------------------------- ----------

         1 /u02/app/oracle/oradata/orcl11/system01.dbf    775946240

         2 /u02/app/oracle/oradata/orcl11/sysaux01.dbf    545259520

         3 /u02/app/oracle/oradata/orcl11/undotbs01.dbf    73400320

         4 /u02/app/oracle/oradata/orcl11/users01.dbf       5242880

         5 /u02/app/oracle/oradata/orcl11/chen01.dbf        1048576

 

SQL> spool off

 

[oracle11@primary ~]$ touch bbed.par

[oracle11@primary ~]$ vim bbed.par

blocksize=8192

listfile=/home/oracle11/filelist.txt

mode=edit

 

[oracle11@primary ~]$ bbed parfile=bbed.par

Password:

 

BBED: Release 2.0.0.0.0 - Limited Production on Sat Jul 30 14:36:34 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> show

        FILE#           1

        BLOCK#          1

        OFFSET          0

        DBA             0x00400001 (4194305 1,1)

        FILENAME        /u02/app/oracle/oradata/orcl11/system01.dbf

        BIFILE          bifile.bbd

        LISTFILE        /home/oracle11/filelist.txt

        BLOCKSIZE       8192

        MODE            Edit

        EDIT            Unrecoverable

        IBASE           Dec

        OBASE           Dec

        WIDTH           80

        COUNT           512

        LOGFILE         log.bbd

        SPOOL           No

 

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

相關文章