This discussion is archived
1 2 Previous Next 16 Replies Latest reply: May 23, 2013 1:23 AM by GSun RSS

Media recovery of file 1

GSun Newbie
Currently Being Moderated
Hello Oracle Forum; I have ORA-01033: Oracle initialization or shutdown in progress and ORA-01113: file 1 needs media recovery errors, i
tried using recovery manager to no avail, all I got after a long wait
cataloging files... is ORA-19870: ERROR READING BACKUP PIECE, ORA-19501: READ ERROR ON FILE, ORA-27070: ASYNC READ/WRITE FAILED, OSD-04016: ERROR QUEUING AN ASYNCHRONOUS I/O REQUEST...then...failover to previous backup...then RMAN-00571; 00569; 03002; 06026; 9 06023:::I have exhausted all the tips I know...PLS HELP
  • 1. Re: Media recovery of file 1
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You seem to have I/O errors. Maybe the storage isn't responding. Are all your database backups on disks -- on the same SAN as the database itself ?
    Have you checked with the SystemAdmin / StorageAdmin why the storage isn't responding.


    But you must also identify why "ORA-01113: file 1 needs media recovery" has occurred ? What was happening before that ? Was there a database shutdown / crash ? Was there a storage crash ? Was the database in BEGIN BACKUP mode ?

    Hemant K Chitale
  • 2. Re: Media recovery of file 1
    Alvaro Pro
    Currently Being Moderated
    It looks like you have a faulty storage.

    Can you please upload the alert log from the time the first ORA-01113: file 1 needs media recovery errors appeared?

    My guess is the storage crashed leaving the datafile 1 with transactions that were not yet written to it halfway done (i.e. recovery needed), which then crashed DBWR or LGWR perhaps which then crashed your instance (Oracle not available error) . Now you're trying to recover from somewhere where the storage is also unavailable.

    We need alert.log to know more.

    Also, please post what commands are you using in recovery manager.
  • 3. Re: Media recovery of file 1
    GSun Newbie
    Currently Being Moderated
    Thanks for yyour reply Hemant KC; The storage is not responding. All my database backups are on disk-- Yes. I am the sysadmin. There were several power outages and our UPS was faulty. Don't really know the BEGIN BACKUP mode I'm actually a PL/SQL developer running APEX
  • 4. Re: Media recovery of file 1
    GSun Newbie
    Currently Being Moderated
    Thanks for your reply Alvaro. I think I do have a faulty storage! Ok, I will upload the alert.log file ASAP for your perusal. This are the commands I used in recovery manager:
    rman target /
    restore controlfile from autobackup;
    alter database mount;
    recover database;
    alter database open resetlogs;
  • 5. Re: Media recovery of file 1
    GSun Newbie
    Currently Being Moderated
    I have been trying to send a part of the alert.log file without succes...it exceeds 30000 characters and I can't find any attachment link

    Edited by: GSun on May 15, 2013 12:16 AM
  • 6. Re: Media recovery of file 1
    Alvaro Pro
    Currently Being Moderated
    Put the alert log on code tags for better view.

    You don't have to upload the whole alert.log, search for the time where the first ORA-01113 error ocurred on datafile 1 and upload some lines before and after that.

    Also, mount the database and upload the following by RMAN:
     report schema
    and
    list failure
    Also check the location of where your archivelogs are stored for the database as well as the online redo logs, see if it's available. Are they stored on ASM or File System ?

    Check the location of your backups with command (RMAN) :
     list backup of datafile 1 
    See if the location of the backup is also available.

    Basically what you need to do now:

    1. Make sure that it was storage problem through alert log. Makes sure all the storage locations are accessible again.

    2. Recover database with archivelogs / redo logs from the crash

    3. Open the database

    Word of caution: Beware of using open resetlogs that way, you may end up losing data. Only use it if you know exactly why.
  • 7. Re: Media recovery of file 1
    jgarry Guru
    Currently Being Moderated
    What version are you on? If on 11.x.x.x, post the relevant part of the text version of the alert log, rather than the xml version.
  • 8. Re: Media recovery of file 1
    GSun Newbie
    Currently Being Moderated
    The errors start from here:

    1814 redo blocks read, 278 data blocks need recovery
    Sun May 12 17:27:48 2013
    Started redo application at
    Thread 1: logseq 7326, block 3144
    Sun May 12 17:27:54 2013
    Recovery of Online Redo Log: Thread 1 Group 1 Seq 7326 Reading mem 0
    Mem# 0 errs 0: C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_5FV7D7OZ_.LOG
    Sun May 12 17:27:54 2013
    Aborting crash recovery due to error 1115
    Sun May 12 17:27:54 2013
    Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_1376.trc:
    ORA-01115: IO error reading block from file 3 (block # 65519)
    ORA-01110: data file 3: 'C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF'
    ORA-27070: async read/write failed
    OSD-04016: Error queuing an asynchronous I/O request.
    O/S-Error: (OS 23) Data error (cyclic redundancy check).

    ORA-1115 signalled during: alter database open...
    Sun May 12 17:42:06 2013
    db_recovery_file_dest_size of 10240 MB is 3.34% used. This is a
    user-specified limit on the amount of space that will be used by this
    database for recovery-related files, and does not reflect the amount of
    space available in the underlying filesystem or ASM diskgroup.
    Dump file c:\oraclexe\app\oracle\admin\xe\bdump\alert_xe.log
  • 9. Re: Media recovery of file 1
    Alvaro Pro
    Currently Being Moderated
    Yup, this is enough to prove that your storage is the culprit here. You need to fix it before attempting to recover the database now:

    ORA-01115: IO error reading block from file 3 (block # 65519)
    ORA-01110: data file 3: 'C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF'
    ORA-27070: async read/write failed
    OSD-04016: Error queuing an asynchronous I/O request.
    O/S-Error: (OS 23) Data error (cyclic redundancy check).
  • 10. Re: Media recovery of file 1
    GSun Newbie
    Currently Being Moderated
    Sun May 12 19:26:53 2013
    Recovery of Online Redo Log: Thread 1 Group 1 Seq 7326 Reading mem 0
    Mem# 0 errs 0: C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_5FV7D7OZ_.LOG
    Sun May 12 19:27:01 2013
    Media Recovery failed with error 1115
    ORA-283 signalled during: ALTER DATABASE RECOVER DATABASE ...
    db_recovery_file_dest_size of 10240 MB is 1.10% used. This is a
    user-specified limit on the amount of space that will be used by this
    database for recovery-related files, and does not reflect the amount of
    space available in the underlying filesystem or ASM diskgroup.
    Sun May 12 19:27:02 2013
    ALTER DATABASE OPEN
    ORA-1113 signalled during: ALTER DATABASE OPEN...
    Sun May 12 19:27:02 2013
    ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLEXE\ORADATA\XE\TEMP.DBF'
    SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
    ORA-1109 signalled during: ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLEXE\ORADATA\XE\TEMP.DBF'
    SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M...
    Sun May 12 19:32:58 2013
    ARCH: Possible network disconnect with primary database
    Dump file c:\oraclexe\app\oracle\admin\xe\bdump\alert_xe.log
  • 11. Re: Media recovery of file 1
    GSun Newbie
    Currently Being Moderated
    How do I fix these issues? I don't always remember my vow to develop DBA skills until I run into such problems..please help me out
  • 12. Re: Media recovery of file 1
    jgarry Guru
    Currently Being Moderated
    Not a DBA issue, you need to fix the hardware first. If you can then read the files without error, no problem, if not, recover from backup.
  • 13. Re: Media recovery of file 1
    GSun Newbie
    Currently Being Moderated
    The hardware is working fine but the recovery is still not successful! How can I read the files to confirm they are ok?
  • 14. Re: Media recovery of file 1
    John Stegeman Oracle ACE
    Currently Being Moderated
    If this is a database that is at all important to you, I'd recommend that you step away from the keyboard right now and contact Support. You don't want to be doing anything that could prevent you from being able to recover (that's not saying you will be able to recover, but you don't want to do anything that can hurt you, and if you don't know what you're doing, that's very easy to do).
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points