4 Replies Latest reply: May 9, 2013 1:55 PM by user12194321 RSS

    alter tablespace online getting the error ORA-01113: file 9 needs media rec


      Oracle version : oracle 10g

      database : no archivelog mode

      we relocated the datafile from one mount to other mount belong to one tablespace by doing tablespace offline and we did alter database command to update the controlfile now if i am unbale to make tablespace online and getting the below error

      alter tablespace DATA online

      SQL Error: ORA-01113: file 9 needs media recovery
      ORA-01110: data file 9: '/database/data/youtele_data01_02.dbf'

      and if i do recover datafile 9 it is asking the archivelog and my database is in no archivelog mode

      really appreciated help

      Prakash GR

      Edited by: user12194321 on May 8, 2013 12:35 PM
        • 1. Re: alter tablespace online getting the error ORA-01113: file 9 needs media rec
          Hi Prakash,

          Can you elaborate a bit on how you moved the datafile?

          From what you've told us so far, you did it with the database opened, putting only the tablespace offline and then moving it. How long was the tablespace offline during this whole operation?

          Here is what I believe happened:

          - You put the tablespace offline while the database was opened. (possibly in immediate mode, thus not applying any dirty blocks to the datafiles)

          - DML kept on being processed, and so the SCN kept advancing.

          - You try to online the datafile, but the datafile is now on an outdated SCN, not in sync with the controlfile and the other datafiles.

          - Controlfile now asks for recover of datafile, but since you are in noarchivelog you cannot bring that datafile to the same SCN as the rest of the database.

          In NOARCHIVELOG mode, it is my opinion that you should have moved your datafile by the other method, shutting down cleanly your database, mounting it, and then moving it with alter database rename file command, thus avoiding your current situation.

          What you can do is try the:

          recover datafile 9;

          And then when he asks for the archive, try giving him the complete path and name of the redologs. It's possible that the CHANGE# required to recover the datafile are still in the redo logs. You can get the names of the redo logs in the MEMBER.V$LOGFILE .

          If that doesn't work, then, oracle needs info from redo logs that have already been re-written and not archived (due to noarchivelog). In that case you have the option of 1)restoring last consistent backup (loss of data) or 2) force the database open inconsistently and then doing a full export/import. That should not be done without assistance from Oracle SUpport.

          I recommend you read MOSC : How To Recover NOARCHIVELOG DB Using The Online Redo Log [ID 186137.1]

          Hope it helps,
          • 2. Re: alter tablespace online getting the error ORA-01113: file 9 needs media rec
            Hemant K Chitale
            How did you set the tablespace Offline ? Did you correctly use "OFFLINE" or "OFFLINE NORMAL" or did you happen to use "OFFLINE IMMEDIATE" ? An Offline Immediate would not have caused a Checkpoint and would require Recovery when attempting to bring the tablespace/datafile online.

            You may be able to use an online redo log as the presumed archivelog .

            Hemant K Chitale
            • 3. Re: alter tablespace online getting the error ORA-01113: file 9 needs media rec
              Pavan DBA
              you might have offline immediate clause during tablespace offline which caused the issue.

              now, when it is asking for archivelog during recovery, try to give name of redo log files. if still redolog files are not over written, then there is a chance that your datafile is recovered and tablespace will be online.

              if that doesn't work, I afraid if you can do online... :-(

              by this, please get importance and enable archivelog mode for your database.
              • 4. Re: alter tablespace online getting the error ORA-01113: file 9 needs media rec
                Thanks alvaro, hemanth, pavan for the inputs

                We did not did offline immediate and we used just offline and we can not give redolog file as it asking log sequence number which some what older

                alter tablespace <name> offline;

                mv source destination

                actually moving the file took some time then we did

                alter database rename file <source> to <destination>;

                then while doing
                alter tablespace <name> online; we got the error ORA-01113 and while doing recovery it is asking the archivelog file which is few sequence number older and as db in no archivelog mode redo logs are overwriten

                and this database is development so we droped the tablespace and recreated and did refresh from production now it is working fine

                Prakash GR