主頁 > 知識庫 > 一次簡單的Oracle恢復(fù)Case實戰(zhàn)記錄

一次簡單的Oracle恢復(fù)Case實戰(zhàn)記錄

熱門標(biāo)簽:安陽外呼系統(tǒng)免費 海南400電話哪里辦理 濟(jì)南地圖標(biāo)注公司 分布式呼叫中心 南寧人工智能電銷機器人費用 呼倫貝爾智能手機地圖標(biāo)注 圖像地圖標(biāo)注 400電話是不是免費申請 貴陽電話外呼系統(tǒng)哪家好

發(fā)現(xiàn)問題

某網(wǎng)友的數(shù)據(jù)庫由于壞盤了,并且存儲掉電,導(dǎo)致數(shù)據(jù)庫無法open了。單看其數(shù)據(jù)庫alert log的錯誤來看,是非常之簡單的,如下:

Fri Oct 26 10:33:53 2018
Recovery of Online Redo Log: Thread 1 Group 3 Seq 39 Reading mem 0
Mem# 0: /fs/fs/oradata/orcl/redo03.log
Block recovery stopped at EOT rba 39.77.16
Block recovery completed at rba 39.77.16, scn 0.1002048587
ORACLE Instance orcl (pid = 8) - Error 600 encountered while recovering transaction (9, 30) on object 9149.
Fri Oct 26 10:33:53 2018
Errors in file /fs/fs/oradata/admin/orcl/bdump/orcl_smon_192644.trc:
ORA-00600: internal error code, arguments: [6856], [0], [43], [], [], [], [], []
Fri Oct 26 10:33:56 2018
Errors in file /fs/fs/oradata/admin/orcl/bdump/orcl_smon_192644.trc:
ORA-00600: internal error code, arguments: [4194], [33], [36], [], [], [], [], []
Doing block recovery for file 2 block 713
Block recovery from logseq 39, block 82 to scn 1002048595

對于這種錯誤,很明顯,屏蔽回滾段即可,屏蔽之后可順利打開數(shù)據(jù)庫,不過后面很快又會crash掉,因此重建undo也就繞過這個問題了。

打開數(shù)據(jù)庫之后,再去觀察數(shù)據(jù)庫,會發(fā)現(xiàn)alert log有不少的錯誤,如下所示:

Fri Oct 26 11:01:46 2018
Errors in file /fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc:
ORA-00600: internal error code, arguments: [17147], [0x110549070], [], [], [], [], [], []
Fri Oct 26 11:01:46 2018
Errors in file /fs/fs/oradata/admin/orcl/bdump/orcl_m001_373218.trc:
ORA-00600: internal error code, arguments: [kdddgb5], [196650], [0], [], [], [], [], []
ORA-600 encountered when generating server alert SMG-4120
Fri Oct 26 11:01:47 2018
Errors in file /fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc:
ORA-00600: internal error code, arguments: [KGHALO4], [0x11047F6F0], [], [], [], [], [], []
ORA-600 encountered when generating server alert SMG-4121
Fri Oct 26 11:01:48 2018
Errors in file /fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc:
ORA-00600: internal error code, arguments: [KGHALO4], [0x11047F6F0], [], [], [], [], [], []
ORA-600 encountered when generating server alert SMG-4121
Fri Oct 26 11:01:50 2018
Errors in file /fs/fs/oradata/admin/orcl/bdump/orcl_m001_373218.trc:
ORA-00600: internal error code, arguments: [kdddgb5], [196650], [0], [], [], [], [], []
Fri Oct 26 11:02:22 2018
Errors in file /fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc:
ORA-00600: internal error code, arguments: [17114], [0x110549070], [], [], [], [], [], []
Fri Oct 26 11:02:23 2018
Errors in file /fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc:
ORA-00600: internal error code, arguments: [kebm_mmon_main_1], [39], [], [], [], [], [], []
ORA-00039: error during periodic action
ORA-00600: internal error code, arguments: [17114], [0x110549070], [], [], [], [], [], []
Fri Oct 26 11:03:30 2018
Restarting dead background process MMON

除此之外,由于之外alert log有壞塊報錯,因此對system進(jìn)行了dbv檢查,發(fā)現(xiàn)確實存在少量壞塊,如下:

DBVERIFY: Release 10.2.0.4.0 - Production on Fri Oct 26 10:37:20 2018
 
Copyright (c) 1982, 2007, Oracle. All rights reserved.
 
DBVERIFY - Verification starting : FILE = system01.dbf
 
DBV-00200: Block, DBA 4255202, already marked corrupt
Block Checking: DBA = 4258751, Block Type = KTB-managed data block
data header at 0x11022a05c
kdbchk: fsbo(596) wrong, (hsz 4178)
Page 64447 failed with check code 6129
Block Checking: DBA = 4259386, Block Type = KTB-managed data block
**** kdxcofbo = 208 != 24
---- end index block validation
Page 65082 failed with check code 6401
Block Checking: DBA = 4269609, Block Type = Unlimited data segment header
Incorrect extent count in the extent map: 16777317
Block Checking: DBA = 4269612, Block Type = KTB-managed data block
**** kdxcofbo = 224 != 216
---- end index block validation
Page 75308 failed with check code 6401
Block Checking: DBA = 4269615, Block Type = KTB-managed data block
**** actual rows locked by itl 2 = 1 != # in trans. header = 0
---- end index block validation
Page 75311 failed with check code 6401
Page 85271 is influx - most likely media corrupt
Corrupt block relative dba: 0x00414d17 (file 1, block 85271)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x00414d17
last change scn: 0x0000.3afaf495 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xfe830601
check value in block header: 0x96c6
computed block checksum: 0x3c6b
 
