1 2 Previous Next 29 Replies Latest reply: Jan 26, 2010 9:24 AM by user222828 RSS

    ORA-01194: file 1 needs more recovery to be consistent

    user222828
      Hi guys.

      Ran a duplicate that failed after applying full, incr and then about 6 archive logs...
      I just want to get the database open, don't care about losing the data.

      Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
      PL/SQL Release 9.2.0.4.0 - Production
      CORE 9.2.0.3.0 Production
      TNS for Solaris: Version 9.2.0.4.0 - Production
      NLSRTL Version 9.2.0.4.0 - Production

      Tried:

      SQL> recover database;
      ORA-00283: recovery session canceled due to errors
      ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


      SQL> recover database using backup controlfile until cancel;
      ORA-00279: change 1687171004 generated at 01/07/2010 10:58:26 needed for thread
      1
      ORA-00289: suggestion : /u01/arch/vcost/arch_1_3.arc
      ORA-00280: change 1687171004 for thread 1 is in sequence #3


      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
      CANCEL
      ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
      ORA-01194: file 1 needs more recovery to be consistent
      ORA-01110: data file 1: '/u02/oradata/vcost/system01.dbf'


      ORA-01112: media recovery not started


      SQL> recover datafile '/u02/oradata/vcost/system01.dbf';
      ORA-00283: recovery session canceled due to errors
      ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

      How can I get this db open?

      Thanks

      Jamie
        • 1. Re: ORA-01194: file 1 needs more recovery to be consistent
          662100
          Hi,

          As long as the system tablespace needs recovery you can not open your database. You should keep applying logs until SYSTEM is consistent.

          Ioan
          • 2. Re: ORA-01194: file 1 needs more recovery to be consistent
            Pavan Kumar
            Hi,

            Do you have valid backup. ??
            What is the reason of crash - what you have performed. ??
            Does the DB is in Archive log Mode or No aRchive log Mode.. ???
            Can you check whether online redo logs exists or missing, if missing what is the present status of Online redo logs
            from V$log and v$logfile.

            Then we an suggest how to go forward.

            - Pavan Kumar N
            Oracle 9i/10g - OCP
            http://oracleinternals.blogspot.com/
            • 3. Re: ORA-01194: file 1 needs more recovery to be consistent
              mbobak
              Jamie,

              Since you duplicated a database, and have not yet successfully opened the duplicate database, you need to copy archive logs from the duplication source, and apply them.

              How did you do the duplicate? RMAN? If not RMAN, did you put the source database into backup mode before you began? Did you remember to exit backup mode and then apply all archive logs through the one that contained the exit from backup mode?

              It would be very useful if you outlined exact steps you took, so that we can understand exactly what state things are in.

              -Mark
              • 4. Re: ORA-01194: file 1 needs more recovery to be consistent
                user222828
                The duplicate was done from RMAN.
                It successfully applied about 6 archive log backups but then couldn't retrieve one of them from tape.

                It was run from a script so the database was attempted to be opened but failed obviously.

                I assumed I should be able to do recover until cancel and then open with resetlogs... I was performing a PIT so I needed logs up to Dec 24 18:15 and instead I got up to Dec 24 09:15...
                • 5. Re: ORA-01194: file 1 needs more recovery to be consistent
                  Mark D Powell
                  Jamie, in addition to Mark's questions why didn't you just apply the requested log, u01/arch/vcost/arch_1_3.arc?

                  I suspect from the earlier message telling you that you need to preform the recovery using a backup control file that you should be doing a point in time recovery.

                  HTH -- Mark D Powell --
                  • 6. Re: ORA-01194: file 1 needs more recovery to be consistent
                    user222828
                    Because that log doesn't exist.
                    I recreated the control file already from trace and tried that way to no avail...

                    Like I said... all of the data was restored successfully up to a certain point in time... I don't know why I can't just do a recover and then open with resetlogs.

                    I've ran into this scenario in the past where an archive log was selected to be applied to the dup db and it got removed before it got to it. The duplicate crashed and I just opened the db with resetlogs and it came up fine. Not working this time however.
                    • 7. Re: ORA-01194: file 1 needs more recovery to be consistent
                      user222828
                      select * from v$log;

                      GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
                      ---------- ---------- ---------- ---------- ---------- --- ----------------
                      FIRST_CHANGE# FIRST_TIM
                      ------------- ---------
                      1 1 2 314572800 2 NO INACTIVE
                      1687151000 07-JAN-10

                      2 1 3 314572800 2 NO CURRENT
                      1687171003 07-JAN-10

                      3 1 1 314572800 2 NO INACTIVE
                      1687130996 07-JAN-10


                      SQL> select * from v$logfile;

                      GROUP# STATUS TYPE
                      ---------- ------- -------
                      MEMBER
                      --------------------------------------------------------------------------------
                      1 STALE ONLINE
                      /u02/oradata/vcost/redo01b.rdo

                      1 STALE ONLINE
                      /u02/oradata/vcost/redo01.rdo

                      3 STALE ONLINE
                      /u02/oradata/vcost/redo03b.rdo


                      GROUP# STATUS TYPE
                      ---------- ------- -------
                      MEMBER
                      --------------------------------------------------------------------------------
                      3 STALE ONLINE
                      /u02/oradata/vcost/redo03.rdo

                      2 ONLINE
                      /u02/oradata/vcost/redo02b.rdo

                      2 ONLINE
                      /u02/oradata/vcost/redo02.rdo


                      6 rows selected.
                      • 8. Re: ORA-01194: file 1 needs more recovery to be consistent
                        user222828
                        K I got it to a point where the following happens:

                        SQL> startup
                        ORACLE instance started.

                        Total System Global Area 1277136624 bytes
                        Fixed Size 732912 bytes
                        Variable Size 1040187392 bytes
                        Database Buffers 234881024 bytes
                        Redo Buffers 1335296 bytes
                        Database mounted.
                        ORA-01113: file 1 needs media recovery
                        ORA-01110: data file 1: '/u02/oradata/vcost/system01.dbf'


                        SQL> recover database;
                        Media recovery complete.
                        SQL> alter database open;
                        alter database open
                        *
                        ERROR at line 1:
                        ORA-01092: ORACLE instance terminated. Disconnection forced


                        SQL> quit
                        Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
                        With the Partitioning, OLAP and Oracle Data Mining options
                        JServer Release 9.2.0.4.0 - Production


                        SQL*Plus: Release 9.2.0.4.0 - Production on Thu Jan 7 11:45:31 2010

                        Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

                        Connected to an idle instance.

                        SQL> startup
                        ORACLE instance started.

                        Total System Global Area 1277136624 bytes
                        Fixed Size 732912 bytes
                        Variable Size 1040187392 bytes
                        Database Buffers 234881024 bytes
                        Redo Buffers 1335296 bytes
                        Database mounted.
                        ORA-01113: file 1 needs media recovery
                        ORA-01110: data file 1: '/u02/oradata/vcost/system01.dbf'


                        SQL>

                        When I did the recover using backup controlfile i applied two of the redo logs and it completed, I then shut it down and now got to the above.

                        Any ideas would be appreciated.

                        Thanks

                        NOTE This is a development db and I am going to rerun my duplicate tonight... I just thought I would play with it during the day to see if I could get it working.

                        Thanks
                        • 9. Re: ORA-01194: file 1 needs more recovery to be consistent
                          662100
                          Hi,

                          There are many reason for ORA-01092: ORACLE, check alertlog, ex. the name of your undo maybe wrong or the password file is not existent when the REMOTE_LOGIN_PASSWORDFILE is set to exclusive.

                          Ioan
                          • 10. Re: ORA-01194: file 1 needs more recovery to be consistent
                            mbobak
                            Yes, it's probably best to just redo the duplicate.

                            FYI, in the case of RMAN duplicate, you'd need to apply all archivelogs from the start of the backup of the source database, to the point in time the backup ended. As long as you have all those, you should be able to do an open resetlogs and avoid ORA-1113.

                            Hope that helps,

                            -Mark
                            • 11. Re: ORA-01194: file 1 needs more recovery to be consistent
                              user222828
                              I redid the duplicate last night, everything said it resored successfully, did all of the steps required but when it tried opening the clone database with resetlogs I got:

                              printing stored script: Memory Script
                              {
                              Alter clone database open resetlogs;
                              }
                              executing script: Memory Script

                              RMAN-00571: ===========================================================
                              RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                              RMAN-00571: ===========================================================
                              RMAN-00601: fatal error in recovery manager
                              RMAN-03004: fatal error during execution of command
                              RMAN-10039: error encountered while polling for RPC completion on channel clone_default
                              RMAN-10006: error running SQL statement: select act from x$ksusex where sid=:1 and serial=:2
                              RMAN-10002: ORACLE error: ORA-03113: end-of-file on communication channel
                              RMAN-03002: failure of Duplicate Db command at 01/07/2010 21:02:05
                              RMAN-03015: error occurred in stored script Memory Script
                              RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced

                              I try to open db and get the same error as before:

                              SQL> alter database open;
                              alter database open
                              *
                              ERROR at line 1:
                              ORA-01113: file 1 needs media recovery
                              ORA-01110: data file 1: '/u02/oradata/vcost/system01.dbf'

                              I am kind of stuck now.

                              The test db is in no archive log mode and every time I issue recover command I get:

                              SQL> recover database;
                              Media recovery complete.
                              SQL> alter database open;
                              alter database open
                              *
                              ERROR at line 1:
                              ORA-01092: ORACLE instance terminated. Disconnection forced

                              I disconnect, reconnect attempt to restart and I get:

                              SQL> startup
                              ORACLE instance started.

                              Total System Global Area 1277136624 bytes
                              Fixed Size 732912 bytes
                              Variable Size 1040187392 bytes
                              Database Buffers 234881024 bytes
                              Redo Buffers 1335296 bytes
                              Database mounted.
                              ORA-01113: file 1 needs media recovery
                              ORA-01110: data file 1: '/u02/oradata/vcost/system01.dbf'

                              I am not getting why it needs recovery.

                              Any additional assistance would be appreciated.

                              Thanks
                              • 12. Re: ORA-01194: file 1 needs more recovery to be consistent
                                662100
                                Hi,

                                Have you checked the alertlog as suggested? Paste last 100 line of alertlog and your pfile/spfile.


                                Ioan
                                • 13. Re: ORA-01194: file 1 needs more recovery to be consistent
                                  user222828
                                  Sorry I haven't updated in a while, been busy with other issues.
                                  I discovered that for some reason the undo tablespace doesn't get duplicated over...

                                  So query from v$datafile on prod server returns 17 rows and on test db returns 16.
                                  We're pretty much at a point where we lost our undo tablespace...

                                  Is there a way to get this database available? I modify the test db to undo management false and nothing for undo tablespace and still get the following:

                                  ORACLE instance started.

                                  Total System Global Area 1277136624 bytes
                                  Fixed Size 732912 bytes
                                  Variable Size 1006632960 bytes
                                  Database Buffers 268435456 bytes
                                  Redo Buffers 1335296 bytes
                                  Database mounted.
                                  ORA-01113: file 1 needs media recovery
                                  ORA-01110: data file 1: '/u02/oradata/vcost/system01.dbf'


                                  SQL> recover database;
                                  Media recovery complete.
                                  SQL> alter database open;
                                  alter database open
                                  *
                                  ERROR at line 1:
                                  ORA-01092: ORACLE instance terminated. Disconnection forced

                                  Any assistance would be appreciated...


                                  Thanks
                                  • 14. Re: ORA-01194: file 1 needs more recovery to be consistent
                                    Hemant K Chitale
                                    Cloning from a Hot Backup with UNDO_MANAGEMENT=AUTO at the source requires the Undo tablespace.

                                    If this was a Cold Backup, you could set undo_management=MANUAL and do a manual cloning -- ie a simple restore.

                                    Hemant K Chitale
                                    1 2 Previous Next