Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Location
    Buenos Aires, Argentina
    Posts
    86

    Unanswered: ORA-1113 file X needs media recovery

    Hi,
    I add a datafile to a tablespace. After that I put it offline. When I try to put it back online I get an "ora-1113: file X needs media recovery".
    First of all, why did I get this error? Is this a bug (It could be, according Metalink)?
    Can I recover the datafile without shuting down th DB?
    This is 24x7 production environment.
    Ora8174 - 64bits, HP-UX11
    Thanks in advance.

    Manf

  2. #2
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49
    While the Tablespace was off line the SCN was not updated now that you are putting it on line the SCN for that data file is in the past. You should be able to just say 'alter database recover' and it will look through the redo logs and see there are no transactions for that table space and it can bring it's SCN up to date. If it has been off line for a long time you may need archive logs.

  3. #3
    Join Date
    Dec 2003
    Location
    Buenos Aires, Argentina
    Posts
    86
    But to do an "alter db..." should I shutdown the DB?
    One other thing: I'm sure that the datafile does not have any pending transaction, and some archive log were backed up to tape. Should I need to restore them back to disk?
    Thanks in advance

    Manf

  4. #4
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    I do not think u need archive log to be restored. Just issue alter db command and u do not need to shutdown the db instance also.

    Kamesh
    - KR

  5. #5
    Join Date
    Dec 2003
    Location
    Buenos Aires, Argentina
    Posts
    86
    Just for U to know.
    When I perform the recover datafile, Oracle specify the log number:

    SVRMGR> RECOVER DATAFILE '/oradbf1/dbs14.dbf';
    ORA-00279: change 110791619 generated at 07/21/2004 16:30:50 needed for thread 1
    ORA-00289: suggestion : /archivelog/arch_1_335767.arc
    ORA-00280: change 110791619 for thread 1 is in sequence #335767
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    Regards,
    Manf

  6. #6
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49
    you will need to get that archive from whereever you have to archive logs and also get all from that point on up to current and have them available. If they are already in the directory you can just say auto and Oracle will grab the archive from that directory and keep running until it updates that tablespaces SCN.

  7. #7
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Quote Originally Posted by Manf
    Hi,
    I add a datafile to a tablespace. After that I put it offline. When I try to put it back online I get an "ora-1113: file X needs media recovery".
    First of all, why did I get this error? Is this a bug (It could be, according Metalink)?
    Can I recover the datafile without shuting down th DB?
    This is 24x7 production environment.
    Ora8174 - 64bits, HP-UX11
    Thanks in advance.

    Manf
    Hi you can do following without shuting down the database

    (1) ALTER TABLESPACE <tablespace name that the file belongs>

    (2) RESTORE the backed up file (s) from backup location

    (3) RECOVER TABLESPACE

    (4) ALTER TABLESPACE <ts_name> ONLINE;

    thanks,
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •