当前位置: 开发笔记 > 数据库 > 正文

Oracle数据恢复顾问（DRA）

作者：U友50140862 | 来源：互联网 | 2017-05-12 15:45

ChangeFailure

Change Failure - 使你可以改变故障的状态。下面通过两个例子来说明DRA工具的用法在测试之前使用RMAN对数据库进行全备场景一、模拟控制文件丢失关闭数据库，mv掉controlfile [ora_tst@test rman]$ mv /u01/oracle/TEST/db/apps_st/data/cntrl01.dbf /u01

Change Failure - 使你可以改变故障的状态。

下面通过两个例子来说明DRA工具的用法

在测试之前使用RMAN对数据库进行全备

场景一、模拟控制文件丢失

关闭数据库，mv掉controlfile

[ora_tst@test rman]$ mv /u01/oracle/TEST/db/apps_st/data/cntrl01.dbf /u01/oracle/TEST/db/apps_st/data/cntrl01.dbf.bak

启动数据库，报错

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2166536 bytes
Variable Size 427819256 bytes
Database Buffers 624951296 bytes
Redo Buffers 14000128 bytes
ORA-00205: error in identifying control file, check alert log for more info

告警日志中错误提示：

ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oracle/TEST/db/apps_st/data/cntrl01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

很容易可以发现是由于cntrl01.dbf丢失导致数据库无法mount

下面通过DRA来检测故障，并修复

[ora_tst@test ~]$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Mon Apr 21 13:35:09 2014

connected to target database: TEST (not mounted)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
782 CRITICAL OPEN 21-APR-14 Control file /u01/oracle/TEST/db/apps_st/data/cntrl01.dbf is missing

通过list failure命令可以发现故障所在，使用list failure ### detail; ( where ### equlas the failure number)可以查看故障的详细信息。

RMAN> list failure 782 detail;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
782 CRITICAL OPEN 21-APR-14 Control file /u01/oracle/TEST/db/apps_st/data/cntrl01.dbf is missing
Impact: Database cannot be mounted

下面可以通过advise failure;命令让Oracle告诉我们遇到这个故障，应该怎么做

RMAN> advise failure;

List of Database Failures
=========================

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=383 device type=DISK
RMAN-06495: must explicitly specify DBID with SET DBID command
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Use a multiplexed copy to restore control file /u01/oracle/TEST/db/apps_st/data/cntrl01.dbf
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/oracle/TEST/db/tech_st/11.1.0/admin/TEST_test/diag/rdbms/test/TEST/hm/reco_2401635629.hm

advise failure命令提示，我们可以通过拷贝冗余的controlfile来恢复出cntrl01.dbf，并且Oracle在/u01/oracle/TEST/db/tech_st/11.1.0/admin/TEST_test/diag/rdbms/test/TEST/hm/reco_2401635629.hm中给出具体的恢复脚本

恢复脚本，我们还可以通过repair failure preview命令来获得

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/oracle/TEST/db/tech_st/11.1.0/admin/TEST_test/diag/rdbms/test/TEST/hm/reco_2401635629.hm

contents of repair script:
# restore control file using multiplexed copy
restore controlfile from '/u01/oracle/TEST/db/apps_st/data/cntrl02.dbf';
sql 'alter database mount';

Oracle提示我们运行

restore controlfile from '/u01/oracle/TEST/db/apps_st/data/cntrl02.dbf';
sql 'alter database mount';

命令来恢复cntrl01.dbf

执行上述命令

RMAN> restore controlfile from '/u01/oracle/TEST/db/apps_st/data/cntrl02.dbf';

Starting restore at 21-APR-14
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=/u01/oracle/TEST/db/apps_st/data/cntrl01.dbf
output file name=/u01/oracle/TEST/db/apps_st/data/cntrl02.dbf
output file name=/u01/oracle/TEST/db/apps_st/data/cntrl03.dbf
Finished restore at 21-APR-14

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

这里我是手工执行的脚本，也可以通过

RMAN> repair failure;

来自动修复故障。

数据库起到了mount状态，说明故障修复成功。

RMAN> sql 'alter database open';

sql statement: alter database open

场景二、数据文件丢失

关闭数据库

