FAQ
hi all,
We have got one oracle 9i rel 2 on windows 2000 running in no-archive log
mode. the problem is one of the dba of the other team has deleted some of
the dbf files while the database was running and tablespace was online.
after deleting the file he re-started the database which while opening throw
error saying that file needs recovery. I was told to do this . I have
created the dbf files also but while recovering the dbfs using RECOVER
DATAFILE ' 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA'; command its asking for
the archive files . output is below mentioned :

SQL> recover datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA';
ORA-00279: change 38062424 generated at 05/13/2005 00:52:05 needed for
thread 1
ORA-00289: suggestion : F:\ORACLE\ORA92\RDBMS\ARC00397.001
ORA-00280: change 38062424 for thread 1 is in sequence #397

Specify log: {=suggested | filename | AUTO | CANCEL}
I dont know why its asking for the archive files.??? Btw the team who is
owning the database has not taken any backup. If anyone has got the problem
like this, plz. share ur experience so that I can perform this.
TIA

--
Thanks & Regards,
T. Onkar Nath
OneAPPS Enterprise Technology Pvt. Ltd.
to_onkar_at_yahoo.com
onkarnath.tiwary_at_gmail.com

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Mladen Gogala at Sep 20, 2005 at 7:17 am

    On 09/20/2005 07:55:06 AM, Onkar N Tiwary wrote:
    hi all,
    We have got one oracle 9i rel 2 on windows 2000 running in no-archive log
    mode.
    Well, now you know what archive log mode is for.
    the problem is one of the dba of the other team has deleted some of
    the dbf files while the database was running and tablespace was online.
    Kiss it goodbye. Your database is gone with the wind.
    after deleting the file he re-started the database which while opening throw
    error saying that file needs recovery. I was told to do this . I have
    created the dbf files also but while recovering the dbfs using RECOVER
    DATAFILE ' 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA'; command its asking for
    the archive files . output is below mentioned :


    SQL> recover datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA';
    ORA-00279: change 38062424 generated at 05/13/2005 00:52:05 needed for
    thread 1
    ORA-00289: suggestion : F:\ORACLE\ORA92\RDBMS\ARC00397.001
    ORA-00280: change 38062424 for thread 1 is in sequence #397


    Specify log: {=suggested | filename | AUTO | CANCEL}
    I dont know why its asking for the archive files.???
    Because it needs them to recover the file.
    Btw the team who is
    owning the database has not taken any backup.
    This is great! You don't have any backup and the data is gone!
    Recovery is usually done from backup. Don't panic, always have
    the Guide and a towel. There will be Heart of Gold, space ship with
    an infinite improbability drive, to help you out. It is the only
    thing that can do that.
    If anyone has got the problem
    like this, plz. share ur experience so that I can perform this.
    Solution can be found by contacting www.dice.com or www.monster.com.
    Have your resume ready. In the mean time, learn to play the blame game.
    Study Wally's moves, he's the master. More about Wally can be found at
    http://www.dilbert.com.
  • Onkarnath Tiwari at Sep 20, 2005 at 7:41 am
    THANX for ur response but it's the other team dba as I had said. As far as
    data is concerned the team is ready to loose the data but the problem is
    database is not opening .How to get it open?????

    Thanks & Regards,


    T.Onkar Nath
    Oracle DBA
    Extn. : 2269
    email : onkar.tiwari_at_in.ness.com

    -----Original Message-----
    From: Mladen Gogala
    Sent: Tuesday, September 20, 2005 5:43 PM
    To: onkarnath.tiwary_at_gmail.com
    Cc: oracle-l_at_freelists.org
    Subject: Re: Database recovery problem
    On 09/20/2005 07:55:06 AM, Onkar N Tiwary wrote:
    hi all,
    We have got one oracle 9i rel 2 on windows 2000 running in no-archive log
    mode.
    Well, now you know what archive log mode is for.
    the problem is one of the dba of the other team has deleted some of
    the dbf files while the database was running and tablespace was online.
    Kiss it goodbye. Your database is gone with the wind.
    after deleting the file he re-started the database which while opening throw
    error saying that file needs recovery. I was told to do this . I have
    created the dbf files also but while recovering the dbfs using RECOVER
    DATAFILE ' 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA'; command its asking for
    the archive files . output is below mentioned :


    SQL> recover datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA';
    ORA-00279: change 38062424 generated at 05/13/2005 00:52:05 needed for
    thread 1
    ORA-00289: suggestion : F:\ORACLE\ORA92\RDBMS\ARC00397.001
    ORA-00280: change 38062424 for thread 1 is in sequence #397


    Specify log: {=suggested | filename | AUTO | CANCEL}
    I dont know why its asking for the archive files.???
    Because it needs them to recover the file.
    Btw the team who is
    owning the database has not taken any backup.
    This is great! You don't have any backup and the data is gone!
    Recovery is usually done from backup. Don't panic, always have
    the Guide and a towel. There will be Heart of Gold, space ship with
    an infinite improbability drive, to help you out. It is the only
    thing that can do that.
    If anyone has got the problem
    like this, plz. share ur experience so that I can perform this.
    Solution can be found by contacting www.dice.com or www.monster.com.
    Have your resume ready. In the mean time, learn to play the blame game.
    Study Wally's moves, he's the master. More about Wally can be found at
    http://www.dilbert.com.
  • Mercadante, Thomas F (LABOR) at Sep 20, 2005 at 7:28 am
    Onkar,



    It is asking for an archive file because the dummy file that you created
    is not an Oracle database file. So it is trying to bring it up to date.
    Even though you are not running in archivelog mode, Oracle still acts
    the same way - that is, it is keeping track of what would be the correct
    archivelog file even though it did not write them out to disk.



    You have one option:



    Startup mount;

    Alter datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline.

    Alter database open.



    You will be able to export all of the data in the database except what
    was stored in this data file. This will allow you to import it into
    another database.



    So all of your data is not gone - just the stuff in this
    file/tablespace. Everything else has been lost.



    Good Luck!



    Tom

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Onkar N Tiwary
    Sent: Tuesday, September 20, 2005 7:55 AM
    To: oracle-l_at_freelists.org
    Subject: Database recovery problem



    hi all,



    We have got one oracle 9i rel 2 on windows 2000 running in no-archive
    log mode. the problem is one of the dba of the other team has deleted
    some of the dbf files while the database was running and tablespace was
    online. after deleting the file he re-started the database which while
    opening throw error saying that file needs recovery. I was told to do
    this . I have created the dbf files also but while recovering the dbfs
    using RECOVER DATAFILE ' 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA';
    command its asking for the archive files . output is below mentioned :

    SQL> recover datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA';
    ORA-00279: change 38062424 generated at 05/13/2005 00:52:05 needed for
    thread 1
    ORA-00289: suggestion : F:\ORACLE\ORA92\RDBMS\ARC00397.001
    ORA-00280: change 38062424 for thread 1 is in sequence #397

    Specify log: {=suggested | filename | AUTO | CANCEL}

    I dont know why its asking for the archive files.??? Btw the team who is
    owning the database has not taken any backup. If anyone has got the
    problem like this, plz. share ur experience so that I can perform this.



    TIA

    --
    Thanks & Regards,
    T. Onkar Nath
    OneAPPS Enterprise Technology Pvt. Ltd.
    to_onkar_at_yahoo.com
    onkarnath.tiwary_at_gmail.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Michael McMullen at Sep 20, 2005 at 7:42 am
    almost correct
    startup mount;
    alter database datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline
    drop;
    alter database open;
    if the database still wants to recover more files you need to drop them
    also, if it wants to recover system it's time to learn irish
    say
    WHALE

    OIL

    BEEF

    HOOKED

    faster and faster with an bit of an irish lilt.
    back to the problem at hand.
    drop tablespace tablespace_of_the_above_datafile;
    then reload your data because you were in noarchivelog mode so you must be
    able to reload your data.

    then take this opportunity to crank up your buffer cache to as big as
    possible -- this is a joke, no flames please.

    Original Message -----
    From: Mercadante, Thomas F (LABOR)
    To: onkarnath.tiwary_at_gmail.com; oracle-l_at_freelists.org
    Sent: Tuesday, September 20, 2005 8:26 AM
    Subject: RE: Database recovery problem

    Onkar,

    It is asking for an archive file because the dummy file that you created is
    not an Oracle database file. So it is trying to bring it up to date. Even
    though you are not running in archivelog mode, Oracle still acts the same
    way - that is, it is keeping track of what would be the correct archivelog
    file even though it did not write them out to disk.

    You have one option:

    Startup mount;
    Alter datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline.
    Alter database open.

    You will be able to export all of the data in the database except what was
    stored in this data file. This will allow you to import it into another
    database.

    So all of your data is not gone - just the stuff in this file/tablespace.
    Everything else has been lost.

    Good Luck!

    Tom

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Onkar N Tiwary
    Sent: Tuesday, September 20, 2005 7:55 AM
    To: oracle-l_at_freelists.org
    Subject: Database recovery problem

    hi all,

    We have got one oracle 9i rel 2 on windows 2000 running in no-archive log
    mode. the problem is one of the dba of the other team has deleted some of
    the dbf files while the database was running and tablespace was online.
    after deleting the file he re-started the database which while opening throw
    error saying that file needs recovery. I was told to do this . I have
    created the dbf files also but while recovering the dbfs using RECOVER
    DATAFILE ' 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA'; command its asking for
    the archive files . output is below mentioned :

    SQL> recover datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA';
    ORA-00279: change 38062424 generated at 05/13/2005 00:52:05 needed for
    thread 1
    ORA-00289: suggestion : F:\ORACLE\ORA92\RDBMS\ARC00397.001
    ORA-00280: change 38062424 for thread 1 is in sequence #397

    Specify log: {=suggested | filename | AUTO | CANCEL}
    I dont know why its asking for the archive files.??? Btw the team who is
    owning the database has not taken any backup. If anyone has got the problem
    like this, plz. share ur experience so that I can perform this.

    TIA

    --
    Thanks & Regards,
    T. Onkar Nath
    OneAPPS Enterprise Technology Pvt. Ltd.

    to_onkar_at_yahoo.com
    onkarnath.tiwary_at_gmail.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Onkarnath Tiwari at Sep 20, 2005 at 7:53 am
    SQL> ALTER DATABASE OPEN;

    ALTER DATABASE OPEN

    *
    ERROR at line 1:
    ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

    SQL> ALTER DATABASE OPEN RESETLOGS;

    ALTER DATABASE OPEN RESETLOGS

    *
    ERROR at line 1:
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: 'F:\ORACLE\ORADATA\BODEV\SYSTEM01.DBF'

    This is what I got after putting this file in offline drop mode. Now
    what???? By the way in the mean time after the suggestion of one of my
    colleage I had issued one command i.e.
    Recover database using backup controlfile until cancel;

    Is this command did something wrong???

    Thanks & Regards,


    T.Onkar Nath
    Oracle DBA
    Extn. : 2269
    email : onkar.tiwari_at_in.ness.com

    -----Original Message-----
    From: Michael McMullen
    Sent: Tuesday, September 20, 2005 6:10 PM
    To: onkarnath.tiwary_at_gmail.com; oracle-l_at_freelists.org
    Subject: Re: Database recovery problem

    almost correct
    startup mount;
    alter database datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline
    drop;
    alter database open;
    if the database still wants to recover more files you need to drop them
    also, if it wants to recover system it's time to learn irish
    say
    WHALE

    OIL

    BEEF

    HOOKED

    faster and faster with an bit of an irish lilt.
    back to the problem at hand.
    drop tablespace tablespace_of_the_above_datafile;
    then reload your data because you were in noarchivelog mode so you must be
    able to reload your data.

    then take this opportunity to crank up your buffer cache to as big as
    possible -- this is a joke, no flames please.

    Original Message -----
    From: Mercadante, Thomas F (LABOR)
    To: onkarnath.tiwary_at_gmail.com; oracle-l_at_freelists.org
    Sent: Tuesday, September 20, 2005 8:26 AM
    Subject: RE: Database recovery problem

    Onkar,

    It is asking for an archive file because the dummy file that you created is
    not an Oracle database file. So it is trying to bring it up to date. Even
    though you are not running in archivelog mode, Oracle still acts the same
    way - that is, it is keeping track of what would be the correct archivelog
    file even though it did not write them out to disk.

    You have one option:

    Startup mount;
    Alter datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline.
    Alter database open.

    You will be able to export all of the data in the database except what was
    stored in this data file. This will allow you to import it into another
    database.

    So all of your data is not gone - just the stuff in this file/tablespace.
    Everything else has been lost.

    Good Luck!

    Tom

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Onkar N Tiwary
    Sent: Tuesday, September 20, 2005 7:55 AM
    To: oracle-l_at_freelists.org
    Subject: Database recovery problem

    hi all,

    We have got one oracle 9i rel 2 on windows 2000 running in no-archive log
    mode. the problem is one of the dba of the other team has deleted some of
    the dbf files while the database was running and tablespace was online.
    after deleting the file he re-started the database which while opening throw
    error saying that file needs recovery. I was told to do this . I have
    created the dbf files also but while recovering the dbfs using RECOVER
    DATAFILE ' 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA'; command its asking for
    the archive files . output is below mentioned :

    SQL> recover datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA';
    ORA-00279: change 38062424 generated at 05/13/2005 00:52:05 needed for
    thread 1
    ORA-00289: suggestion : F:\ORACLE\ORA92\RDBMS\ARC00397.001
    ORA-00280: change 38062424 for thread 1 is in sequence #397

    Specify log: {=suggested | filename | AUTO | CANCEL}
    I dont know why its asking for the archive files.??? Btw the team who is
    owning the database has not taken any backup. If anyone has got the problem
    like this, plz. share ur experience so that I can perform this.

    TIA

    --
    Thanks & Regards,
    T. Onkar Nath
    OneAPPS Enterprise Technology Pvt. Ltd.
    to_onkar_at_yahoo.com
    onkarnath.tiwary_at_gmail.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Michael McMullen at Sep 20, 2005 at 7:54 am
    almost correct
    startup mount;
    alter database datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline
    drop;
    alter database open;
    if the database still wants to recover more files you need to drop them
    also, if it wants to recover system it's time to learn irish
    say
    WHALE

    OIL

    BEEF

    HOOKED

    faster and faster with an bit of an irish lilt.
    back to the problem at hand.
    drop tablespace tablespace_of_the_above_datafile;
    then reload your data because you were in noarchivelog mode so you must be
    able to reload your data.

    then take this opportunity to crank up your buffer cache to as big as
    possible -- this is a joke, no flames please.

    Original Message -----
    From: Mercadante, Thomas F (LABOR)
    To: onkarnath.tiwary_at_gmail.com; oracle-l_at_freelists.org
    Sent: Tuesday, September 20, 2005 8:26 AM
    Subject: RE: Database recovery problem

    Onkar,

    It is asking for an archive file because the dummy file that you created is
    not an Oracle database file. So it is trying to bring it up to date. Even
    though you are not running in archivelog mode, Oracle still acts the same
    way - that is, it is keeping track of what would be the correct archivelog
    file even though it did not write them out to disk.

    You have one option:

    Startup mount;
    Alter datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline.
    Alter database open.

    You will be able to export all of the data in the database except what was
    stored in this data file. This will allow you to import it into another
    database.

    So all of your data is not gone - just the stuff in this file/tablespace.
    Everything else has been lost.

    Good Luck!

    Tom

    Original Message -----
    From: Mercadante, Thomas F (LABOR)
    To: onkarnath.tiwary_at_gmail.com; oracle-l_at_freelists.org
    Sent: Tuesday, September 20, 2005 8:26 AM
    Subject: RE: Database recovery problem

    Onkar,

    It is asking for an archive file because the dummy file that you created is
    not an Oracle database file. So it is trying to bring it up to date. Even
    though you are not running in archivelog mode, Oracle still acts the same
    way - that is, it is keeping track of what would be the correct archivelog
    file even though it did not write them out to disk.

    You have one option:

    Startup mount;
    Alter datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline.
    Alter database open.

    You will be able to export all of the data in the database except what was
    stored in this data file. This will allow you to import it into another
    database.

    So all of your data is not gone - just the stuff in this file/tablespace.
    Everything else has been lost.

    Good Luck!

    Tom
  • Onkarnath Tiwari at Sep 20, 2005 at 9:21 am
    I got my database open. I just took that datafile offline using the command
    told and then taken a backup of the controlfile and issued the command
    recover database using backup controlfile until cancel and on prompting for
    archive log, I passed the online redo log file name with absolute path and
    bingo.. it worked.

    Anyways thanx a million to Tom, Michael & Gogala for all ur response and
    guidance.

    Thanks & Regards,


    T.Onkar Nath
    Oracle DBA
    Extn. : 2269
    email : onkar.tiwari_at_in.ness.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Michael McMullen
    Sent: Tuesday, September 20, 2005 6:22 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Database recovery problem

    almost correct
    startup mount;
    alter database datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline
    drop;
    alter database open;
    if the database still wants to recover more files you need to drop them
    also, if it wants to recover system it's time to learn irish
    say
    WHALE

    OIL

    BEEF

    HOOKED

    faster and faster with an bit of an irish lilt.
    back to the problem at hand.
    drop tablespace tablespace_of_the_above_datafile;
    then reload your data because you were in noarchivelog mode so you must be
    able to reload your data.

    then take this opportunity to crank up your buffer cache to as big as
    possible -- this is a joke, no flames please.

    Original Message -----
    From: Mercadante, Thomas F (LABOR)
    To: onkarnath.tiwary_at_gmail.com; oracle-l_at_freelists.org
    Sent: Tuesday, September 20, 2005 8:26 AM
    Subject: RE: Database recovery problem

    Onkar,

    It is asking for an archive file because the dummy file that you created is
    not an Oracle database file. So it is trying to bring it up to date. Even
    though you are not running in archivelog mode, Oracle still acts the same
    way - that is, it is keeping track of what would be the correct archivelog
    file even though it did not write them out to disk.

    You have one option:

    Startup mount;
    Alter datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline.
    Alter database open.

    You will be able to export all of the data in the database except what was
    stored in this data file. This will allow you to import it into another
    database.

    So all of your data is not gone - just the stuff in this file/tablespace.
    Everything else has been lost.

    Good Luck!

    Tom

    Original Message -----
    From: Mercadante, Thomas F (LABOR)
    To: onkarnath.tiwary_at_gmail.com; oracle-l_at_freelists.org
    Sent: Tuesday, September 20, 2005 8:26 AM
    Subject: RE: Database recovery problem

    Onkar,

    It is asking for an archive file because the dummy file that you created is
    not an Oracle database file. So it is trying to bring it up to date. Even
    though you are not running in archivelog mode, Oracle still acts the same
    way - that is, it is keeping track of what would be the correct archivelog
    file even though it did not write them out to disk.

    You have one option:

    Startup mount;
    Alter datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline.
    Alter database open.

    You will be able to export all of the data in the database except what was
    stored in this data file. This will allow you to import it into another
    database.

    So all of your data is not gone - just the stuff in this file/tablespace.
    Everything else has been lost.

    Good Luck!

    Tom

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Branimir Petrovic at Sep 20, 2005 at 8:08 am
    Try as hard as you may - you can not delete any Oracle data files on
    Windows (should have said - on Windows ONLY) while the database is up
    and running. Windows keeps them locked 'n safe from deletion attempts.
    However, take the tablespace offline and watch its individual data
    files become "fair game" for deletion attempts...


    The story you've been told on how the problem came to be - simply ain't
    true. It takes more than a simple mistake (on Windows that is) to get
    to this point. FYI - it takes *deliberate* aim.


    Branimir


    P.S.


    If there are no recent cold backups (or schema exports) - expect from
    partial to total data loss. If data loss is not acceptable - somebody
    should get "walking papers" over this.



    -----Original Message-----
    From: Onkar N Tiwary
    Sent: Tuesday, September 20, 2005 7:55 AM
    To: oracle-l_at_freelists.org
    Subject: Database recovery problem

    hi all,


    We have got one oracle 9i rel 2 on windows 2000 running in no-archive log
    mode. the problem is one of the dba of the other team has deleted some of
    the dbf files while the database was running and tablespace was online.

    --
    http://www.freelists.org/webpage/oracle-l
  • Paul Drake at Sep 20, 2005 at 11:19 am

    On 9/20/05, Branimir Petrovic wrote:

    Try as hard as you may - you can not delete any Oracle data files on
    Windows (should have said - on Windows ONLY) while the database is up
    and running. Windows keeps them locked 'n safe from deletion attempts.
    However, take the tablespace offline and watch its individual data
    files become "fair game" for deletion attempts...
    The story you've been told on how the problem came to be - simply ain't
    true. It takes more than a simple mistake (on Windows that is) to get
    to this point. FYI - it takes *deliberate* aim.
    Branimir
    Branimir,

    This is not correct when it comes to files mounted on a SAN.
    It is entirely possible to overwrite files owned by a different database
    instance if account separation is not used.
    I know someone that proved this to be true (not saying who that might be)
    via a "create controlfile" statement during a cloning exercise. Lets just
    say that it counted as a backup/restore/recover exercise.

    Paul

    P.S.
    If there are no recent cold backups (or schema exports) - expect from
    partial to total data loss. If data loss is not acceptable - somebody
    should get "walking papers" over this.

    -----Original Message-----
    *From:* Onkar N Tiwary
    *Sent:* Tuesday, September 20, 2005 7:55 AM
    *To:* oracle-l_at_freelists.org
    *Subject:* Database recovery problem

    hi all,
    We have got one oracle 9i rel 2 on windows 2000 running in no-archive log
    mode. the problem is one of the dba of the other team has deleted some of
    the dbf files while the database was running and tablespace was online.
    --
    http://www.freelists.org/webpage/oracle-l
  • Oracle-l-bounce_at_freelists.org at Sep 20, 2005 at 8:15 am
    Onkar,

    Why did you drop the system file? Did this file get deleted also? If
    so, then you are all done. You cannot open the database if the system
    file is gone.

    Your prior email only mentioned the
    D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA file. Michael's and my response
    would work fine if only a data file was deleted. But if the system file
    is gone, then call it a day. Your database is gone.

    Tom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Onkarnath Tiwari
    Sent: Tuesday, September 20, 2005 8:50 AM
    To: 'Michael McMullen'; oracle-l_at_freelists.org
    Subject: RE: Database recovery problem

    SQL> ALTER DATABASE OPEN;

    ALTER DATABASE OPEN

    *
    ERROR at line 1:
    ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

    SQL> ALTER DATABASE OPEN RESETLOGS;

    ALTER DATABASE OPEN RESETLOGS

    *
    ERROR at line 1:
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: 'F:\ORACLE\ORADATA\BODEV\SYSTEM01.DBF'

    This is what I got after putting this file in offline drop mode. Now
    what???? By the way in the mean time after the suggestion of one of my
    colleage I had issued one command i.e.
    Recover database using backup controlfile until cancel;

    Is this command did something wrong???

    Thanks & Regards,


    T.Onkar Nath
    Oracle DBA
    Extn. : 2269
    email : onkar.tiwari_at_in.ness.com

    -----Original Message-----
    From: Michael McMullen
    Sent: Tuesday, September 20, 2005 6:10 PM
    To: onkarnath.tiwary_at_gmail.com; oracle-l_at_freelists.org
    Subject: Re: Database recovery problem

    almost correct
    startup mount;
    alter database datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline
    drop;
    alter database open;
    if the database still wants to recover more files you need to drop them
    also, if it wants to recover system it's time to learn irish
    say
    WHALE

    OIL

    BEEF

    HOOKED

    faster and faster with an bit of an irish lilt.
    back to the problem at hand.
    drop tablespace tablespace_of_the_above_datafile;
    then reload your data because you were in noarchivelog mode so you must
    be
    able to reload your data.

    then take this opportunity to crank up your buffer cache to as big as
    possible -- this is a joke, no flames please.

    Original Message -----
    From: Mercadante, Thomas F (LABOR)
    To: onkarnath.tiwary_at_gmail.com; oracle-l_at_freelists.org
    Sent: Tuesday, September 20, 2005 8:26 AM
    Subject: RE: Database recovery problem

    Onkar,

    It is asking for an archive file because the dummy file that you created
    is
    not an Oracle database file. So it is trying to bring it up to date.
    Even
    though you are not running in archivelog mode, Oracle still acts the
    same
    way - that is, it is keeping track of what would be the correct
    archivelog
    file even though it did not write them out to disk.

    You have one option:

    Startup mount;
    Alter datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline.
    Alter database open.

    You will be able to export all of the data in the database except what
    was
    stored in this data file. This will allow you to import it into another
    database.

    So all of your data is not gone - just the stuff in this
    file/tablespace.
    Everything else has been lost.

    Good Luck!

    Tom

    From: oracle-l-bounce_at_freelists.org

    On Behalf Of Onkar N Tiwary
    Sent: Tuesday, September 20, 2005 7:55 AM
    To: oracle-l_at_freelists.org
    Subject: Database recovery problem

    hi all,

    We have got one oracle 9i rel 2 on windows 2000 running in no-archive
    log
    mode. the problem is one of the dba of the other team has deleted some
    of
    the dbf files while the database was running and tablespace was online.
    after deleting the file he re-started the database which while opening
    throw
    error saying that file needs recovery. I was told to do this . I have
    created the dbf files also but while recovering the dbfs using RECOVER
    DATAFILE ' 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA'; command its asking
    for
    the archive files . output is below mentioned :

    SQL> recover datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA';
    ORA-00279: change 38062424 generated at 05/13/2005 00:52:05 needed for
    thread 1
    ORA-00289: suggestion : F:\ORACLE\ORA92\RDBMS\ARC00397.001
    ORA-00280: change 38062424 for thread 1 is in sequence #397

    Specify log: {=suggested | filename | AUTO | CANCEL}
    I dont know why its asking for the archive files.??? Btw the team who is
    owning the database has not taken any backup. If anyone has got the
    problem
    like this, plz. share ur experience so that I can perform this.

    TIA

    --
    Thanks & Regards,
    T. Onkar Nath
    OneAPPS Enterprise Technology Pvt. Ltd.
    to_onkar_at_yahoo.com
    onkarnath.tiwary_at_gmail.com

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Onkarnath Tiwari at Sep 20, 2005 at 11:49 pm
    Tom,

    I had not deleted the system files but after seeking for ur help, I had
    been suggested by one of my colleague to try to recover the database using
    the backup of controlfile. The steps were :

    after the user deleted the datafiles, I took the backup of controlfile to
    trace.
    then issue the command recover database using backup of controlfile until
    cancel and after that it was giving this error. But just to mention that I
    had not dropped the system files.

    After ur suggestion I had issued the command alter database datafile
    '<>' offline drop; then repeated the steps once again and supplied the
    redo log file name with the path and the database got opened. However I had
    intimated the owner team of the database to take the backup regularly and
    put it in archivelog mode so that recovery of the data could also be
    possible.

    Thanks a million once again for ur help and co-operation

    Thanks & Regards,


    T.Onkar Nath
    Oracle DBA
    Extn. : 2269
    email : onkar.tiwari_at_in.ness.com

    -----Original Message-----
    From: Mercadante, Thomas F (LABOR)

    Sent: Tuesday, September 20, 2005 6:44 PM
    To: onkar.tiwari_at_in.ness.com; Michael McMullen; oracle-l_at_freelists.org
    Subject: RE: Database recovery problem

    Onkar,

    Why did you drop the system file? Did this file get deleted also? If
    so, then you are all done. You cannot open the database if the system
    file is gone.

    Your prior email only mentioned the
    D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA file. Michael's and my response
    would work fine if only a data file was deleted. But if the system file
    is gone, then call it a day. Your database is gone.

    Tom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Onkarnath Tiwari
    Sent: Tuesday, September 20, 2005 8:50 AM
    To: 'Michael McMullen'; oracle-l_at_freelists.org
    Subject: RE: Database recovery problem

    SQL> ALTER DATABASE OPEN;

    ALTER DATABASE OPEN

    *
    ERROR at line 1:
    ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

    SQL> ALTER DATABASE OPEN RESETLOGS;

    ALTER DATABASE OPEN RESETLOGS

    *
    ERROR at line 1:
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: 'F:\ORACLE\ORADATA\BODEV\SYSTEM01.DBF'

    This is what I got after putting this file in offline drop mode. Now
    what???? By the way in the mean time after the suggestion of one of my
    colleage I had issued one command i.e.
    Recover database using backup controlfile until cancel;

    Is this command did something wrong???

    Thanks & Regards,


    T.Onkar Nath
    Oracle DBA
    Extn. : 2269
    email : onkar.tiwari_at_in.ness.com

    -----Original Message-----
    From: Michael McMullen
    Sent: Tuesday, September 20, 2005 6:10 PM
    To: onkarnath.tiwary_at_gmail.com; oracle-l_at_freelists.org
    Subject: Re: Database recovery problem

    almost correct
    startup mount;
    alter database datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline
    drop;
    alter database open;
    if the database still wants to recover more files you need to drop them
    also, if it wants to recover system it's time to learn irish
    say
    WHALE

    OIL

    BEEF

    HOOKED

    faster and faster with an bit of an irish lilt.
    back to the problem at hand.
    drop tablespace tablespace_of_the_above_datafile;
    then reload your data because you were in noarchivelog mode so you must
    be
    able to reload your data.

    then take this opportunity to crank up your buffer cache to as big as
    possible -- this is a joke, no flames please.

    Original Message -----
    From: Mercadante, Thomas F (LABOR)
    To: onkarnath.tiwary_at_gmail.com; oracle-l_at_freelists.org
    Sent: Tuesday, September 20, 2005 8:26 AM
    Subject: RE: Database recovery problem

    Onkar,

    It is asking for an archive file because the dummy file that you created
    is
    not an Oracle database file. So it is trying to bring it up to date.
    Even
    though you are not running in archivelog mode, Oracle still acts the
    same
    way - that is, it is keeping track of what would be the correct
    archivelog
    file even though it did not write them out to disk.

    You have one option:

    Startup mount;
    Alter datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA' offline.
    Alter database open.

    You will be able to export all of the data in the database except what
    was
    stored in this data file. This will allow you to import it into another
    database.

    So all of your data is not gone - just the stuff in this
    file/tablespace.
    Everything else has been lost.

    Good Luck!

    Tom

    From: oracle-l-bounce_at_freelists.org

    On Behalf Of Onkar N Tiwary
    Sent: Tuesday, September 20, 2005 7:55 AM
    To: oracle-l_at_freelists.org
    Subject: Database recovery problem

    hi all,

    We have got one oracle 9i rel 2 on windows 2000 running in no-archive
    log
    mode. the problem is one of the dba of the other team has deleted some
    of
    the dbf files while the database was running and tablespace was online.
    after deleting the file he re-started the database which while opening
    throw
    error saying that file needs recovery. I was told to do this . I have
    created the dbf files also but while recovering the dbfs using RECOVER
    DATAFILE ' 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA'; command its asking
    for
    the archive files . output is below mentioned :

    SQL> recover datafile 'D:\DB\ORADATA\BODEV\NMSANDBOX_DATA.ORA';
    ORA-00279: change 38062424 generated at 05/13/2005 00:52:05 needed for
    thread 1
    ORA-00289: suggestion : F:\ORACLE\ORA92\RDBMS\ARC00397.001
    ORA-00280: change 38062424 for thread 1 is in sequence #397

    Specify log: {=suggested | filename | AUTO | CANCEL}
    I dont know why its asking for the archive files.??? Btw the team who is
    owning the database has not taken any backup. If anyone has got the
    problem
    like this, plz. share ur experience so that I can perform this.

    TIA

    --
    Thanks & Regards,
    T. Onkar Nath
    OneAPPS Enterprise Technology Pvt. Ltd.
    to_onkar_at_yahoo.com
    onkarnath.tiwary_at_gmail.com

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 20, '05 at 6:57a
activeSep 20, '05 at 11:49p
posts12
users8
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase