程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle 9i數據壞塊的處理實例

Oracle 9i數據壞塊的處理實例

編輯:Oracle數據庫基礎

筆者在一台生產用測試庫上SELECT一個表時出現ORA-01578,一個塊損壞,以前學習過塊損壞怎麼處理,到還真沒遇到過,今天總算讓我遇到了,還是一台生產用測試庫,就不用很緊張了。

數據庫版本是9.2.0.4,Oracle9i的RMAN有一個blockrecover命令,可以在線修復壞塊,以下就是使用RMAN修復壞塊的過程。

SQL> conn owi/owi
Connected.
SQL> select * from dpa_history;
select * from dpa_history
*
ERROR at line 1:
ORA-01578: Oracle data block corrupted (file # 15, block # 18)
ORA-01110: data file 15: '/d01/app/Oracle/oradata/dpa/dpa01.dbf'

報ORA-01578數據塊損壞,以下使用RMAN命令查詢是否可以使用blockrecover命令恢復以及怎樣恢復

使用rman登錄catalog數據庫

[ora9@rmanserver ~]$ rman target sys/Oracle@dpa catalog rman/rman

Recovery Manager: Release 9.2.0.8.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: DPA (DBID=843495022)
connected to recovery catalog database

查找最近datafile 15的全備份,今天下午剛做了一次RMAN的全備份

RMAN> list backup of datafile 15;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
643     Full    64K        DISK        00:00:27     16-MAR-09
BP Key: 650   Status: AVAILABLE   Tag: TAG20090316T154352
PIEce Name: /d02/fullbackup/20090316_data_24_1
List of Datafiles in backup set 643
File LV Type Ckp SCN    Ckp Time  Name
---- -- ---- ---------- --------- ----
15      Full 11856250905 16-MAR-09 /d01/app/Oracle/oradata/dpa/dpa01.dbf

查找SCN 11856250905 以後的archivelog是否有備份

RMAN> list backup of archivelog scn from 11856250905
List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
680     265K       DISK        00:00:00     16-MAR-09
BP Key: 681   Status: AVAILABLE   Tag: TAG20090316T154731
PIEce Name: /d02/fullbackup/20090316_arch_28
List of Archived Logs in backup set 680
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
---- ------- ---------- --------- ---------- ---------
1    109     11856250805 16-MAR-09 11856251483 16-MAR-09
1    110     11856251483 16-MAR-09 11856251487 16-MAR-09

查找sequence 110 以後的archivelog是否有備份

RMAN> list copy of archivelog from sequence 110;

List of Archived Log CopIEs
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
694     1    111     A 16-MAR-09 /d02/arch/1_111.dbf
695     1    112     A 16-MAR-09 /d02/arch/1_112.dbf

查詢online archive log

SQL> select sequence#,members,archived,status from v$log;

SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- --- ----------------
113          1 NO  CURRENT
111          1 YES INACTIVE
112          1 YES INACTIVE

從以上查詢中可以看出datafile 15有一次最近的全備份,有全備份以來的所有archivelog,online redo log

下面開始blockreocver,其實命令很簡單

RMAN> blockrecover datafile 15 block 18;

Starting blockrecover at 16-MAR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK


channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00015
channel ORA_DISK_1: restored block(s) from backup pIEce 1
pIEce handle=/d02/fullbackup/20090316_data_24_1 tag=TAG20090316T154352 params=NULL
channel ORA_DISK_1: block restore complete

starting media recovery

archive log thread 1 sequence 111 is already on disk as file /d02/arch/1_111.dbf
archive log thread 1 sequence 112 is already on disk as file /d02/arch/1_112.dbf
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=109
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=110
channel ORA_DISK_1: restored backup pIEce 1
pIEce handle=/d02/fullbackup/20090316_arch_28 tag=TAG20090316T154731 params=NULL
channel ORA_DISK_1: restore complete
media recovery complete
Finished blockrecover at 16-MAR-09

再SELECT一下表DPA_HISTORY

SQL> select * from dpa_history;

PRODLINEID BARCODE                        PA
---------- ------------------------------ --
7          S*33040-D8311050149512B        03
7          S*33040-D8311050143512B        03
7          S*33040-D8311050140512B        03
7          S*33040-D8311050144512B        03
7          S*33040-D8311050151512B        03
7          S*33040-D8311050262512B        03
7          S*33040-D8311050552512B        03
7          S*33040-D8311050345512B        03
7          S*33040-D8311050170512B        03

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved