1 2 Previous Next 23 Replies Latest reply: Apr 28, 2009 8:04 AM by 698683 RSS

    ORA-01113: file 1 needs media recovery

    user00726
      Hi all,


      I am not able to open the db ,my db version is oracle 9.2.4 and i am also not having backups ,pls suggest me how to open the db....
      SQL> alter database open resetlogs;
      alter database open resetlogs
      *
      ERROR at line 1:
      ORA-01113: file 1 needs media recovery
      ORA-01110: data file 1: 'G:\ORACLE\ORADATA\HOTEST\SYSTEM01.DBF'
        • 1. Re: ORA-01113: file 1 needs media recovery
          19426
          No backups? That's bad. Do you have archivelogs?

          ORA-01113: file string needs media recovery
          Cause: An attempt was made to online or open a database with a file that is in need of media recovery.
          Action: First apply media recovery to the file.


          Datafile 1 (always the first datafile of the SYSTEM tablespace) is not consistent,you need logfiles (archived and possibly online) to make it consistent.

          Werner
          • 2. Re: ORA-01113: file 1 needs media recovery
            user00726
            I am not having an online log sequence no 801

            SQL> recover database until cancel using backup controlfile;
            ORA-00279: change 16679127 generated at 12/22/2008 10:37:11 needed for thread 1
            ORA-00289: suggestion : G:\ORACLE\ARCH\ARC_801_1.ARC
            ORA-00280: change 16679127 for thread 1 is in sequence #801


            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: 'G:\ORACLE\ORADATA\HOTEST\SYSTEM01.DBF'


            ORA-01112: media recovery not started
            • 3. Re: ORA-01113: file 1 needs media recovery
              70824
              Which ones do you have, then?
              If the numbers are close (e.g. 799 is out there), try filling in the name of the online redo log file(s)
              • 4. Re: ORA-01113: file 1 needs media recovery
                user00726
                I am having 800 log abd i have copied the same and renamed it to 801 but still problem not solved....
                • 5. Re: ORA-01113: file 1 needs media recovery
                  Nishant Santhan
                  I am having 800 log abd i have copied the same and renamed it to 801 but still problem not solved....
                  What was the current redo log file when your instance aborted last time? If there is no 801 and the last archive generated was 800, then you can give that redo log file for recovery. I think till 800 its applied and still files need recovry, and oracle guessing that there will be 801 sequence... which is not generated.

                  If you dont know the last current redo, try with all redo logs, the needed information is there in the redo, it will apply otherwise it will through an error.


                  Regards
                  Nishant S
                  • 6. Re: ORA-01113: file 1 needs media recovery
                    user00726
                    SQL> recover database until cancel using backup controlfile;
                    ORA-00279: change 16679127 generated at 12/22/2008 10:37:11 needed for thread 1
                    ORA-00289: suggestion : G:\ORACLE\ARCH\ARC_801_1.ARC
                    ORA-00280: change 16679127 for thread 1 is in sequence #801


                    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                    G:\ORACLE\ARCH\ARC_800_1.ARC
                    ORA-00310: archived log contains sequence 800; sequence 801 required
                    ORA-00334: archived log: 'G:\ORACLE\ARCH\ARC_800_1.ARC'


                    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: 'G:\ORACLE\ORADATA\HOTEST\SYSTEM01.DBF'
                    still not able to recover....
                    • 7. Re: ORA-01113: file 1 needs media recovery
                      NitinJoshi
                      Hi,
                      Renaming archive logs never solves the problem.
                      Mount your DB and see/post output.

                      as Nishant has said, you may try to use your ONLINE REDO LOG for the recovery.

                      Regards!
                      • 8. Re: ORA-01113: file 1 needs media recovery
                        user00726
                        I have applied all the online redo logfile.....

                        but still the problem persists....

                        SQL> recover database until cancel using backup controlfile;
                        ORA-00279: change 16679127 generated at 12/22/2008 10:37:11 needed for thread 1
                        ORA-00289: suggestion : G:\ORACLE\ARCH\ARC_801_1.ARC
                        ORA-00280: change 16679127 for thread 1 is in sequence #801


                        Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                        G:\oracle\oradata\hotest\redo01.log
                        ORA-00310: archived log contains sequence 800; sequence 801 required
                        ORA-00334: archived log: 'G:\ORACLE\ORADATA\HOTEST\REDO01.LOG'


                        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: 'G:\ORACLE\ORADATA\HOTEST\SYSTEM01.DBF'


                        SQL> recover database until cancel using backup controlfile;
                        ORA-00279: change 16679127 generated at 12/22/2008 10:37:11 needed for thread 1
                        ORA-00289: suggestion : G:\ORACLE\ARCH\ARC_801_1.ARC
                        ORA-00280: change 16679127 for thread 1 is in sequence #801


                        Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                        G:\oracle\oradata\hotest\redo02.log
                        ORA-00339: archived log does not contain any redo
                        ORA-00334: archived log: 'G:\ORACLE\ORADATA\HOTEST\REDO02.LOG'


                        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: 'G:\ORACLE\ORADATA\HOTEST\SYSTEM01.DBF'


                        SQL> recover database until cancel using backup controlfile;
                        ORA-00279: change 16679127 generated at 12/22/2008 10:37:11 needed for thread 1
                        ORA-00289: suggestion : G:\ORACLE\ARCH\ARC_801_1.ARC
                        ORA-00280: change 16679127 for thread 1 is in sequence #801


                        Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                        G:\oracle\oradata\hotest\redo03.log
                        ORA-00310: archived log contains sequence 799; sequence 801 required
                        ORA-00334: archived log: 'G:\ORACLE\ORADATA\HOTEST\REDO03.LOG'


                        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: 'G:\ORACLE\ORADATA\HOTEST\SYSTEM01.DBF'


                        SQL>
                        • 9. Re: ORA-01113: file 1 needs media recovery
                          NitinJoshi
                          how was your backup?
                          How was your restoration?
                          also post your V$log output

                          Regards!
                          • 10. Re: ORA-01113: file 1 needs media recovery
                            user00726
                            i am not having any backups.....
                            SQL> select * from v$log;

                                GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
                            ---------- ---------- ---------- ---------- ---------- --- ----------------
                            FIRST_CHANGE# FIRST_TIM
                            ------------- ---------
                                     1          1          0  104857600          1 YES UNUSED
                                        0

                                     2          1          0  104857600          1 YES UNUSED
                                        0

                                     3          1          0  104857600          1 YES INVALIDATED
                                        0
                            Edited by: user00726 on Jan 9, 2009 2:57 AM
                            • 11. Re: ORA-01113: file 1 needs media recovery
                              Pavan DBA
                              well you don't have backups which is very crazy situation.

                              is your database is in archive log mode? if so what is the last archive file generated? i mean sequence number.
                              • 12. Re: ORA-01113: file 1 needs media recovery
                                user00726
                                its is a test server....


                                and the l*ast sequence number which is generated is 800*

                                and i have tried to clear the unarchived log file but still getting the problem....

                                SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

                                    GROUP# ARC STATUS
                                ---------- --- ----------------
                                         1 YES UNUSED
                                         2 YES UNUSED
                                         3 YES INVALIDATED

                                SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

                                Database altered.


                                SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

                                    GROUP# ARC STATUS
                                ---------- --- ----------------
                                         1 YES UNUSED
                                         2 YES UNUSED
                                         3 YES INVALIDATED

                                SQL>
                                • 13. Re: ORA-01113: file 1 needs media recovery
                                  NitinJoshi
                                  Hi,
                                  i was thinking of it.
                                  You've reseted logs.
                                  see your sequence number is 1 & oracle is asking 801.
                                  so you need a older controlfile & datfiles for that(of seqence series 799+).

                                  Regards!
                                  • 14. Re: ORA-01113: file 1 needs media recovery
                                    user00726
                                    so what is the exact solution for the same.....
                                    1 2 Previous Next