[ora_tst@test trace]$ mv /u01/oracle/TEST/db/apps_st/data/a_txn_data02.dbf /u01/oracle/TEST/db/apps_st/data/a_txn_data02.dbf.bak

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2166536 bytes
Variable Size 427819256 bytes
Database Buffers 624951296 bytes
Redo Buffers 14000128 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 401 - see DBWR trace file
ORA-01110: data file 401: '/u01/oracle/TEST/db/apps_st/data/a_txn_data02.dbf'

启动数据库时报错，查看告警日志，如下：

ALTER DATABASE OPEN
Errors in file /u01/oracle/TEST/db/tech_st/11.1.0/admin/TEST_test/diag/rdbms/test/TEST/trace/TEST_dbw0_27581.trc:
ORA-01157: cannot identify/lock data file 401 - see DBWR trace file
ORA-01110: data file 401: '/u01/oracle/TEST/db/apps_st/data/a_txn_data02.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

下面通过DRA来查看故障，并解决

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
825 HIGH OPEN 21-APR-14 One or more non-system datafiles are missing

RMAN> list failure 825 detail;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
825 HIGH OPEN 21-APR-14 One or more non-system datafiles are missing
Impact: See impact for inpidual child failures
List of child failures for parent failure ID 825
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
828 HIGH OPEN 21-APR-14 Datafile 401: '/u01/oracle/TEST/db/apps_st/data/a_txn_data02.dbf' is missing
Impact: Some objects in tablespace APPS_TS_TX_DATA might be unavailable

通过命令很明显的可以发现故障所在。

RMAN> advise failure;

List of Database Failures
=========================

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=382 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/oracle/TEST/db/apps_st/data/a_txn_data02.dbf was unintentionally renamed or moved, restore it
2. If a standby database is available, then consider a Data Guard switchover or failover

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 401
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/oracle/TEST/db/tech_st/11.1.0/admin/TEST_test/diag/rdbms/test/TEST/hm/reco_556356707.hm

Oracle告诉我们/u01/oracle/TEST/db/apps_st/data/a_txn_data02.dbf文件renamed或者moved需要恢复，Restore and recover datafile 401。

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/oracle/TEST/db/tech_st/11.1.0/admin/TEST_test/diag/rdbms/test/TEST/hm/reco_556356707.hm

contents of repair script:
# restore and recover datafile
restore datafile 401;
recover datafile 401;

可通过

restore datafile 401;
recover datafile 401;

来恢复datafile 401。本次测试通过repair failure;命令来自动修改故障

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/oracle/TEST/db/tech_st/11.1.0/admin/TEST_test/diag/rdbms/test/TEST/hm/reco_556356707.hm

contents of repair script:
# restore and recover datafile
restore datafile 401;
recover datafile 401;

Do you really want to execute the above repair (enter YES or NO)? YES
" YES" is an invalid response - please re-enter.

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 21-APR-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00401 to /u01/oracle/TEST/db/apps_st/data/a_txn_data02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/TEST/db/tech_st/11.1.0/dbs/0ep69g7a_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/TEST/db/tech_st/11.1.0/dbs/0ep69g7a_1_1 tag=TAG20140421T110305
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:35
Finished restore at 21-APR-14

Starting recover at 21-APR-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file /u01/PROD/db/apps_st/data/archive/ARC1_22_825013351.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21
channel ORA_DISK_1: reading from backup piece /u01/oracle/TEST/db/tech_st/11.1.0/dbs/0fp69laa_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/TEST/db/tech_st/11.1.0/dbs/0fp69laa_1_1 tag=TAG20140421T123001
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/PROD/db/apps_st/data/archive/ARC1_21_825013351.arc thread=1 sequence=21
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-APR-14
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened

场景三、日志组丢失

关闭数据库

删除非当前日志组所有的日志文件

[ora_tst@test trace]$ rm -f /u01/oracle/TEST/db/apps_st/data/log01b.dbf

[ora_tst@test trace]$ rm -f /u01/oracle/TEST/db/apps_st/data/log01a.dbf

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2166536 bytes
Variable Size 427819256 bytes
Database Buffers 624951296 bytes
Redo Buffers 14000128 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oracle/TEST/db/apps_st/data/log01b.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/oracle/TEST/db/apps_st/data/log01a.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

启动数据库时报错

