?Oracle数据库执行过程的问题怎么修复

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层面删除。此时，我们在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-syste开发云主机域名m datafiles are missing我们使用advisor failure，查看一个Oracle的建议。说明Oracle好像在目前也没有太好的方法。在Manual Actions中，Oracle DRA要求将数据库重启到mount状态，才能有自动脚

本篇内容介绍了“Oracle数据库执行过程的问题怎么修复”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！


运行过程中故障

在运行过程中的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 2013Checker run found 1 new persistent data failuresFri Sep 06 07:26:34 2013Starting background process SMCOFri Sep 06 07:26:34 2013SMCO started with pid=19, OS id=4819 Fri Sep 06 07:26:46 2013Errors in file /u01/diag/rdbms/wilson/wilson/trace/wilson_mmnl_4418.trc:ORA-01116: error in opening database file 3ORA-01110: data file 3: ‘/u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf’ORA-27041: unable to open fileLinux Error: 2: No such file or directoryAdditional information: 3Fri Sep 06 07:26:48 2013Errors in file /u01/diag/rdbms/wilson/wilson/trace/wilson_m000_4835.trc:ORA-01116: error in opening database file 3ORA-01110: data file 3: ‘/u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf’ORA-27041: unable to open fileLinux Error: 2: No such file or directoryAdditional 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-syste开发云主机域名m 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 missinganalyzing automatic repair options; this may take some timeallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=30 device type=DISKanalyzing automatic repair options completeMandatory Manual Actions========================1. If file /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf was unintentionally renamed or moved, restore it2. Automatic repairs may be available if you shutdown the database and restart it in mount mode3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repairOptional Manual Actions=======================no manual actions availableAutomated 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 downRMAN> startup mount;connected to target database (not started)Oracle instance starteddatabase mountedTotal System Global Area 849530880 bytesFixed Size 1339824 bytesVariable Size 616566352 bytesDatabase Buffers 226492416 bytesRedo 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 missinganalyzing automatic repair options; this may take some timeallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=18 device type=DISKanalyzing automatic repair options 开发云主机域名completeMandatory Manual Actions========================no manual actions availableOptional Manual Actions=======================1. If file /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf was unintentionally renamed or moved, restore itAutomated 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 lossRepair script. /u01/diag/rdbms/wilson/wilson/hm/reco_1850469943.hmcontents 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 lossRepair script. /u01/diag/rdbms/wilson/wilson/hm/reco_1850469943.hmcontents 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)? yesexecuting repair scriptStarting restore at 06-SEP-13using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbfchannel ORA_DISK_1: reading from backup piece /u01/flash_recovery_area/WILSON/backupset/2013_09_06/o1_mf_nnndf_TAG20130906T061608_92l0od6w_.bkpchannel ORA_DISK_开发云主机域名1: piece handle=/u01/flash_recovery_area/WILSON/backupset/2013_09_06/o1_mf_nnndf_TAG20130906T061608_92l0od6w_.bkp tag=TAG20130906T061608channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:25Finished restore at 06-SEP-13Starting recover at 06-SEP-13using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:02Finished recover at 06-SEP-13repair failure complete—可以选择打开数据库Do you want to open the database (enter YES or NO)? yesdatabase opened我们在alert log中，可以监控到恢复的步骤。–Restore过程Fri Sep 06 07:35:49 2013Full 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 3815429Fri Sep 06 07:35:54 2013alter database recover datafile list clearCompleted: alter database recover datafile list clear–recovery过程alter database recover if neededdatafile 3Media Recovery StartSerial Media Recovery startedRecovery 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_.logRecovery 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_.logRecovery 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_.logMedia Recovery Complete (wilson)Completed: alter database recover if neededdatafile 3Fri Sep 06 07:36:04 2013alter 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数据库执行过程的问题怎么修复”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注开发云网站，小编将为大家输出更多高质量的实用文章！

本文从转载，原作者保留一切权利，若侵权请联系删除。

《?Oracle数据库执行过程的问题怎么修复》来自互联网同行内容，若有侵权，请联系我们删除！