Oracle online log recovery case

Case 1 of Oracle online Log Recovery: it is a good solution to the loss of a single member in a multi-path log. You only need to copy the intact online log to the bad log. Case 2: All members of non-current online logs lose the simulated environment:

1. view online log status information sys @ SIQIAN11> select group #, sequence #, archived, status from v $ log; 1 4 no current 2 2 yes inactive 3 3 YES INACTIVEsys @ SIQIAN11> select member from v $ logfile; /u01/oradata/siqian11g/redo01.log/u01/oradata/siqian11g/redo03.log/u01/oradata/siqian11g/redo02.log2. shut down the database and delete non-current online log shutdown immediate [oracle @ siqian siqian11g] $ rm-f/u01/oradata/siqian11g/redo02.log3. start the database and view error information ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: '/u01/oradata/siqian11g/redo02.log' 4. clean up the second group of logs sys @ SIQIAN11> alter database clear logfile group 2; 5. open the database alter database open;

 

Case 3: All current online log members are lost
Simulated environment:

1. view online log status information sys @ SIQIAN11> select group #, sequence #, archived, status from v $ log; GROUP # SEQUENCE # arc status ------------------ --- ---------------- 1 7 no current 2 5 yes inactive 3 6 YES ACTIVE2. close the database and delete all the online logs of the first GROUP shutdown immediate [oracle @ siqian siqian11g] $ rm -f redo01.log3. start the database and view the error message ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/u01/oradata/siqian11g/redo01.log' 4. try to use the above method to clear the first group of logs sys @ SIQIAN11> alter database clear logfile group 1; alter database clear logfile group 1 * ERROR at line 1: ORA-00350: log 1 of instance siqian11g (thread 1) needs to be archivedORA-00312: online log 1 thread 1: '/u01/oradata/siqian11g/redo01.log' 5. incomplete recovery database sys @ SIQIAN11> recover database until cancel; Media recovery complete.6. open database sys @ SIQIAN11> alter database open resetlogs in resetlogs mode; 7. cold backup

 

Case 4: All data files and current log groups are lost
Simulated environment:

1. view the current log information sys @ SIQIAN11> select group #, sequence #, archived, status from v $ log; GROUP # SEQUENCE # arc status ------------------ --- ---------------- 1 7 yes active 2 8 yes active 3 9 NO CURRENT2. Log On with the test user and add some data to table t01 test @ SIQIAN11> select count (*) from t01; COUNT (*) ---------- 10test @ SIQIAN11> begin 2 for I in 11 .. 15 3 loop 4 insert into t01 values (I); 5 end loop; 6 end; 7/PL/SQL procedure successfully comp Leted. commit; test @ SIQIAN11> select count (*) from t01; COUNT (*) ---------- 153. close the database and delete the data file and the Current Log File shutdown immediate [oracle @ siqian siqian11g] $ rm-f redo03.log [oracle @ siqian siqian11g] $ rm-f *. dbf4. start the database and view error information startupORA-01157: cannot identify/lock data file 1-see DBWR trace fileORA-01110: data file 1: '/u01/oradata/siqian11g/system01.dbf' sys @ SIQIAN11> select file #, error from v $ recover_file; FILE # ERROR occurred 1 file not found 2 file not found 3 file not found 4 file not found 5 file not found 6 file not found 7 file not found 8 file not FOUND8 rows selected.5. restore the data FILE [oracle @ siqian siqian11g] $ cp/backup/cold /*. dbf/u01/oradata/siqian11g/6. view the SCN information in the control file and data FILE. sys @ SIQIAN11> select file #, checkpoint_change # from v $ datafile; FILE # CHECKPOINT_CHANGE # -------------------------- 1 2267395 2 2267395 3 2267395 4 2267395 5 2267395 6 2267395 7 2267395 8 22673958 rows selected. sys @ SIQIAN11> select file #, checkpoint_change # from v $ datafile_header; FILE # CHECKPOINT_CHANGE # ------------------------------ 1 2266285 2 2266285 3 2266285 4 2266285 5 2266285 6 2266285 7 2266285 8 22662858 rows selected. it is found that the SCN number in the control file is relatively new, because the data file is copied from the cold backup. 7. try restoring database sys @ SIQIAN11> recover database; ORA-00283: recovery session canceled due to errorsORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: '/u01/oradata/siqian11g/redo03.log' ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3 just deleted the current log, so the log cannot be found to restore 8. restore database sys @ SIQIAN11> recover database un using until cancel Til cancel; ORA-00279: change 2266772 generated at 06/30/2013 10:14:43 needed for thread 1ORA-00289: suggestion:/backup/arch/arch_1_819452646_9.logORA-00280: change 2266772 for thread 1 is in sequence #9 Specify log: {<RET> = suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log '/backup/arch/arch_1_819452646_9.log 'ora-27037: unable to obtain file statusLinux Error: 2: no such fil E or directoryAdditional information: 3 view the current log information sys @ SIQIAN11> select group #, sequence #, archived, status from v $ log; GROUP # SEQUENCE # arc status ------------------ --- ---------------- 1 7 yes inactive 3 9 no current 2 8 YES INACTIVE9. restore database sys @ SIQIAN11> recover database until cancel through incomplete cancel recovery; ORA-00279: change 2266772 generated at 06/30/2013 10:14:43 needed for thread 1ORA-00289: suggestion:/B Ackup/arch/arch_1_819452646_9.logORA-00280: change 2266772 for thread 1 is in sequence #9 Specify log: {<RET> = suggested | filename | AUTO | CANCEL} cancelMedia recovery cancelled.10. open the database sys @ SIQIAN11> alter database open resetlogs; 11. verify that test @ SIQIAN11> select count (*) from t01; COUNT (*) ---------- 10 is visible and can only be restored to the last archive. 12. Cold backup