共计 7100 个字符，预计需要花费 18 分钟才能阅读完成。
本篇内容介绍了“Oracle 数据库执行过程的问题怎么修复”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
运行过程中故障
在运行过程中的 oracle 故障，坏块和文件异常删除出现的比较多，特别是初级 DBA 刚刚上手的时候。我们先来模拟一下这个场景。
Undo 表空间是 Oracle 核心表空间之一，删除之后会引起比较严重的问题故障。
SQL select file_name from dba_data_files where tablespace_name= UNDOTBS1
FILE_NAME
——————————————————————————–
/u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf
当前数据库处在 Open 运行状态，突然 Undo 文件被后 OS 层面删除。
[oracle@bspdev datafile]$ ls -l | grep undo
-rw-r—– 1 oracle oinstall 346038272 Sep 6 07:21 o1_mf_undotbs1_7xt3yzl5_.dbf
[oracle@bspdev datafile]$ mv o1_mf_undotbs1_7xt3yzl5_.dbf o1_mf_undotbs1_7xt3yzl5_.dbf.bak
[oracle@bspdev datafile]$ ls -l | grep undo
-rw-r—– 1 oracle oinstall 346038272 Sep 6 07:21 o1_mf_undotbs1_7xt3yzl5_.dbf.bak
此时，alert log 中可以出现上篇中那个“checker”的工作过程。
Fri Sep 06 07:25:47 2013
Checker run found 1 new persistent data failures
Fri Sep 06 07:26:34 2013
Starting background process SMCO
Fri Sep 06 07:26:34 2013
SMCO started with pid=19, OS id=4819
Fri Sep 06 07:26:46 2013
Errors in file /u01/diag/rdbms/wilson/wilson/trace/wilson_mmnl_4418.trc:
ORA-01116: error in opening database file 3
ORA-01110: data file 3: /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Fri Sep 06 07:26:48 2013
Errors in file /u01/diag/rdbms/wilson/wilson/trace/wilson_m000_4835.trc:
ORA-01116: error in opening database file 3
ORA-01110: data file 3: /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
差不多两秒钟报一个错误，发现文件被删除无法打开。
此时，我们在 rman 上使用 list failure 命令，查看生成的错误信息。
RMAN list failure all;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
242 HIGH OPEN 06-SEP-13 One or more non-system datafiles are missing
我们使用 advisor failure，查看一个 Oracle 的建议。
RMAN advise failure ;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
242 HIGH OPEN 06-SEP-13 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
1. If file /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf was unintentionally renamed or moved, restore it
2. Automatic repairs may be available if you shutdown the database and restart it in mount mode
3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
no automatic repair options available
注意，在 automated repair options 中，我们没有看到脚本信息。说明 Oracle 好像在目前也没有太好的方法。在 Manual Actions 中，Oracle DRA 要求将数据库重启到 mount 状态，才能有自动脚本的出现。Manual Actions 是那些 Oracle 觉得需要用户手工执行才能继续下去的步骤。
重新启动一下库，加载到 mount 状态。
– 强制关闭
RMAN shutdown abort;
Oracle instance shut down
RMAN startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 616566352 bytes
Database Buffers 226492416 bytes
Redo Buffers 5132288 bytes
此时再次使用 DRA 工具，看问题和提示内容。
RMAN advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
242 HIGH OPEN 06-SEP-13 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
—— ——————
1 Restore and recover datafile 3
Strategy: The repair includes complete media recovery with no data loss
Repair script. /u01/diag/rdbms/wilson/wilson/hm/reco_1850469943.hm
使用 repair failure review 命令来查看执行语句。
RMAN repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script. /u01/diag/rdbms/wilson/wilson/hm/reco_1850469943.hm
contents of repair script.:
# restore and recover datafile
restore datafile 3;
recover datafile 3;
注意：此时 Oracle DRA 发现了当前我们有 Undo 的备份和归档日志。所以使用 restore 之后伴随 recover，可以快速实现恢复。
如果在 preview 中没有发现什么问题，可以 repair failure 命令执行进行恢复。
RMAN repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script. /u01/diag/rdbms/wilson/wilson/hm/reco_1850469943.hm
contents of repair script.:
# restore and recover datafile
restore datafile 3;
recover datafile 3;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 06-SEP-13
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 00003 to /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf
channel ORA_DISK_1: reading from backup piece /u01/flash_recovery_area/WILSON/backupset/2013_09_06/o1_mf_nnndf_TAG20130906T061608_92l0od6w_.bkp
channel ORA_DISK_1: piece handle=/u01/flash_recovery_area/WILSON/backupset/2013_09_06/o1_mf_nnndf_TAG20130906T061608_92l0od6w_.bkp tag=TAG20130906T061608
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 06-SEP-13
Starting recover at 06-SEP-13
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 06-SEP-13
repair failure complete
– 可以选择打开数据库
Do you want to open the database (enter YES or NO)? yes
database opened
我们在 alert log 中，可以监控到恢复的步骤。
–Restore 过程
Fri Sep 06 07:35:49 2013
Full restore complete of datafile 3 /u01/oradata/WILSON/datafile/o1_mf_undotbs1_92l5b0v4_.dbf. Elapsed time: 0:00:15
checkpoint is 3838694
last deallocation scn is 3817636
Undo Optimization current scn is 3815429
Fri Sep 06 07:35:54 2013
alter database recover datafile list clear
Completed: alter database recover datafile list clear
–recovery 过程
alter database recover if needed
datafile 3
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 176 Reading mem 0
Mem# 0: /u01/oradata/WILSON/onlinelog/o1_mf_2_870n48hc_.log
Mem# 1: /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_870n4dtl_.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 177 Reading mem 0
Mem# 0: /u01/oradata/WILSON/onlinelog/o1_mf_3_870n4lsg_.log
Mem# 1: /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_870n4o31_.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 178 Reading mem 0
Mem# 0: /u01/oradata/WILSON/onlinelog/o1_mf_1_870n42n1_.log
Mem# 1: /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870n44z3_.log
Media Recovery Complete (wilson)
Completed: alter database recover if needed
datafile 3
Fri Sep 06 07:36:04 2013
alter database open
此时，数据库错误消除。
RMAN list failure;
no failures found that match specification
最后，我们还有一个命令可以使用，就是 change failure。Change Failure 命令的作用就是显示的将错误的状态修改掉。最常用的做法是：当一个错误发生的时候，如果我们没有在 RMAN 层面上去解决，比如使用冷备份方法还原。Failure 信息是不会变化状态的。此时，可以使用 change failure 命令将状态设置为 Closed，命令如：change failure all closed。
“Oracle 数据库执行过程的问题怎么修复”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
