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

Oracle数据恢复顾问（DRA）

作者：Annfeliz | 来源：互联网 | 2018-06-01 10: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
Hadoop入门与核心组件详解

本文详细介绍了Hadoop的基础知识及其核心组件，包括HDFS、MapReduce和YARN。通过本文，读者可以全面了解Hadoop的生态系统及应用场景。 ... [详细]

蜡笔小新 2024-12-26 13:12:48
json
Python配置文件读写指南

本文详细介绍如何使用Python进行配置文件的读写操作，涵盖常见的配置文件格式（如INI、JSON、TOML和YAML），并提供具体的代码示例。 ... [详细]

蜡笔小新 2024-12-28 08:39:55
sql
新浪笔试题

1:有如下一段程序：packagea.b.c;publicclassTest{privatestaticinti0;publicintgetNext(){return ... [详细]

蜡笔小新 2024-12-27 19:32:17
sql
构建基于BERT的中文NL2SQL模型：一个简明的基准

本文探讨了将自然语言转换为SQL语句（NL2SQL）的任务，这是人工智能领域中一项非常实用的研究方向。文章介绍了笔者在公司举办的首届中文NL2SQL挑战赛中的实践，该比赛提供了金融和通用领域的表格数据，并标注了对应的自然语言与SQL语句对，旨在训练准确的NL2SQL模型。 ... [详细]

蜡笔小新 2024-12-27 17:36:19
sql
在Ubuntu 16.04 LTS上配置Qt Creator开发环境

本文详细介绍了如何在Ubuntu 16.04 LTS系统中安装和配置Qt Creator，涵盖了从下载到安装的全过程，并提供了常见问题的解决方案。 ... [详细]

蜡笔小新 2024-12-27 13:19:53
sql
在 Linux 系统中部署 PostgreSQL 数据库

本文详细介绍了如何在 Linux 平台上安装和配置 PostgreSQL 数据库。通过访问官方资源并遵循特定的操作步骤，用户可以在不同发行版（如 Ubuntu 和 Red Hat）上顺利完成 PostgreSQL 的安装。 ... [详细]

蜡笔小新 2024-12-27 03:46:27
数据库
解决Hive启动时权限被拒问题

本文详细分析了Hive在启动过程中遇到的权限拒绝错误，并提供了多种解决方案，包括调整文件权限、用户组设置以及环境变量配置等。 ... [详细]

蜡笔小新 2024-12-26 19:14:29
redis
MySQL缓存机制深度解析

本文详细探讨了MySQL的缓存机制，包括主从复制、读写分离以及缓存同步策略等内容。通过理解这些概念和技术，读者可以更好地优化数据库性能。 ... [详细]

蜡笔小新 2024-12-26 15:15:06
sql
MySQL 数据库迁移指南：从本地到远程及磁盘间迁移

本文详细介绍了如何在不同场景下进行 MySQL 数据库的迁移，包括从一个硬盘迁移到另一个硬盘、从一台计算机迁移到另一台计算机，以及解决迁移过程中可能遇到的问题。 ... [详细]

蜡笔小新 2024-12-26 13:21:38
insert
深入理解 SQL 视图、存储过程与事务

本文详细介绍了SQL中的视图、存储过程和事务的概念及应用。视图为用户提供了一种灵活的数据查询方式，存储过程则封装了复杂的SQL逻辑，而事务确保了数据库操作的完整性和一致性。 ... [详细]

蜡笔小新 2024-12-27 17:40:42
insert
告别传统文件传输，迎接新一代高效工具Croc

在现代网络环境中，两台计算机之间的文件传输需求日益增长。传统的FTP和SSH方式虽然有效，但其配置复杂、步骤繁琐，难以满足快速且安全的传输需求。本文将介绍一种基于Go语言开发的新一代文件传输工具——Croc，它不仅简化了操作流程，还提供了强大的加密和跨平台支持。 ... [详细]

蜡笔小新 2024-12-26 16:16:06
insert
解决微信电脑版无法刷朋友圈问题：使用安卓远程投屏方案

在工作期间想要浏览微信和朋友圈却不太方便？虽然微信电脑版目前不支持直接刷朋友圈，但通过远程投屏技术，可以轻松实现在电脑上操作安卓设备的功能。 ... [详细]

蜡笔小新 2024-12-26 15:23:19
insert
2011年12月13日：人生的转折点

在即将迎来26岁生日之际，作者的人生陷入了低谷。经过近三年的硕士学习后，最终决定退学，并且面临没有工作经验的困境。尽管如此，作者依然坚定地选择为自己的人生负责。 ... [详细]

蜡笔小新 2024-12-26 13:51:06
数据库
使用Python在SAE上开发新浪微博应用的初步探索

最近重新审视了新浪云平台（SAE）提供的服务，发现其已支持Python开发。本文将详细介绍如何利用Django框架构建一个简单的新浪微博应用，并分享开发过程中的关键步骤。 ... [详细]

蜡笔小新 2024-12-26 13:36:52
数据库
美国主要财团概览

本文详细介绍了美国最具影响力的十大财团，包括洛克菲勒、摩根、花旗银行等。这些财团在历史发展过程中逐渐形成，并对美国的经济、政治和社会产生深远影响。 ... [详细]

蜡笔小新 2024-12-26 13:32:29

Annfeliz

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

Tags | 热门标签

RankList | 热门文章