HELP on recovery with lost datafile
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: HELP on recovery with lost datafile

  1. #1
    Join Date
    Nov 2000
    Posts
    212
    I lost datafiles of rollback segment of Test DB and it is closed now => I can not drop the tablespace.

    Database was not in archive log mode, but lossing of last changes is OK.

    What is the best way to get database opened?

    P.S.
    So far I used 'CREATE CONTROLFILE' with entries for lost files removed, but got an error on 'ALTER DATABASE OPEN RESETLOGS':

    SQL> ALTER DATABASE OPEN RESETLOGS;
    ALTER DATABASE OPEN RESETLOGS
    *
    ERROR at line 1:
    ORA-01194: file 1 needs more recovery to be consistent
    ORA-01110: data file 1: 'G:\ORACLE\ORADATA\ORCC\SYSTEM01.DBF'




  2. #2
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    When you don't mind loosing recent data, if you have last night's backup, it might be OK to restore it.
    Thanks
    Kishore Kumar

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Since you aren't in archivelog mode, your best bet is to restore from your last cold backup.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Nov 2000
    Posts
    212
    I have no cold backup (DB crashed before it was made but after it was created and application schema installed)
    So is there a method to get rid of this error
    "ORA-01194: file 1 needs more recovery to be consistent "?
    What does is actually indicate?

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    have you tried alter database datafile xxxxx offline drop?

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    do not do what pando suggested unless otherwise you want to blow the tablespace off.

    ***ORACLENOTE***

    "alter database datafile xxxxx offline drop" is not meant for removing the datafile at all. Its meant for removing datafile with the intention of dropping the tablespace.

    ******************

    I am sorry to say guys. Oracle Don't mention this where the syntax is given as caution and lot people confused with this syntax.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well if lnd didnt shutdown the database with abort option he might as well drop the datafile with offline drop option then open the database and drop the rbs tablespace, at least with this he might have a chance to open the database...

  8. #8
    Join Date
    Nov 2000
    Posts
    212
    Actually, the database (and OS) crashed.
    The status is that I have all datafiles+control file except of rollback tablespace.

    P.S. Generally this recovery is not a big deal, I just want to save some work and make some things more clear regarding db recovery.
    What I do not understand is
    why Oracle requires recovery for datafile when I try to open database. What in general should happen for this condition?
    All considerations including some view into Oracle internals is welcommed.

  9. #9
    Join Date
    Jan 2001
    Posts
    191
    startup mount
    ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\ORCL\RBS01.DBF' OFFLINE DROP;
    alter database open;
    DROP TABLESPACE RBS INCLUDING CONTENTS;

    CREATE TABLESPACE RBS
    DATAFILE 'D:\ORACLE\ORADATA\ORCL\RBS02.DBF'
    .........
    .........

    shutdown immediate

    rename file D:\ORACLE\ORADATA\ORCL\RBS02.DBF to D:\ORACLE\ORADATA\ORCL\RBS01.DBF on NT

    startup mount

    alter database rename file
    'D:\ORACLE\ORADATA\ORCL\RBS02.DBF'
    to
    'D:\ORACLE\ORADATA\ORCL\RBS01.DBF'
    alter database open;



  10. #10
    Join Date
    Jan 2001
    Posts
    191
    By the way, do a cold backup before you try.

Posting Permissions

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



Click Here to Expand Forum to Full Width