ora-01194 file 1 needs more recovery - After Blackout
Igor Kaplun
the archived mode. I have export of the DB from a day before. When I
try to recover it, I get the error below.
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
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'
news
this is a critical situation..if you haven't a backup of db...
The "SYSTEM" tablespace needed to recover....
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
Niall Litchfield
news:9967d711.03081...@posting.google.com...
This is my understanding of what your situation is, but you would be well
advised to open a TAR with Oracle rather than relying on some guy in the
newsgroups :)
Your database needs change 73726420 to recover. This change was recorded in
a redo log that has been overwritten. Thus Oracle wants to get it from what
the archived log file would be called if you had it. Unfortunately you don't
have it and so recovery is not possible. You seem to be saying that the only
backup you have is an export from last night, so it would look like
recreating the database (since it is the system tablespace that is stuffed
up) and importing the data from export is your next best bet. This obviously
loses data which is why I'd suggest a TAR if I'm incorrect then taking my
advice would be very unhelpful indeed.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Howard J. Rogers
> 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