Oracle9i数据坏块处理,数据库教程：Oracle 9i数据坏块处理实例说明

数据坏块处理,数据库教程：Oracle 9i数据坏块处理实例说明"/>

Oracle9i数据坏块处理,数据库教程：Oracle 9i数据坏块处理实例说明

小编今天的Oracle数据库学习教程要讲的是用实例来讲解Oracle 9i数据坏块的处理方法。大家赶紧来学习一下吧！

Oracle9i 实际上是指 Oracle9iDatabase, Oracle 9i Application Server 和Oracle9i Developer Suite的完整集成。随着软件逐渐开始转变为一种托管服务( hosted services)，具有internet上的高伸缩性能的、智能化的、和可靠的Oracle9i 将成为高质量的电子商务服务实现的关键软件。

一台生产用测试库上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

以上便是全部内容。更多Oracle学习视频，尽在课课家官方网。