8 Replies Latest reply: Jun 29, 2009 5:12 PM by 655968 RSS

    how to recover from corrupt redo log file in non-archived 10g db

    655968
      Hello Friends,
      I don't know much about recovering databases. I have a 10.2.0.2 database with corrupt redo file and I am getting following error on startup. (db is non archived and no backup) Thanks very much for any help.

      Database mounted.
      ORA-00368: checksum error in redo log block
      ORA-00353: log corruption near block 6464 change 9979452011066 time 06/27/2009
      15:46:47
      ORA-00312: online log 1 thread 1: '/dbfiles/data_files/log3.dbf'

      ====
      SQL> select Group#,members,status from v$log;

      GROUP# MEMBERS STATUS
      ---------- ---------- ----------------
      1 1 CURRENT
      3 1 UNUSED
      2 1 INACTIVE
      ==
      I have tried this so far but no luck
      I have tried following commands but no help.
      SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

      Database altered.

      SQL> alter database open resetlogs;
      alter database open resetlogs
      *
      ERROR at line 1:
      ORA-01139: RESETLOGS option only valid after an incomplete database recovery


      SQL> alter database open;
      alter database open
      *
      ERROR at line 1:
      ORA-00368: checksum error in redo log block
      ORA-00353: log corruption near block 6464 change 9979452011066 time 06/27/2009
      15:46:47
      ORA-00312: online log 1 thread 1: '/dbfiles/data_files/log3.dbf'
        • 1. Re: how to recover from corrupt redo log file in non-archived 10g db
          Surachart Opun
          ORA-00312: online log 1 thread 1: '/dbfiles/data_files/log3.dbf'
          that mean group 1, right?
          So, It' should
          SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;


          By the way , try to test:

          SQL > ALTER DATABASE CLEAR UNARCHIVED LOGFILE '/dbfiles/data_files/log3.dbf';
          SQL> alter database open;
          SQL> shutdown
          SQL> startup
          • 2. Re: how to recover from corrupt redo log file in non-archived 10g db
            655968
            Thank you for your reply. unfortunately didn't work.
            SQL> select status from v$instance;

            STATUS
            ------------
            MOUNTED

            SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE '/dbfiles/data_files/log3.dbf';
            ALTER DATABASE CLEAR UNARCHIVED LOGFILE '/dbfiles/data_files/log3.dbf'
            *
            ERROR at line 1:
            ORA-01624: log 1 needed for crash recovery of instance LA0607 (thread 1)
            ORA-00312: online log 1 thread 1: '/dbfiles/data_files/log3.dbf'


            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 LA0607 (thread 1)
            ORA-00312: online log 1 thread 1: '/dbfiles/data_files/log3.dbf'
            • 3. Re: how to recover from corrupt redo log file in non-archived 10g db
              user10929871
              Suggestion: It is always recommended to have more then one redo log member on different file systems and to run the database in Archive mode.

              SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

              After performing the above step, perform an false recovery. i.e.

              SQL> RECOVER DATABASE UNTIL CANCEL;

              CANCEL

              SQL> ALTER DATABASE OPEN RESETLOGS;


              See if it works.
              • 4. Re: how to recover from corrupt redo log file in non-archived 10g db
                Surachart Opun
                Cause:     A log cannot be dropped or cleared until the thread's checkpoint
                     has advanced out of the log.
                Action:     If the database is not open, then open it. Crash recovery will
                     advance the checkpoint. If the database is open force a global
                     checkpoint. If the log is corrupted so that the database cannot be
                     opened, it may be necessary to do incomplete recovery until cancel
                     at this log.


                try to shutdown the database normally

                startup mount and then try
                ALTER DATABASE CLEAR UNARCHIVED LOGFILE '/dbfiles/data_files/log3.dbf';

                adn then ...

                http://www.dbasupport.com/forums/archive/index.php/t-33564.html
                • 5. Re: how to recover from corrupt redo log file in non-archived 10g db
                  Daljit
                  Try:

                  sqlplus / as sysdba

                  SQL> recover database until cancel;

                  {Cancel the recovery}

                  SQL> alter database open resetlogs;

                  By doing this, you will lose all the transactions which were there in that redo log.

                  Thanks
                  Daljit Singh
                  • 6. Re: how to recover from corrupt redo log file in non-archived 10g db
                    655968
                    Thanks very much for your help guys. I appreciate it. unfortunately none of these commands worked for me. I kept getting error on clearing logs that redo log is needed to perform recovery so it can't be cleared. So I ended up restoring from earlier snapshot of my db volume. Database is now open.

                    Thanks again for your input.
                    • 7. Re: how to recover from corrupt redo log file in non-archived 10g db
                      EdStevens
                      user652965 wrote:
                      Thanks very much for your help guys. I appreciate it. unfortunately none of these commands worked for me. I kept getting error on clearing logs that redo log is needed to perform recovery so it can't be cleared. So I ended up restoring from earlier snapshot of my db volume. Database is now open.

                      Thanks again for your input.
                      And now, as a follow-up, at a minimum you should make sure that all redo log groups have at least 3 members. Then, if you lose a single redo log file, all you have to do is shutdown the db and copy one of the good members (of the same group as the lost member) over the lost member.

                      And as an additional follow-up, if you value your data you will run in archivelog mode and take regular backups of the database and archivelogs. If you fail to do this you are saying that your data is not worth saving.