Page 85383 is influx - most likely media corrupt
Corrupt block relative dba: 0x00414d87 (file 1, block 85383)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x00414d87
last change scn: 0x0000.3b6b9d19 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x970f0601
check value in block header: 0xe825
computed block checksum: 0x3c6b
 
DBVERIFY - Verification complete
 
Total Pages Examined : 640000
Total Pages Processed (Data) : 116312
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 65914
Total Pages Failing (Index): 3
Total Pages Processed (Other): 64634
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 393138
Total Pages Marked Corrupt : 3
Total Pages Influx : 2
Highest block SCN : 1002028510 (0.1002028510)

這部分錯誤,其實處理起來也不困難,部分是業(yè)務(wù)表的index,但是其他的幾乎都是AWR相關(guān)基表,有2個壞塊跟是system相關(guān)的基表和索引,分別是I_H_OBJ#_COL#和COM$ ,HISTGRM$。

對于業(yè)務(wù)索引,很簡單,直接drop 重建即可,對于這個sys的index,可以通過設(shè)置38003 event進(jìn)行drop重建。

對于基表COM$,HISTGRM$,由于是非bootstrap$核心對象,其實也可以處理掉的。

處理方法

不過考慮到這種畢竟是存儲掉電,undo異常的情況,還是重建庫更穩(wěn)妥一些。最后補充一點,這個庫稍微有點奇葩的地方是全庫1.2TB,其中有個表的LOB自動980GB,重建數(shù)據(jù)庫是相對較慢的。對于大表,且有LOB自動,通常建議基于分片,否則會報ORA-01555錯誤的,如下是常用的一個基于rowid的分片腳本,供大家參考:

set verify off
 
undefine rowid_ranges
 
undefine segment_name
 
undefine owner
 
set head off
 
set pages 0
 
set trimspool on
 
select 'where rowid between ''' ||
 
  sys.dbms_rowid.rowid_create(1, d.oid, c.fid1, c.bid1, 0) ||
 
  ''' and ''' ||
 
  sys.dbms_rowid.rowid_create(1, d.oid, c.fid2, c.bid2, 9999) || '''' || ';'
 
 from (select distinct b.rn,
 
      first_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid1,
 
      last_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid2,
 
      first_value(decode(sign(range2 - range1),
 
           1,
 
           a.bid +
 
           ((b.rn - a.range1) * a.chunks1),
 
           a.bid)) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid1,
 
      last_value(decode(sign(range2 - range1),
 
           1,
 
           a.bid +
 
           ((b.rn - a.range1 + 1) * a.chunks1) - 1,
 
           (a.bid + a.blocks - 1))) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid2
 
   from (select fid,
 
      bid,
 
      blocks,
 
      chunks1,
 
      trunc((sum2 - blocks + 1 - 0.1) / chunks1) range1,
 
      trunc((sum2 - 0.1) / chunks1) range2
 
     from (select /*+ rule */
 
       relative_fno fid,
 
       block_id bid,
 
       blocks,
 
       sum(blocks) over() sum1,
 
       trunc((sum(blocks) over()) / rowid_ranges) chunks1,
 
       sum(blocks) over(order by relative_fno, block_id) sum2
 
       from dba_extents
 
       where segment_name = upper('segment_name')
 
       and owner = upper('owner'))
 
     where sum1 > rowid_ranges) a,
 
    (select rownum - 1 rn
 
     from dual
 
    connect by level = rowid_ranges) b
 
   where b.rn between a.range1 and a.range2) c,
 
  (select max(data_object_id) oid
 
   from dba_objects
 
   where object_name = upper('segment_name')
 
   and owner = upper('owner')
 
   and data_object_id is not null) d
 
/

總結(jié)

以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,如果有疑問大家可以留言交流,謝謝大家對腳本之家的支持。

您可能感興趣的文章:
  • oracle case when 語句的用法詳解
  • Oracle case函數(shù)使用介紹
  • Oracle數(shù)據(jù)庫的備份與恢復(fù)
  • Oracle誤刪除表數(shù)據(jù)后的數(shù)據(jù)恢復(fù)詳解
  • oracle冷備份恢復(fù)和oracle異機恢復(fù)使用方法
  • oracle如何恢復(fù)被覆蓋的存儲過程
  • rman恢復(fù)方案和oracle異機恢復(fù)
  • Oracle數(shù)據(jù)庫數(shù)據(jù)丟失恢復(fù)的幾種方法總結(jié)
  • oracle drop table(表)數(shù)據(jù)恢復(fù)方法
  • oracle數(shù)據(jù)庫創(chuàng)建備份與恢復(fù)腳本整理

標(biāo)簽:合肥 遼源 許昌 焦作 郴州 南充 滁州 涼山

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《一次簡單的Oracle恢復(fù)Case實戰(zhàn)記錄》,本文關(guān)鍵詞  一次,簡單,的,Oracle,恢復(fù),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《一次簡單的Oracle恢復(fù)Case實戰(zhàn)記錄》相關(guān)的同類信息!
  • 本頁收集關(guān)于一次簡單的Oracle恢復(fù)Case實戰(zhàn)記錄的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章