继上一篇不完全恢复
oracle-不完全数据库恢复-被动恢复-ORA-00313/ORA-00366
场景2：数据库拥有备份，CURRENT状态日志组中所有的在线日志头损坏，在发生日志切换时实例被自动强行关闭，视图重新启动db，在mount状态。
--此场景类似在DSI系列中的，归档模式下CURRENT的redo log损坏或丢失
当前redo log状态
SQL> select group#,sequence#,status from v$log; GROUP# SEQUENCE# STATUS ---------- ---------- ---------------- 1 10 CURRENT 2 8 INACTIVE 3 9 INACTIVE
手动损坏current文件
[oracle@DSI orcl]$ dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo01.log bs=512 count=1 0+0 records in 0+0 records out 0 bytes (0 B) copied, 0.000102439 s, 0.0 kB/s
切换报错，且实例自动关闭
SQL> alter system switch logfile; alter system switch logfile * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 29989 Session ID: 134 Serial number: 19 SQL> alter system switch logfile; ERROR: ORA-03114: not connected to ORACLE
启动实例报错，只能到mount阶段
SQL> startup; ORACLE instance started. Total System Global Area 784998400 bytes Fixed Size 2257352 bytes Variable Size 499125816 bytes Database Buffers 276824064 bytes Redo Buffers 6791168 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log' ORA-27048: skgfifi: file header information is invalid Additional information: 13
查看告警日志
[oracle@DSI orcl]$ grep ORA-00313 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log |uniq ORA-00313: open failed for members of log group 1 of thread 1
查看v$log观察当前在线日志组的状态
SQL> select group#,sequence#,status from v$log; GROUP# SEQUENCE# STATUS ---------- ---------- ---------------- 1 10 CURRENT 2 8 INACTIVE 3 9 INACTIVE
10号文件处于current状态，8,9号是unused（或者是inactive的），实例恢复不需要他们，这意味这完全检查点的位置已经进入了10号日志，所有数据文件头的检查点SCN全都比10号日志的第一条重做记录的SCN高，恢复操作也仅仅需要10号日志。
但是10号日志损坏，而且是CURRENT日志不可能存在归档，所以归档没有用处。
此时完全恢复已不可能，而recover database也不行，ORA-00313
SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log' ORA-27048: skgfifi: file header information is invalid Additional information: 12
因为实例无法恢复，数据文件不能同步，不满足数据库打开的条件
此时不得不采用不完全恢复，控制恢复操作在10号日志之前结束。
从数据文件的角度理解，就是将数据文件中的10号日志中的变更不论事务提交与否统统从文件中“挖”处理，这样就要求数据文件首先必须足够“旧”，不能一开始就索要10号日志。
在已知10号日志问题的前提下，
RMAN> run { set until sequence 10; restore database; recover database; alter database open resetlogs; }2> 3> 4> 5> 6> executing command: SET until clause Starting restore at 25-JUL-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=11 device type=DISK 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/orcl/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/test01.dbf channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/orcl/rc_data01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bol_fullbak0cu7i4i6_1_1_20190725 channel ORA_DISK_1: piece handle=/home/oracle/backup/bol_fullbak0cu7i4i6_1_1_20190725 tag=BOL_FULLBAK channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 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/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/assm01.dbf channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/mssm01.dbf channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl/ogg01.dbf channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/orcl/yhqt01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bol_fullbak0du7i4id_1_1_20190725 channel ORA_DISK_1: piece handle=/home/oracle/backup/bol_fullbak0du7i4id_1_1_20190725 tag=BOL_FULLBAK channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 25-JUL-19 Starting recover at 25-JUL-19 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_07_25/o1_mf_1_9_gmlslqts_.arc channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=7 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=8 channel ORA_DISK_1: reading from backup piece /home/oracle/backup/arch_0ju7i4ik_1_1_20190725 channel ORA_DISK_1: piece handle=/home/oracle/backup/arch_0ju7i4ik_1_1_20190725 tag=TAG20190725T161824 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_07_25/o1_mf_1_7_gmltlc7h_.arc thread=1 sequence=7 channel default: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_07_25/o1_mf_1_7_gmltlc7h_.arc RECID=28 STAMP=1014568651 archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_07_25/o1_mf_1_8_gmltlc83_.arc thread=1 sequence=8 channel default: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_07_25/o1_mf_1_8_gmltlc83_.arc RECID=29 STAMP=1014568651 archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_07_25/o1_mf_1_9_gmlslqts_.arc thread=1 sequence=9 media recovery complete, elapsed time: 00:00:00 Finished recover at 25-JUL-19 database opened
--alter database open resetlogs会重新格式化所有的在线日志，顺便修复了1号日志损坏的问题。
本实例在所有数据文件完好的情况下，仅current的redo log损坏，居然会导致必须还原所有数据文件、丢失事务的结果，且数据库越大消耗的时间就越多。
SQL> select group#,sequence#,status from v$log; GROUP# SEQUENCE# STATUS ---------- ---------- ---------------- 1 1 CURRENT 2 0 UNUSED 3 0 UNUSED SQL> conn yhqt/yhqt Connected. SQL> select count(*) from yhqtest_1; COUNT(*) ---------- 5005
场景3：数据库有备份，current状态日志组中所有的（1号组序列34）在线日志头损坏，在发生日志切换时实例已经被强行关闭，重启db时，数据库在mount阶段
SQL> select group#,sequence#,status from v$log; GROUP# SEQUENCE# STATUS ---------- ---------- ---------------- 1 34 CURRENT 2 33 ACTIVE 3 0 UNUSED
虽然与场景2损坏相同，同为current，但是这个场景中的33号日志处于ACTIVE状态，说明实例崩溃时完全检查点的RBA尚未超越过33号日志组的最后一条记录
RMAN> recover database until sequence 34; RMAN-03002: failure of recover command at .. RMAN-11003: failure during parse/execution of SQL statement : alter database recover cancel ORA-10879: error signaled in parallel recovery slave ORA-01547: warning RECOVER succeeded bu OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1 /u01/app/oracle/oradata/orcl/system01.dbf
告警ORA-01547 接下来open resetlogs会失败
RMAN> alter database open resetlogs; ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1 /u01/app/oracle/oradata/orcl/system01.dbf
还是采用场景2的恢复方法
RMAN> run { set until sequence 34; restore database; recover database; alter database open resetlogs; }2> 3> 4> 5> 6>
处理current日志损坏并且实例已崩溃的一般方法：当current日志损坏，只能采用restore-recover-resetlogs的方案不完全恢复数据库。