1 2 Previous Next 15 Replies Latest reply: Jun 8, 2010 8:43 PM by sb92075 RSS

    ORA-01113: file 26 needs media recovery

    603447
      I cannot start the database, need help on how to recover media:

      SQL> shutdown immediate;
      ORA-01109: database not open


      Database dismounted.
      ORACLE instance shut down.
      SQL> startup
      ORACLE instance started.

      Total System Global Area 1367343104 bytes
      Fixed Size 1292492 bytes
      Variable Size 327157556 bytes
      Database Buffers 1031798784 bytes
      Redo Buffers 7094272 bytes
      Database mounted.
      ORA-01113: file 26 needs media recovery
      ORA-01110: data file 26: 'D:\DBDIR\ORADATA\AXIDX_03.ORA'
        • 1. Re: ORA-01113: file 26 needs media recovery
          Maran Viswarayar
          Can you tell us what happened first?

          Was there a power failure?
          • 2. Re: ORA-01113: file 26 needs media recovery
            Zekeriya Besiroglu
            Hi

            pls try

            if your database archivelog
            then

            Recover datafile 'your datafile with path'

            hope this helps
            Zekeriya
            • 3. Re: ORA-01113: file 26 needs media recovery
              Kamran Agayev A.
              Have you any backup of the database?
              What have you done so far before getting this error?
              • 4. Re: ORA-01113: file 26 needs media recovery
                Aman....
                mrp wrote:
                I cannot start the database, need help on how to recover media:

                SQL> shutdown immediate;
                ORA-01109: database not open


                Database dismounted.
                ORACLE instance shut down.
                SQL> startup
                ORACLE instance started.

                Total System Global Area 1367343104 bytes
                Fixed Size 1292492 bytes
                Variable Size 327157556 bytes
                Database Buffers 1031798784 bytes
                Redo Buffers 7094272 bytes
                Database mounted.
                ORA-01113: file 26 needs media recovery
                ORA-01110: data file 26: 'D:\DBDIR\ORADATA\AXIDX_03.ORA'
                What happens when you run
                recover datafile 26
                Aman....
                • 5. Re: ORA-01113: file 26 needs media recovery
                  603447
                  no power interruption. it was ok after I restart the server (machine). what do you think the cause of this error? Btw, this is only a QA site, I do not use archive logs (using cold backup). Not sure if this will happen again. How can I investigate this further?
                  • 6. Re: ORA-01113: file 26 needs media recovery
                    Kamran Agayev A.
                    mrp wrote:
                    no power interruption. it was ok after I restart the server (machine).
                    There's a inconsistency between datafiles so you need to recover the datafile 26 to make the database consistent
                    I do not use archive logs (using cold backup).
                    Believe me, taking RMAN backup is more easy (and secure) than cold backup

                    Post some entries from alert.log file that's "before" and "after" the specified error occured
                    • 7. Re: ORA-01113: file 26 needs media recovery
                      561093
                      Did you shutdown database gracefully before restarting the server ???

                      If you are not using RMAN for your backup's and your database is not in archivelog mode then the only option left for you is to restore the last cold backup.

                      Also, what type of objects does the datafile/tablespace in question consists of ?



                      Asif Momen
                      http://momendba.blogspot.com
                      • 8. Re: ORA-01113: file 26 needs media recovery
                        603447
                        Kamran Agayev A. wrote:
                        mrp wrote:
                        no power interruption. it was ok after I restart the server (machine).
                        There's a inconsistency between datafiles so you need to recover the datafile 26 to make the database consistent
                        I do not use archive logs (using cold backup).
                        Believe me, taking RMAN backup is more easy (and secure) than cold backup

                        Post some entries from alert.log file that's "before" and "after" the specified error occured
                        Here it is...

                        Mon Jun 07 16:51:51 2010
                        ALTER DATABASE MOUNT
                        Mon Jun 07 16:51:56 2010
                        Setting recovery target incarnation to 2
                        Mon Jun 07 16:51:56 2010
                        Successful mount of redo thread 1, with mount id 2493849767
                        Mon Jun 07 16:51:56 2010
                        Database mounted in Exclusive Mode
                        Completed: ALTER DATABASE MOUNT
                        Mon Jun 07 16:51:56 2010
                        ALTER DATABASE OPEN
                        Mon Jun 07 16:51:57 2010
                        Errors in file g:\oracle\product\10.2.0\admin\DB\udump\DB_ora_3092.trc:
                        ORA-01110: data file 26: 'D:\DB\ORADATA\AXIDX_03.ORA'
                        ORA-01114: IO error writing block to file 26 (block # 1)
                        ORA-27041: unable to open file
                        OSD-04002: unable to open file
                        O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.

                        Mon Jun 07 16:51:57 2010
                        ORA-01201: file 26 header failed to write correctly

                        ORA-1113 signalled during: ALTER DATABASE OPEN...
                        Mon Jun 07 17:02:23 2010
                        Shutting down instance: further logons disabled
                        Mon Jun 07 17:02:23 2010
                        • 9. Re: ORA-01113: file 26 needs media recovery
                          603447
                          Asif Momen wrote:
                          Did you shutdown database gracefully before restarting the server ???

                          If you are not using RMAN for your backup's and your database is not in archivelog mode then the only option left for you is to restore the last cold backup.

                          Also, what type of objects does the datafile/tablespace in question consists of ?



                          Asif Momen
                          http://momendba.blogspot.com
                          how do I know what objects that resides on the datafile?
                          • 10. Re: ORA-01113: file 26 needs media recovery
                            603447
                            I saw other logs on the alert logs. It seems that there are OS File I/O error. Can you help us recommend what to do?

                            Mon Jun 07 16:40:50 2010
                            Errors in file g:\oracle\product\10.2.0\admin\DB\bdump\DB_ckpt_2756.trc:
                            ORA-01110: data file 24: 'D:\DB\ORADATA\AXIDX_01.ORA'
                            ORA-01115: IO error reading block from file 24 (block # 1)
                            ORA-27072: File I/O error
                            OSD-04006: ReadFile() failure, unable to read from file
                            O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.

                            Mon Jun 07 16:41:05 2010
                            Errors in file g:\oracle\product\10.2.0\admin\DB\bdump\DB_ckpt_2756.trc:
                            ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
                            ORA-01110: data file 24: 'D:\DB\ORADATA\AXIDX_01.ORA'
                            ORA-01115: IO error reading block from file 24 (block # 1)
                            ORA-27072: File I/O error
                            OSD-04006: ReadFile() failure, unable to read from file
                            O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.

                            Mon Jun 07 16:41:06 2010
                            Errors in file g:\oracle\product\10.2.0\admin\DB\bdump\DB_ckpt_2756.trc:
                            ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
                            ORA-01110: data file 24: 'D:\DB\ORADATA\AXIDX_01.ORA'
                            ORA-01115: IO error reading block from file 24 (block # 1)
                            ORA-27072: File I/O error
                            OSD-04006: ReadFile() failure, unable to read from file
                            O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.

                            Mon Jun 07 16:41:06 2010
                            CKPT: terminating instance due to error 1242
                            Mon Jun 07 16:41:10 2010
                            Errors in file g:\oracle\product\10.2.0\admin\DB\bdump\DB_pmon_2736.trc:
                            ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
                            • 11. Re: ORA-01113: file 26 needs media recovery
                              sb92075
                              O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.
                              OS error is NOT caused by Oracle.
                              Do any clues exist in OS system log?
                              Without backup, this DB may be not recoverable.
                              • 12. Re: ORA-01113: file 26 needs media recovery
                                603447
                                sb92075 wrote:
                                O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.
                                OS error is NOT caused by Oracle.
                                Do any clues exist in OS system log?
                                Without backup, this DB may be not recoverable.
                                I use cold backup cause the datafiles are dirrectly stored on a tape to reduce disk usage. Can you send me backup best practice?
                                • 13. Re: ORA-01113: file 26 needs media recovery
                                  sb92075
                                  http://www.oracle.com/pls/db102/portal.portal_db?selected=3#index-BAC
                                  • 14. Re: ORA-01113: file 26 needs media recovery
                                    Aman....
                                    It appears to be an o/s level error. What's your system admin guys are saying about it? Did you ask them to confirm your media is not having any corruptions? You can DBVERIFY on the file and see what it says? If there would be an block corruption , it would report it. But the corruption won't go away, even after recovery , if your base media is corrupted. So you should recheck it for sure.

                                    HTH
                                    Aman....
                                    1 2 Previous Next