Unable to open DB - file 1 needs more recovery to be consistent

[former_member199632]

Hello,

When trying to start the database it prompts the following error.

How to resolve this!!

----------------------------

SQL> startup

ORACLE instance started.

Total System Global Area 4613734400 bytes

Fixed Size                  2090048 bytes

Variable Size            2315258816 bytes

Database Buffers         2281701376 bytes

Redo Buffers               14684160 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS

  2  ;

alter database open RESETLOGS

*

ERROR at line 1:

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/QAS/sapdata1/system_1/system.data1'

----------------------------

Then i issued this command;

--------------------------

SQL> recover database until cancel;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

--------------------------

SQL>  recover database until cancel using backup controlfile

ORA-00279: change 849021040 generated at 05/31/2014 21:59:24 needed for thread

1

ORA-00289: suggestion : /oracle/QAS/oraarch/QASarch1_72357_662901117.dbf

ORA-00280: change 849021040 for thread 1 is in sequence #72357

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log

'/oracle/QAS/oraarch/QASarch1_72357_662901117.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/QAS/sapdata1/system_1/system.data1'

-----------------------

But here it asking for the QASarch1_72357_662901117.dbf  file, which is not existing. [deleted, and not exiting even in the backups]

in /oracle/QAS/oraarch, it is having from  QASarch1_75954_662901117.dbf.

So please guide, how to solve this issue, and start the DB

regards,

zerandib

[willi_eimler]

Hi Zerandib,

we had a similar problem. The tablespaces were in backup mode. After the "alter database open RESETLOGS" command we killed the database.

Best regards

Willi Eimler

[former_member225142]

Hi Zerandib,

Did you check the output of the recover command?

SQL>  recover database until cancel using backup controlfile

ORA-00279: change 849021040 generated at 05/31/2014 21:59:24 needed for thread

1

ORA-00289: suggestion : /oracle/QAS/oraarch/QASarch1_72357_662901117.dbf

ORA-00280: change 849021040 for thread 1 is in sequence #72357

Is there no backup happening on this system since past 3 months. Kindly explain the situation here.

In case there are no old redologs present, kindly restore the database with the latest backup and apply logs before the issue happened.

Regards,

Meenu Hans

[former_member199632]

Hi Meenu ,

Yes.... Seems like that there are no proper backups happening from  05/31/2014

In this case , what is the best possible thing we can done to make it up and running

Can we up the DB from the state of  05/31/2014

No Backup is available either....

Looking forward for your guidance

regards,

Ushan

[former_member225142]

Hi Zerandib,

Kindly check if the offline redolog files are present on the tapes where the backups are moved.

Regards,

Meenu Hans

[former_member199632]

No offline redologs Meenu.

When backing up, those got deleted.....!!!.. but backups were failed as well.... zero backups exists now

Without those, up to which level is it possible us for reach!. Any suggestions

[Reagan]

Hello

If you don't have the required offline redo's then recovery is not possible.

As you are recovering a quality system (based on the SID) why don't you restore the latest backup of the production system and then recover it ?

Regards

RB

[former_member225142]

Hi Zerandib,

In the current situation, it will not be able to recover the database since even the redologs present for the backup are not there. The best solution here would be to restore the system from production database and thereafter make it a point for checking timely backups of the system.

[Former Member]

Hi Zerandib,

In order to restore online backups, you need all datafiles plus archive logs generated during your online backup window. There is no other way to perform a consistent recovery from online backup without archives.

The only way to perform a PIT (Point in time recovery) is from a incomplete recovery. One example to do this is restoring all datafiles, plus archivelogs, and execute the "RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL" statement. Incomplete recovery will not work anymore if you firstly try to perform a complete recovery instead (so you might get the "ORA-01195" either from doing this, or from a missing archivelog during your recovery).

Personally I would try to do recover one more time from scratch. If it fails again due missing archives, There is a way to "force" opening your database even with missing archives, setting the "_ALLOW_RESETLOGS_CORRUPTION=TRUE"  parameter in your init<sid>.ora file. This will allow to open database without performing a consistency check, but attention - Oracle does not guarantee your database consistency after setting this parameter. Check Oracle alert file after doing this to check the side effects. I was able to recover a database once using this param because only the UNDO tablespace was corrupted. After recreating UNDO tablespace, the issue was cleared.

Hope it helps.

Regards

Marcelo Macedo

[Former Member]

Hi,

Try this and update the status:

recover database;

And I could see ~2000 logs gap between the current and what system is asking!

Explain the background, what are all the changes happened on this system?

Regards,

Nick Loy

[former_member199632]

Hi Nick,

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

-----

Yes there are around ~2000 gap in the logs.

But nothing special happens in the system, except day to day transactions.

regards,

Zerandib

[Former Member]

Hi,

Why don't you do a restore using latest backup of production, since this issue is in QA?

Regards,

Nick Loy