Oracle資料庫壞塊修復

aishu521發表於2012-06-29
Oracle資料庫

資料來源：http://space.itpub.net/?uid-23009281-action-viewspace-itemid-734044

昨天下午資料庫負載突然升高，最高達每分鐘40左右。正常情況下最高也就每分鐘2左右。經過常用的方法檢查，也沒有找到原因，後來自動好了。為了確保這樣的情況不再莫名的發生，需要從更深的層次的找到原因。

        今天開啟alert日誌檔案，發現有壞塊錯誤。這時才懷疑是不是由於壞塊導致的負載升高，這還有待進一步的確認。不過當前最主要的是把壞塊給處理掉。
alert.log錯誤日誌如下：
Hex dump of (file 34, block 43487) in trace file /opt/oracle/admin/mbar/udump/orcl_ora_18143.trc
Corrupt block relative dba: 0x0880a9df (file 34, block 43487)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x0880a9df
 last change scn: 0x09c9.33765b70 seq: 0x2 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x44a90602
 check value in block header: 0xf70d
 computed block checksum: 0xe59a
Reread of rdba: 0x0880a9df (file 34, block 43487) found same corrupted data
Wed Jun 27 15:11:24 2012
Corrupt Block Found
         TSN = 10, TSNAME = BBS_DATA
         RFN = 34, BLK = 43487, RDBA = 142649823
         OBJN = 97543, BJD = 97543, BJECT = T_USER_LOGON, SUBOBJECT = 
         SEGMENT WNER = BBS, SEGMENT TYPE = Table Segment

從日誌中可以清晰的看出是使用者BBS下的BBS_DATA表空間的檔案號為34存在壞塊，並且表名也給出來了是T_USER_LOGON。

[oracle@db02 ~]$ sqlplus 使用者名稱/密碼
SQL> SELECT COUNT(*) FROM T_USER_LOGON;
執行命令後可以查出來資料，並沒有報錯。但是執行下面命令後就報錯了。
SQL> SELECT COUNT(*) FROM T_USER_LOGON  T WHERE TO_CHAR(T.LOGON_TIME,'YYYY-MM-DD')='2012-06-28';
提示有壞塊，為什麼加上個where條件就不行，不知道是那裡的問題。

以下是解決過程 ：
1、根據檔案號查詢對應的檔案：
SQL> select file#,name from v$datafile where file#=34;

2、使用dbv檢查： 
[oracle@db02 ~]$ dbv file=/opt/oradata/play03.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on 星期四 28 10:30:51 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = /opt/oradata/play03.dbf

DBV-00200: Block, dba 30767628, already marked corrupted

DBVERIFY - Verification complete

Total Pages Examined : 4194302
Total Pages Processed (Data) : 3508146
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 605887
Total Pages Failing (Index): 0
Total Pages Processed (Other): 75876
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4393
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 3781089739 (0.3781089739)

3、其次要確認壞塊型別是物理壞塊還是邏輯壞塊
physical corruption check: backup validate datafile 34;
logical corruption check: backup check logical validate datafile 34;

先進行物理壞塊檢測
RMAN> backup validate datafile 34;

Starting backup at 28-6月 -12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1035 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00034 name=/opt/oradata/play03.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 28-6月 -12

4、檢視檢視：
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        34      43487          1                  0 FRACTURED

確認為物理壞塊！

5、最後也是最重要的一步，進行壞塊恢復。注意這一步必須在有備份的情況下執行，否則恢復不了。
RMAN> BLOCKRECOVER CORRUPTION LIST;
注：由於我沒有備份，所以也不能用這種方式恢復。哭呀！現在終於知道備份的重要性了。所以改用其他方法（exp/imp匯出匯入）。

6、恢復步驟:
[oracle@db02 bdump]$ exp 使用者名稱/密碼 file='/opt/tlogon-0628.dmp' tables=(T_USER_LOGON)

Export: Release 10.2.0.1.0 - Production on 28 10:08:43 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table               T_USER_LOGON
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 34, block # 43487)
ORA-01110: data file 34: '/opt/oradata/play03.dbf'
Export terminated successfully with warnings.
[oracle@db02 bdump]$ 

[oracle@db02 bdump]$ sqlplus 使用者名稱/密碼

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 28 10:30:51 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10';

System altered.

SQL> host
I注：
ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10' ;
內部事件，設定在全表掃描時跳過損壞的資料塊.

[oracle@db02 bdump]$ exp  使用者名稱/密碼  file='/opt/tlogon-0628.dmp' tables=(T_USER_LOGON)

Export: Release 10.2.0.1.0 - Production on 星期四 28 10:31:11 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table               T_USER_LOGON     768657 rows exported
Export terminated successfully without warnings.
注：這時候成功匯出!
然後我們可以drop table，recreate，然後匯入資料！

[oracle@db02 bdump]$ exit

SQL> ALTER SYSTEM SET EVENTS='10231 trace name context off'; 

System altered.

SQL> drop table T_USER_LOGON;
SQL>host
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@db02 bdump]$ imp  使用者名稱/密碼  file='/opt/tlogon-0628.dmp' ignore=y full=y

7、完成資料恢復！驗證。
[oracle@db02 ~]$ sqlplus 使用者名稱/密碼
SQL> SELECT COUNT(*) FROM T_USER_LOGON;

SQL> SELECT COUNT(*) FROM T_USER_LOGON  T WHERE TO_CHAR(T.LOGON_TIME,'YYYY-MM-DD')='2012-06-28';
都沒有報錯，正常。

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

相關文章