Oracle online redo log file recovery (redo log)

Oracle online redo log recovery (redo log) Non-current online log File fault recovery www.2cto.com 1. ORA-00313 error when starting the database. ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/home/oracle/oradata/s1/redo01.log' 2. View v $ log view. SQL> select * from v $ log; GROUP # THREAD # SEQUENCE # bytes members arc status FIRST_CHANGE # FIRST_TIM ---------- --- BYTES ------------- --------- 1 1 11 52428800 1 yes inactive 489912 limit 1 10 52428800 1 yes inactive 488868 limit 1 12 52428800 1 no current 519028 11-NOV-123. run the clear command to recreate the log file www.2cto.com. If the log file has been archived, run SQL> alter database clear logfile group 1; Database altered. if the log file is not archived, run SQL> alter database clear unarchived logfile group 3; Database altered.4. Open the database and back up the Database again (clear, especially clear unarchived, and perform a full backup) SQL> alter database open; Database altered. in the current online Log File fault recovery archiving mode, the current log file is damaged in two situations: 1. The database is shut down normally, and instances must be recovered for transactions not resolved in the log file, if the current log group is damaged, you can directly use alter database clear unarchived logfile group n to recreate it. 2. There are active events in the log file, and the database needs to recover the media, there are two ways to remedy this problem. a. in archive mode, backup B is available, and backup is not available. Forced recovery may lead to Database Inconsistency.. backup available in archive mode (Incomplete recovery) 1. 1. an error is reported when you start the database, and use v $ log to view the ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/home/oracle/oradata/s1/redo01.log' ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3SQL> select * from v $ log; GROUP # THREAD # SEQUENCE # bytes members arc status FIRST_CHANGE # FIRST_TIM ---------- ------------ ---------- --- hour --------------- --------- 1 1 1 16 52428800 1 no current 520744 running 1 15 52428800 1 yes active 520712 11-no123 1 14 52428800 1 yes inactive 520705 11-NOV-12 2. try clear restore SQL> alter database clear logfile group 1; SQL> alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance s1 (thread 1) ORA-00312: online log 1 thread 1: '/home/oracle/oradata/s1/redo01.log' 3. execute recover database until cancel first user auto then use cancel, the error is as follows: SQL> recover database until cancel; ORA-00279: change 520744 generated at 11/11/2012 22:43:39 needed for thread 1ORA-00289: suggestion: /archive1/10916_798842981.dbfora-00280: change 520744 for thread 1 is in sequence #16 Specify log: {<RET> = suggested | filename | AUTO | CANCEL} autoORA-00308: cannot open archived log '/archive1/41516_798842981.dbf' ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3ORA-00308: cannot open archived log '/archive1/41516_798842981.dbf' ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3ORA-01547: warning: RECOVER succeeded but open resetlogs wowould get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/home/oracle/oradata/s1/system01.dbf' SQL> recover database until cancel; ORA-00279: change 520744 generated at 11/11/2012 22:43:39 needed for thread 1ORA-00289: suggestion:/archive1/found: change 520744 for thread 1 is in sequence #16 Specify log: {<RET> = suggested | filename | AUTO | CANCEL} cancelORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/home/oracle/oradata/s1/system01.dbf' ORA-01112: media recovery not started4. restore database under rman; RMAN> restore database; 5. recover database under rman: Error: RMAN> recover database; Starting recover at 11-NOV-12using channel ORA_DISK_1starting media recoverymedia recovery failedRMAN-00571: ========================================================== ============================== RMAN-00569: ================ error message stack follows ============================ RMAN-00571: ========================================================== ============================== RMAN-03002: failure of recover command at 11/11/2012 22: 54: 15ORA-00283: recovery session canceled due to errorsRMAN-11003: failure during parse/execution of SQL statement: alter database recover if neededstartORA-00283: recovery session canceled due to errorsORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/home/oracle/oradata/s1/redo01.log' ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 36. use recover database until cancel; auto first and then cancel restore SQL> recover database until cancel; ORA-00279: change 520744 generated at 11/11/2012 22:43:39 needed for thread 1ORA-00289: suggestion:/archive1/recovery: change 520744 for thread 1 is in sequence #16 Specify log: {<RET> = suggested | filename | AUTO | CANCEL} autoORA-00308: cannot open archived log '/archive1/41516_798842981.dbf' ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3ORA-00308: cannot open archived log '/archive1/41516_798842981.dbf' ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3SQL> recover database until cancel; ORA-00279: change 520744 generated at 11/11/2012 22:43:39 needed for thread 1ORA-00289: suggestion:/archive1/10916_798842981.dbfora-00280: change 520744 for thread 1 is in sequence #16 Specify log: {<RET> = suggested | filename | AUTO | CANCEL} cancelMedia recovery cancelled.7, use resetlogs to open database SQL> alter Database open resetlogs; database altered.8. no backup is available for full Database backup, through forced recovery (Incomplete recovery) 1. an error is reported when the database is started. Use v $ log to view SQL> startupORACLE instance started. total System Global Area 167772160 bytesFixed Size 1218316 bytesVariable Size 67111156 bytesDatabase Buffers 96468992 bytesRedo Buffers 2973696 bytesDatabase mounted. ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/home/oracle/oradata/s1/redo01.log 'ora-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3SQL> select * from v $ log; GROUP # THREAD # SEQUENCE # bytes members arc status FIRST_CHANGE # FIRST_TIM ---------- ------------ ---------- --- hour --------------- --------- 1 1 1 4 52428800 1 no current 521392 11-NOV-123 1 3 52428800 1 yes active 521358 found 1 2 52428800 1 yes inactive 521352 11-NOV-122. try the clear method to restore SQL> alter database clear logfile group 1; alter database clear logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance s1 (thread 1) ORA-00312: online log 1 thread 1: '/home/oracle/oradata/s1/redo01.log' SQL> alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance s1 (thread 1) ORA-00312: online log 1 thread 1: '/home/oracle/oradata/s1/redo01.log' 3. if the pfile File is not created, rename the original spfile (ensure that the user pfile starts) SQL> create pfile from spfile; File created.4. close the database SQL> shutdown immediate; ORA-01109: database not openDatabase dismounted. ORACLE instance shut down.5. modify the pfile file and add: _ allow_resetlogs_appsuption = TRUE6. start the database and run recover database until cancel. Enter cancelSQL> startupORACLE instance started. total System Global Area 167772160 bytesFixed Size 1218316 bytesVariable Size 67111156 bytesDatabase Buffers 96468992 bytesRedo Buffers 2973696 bytesDatabase mounted. ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/home/oracle/oradata/s1/redo01.log 'ora-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3SQL> recover database until cancel; ORA-00279: change 521368 generated at 23:13:13 needed for thread 1ORA-00289: suggestion: /archive1/1_3_799109734.dbfORA-00280: change 521368 for thread 1 is in sequence #3 Specify log: {<RET> = suggested | filename | AUTO | CANCEL} cancelORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/home/oracle/oradata/s1/system01.dbf' ORA-01112: media recovery not started8. user resetlogs open database SQL> alter Database open resetlogs; database altered.9. when the Database is opened, full export10. disable database remove _ allow_resetlogs_uption = TRUE; 11. rebuilding the database 12. full import and Recovery completed 13. we recommend that you execute analyze table .. validate structure cascade; 14. full database backup