9 Replies Latest reply on Jun 21, 2007 7:56 PM by 223563

    ora-00333, bad redo, want to replace redo or db

    223563
      Hi:

      I tried (and failed) to restart the db. Here’s what happened...

      SQL> startup open pfile="/odb0/NDB/mgmt/init_ndb.ora"
      ORACLE instance started.

      Total System Global Area 1797277888 bytes
      Fixed Size 755904 bytes
      Variable Size 184549376 bytes
      Database Buffers 1610612736 bytes
      Redo Buffers 1359872 bytes
      Database mounted.

      ORA-00333: redo log read error block 2 count 8192



      From the alert log, things started to go bad last night...

      Wed Jun 20 19:04:13 2007
      Thread 1 cannot allocate new log, sequence 84043
      Checkpoint not complete

      Current log# 2 seq# 84042 mem# 0: /odb0/oracle/oracle920/oradata/ndb/redo02.log


      I' afraid the redo02.log is messed-up.

      I have 3 redo logs....

      ORA_IA64> ls /odb0/oracle/oracle920/oradata/ndb/redo*
      /odb0/oracle/oracle920/oradata/ndb/redo01.log
      /odb0/oracle/oracle920/oradata/ndb/redo02.log
      /odb0/oracle/oracle920/oradata/ndb/redo03.log

      I have no reason to believe either of the other 2 are bad.

      Q: Is there a way to copy one of the good ones over the bad redo02.log file?
      I tried an OS level “mv redo01.log redo02.log” (after saving redo02.log to “save_redo02.log” so that I could go back). That failed, the message indicated something to the effect that the log id number was wrong in redo02.log. Makes sense. So I "mv save_redo02.log redo02.log". So, is there a Oracle utility that allows me to 1) copy one of the good log files over the bad one. or... 2) take the redo02.log “offline” (given that the content of the other 2 will suffice).

      If none of this is possible, and I lost all my data, I can live with that (it won't be the first time this has happened). But I would like to save the database instance. I need the draconian commands that will say (in effect) “forget all the content in the undo/redo logs/files, just clear them out and restart the DB. Then I will "truncate" all the tables and reload all the data. As it is now, I can’t do anything because I can't get poast 0ra-00333.

      Ano other suggestions?

      No, I don't do RMAN-style backups (and I've already been lectured on this).

      Oracle 9.2.0.2.0

      Thanks in Advance
      -dave



      Thanks

      -dave
        • 1. Re: ora-00333, bad redo, want to replace redo or db
          561093
          How many members do you have in a group? If your redo logs are mirrored then you can copy the good one over the bad one and start your database happily otherwise you have to open your database with RESETLOG.
          • 2. Re: ora-00333, bad redo, want to replace redo or db
            oradba
            /*
            Thread 1 cannot allocate new log, sequence 84043
            Checkpoint not complete
            Current log# 2 seq# 84042 mem# 0: /odb0/oracle/oracle920/oradata/ndb/redo02.log
            */

            These lines are pure informational messages and don't mean redo02.log is corrupt.

            To analyze the problem post the whole error stack, (ORA-00333) does not come alone.

            Redo01.log is completely different from redo02.log, so you will destroy your redologs, when you replace redo02.log by redo01.log.

            Only 2 redolog groups are required, but you can only drop an inactive redolog group. When an active redolog is lost, only incomplete recovery will help.

            Werner
            • 3. Re: ora-00333, bad redo, want to replace redo or db
              223563
              Not sure what you mean "in a group". I'm not sure of my facts here, but all 3 exist side-by-side on the same disk and have identical size. I'm thinking that they are all redundant copied of each other?

              No mirrors. Was hoping that redo01.log = redo02.log = redo03.log. (guessing not :-( )

              If not, then how does one "open with resetlog" (what's the syntax after logging in as SYSTEM as SYSDBA given that Inormally just run an init.ora file)

              Thanks
              -dave
              • 4. Re: ora-00333, bad redo, want to replace redo or db
                223563
                I'm afraid that it. Here's a cut/paste....

                SQL> startup open pfile="/odb0/NDB/mgmt/init_ndb.ora"
                ORACLE instance started.

                Total System Global Area 1797277888 bytes
                Fixed Size 755904 bytes
                Variable Size 184549376 bytes
                Database Buffers 1610612736 bytes
                Redo Buffers 1359872 bytes
                Database mounted.
                ORA-00333: redo log read error block 2 count 8192


                SQL>
                • 5. Re: ora-00333, bad redo, want to replace redo or db
                  223563
                  The only mention of RESETLOGS I can find talk about using it in the context of :alter database open resetlogs". But when I try that (after a successful "startup mount"), it says...

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

                  I don't want to recover anything. I'm willing to truncate away all my user tables. Is there a way to get around these barriers?

                  My fallback solution is to (gulp) drop the instance (manuall start deleting files everywhere and run dbca). THere's got to be a better way :-|

                  -dave
                  • 6. Re: ora-00333, bad redo, want to replace redo or db
                    oradba
                    Try 'recover database until cancel;' and enter 'cancel' when prompted. Then try again 'alter database open resetlogs;'

                    Werner
                    • 7. Re: ora-00333, bad redo, want to replace redo or db
                      223563
                      "needs more recovery???"
                      Hmmmmm....

                      SQL> startup mount;
                      ORACLE instance started.

                      Total System Global Area 1981827528 bytes
                      Fixed Size 756168 bytes
                      Variable Size 301989888 bytes
                      Database Buffers 1677721600 bytes
                      Redo Buffers 1359872 bytes
                      Database mounted.
                      SQL> recover database until cancel;
                      ORA-00279: change 3332551091 generated at 06/21/2007 00:58:16 needed for thread
                      1
                      ORA-00289: suggestion : /odb0/oracle/oracle920/dbs/arch1_84171.dbf
                      ORA-00280: change 3332551091 for thread 1 is in sequence #84171


                      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                      cancel
                      ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                      ORA-01194: file 1 needs more recovery to be consistent
                      ORA-01110: data file 1: '/odb0/oracle/oracle920/oradata/ndb/system01.dbf'


                      ORA-01112: media recovery not started


                      SQL> alter database open resetlogs;
                      alter database open resetlogs
                      *
                      ERROR at line 1:
                      ORA-01194: file 1 needs more recovery to be consistent
                      ORA-01110: data file 1: '/odb0/oracle/oracle920/oradata/ndb/system01.dbf'


                      SQL>
                      • 8. Re: ora-00333, bad redo, want to replace redo or db
                        oradba
                        In this case the database needs archivelogs to be consistent. The requested archivelog (arch1_84171.dbf), does it exist? If yes, you can apply it and continue the process until the last requested logfile (unfortunately this could be the corrupt logfile).

                        If after the last applied logfile you still see '...needs more recovery...', then you cannot recover the database without a valid backup. And if there is no backup, you have to recreate the database. For this reason backups should always be made.

                        Werner
                        • 9. Re: ora-00333, bad redo, want to replace redo or db
                          223563
                          There is no such log file. So I have to recreate the database.

                          Is there a way to drop the existing database without my having to manually scrounge around the disks deleting .dbf files and the like? Is there something like a "alter database vanish" command?

                          Then I suppose I'l have to run dbca and start all over again.

                          Seems like there ought to be a less drastic way to accomplish this. I mean I'm willing to forego all the data in the DB. Why do I have to recover the DB when I don
                          t want anything in it? Seems like there should be a way around this.