This discussion is archived
7 Replies Latest reply: Dec 4, 2012 7:17 AM by Acooper RSS

ORA-01589 must use RESETLOGS option for database open

977872 Newbie
Currently Being Moderated
I am running 10.1.0 and the database went down and I have been trying to get it started again. I have worked through several errors but stuck on this one.
When trying to run startup I get this error ORA-01589 must use RESETLOGS or NORESETLOGS option for database open.

Thanks,
Tim
  • 1. Re: ORA-01589 must use RESETLOGS option for database open
    Balazs Papp Expert
    Currently Being Moderated
    974869 wrote:
    I am running 10.1.0 and the database went down and I have been trying to get it started again. I have worked through several errors but stuck on this one.
    When trying to run startup I get this error ORA-01589 must use RESETLOGS or NORESETLOGS option for database open.

    Thanks,
    Tim
    and which part of the error you can not understand?

    use one of the following:

    alter database open noresetlogs;
    alter database open resetlogs;
  • 2. Re: ORA-01589 must use RESETLOGS option for database open
    mseberg Guru
    Currently Being Moderated
    Hello;

    Sounds like either an incomplete or backup control file recovery has been performed.

    Find the datafile(s) :

    SELECT FILE#,STATUS, FUZZY FROM V$DATAFILE_HEADER;

    If you need to recover one option is :

    SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

    ALTER DATABASE OPEN RESETLOGS;

    Best Regards

    mseberg
  • 3. Re: ORA-01589 must use RESETLOGS option for database open
    977872 Newbie
    Currently Being Moderated
    This is what I got.
    SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

    ALTER DATABASE OPEN RESETLOGS;ORA-00279: change 222687640 generated at 07/20/2012 06:58:56 needed for thread
    1
    ORA-00289: suggestion :
    /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/ORCL/archivelog/2012_12_
    04/o1_mf_1_6365_%u_.arc
    ORA-00280: change 222687640 for thread 1 is in sequence #6365


    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    ORA-00308: cannot open archived log
    '/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/ORCL/archivelog/2012_12
    _04/o1_mf_1_6365_%u_.arc'
    ORA-27037: unable to obtain file status
    Linux Error: 2: No such file or directory
    Additional information: 3


    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01194: file 2 needs more recovery to be consistent
    ORA-01110: data file 2:
    '/u01/app/oracle/product/10.2.0/oradata/orcl/undotbs01.dbf'
  • 4. Re: ORA-01589 must use RESETLOGS option for database open
    mseberg Guru
    Currently Being Moderated
    Hello again;

    alter database datafile 2 offline;
    alter database open;

    At this point I would consider creating a new Undo tablespace

    drop tablespace undo including contents and datafiles;
    create undo tablespace <your_name> datafile <full_path_and_name> size;

    alter system set undo_tablespace = <your_name> scope=both;

    Example

    http://nuwankaluwila.blogspot.com/2009/03/shrink-undo-tablespace-in-10g.html#!/2009/03/shrink-undo-tablespace-in-10g.html

    Best Regards

    mseberg
  • 5. Re: ORA-01589 must use RESETLOGS option for database open
    ShishirTekadeR Explorer
    Currently Being Moderated
    please try below steps:--

    SQL> select * from v$logfile;

    GROUP# STATUS TYPE MEMBER IS_
    ---------- ------- ------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---
    4 ONLINE /restore-11/POS/posebu-db/corppos-data02/oradata/EBUPOSPR/redo4_01.log NO
    4 ONLINE /restore-11/POS/posebu-db/corppos-data01/oradata/EBUPOSPR/redo4_02.log NO
    3 ONLINE /restore-11/POS/posebu-db/corppos-data02/oradata/EBUPOSPR/redo3_01.log NO
    3 ONLINE /restore-11/POS/posebu-db/corppos-data01/oradata/EBUPOSPR/redo3_02.log NO
    2 ONLINE /restore-11/POS/posebu-db/corppos-index01/oradata/EBUPOSPR/redo2_01.log NO
    2 ONLINE /restore-11/POS/posebu-db/corppos-data02/oradata/EBUPOSPR/redo02_02.log NO
    1 ONLINE /restore-11/POS/posebu-db/corppos-data01/oradata/EBUPOSPR/redo1_01.log NO
    1 ONLINE /restore-11/POS/posebu-db/corppos-index01/oradata/EBUPOSPR/redo1_02.log NO

    8 rows selected.

    SQL> select * from v$log;

    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
    ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
    1 1 0 1073741824 2 YES UNUSED 0
    4 1 1 1073741824 2 NO CURRENT 7.6915E+12 21-SEP-11
    3 1 0 1073741824 2 YES UNUSED 0
    2 1 0 1073741824 2 YES UNUSED 0

    SQL> recover database until cancel using backup controlfile;
    ORA-00279: change 7691479663660 generated at 09/21/2011 05:10:48 needed for thread 1
    ORA-00289: suggestion : /restore-11/POS/posebu-db/corppos-archive01/EBUPOSPR/1_1_762412057.arc
    ORA-00280: change 7691479663660 for thread 1 is in sequence #1


    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    /restore-11/POS/posebu-db/corppos-data02/oradata/EBUPOSPR/redo4_01.log
    Log applied.
    Media recovery complete.
    SQL> select hxfil file_id, fhtnm tablespace_name from x$kcvfh;

    FILE_ID TABLESPACE_NAME
    ---------- ------------------------------
    1 SYSTEM
    2 UNDOTBS
    3 SYSAUX
    4 USERS

    SQL> select file#, checkpoint_change#, checkpoint_time, error from v$datafile_header;

    FILE# CHECKPOINT_CHANGE# CHECKPOIN ERROR
    ---------- ------------------ --------- -----------------------------------------------------------------
    1 7.6915E+12 21-SEP-11
    2 7.6915E+12 21-SEP-11
    3 7.6915E+12 21-SEP-11
    4 7.6915E+12 21-SEP-11

    SQL> col CHECKPOINT_CHANGE# for 9999999999999
    SQL> l
    1* select file#, checkpoint_change#, checkpoint_time, error from v$datafile_header
    SQL> /

    FILE# CHECKPOINT_CHANGE# CHECKPOIN ERROR
    ---------- ------------------ --------- -----------------------------------------------------------------
    1 7691479663749 21-SEP-11
    2 7691479663749 21-SEP-11
    3 7691479663749 21-SEP-11
    4 7691479663749 21-SEP-11


    SQL> select hxfil file_id, fhscn scn, fhthr thread, fhrba_seq sequence, fhsta status from x$kcvfh;

    FILE_ID SCN THREAD SEQUENCE STATUS
    ---------- ---------------- ---------- ---------- ----------
    1 7691479663749 1 1 8192
    2 7691479663749 1 1 0
    3 7691479663749 1 1 0
    4 7691479663749 1 1 0

    24 rows selected.

    SQL> select * from v$recover_file;

    no rows selected


    SQL> alter database open resetlogs;

    Database altered.

    +++++++++++++++++++++++++++++++++++++++++++++++++

    Best regards
    Shishir Tekade.
  • 6. Re: ORA-01589 must use RESETLOGS option for database open
    977872 Newbie
    Currently Being Moderated
    This is what I got.
    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-01194: file 2 needs more recovery to be consistent
    ORA-01110: data file 2:
    '/u01/app/oracle/product/10.2.0/oradata/orcl/undotbs01.dbf'
  • 7. Re: ORA-01589 must use RESETLOGS option for database open
    Acooper Explorer
    Currently Being Moderated
    You have been given this answer already but here's the MOS doc for it.


    RECOVERING FROM A LOST DATAFILE IN A UNDO TABLESPACE [ID 1013221.6]

Legend

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