Re: ora-01194 file 1 needs more recovery - After Blackout

news wrote:

> hi,
> this is a critical situation..if you haven't a backup of db...
> The "SYSTEM" tablespace needed to recover....

The system tablespace most probably is not the source of the problem. The error message 'datafile 1 needs more recovery to be consistent' is the standard message you get when you perform an incomplete recovery and forget to restore every single data file. The actual problem could well be that file 621 is the one that didn't get restored. Therefore, it is ahead of all other files.

Since data files can't be rolled backwards in time, Oracle is giving you a hint that the other 620 files need to be rolled forward to become consistent. But, quite rightly, it doesn't list all 620 of them, but merely mentions the first one that needs further rolling forward... which is invariably the system one.

But that doesn't mean system is the problem. On the contrary, in this scenario, 620 files are right and one (#621) is 'wrong'.

None of which helps our original poster because he's in noarchivelog mode, can't rollforward at all as a result, and only has an export of the previous day's work.

> the recover is available only if you have archivelogs or backup file...
> so..if the tablespaces isn't "SYSTEM" you can take offline them and try 
> again to recover....
> otherwise..I think  you need to recreate db and import with old export 
> file.
> ..look the attach file...
> bye Orietta
> 
> Igor Kaplun wrote:
> 

As for Igor....

>> I am having trouble opening DB after Blackout. It was NOT running in
>> the archived mode. I have export of the DB from a day before. When I
>> try to recover it, I get the error below.
>>

This I don't understand. You aren't in archivelog mode, and yet you try and recover. Why?

The rule is blindingly simple (and has very few exceptions). If you are in noarchivelog mode, when there is a problem, you simply restore the entire database from the time of the last, closed, consistent, whole database backup.

If you don't have redo logs included in that backup, you then blow away the existing current redo logs, mount the database, do a recover until cancel, and immediately issue 'cancel'.

If your do have redo logs included in that backup, you then simply restore the lot and open the database.

In either case, any transactions done since the time of the last closed etc etc backup are irretrievably lost.

>> PLEASE HELP.
>>
>>
>> SVRMGR> recover database
>> ORA-00283: recovery session canceled due to errors
>> ORA-00314: log 1 of thread 1, expected sequence# 99835 doesn't match
>> 99838

In other words, the data files are at time 99834, the recover command wants to roll them forward to time 99835, but your existing online redo logs only contain changes from time 99838 onwards. That's because online logs get over-written.

The mistake is simply to try recovering anything in the first place. You can't do recovery of you don't have an uniterrupted stream of redo. And you don't, because you're not in archivelog mode.

>> ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ICMS\REDO01.LOG'
>> SVRMGR> recover database until cancel
>> ORA-00279: change 73726420 generated at 08/14/2003 08:48:54 needed for
>> thread 1
>> ORA-00289: suggestion : C:\ORACLE\ORA81\RDBMS\ARC99835.001
>> ORA-00280: change 73726420 for thread 1 is in sequence #99835
>> 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: 'C:\ORACLE\ORADATA\ICMS\SYS1ICMS.ORA'

See my comments regarding 'file 1 needs more recovery errors' above. It probably doesn't. If, after the blackout, you recovered *every* data file, without exception, from a closed, whole, consistent database backup, then this error will not arise.

If you do a proper restore from the latest suitable backup, you may be able to use your export to recover inserts performed after the time of that export (by simply running import, with IGNORE=Y, and bracing yourself for the million plus errors you will get about primary key constraint violations. Nevertheless, those records which the export contains which don't already exist will be inserted correctly). But exports don't record updates or deletes, and therefore those are irretrievably lost.

Which kind of underlies the point that export is NOT a backup tool, and is no substitute for proper, physical backups on a regular basis. Don't rely on it, in other words, as your sole source of reassurance.

Regards
HJR Received on Wed Aug 20 2003 - 05:55:29 CDT