今天在做rman实验的时候，把system.dbf和SYSAUX.dbf还有redo.log删除以后做恢复，发现数据库无法恢复，使用傻瓜式修复，居然没有建议。刚开始以为是oracle的bug，猛然一想自己的实验库有两个，在创建恢复目录实验的时候因为相同路径有一个同名备份，所以当前操作的库的备份没有重新做。
很僵硬。于是上网查询解决办法，找到一个不完全恢复的方法。首先:
SQL> recover datafile '/u01/app/oracle/oradata/PROD2/system01.dbf' until cancel;
ORA-00274: illegal recovery option UNTIL
SQL> recover database until cancel;
ORA-00279: change 906846 generated at 03/06/2019 19:38:30 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2019_03_06/o1_mf_1_9_%u_.arc
ORA-00280: change 906846 for thread 1 is in sequence #9
看到退出在 sequence #9 于是有
RMAN> run{
2> set until sequence 9 thread 1;
3> recover database;}
executing command: SET until clause
Starting recover at 06-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/06/2019 21:14:25
RMAN-06556: datafile 1 must be restored from backup older than SCN 906828
在这里找到了最后的时间点，本以为再用基于scn的修复就可以，然而：
RMAN> run{set until scn 906828;
2> restore database;
3> recover database;}
executing command: SET until clause
Starting restore at 06-MAR-19
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/06/2019 21:16:07
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN> run{set until scn 906828;restore database;recover database until cancel;}
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "cancel": expecting one of: "scn, sequence, time"
RMAN-01007: at line 1 column 66 file: standard input
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
166 181 PROD2 1650271301 PARENT 1 18-SEP-11
166 167 PROD2 1650271301 CURRENT 787897 24-JAN-19
1 22 PROD1 2219776435 PARENT 1 18-SEP-11
1 2 PROD1 2219776435 CURRENT 787897 24-JAN-19
RMAN> exit
！！！！！！看来上一次执行的备份是很久了，已经没办法恢复了。但是也学习到一些关于不完全恢复的知识。在另一个做过全备的库上执行同样的实验，结果是成功的。
[oracle@odd-PROD3 ~]$ rman target sys/oracle@prod1 catalog rc_admin/RC_ADMIN@prod4
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 6 21:23:08 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (DBID=2219776435, not open)
connected to recovery catalog database
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
348 CRITICAL OPEN 06-MAR-19 System datafile 1: '/u01/app/oracle/oradata/PROD1/system01.dbf' is missing
342 CRITICAL OPEN 06-MAR-19 Redo log group 3 is unavailable
351 HIGH OPEN 06-MAR-19 One or more non-system datafiles are missing
345 HIGH OPEN 06-MAR-19 Redo log file /u01/app/oracle/oradata/PROD1/redo03.log is missing
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
348 CRITICAL OPEN 06-MAR-19 System datafile 1: '/u01/app/oracle/oradata/PROD1/system01.dbf' is missing
342 CRITICAL OPEN 06-MAR-19 Redo log group 3 is unavailable
351 HIGH OPEN 06-MAR-19 One or more non-system datafiles are missing
345 HIGH OPEN 06-MAR-19 Redo log file /u01/app/oracle/oradata/PROD1/redo03.log is missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
analyzing automatic repair options complete
Not all specified failures can currently be repaired.
The following failures must be repaired before advise for others can be given.
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
342 CRITICAL OPEN 06-MAR-19 Redo log group 3 is unavailable
345 HIGH OPEN 06-MAR-19 Redo log file /u01/app/oracle/oradata/PROD1/redo03.log is missing
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD1/redo03.log was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Clear redo log group 3
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod1/PROD1/hm/reco_2004838160.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod1/PROD1/hm/reco_2004838160.hm
contents of repair script:
# clear redo log group
sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod1/PROD1/hm/reco_3796643168.hm'' ); end;";
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
sql statement: begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/prod1/PROD1/hm/reco_3796643168.hm'' ); end;
repair failure complete
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
348 CRITICAL OPEN 06-MAR-19 System datafile 1: '/u01/app/oracle/oradata/PROD1/system01.dbf' is missing
351 HIGH OPEN 06-MAR-19 One or more non-system datafiles are missing
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
348 CRITICAL OPEN 06-MAR-19 System datafile 1: '/u01/app/oracle/oradata/PROD1/system01.dbf' is missing
351 HIGH OPEN 06-MAR-19 One or more non-system datafiles are missing
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 /u01/app/oracle/oradata/PROD1/system01.dbf was unintentionally renamed or moved, restore it
2. If file /u01/app/oracle/oradata/PROD1/sysaux01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 1; Restore and recover datafile 2
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod1/PROD1/hm/reco_1476702341.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod1/PROD1/hm/reco_1476702341.hm
contents of repair script:
# restore and recover datafile
restore datafile 1, 2;
recover datafile 1, 2;
sql 'alter database datafile 1, 2 online';
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 06-MAR-19
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/app/oracle/oradata/PROD1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD1/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD1/backupset/2019_03_06/o1_mf_nnndf_TAG20190306T193320_g7zd01op_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2019_03_06/o1_mf_nnndf_TAG20190306T193320_g7zd01op_.bkp tag=TAG20190306T193320
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 06-MAR-19
Starting recover at 06-MAR-19
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD1/archivelog/2019_03_06/o1_mf_1_27_g7zd314l_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/PROD1/archivelog/2019_03_06/o1_mf_1_27_g7zd314l_.arc thread=1 sequence=27
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-MAR-19
sql statement: alter database datafile 1, 2 online
repair failure complete
Do you want to open the database (enter YES or NO)? yes
database opened
虽然只是实验库！但教训是惨痛的，所以在日后的工作中一定要保持清醒的头脑！
扫一扫
9996
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
