日期：2011-01-25 22:55:00 来源：本站整理

实例讲授Oracle 9i数据坏块的处理-性能调优[Oracle防范]

赞助商链接

分享到： QQ空间新浪微博腾讯微博人人网

　　本文“实例讲授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

　　以上是“实例讲授Oracle 9i数据坏块的处理-性能调优[Oracle防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：

实例讲授操纵JDOM对XML文件举行操作

实例讲授Java中的接口的作用

实例讲授Servlet的图象处理

<b>实例讲授Tomcat下绑定JMS操纵服务器</b>

实例讲授Oracle里抽取随机数的多种办法

<b>Oracle数据库链接成立本领与实例讲授-开辟技术</b>

实例讲授Oracle 9i数据坏块的处理-性能调优

操纵实例讲授MySQL数据库做到查询最优化

Linux at号令编辑和配置实例讲授

本文地址：

与您的QQ/BBS好友分享!