利用 Data Recovery Advisor诊断并修复失败ORA-01110
关于Data Recovery Advisor官方文档说明:
Diagnosing and Repairing Failures with Data Recovery Advisor
The simplest way to diagnose and repair database problems is to use the Data Recovery Advisor. This Oracle Database tool provides an infrastructure for diagnosing persistent data failures, presenting repair options to the user, and automatically executing repairs.
See Also:
"Overview of Data Recovery Advisor"
Listing Failures and Determining Repair Options
A failure is a persistent data corruption detected by the Health Monitor. Examples include physical and logical data block corruptions and missing datafiles. Each failure has a failure priority and failure status. The priority can be CRITICAL, HIGH, or LOW. The status can be OPEN or CLOSED.
You can run the LIST FAILURE command to show all known failures. If failures exist, then run the ADVISE FAILURE command in the same session to determine manual and automated repair options. The following example illustrates these two commands (sample output included).
Example 2-1 LIST FAILURE and ADVISE FAILURE
RMAN> LIST FAILURE;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
142 HIGH OPEN 23-APR-07 One or more non-system datafiles are missing
101 HIGH OPEN 23-APR-07 Datafile 1: '/disk1/oradata/prod/system01.dbf'
contains one or more corrupt blocks
RMAN> ADVISE FAILURE;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
142 HIGH OPEN 23-APR-07 One or more non-system datafiles are missing
101 HIGH OPEN 23-APR-07 Datafile 1: '/disk1/oradata/prod/system01.dbf'
contains one or more corrupt blocks
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /disk1/oradata/prod/users01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 28; Perform block media recovery of
block 56416 in file 1
Strategy: The repair includes complete media recovery with no data loss
Repair script: /disk1/oracle/log/diag/rdbms/prod/prod/hm/reco_660500184.hm
The ADVISE FAILURE output shows both manual and automated repair options. First try to fix the problem manually. If you cannot fix the problem manually, then review the automated repair section.
An automated repair option describes a server-managed repair for one or more failures. Repairs are consolidated when possible so that a single repair can fix multiple failures. The repair option indicates which repair is performed and whether data is lost by performing the repair operation.
In Example 2-1, the output indicates the filename of a repair script containing RMAN commands. If you do not want to use Data Recovery Advisor to repair the failure automatically, then you can use the script as the basis of your own recovery strategy.
See Also:
"Listing Failures" and "Determining Repair Options"
Repairing Failures
After running LIST FAILURE and ADVISE FAILURE in an RMAN session, you can run REPAIR FAILURE to execute a repair option. If you execute REPAIR FAILURE with no other command options, then RMAN uses the first repair option of the most recent ADVISE FAILURE command in the current session. Alternatively, specify the repair option number obtained from the most recent ADVISE FAILURE command. Example 2-2 illustrates how to repair the failures identified in Example 2-1.
Example 2-2 REPAIR FAILURE
RMAN> REPAIR FAILURE;
By default, REPAIR FAILURE prompts for confirmation before it begins executing. After executing a repair, Data Recovery Advisor reevaluates all existing failures on the possibility that they may also have been fixed. Data Recovery Advisor always verifies that failures are still relevant and automatically closes fixed failures. If a repair fails to complete because of an error, then the error triggers a new assessment and re-evaluation of existing failures and repairs.
事例:在此操作之前必须做备份
ASMCMD> rm -f KING.265.882673099
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/netdata/DATAFILE/KING.265.882673099' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute
数据库实例未关闭
关闭数据库实例删除数据库文件
ASMCMD> ls
EXAMPLE.259.882673075
KING.265.882673099
SYSAUX.256.882673003
SYSTEM.268.882673003
UNDOTBS1.266.882673059
USERS.257.882673103
ASMCMD> rm -f KING.265.882673099
ASMCMD>
RMAN> shutdown immediate;
数据库已关闭
数据库已卸装
Oracle 实例已关闭
RMAN> startup;
已连接到目标数据库 (未启动)
Oracle 实例已启动
数据库已装载
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: startup 命令 (在 06/21/2015 01:03:49 上) 失败
ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 6: '+DATA/netdata/datafile/king.265.882673099'
RMAN> LIST FAILURE;
数据库故障列表
=========================
失败 ID 优先级状态 检测时间 概要
------- -------- --------- ---------- -------
742 HIGH OPEN 21-6月 -15 缺失一个或多个非系统数据文件
RMAN> ADVISE FAILURE;
数据库故障列表
=========================
失败 ID 优先级状态 检测时间 概要
------- -------- --------- ---------- -------
742 HIGH OPEN 21-6月 -15 缺失一个或多个非系统数据文件
正在分析自动修复选项; 这可能需要一些时间
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=11 设备类型=DISK
分析自动修复选项完成
必需的手动操作
========================
没有可用的手动操作
可选手动操作
=======================
1. 如果无意中重命名或移动了文件 +DATA/netdata/datafile/king.265.882673099, 请还原该文件
自动修复选项
========================
选项修复说明
------ ------------------
1 还原和恢复数据文件 6
策略: 修复操作包括无数据丢失的完全介质恢复
修复脚本: /u01/app/oracle/diag/rdbms/netdata/netdata/hm/reco_679749301.hm
这里我们可以查看修复脚本：
[oracle@OLEASM1 ~]$ cat /u01/app/oracle/diag/rdbms/netdata/netdata/hm/reco_679749301.hm
# restore and recover datafile
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';
[oracle@OLEASM1 ~]$
RMAN> REPAIR FAILURE;
策略: 修复操作包括无数据丢失的完全介质恢复
修复脚本: /u01/app/oracle/diag/rdbms/netdata/netdata/hm/reco_679749301.hm
修复脚本的内容:
# restore and recover datafile
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';
是否确实要执行以上修复 (输入 YES 或 NO)? yes
执行修复脚本
启动 restore 于 21-6月 -15
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00006 还原到 +DATA/netdata/datafile/king.265.882673099
通道 ORA_DISK_1: 正在读取备份片段 +FRA/netdata/backupset/2015_06_21/nnndf0_tag20150621t005148_0.271.882924709
通道 ORA_DISK_1: 段句柄 = +FRA/netdata/backupset/2015_06_21/nnndf0_tag20150621t005148_0.271.882924709 标记 = TAG20150621T005148
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:03
完成 restore 于 21-6月 -15
启动 recover 于 21-6月 -15
使用通道 ORA_DISK_1
正在开始介质的恢复
介质恢复完成, 用时: 00:00:01
完成 recover 于 21-6月 -15
sql 语句: alter database datafile 6 online
修复故障已完成
是否要打开数据库 (输入 YES 或 NO)? yes
数据库已打开
RMAN>
Diagnosing and Repairing Failures with Data Recovery Advisor
The simplest way to diagnose and repair database problems is to use the Data Recovery Advisor. This Oracle Database tool provides an infrastructure for diagnosing persistent data failures, presenting repair options to the user, and automatically executing repairs.
See Also:
"Overview of Data Recovery Advisor"
Listing Failures and Determining Repair Options
A failure is a persistent data corruption detected by the Health Monitor. Examples include physical and logical data block corruptions and missing datafiles. Each failure has a failure priority and failure status. The priority can be CRITICAL, HIGH, or LOW. The status can be OPEN or CLOSED.
You can run the LIST FAILURE command to show all known failures. If failures exist, then run the ADVISE FAILURE command in the same session to determine manual and automated repair options. The following example illustrates these two commands (sample output included).
Example 2-1 LIST FAILURE and ADVISE FAILURE
RMAN> LIST FAILURE;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
142 HIGH OPEN 23-APR-07 One or more non-system datafiles are missing
101 HIGH OPEN 23-APR-07 Datafile 1: '/disk1/oradata/prod/system01.dbf'
contains one or more corrupt blocks
RMAN> ADVISE FAILURE;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
142 HIGH OPEN 23-APR-07 One or more non-system datafiles are missing
101 HIGH OPEN 23-APR-07 Datafile 1: '/disk1/oradata/prod/system01.dbf'
contains one or more corrupt blocks
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /disk1/oradata/prod/users01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 28; Perform block media recovery of
block 56416 in file 1
Strategy: The repair includes complete media recovery with no data loss
Repair script: /disk1/oracle/log/diag/rdbms/prod/prod/hm/reco_660500184.hm
The ADVISE FAILURE output shows both manual and automated repair options. First try to fix the problem manually. If you cannot fix the problem manually, then review the automated repair section.
An automated repair option describes a server-managed repair for one or more failures. Repairs are consolidated when possible so that a single repair can fix multiple failures. The repair option indicates which repair is performed and whether data is lost by performing the repair operation.
In Example 2-1, the output indicates the filename of a repair script containing RMAN commands. If you do not want to use Data Recovery Advisor to repair the failure automatically, then you can use the script as the basis of your own recovery strategy.
See Also:
"Listing Failures" and "Determining Repair Options"
Repairing Failures
After running LIST FAILURE and ADVISE FAILURE in an RMAN session, you can run REPAIR FAILURE to execute a repair option. If you execute REPAIR FAILURE with no other command options, then RMAN uses the first repair option of the most recent ADVISE FAILURE command in the current session. Alternatively, specify the repair option number obtained from the most recent ADVISE FAILURE command. Example 2-2 illustrates how to repair the failures identified in Example 2-1.
Example 2-2 REPAIR FAILURE
RMAN> REPAIR FAILURE;
By default, REPAIR FAILURE prompts for confirmation before it begins executing. After executing a repair, Data Recovery Advisor reevaluates all existing failures on the possibility that they may also have been fixed. Data Recovery Advisor always verifies that failures are still relevant and automatically closes fixed failures. If a repair fails to complete because of an error, then the error triggers a new assessment and re-evaluation of existing failures and repairs.
事例:在此操作之前必须做备份
ASMCMD> rm -f KING.265.882673099
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/netdata/DATAFILE/KING.265.882673099' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute
数据库实例未关闭
关闭数据库实例删除数据库文件
ASMCMD> ls
EXAMPLE.259.882673075
KING.265.882673099
SYSAUX.256.882673003
SYSTEM.268.882673003
UNDOTBS1.266.882673059
USERS.257.882673103
ASMCMD> rm -f KING.265.882673099
ASMCMD>
RMAN> shutdown immediate;
数据库已关闭
数据库已卸装
Oracle 实例已关闭
RMAN> startup;
已连接到目标数据库 (未启动)
Oracle 实例已启动
数据库已装载
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: startup 命令 (在 06/21/2015 01:03:49 上) 失败
ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 6: '+DATA/netdata/datafile/king.265.882673099'
RMAN> LIST FAILURE;
数据库故障列表
=========================
失败 ID 优先级状态 检测时间 概要
------- -------- --------- ---------- -------
742 HIGH OPEN 21-6月 -15 缺失一个或多个非系统数据文件
RMAN> ADVISE FAILURE;
数据库故障列表
=========================
失败 ID 优先级状态 检测时间 概要
------- -------- --------- ---------- -------
742 HIGH OPEN 21-6月 -15 缺失一个或多个非系统数据文件
正在分析自动修复选项; 这可能需要一些时间
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=11 设备类型=DISK
分析自动修复选项完成
必需的手动操作
========================
没有可用的手动操作
可选手动操作
=======================
1. 如果无意中重命名或移动了文件 +DATA/netdata/datafile/king.265.882673099, 请还原该文件
自动修复选项
========================
选项修复说明
------ ------------------
1 还原和恢复数据文件 6
策略: 修复操作包括无数据丢失的完全介质恢复
修复脚本: /u01/app/oracle/diag/rdbms/netdata/netdata/hm/reco_679749301.hm
这里我们可以查看修复脚本：
[oracle@OLEASM1 ~]$ cat /u01/app/oracle/diag/rdbms/netdata/netdata/hm/reco_679749301.hm
# restore and recover datafile
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';
[oracle@OLEASM1 ~]$
RMAN> REPAIR FAILURE;
策略: 修复操作包括无数据丢失的完全介质恢复
修复脚本: /u01/app/oracle/diag/rdbms/netdata/netdata/hm/reco_679749301.hm
修复脚本的内容:
# restore and recover datafile
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';
是否确实要执行以上修复 (输入 YES 或 NO)? yes
执行修复脚本
启动 restore 于 21-6月 -15
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00006 还原到 +DATA/netdata/datafile/king.265.882673099
通道 ORA_DISK_1: 正在读取备份片段 +FRA/netdata/backupset/2015_06_21/nnndf0_tag20150621t005148_0.271.882924709
通道 ORA_DISK_1: 段句柄 = +FRA/netdata/backupset/2015_06_21/nnndf0_tag20150621t005148_0.271.882924709 标记 = TAG20150621T005148
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:03
完成 restore 于 21-6月 -15
启动 recover 于 21-6月 -15
使用通道 ORA_DISK_1
正在开始介质的恢复
介质恢复完成, 用时: 00:00:01
完成 recover 于 21-6月 -15
sql 语句: alter database datafile 6 online
修复故障已完成
是否要打开数据库 (输入 YES 或 NO)? yes
数据库已打开
RMAN>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24486203/viewspace-1708584/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