使用DRA修复上述故障

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
999 CRITICAL OPEN 21-APR-14 Redo log group 1 is unavailable
1005 HIGH OPEN 21-APR-14 Redo log file /u01/oracle/TEST/db/apps_st/data/log01a.dbf is missing
1002 HIGH OPEN 21-APR-14 Redo log file /u01/oracle/TEST/db/apps_st/data/log01b.dbf is missing

RMAN> advise failure;

List of Database Failures
=========================

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=381 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/oracle/TEST/db/apps_st/data/log01a.dbf was unintentionally renamed or moved, restore it
2. If file /u01/oracle/TEST/db/apps_st/data/log01b.dbf was unintentionally renamed or moved, restore it
3. If a standby database is available, then consider a Data Guard switchover or failover

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Perform incomplete database recovery to SCN 5965141836565
Strategy: The repair includes point-in-time recovery with some data loss
Repair script: /u01/oracle/TEST/db/tech_st/11.1.0/admin/TEST_test/diag/rdbms/test/TEST/hm/reco_3499717585.hm

RMAN> repair failure preview;

Strategy: The repair includes point-in-time recovery with some data loss
Repair script: /u01/oracle/TEST/db/tech_st/11.1.0/admin/TEST_test/diag/rdbms/test/TEST/hm/reco_3499717585.hm

contents of repair script:
# database point-in-time recovery
restore database until scn 5965141836565;
recover database until scn 5965141836565;
alter database open resetlogs;

RMAN> repair failure;

Strategy: The repair includes point-in-time recovery with some data loss
Repair script: /u01/oracle/TEST/db/tech_st/11.1.0/admin/TEST_test/diag/rdbms/test/TEST/hm/reco_3499717585.hm

contents of repair script:
# database point-in-time recovery
restore database until scn 5965141836565;
recover database until scn 5965141836565;
alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 21-APR-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/TEST/db/apps_st/data/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/TEST/db/apps_st/data/system02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/TEST/db/apps_st/data/system03.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/TEST/db/apps_st/data/system04.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oracle/TEST/db/apps_st/data/system05.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/TEST/db/apps_st/data/ctxd01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/oracle/TEST/db/apps_st/data/owad01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/oracle/TEST/db/apps_st/data/a_queue02.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/oracle/TEST/db/apps_st/data/odm.dbf

................................

channel ORA_DISK_1: restoring datafile 00407 to /u01/oracle/TEST/db/apps_st/data/a_ref02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/TEST/db/tech_st/11.1.0/dbs/0ep69g7a_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/TEST/db/tech_st/11.1.0/dbs/0ep69g7a_1_1 tag=TAG20140421T110305
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 01:16:04
Finished restore at 21-APR-14

Starting recover at 21-APR-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 21 is already on disk as file /u01/PROD/db/apps_st/data/archive/ARC1_21_825013351.arc
archived log for thread 1 with sequence 22 is already on disk as file /u01/PROD/db/apps_st/data/archive/ARC1_22_825013351.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/PROD/db/apps_st/data/archive/ARC1_23_825013351.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/PROD/db/apps_st/data/archive/ARC1_24_825013351.arc
archived log file name=/u01/PROD/db/apps_st/data/archive/ARC1_21_825013351.arc thread=1 sequence=21
archived log file name=/u01/PROD/db/apps_st/data/archive/ARC1_22_825013351.arc thread=1 sequence=22
archived log file name=/u01/PROD/db/apps_st/data/archive/ARC1_23_825013351.arc thread=1 sequence=23
media recovery complete, elapsed time: 00:00:19
Finished recover at 21-APR-14

database opened
repair failure complete

推荐阅读

sql
mysql 分库分表策略_【数据库】分库分表策略

关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多， ... [详细]

蜡笔小新 2024-12-03 13:09:47
sql
探索在.NET环境中恢复PB12.5 DataWindow功能的方法

本文探讨了为何DataWindow.NET在PowerBuilder 11之后版本中不再被支持，并提供了一种在PB12.5中恢复此功能的解决方案。欢迎交流与反馈。 ... [详细]

蜡笔小新 2024-12-02 20:07:41
sql
ThinkPHP 文件缓存组件详解与应用

本文深入探讨了ThinkPHP框架中的文件缓存类实现，提供了详细的代码示例和使用说明，旨在帮助开发者更好地理解和利用这一功能来优化应用程序性能。 ... [详细]

蜡笔小新 2024-12-02 17:26:54
sql
SonarQube配置与使用指南

本文档详细介绍了SonarQube的配置方法及使用流程，包括环境准备、样本分析、数据库配置、项目属性文件解析以及插件安装等内容，适用于具有Linux基础操作能力的用户。 ... [详细]

蜡笔小新 2024-12-02 17:00:28
sql
深入解析ThinkPHP中的缓存管理方法：cache

本文详细介绍ThinkPHP框架中的cache方法，涵盖其功能、参数配置及使用场景，特别指出从3.1.2版本起，cache方法已被S方法取代。 ... [详细]

蜡笔小新 2024-12-04 16:49:54
sql
linux图形图像三剑客,Linux必会的三剑客命令

grep、sed、awk一、grep的用法1、grep简介grep是一种强大的文本搜索工具，它能使用正则表达式搜索文本，并把匹配的行打印出来。grep包 ... [详细]

蜡笔小新 2024-12-03 13:02:44
sql
深入解析 OpenWrt 中的 Luci API 文档及 szloogson 模块构建

OpenWrt 是一款高度可定制的嵌入式 Linux 发行版，广泛应用于无线路由器等领域，拥有超过百个预装软件包。本文详细探讨如何在 OpenWrt 上通过 Luci 构建自定义模块，以扩展其功能。 ... [详细]

蜡笔小新 2024-12-03 12:06:08
数据库
Python语言的核心特点及其应用领域

Python作为一种广泛使用的高级编程语言，以其简洁的语法、强大的功能和丰富的库支持著称。本文将详细介绍Python的主要特点及其在现代软件开发中的应用。 ... [详细]

蜡笔小新 2024-12-03 12:00:39
数据库
linux下ipvsadm命令,Linux命令：ipvsadm

ipvsadm命令简介：ipvsadm是LVS在应用层的管理命令，我们可以通过这个命令去管理LVS的配置。在fedora14、Linux6.0之后系统中 ... [详细]

蜡笔小新 2024-12-03 11:30:18
数据库
深入解析C/S与B/S架构的差异及其发展趋势

本文详细探讨了当前主流的两种系统架构——C/S（客户端/服务器）与B/S（浏览器/服务器）架构的特点、优缺点及未来的发展趋势。 ... [详细]

蜡笔小新 2024-12-03 11:04:55
数据库
Kodi与XBMC：探索开源媒体中心的演变

Kodi（原名XBMC）是一款由XBMC基金会开发的免费开源媒体播放器应用，支持多种操作系统和硬件平台。该软件以其强大的多媒体处理能力和用户友好的界面受到广泛欢迎。 ... [详细]

蜡笔小新 2024-12-03 10:04:55
数据库
Zsh 开发指南（第三篇字符串处理之转义字符和格式化输出）

导读上一篇讲了zsh的常用字符串操作，这篇开始讲更为琐碎的转义字符和格式化输出相关内容。包括转义字符、引号、print、printf的使用等等。其中很多内容没有必要记忆，作为手册参 ... [详细]

蜡笔小新 2024-12-03 03:30:40
数据库
探索PWA H5 Web App优化之路（Service Worker与Lighthouse的应用）

本文探讨了如何通过Service Worker和Lighthouse工具来优化PWA H5 Web App，旨在提升用户体验，包括提高加载速度、增强离线访问能力等方面。 ... [详细]

蜡笔小新 2024-12-03 00:01:42
数据库
Linux环境下MySQL主从复制机制详解与实践

本文详细介绍了MySQL在Linux环境下的主从复制技术，包括单向复制、双向复制、级联复制及异步复制等多种模式。主从复制架构中，一个主服务器（Master）可与一个或多个从服务器（Slave）建立连接，实现数据的实时同步。 ... [详细]

蜡笔小新 2024-12-02 23:08:49
数据库
辅助类与单元测试的重要性

本章节深入探讨了多种实用的辅助类，这些类将在未来的项目中发挥关键作用。此外，单元测试被强调为游戏开发初期的重要步骤。文章最后通过Breakout游戏的实例，展示了如何有效地利用前文所述的技术。 ... [详细]

蜡笔小新 2024-12-02 17:07:42

U友50140862

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章