7 Replies Latest reply: Dec 4, 2012 9:17 AM by Seberg RSS

    ORA-01589 must use RESETLOGS option for database open

    977872
      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
          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
            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
              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
                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
                  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
                    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
                      Seberg
                      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]