Re: Recover with RESETLOGS

"Bonjour" from Paris,

I have the book and I confirm that there is a workaround to recover through resetlogs in particular cases (p 514).

It's quite strange (!!!) and assume :

• previous recovery with resetlogs has been successfull
• no consistent backup after the resetlogs is present
• a consistent backup prior to resetlogs is present
• controlfile after the resetlogs is present
• all archive log files and online redologs are present for recovery

Warning from the book :

This is not an alternative for not backing up your database after a reset logs ! This method should be used only in emergencies.

The method is :

Point A : Before the resetlogs. A backup from A (and no other backup after this point). Point B : Base started with the resetlogs option and another failure occured at point C.

It needs :

• the current controlfile (any controlfile after point B)
• backup before the resetlogs (at point A) and do the following :
• find the SCN immediately before the database was opened with resetlogs.This SCN is reported in the alert log just before the database is opened. Look for RESETLOGS after incomplete recovery UNTIL CHANGE xxx or RESETLOGS after complete recovery through change xxxx. You should also query this information from any controlfile that is valid after the resetlogs point by using the following query :

select RESETLOGS_CHANGE# -1 from v$database;

The SCN number in the alert.log and the SCN from the query should return the same number.

• shutdown the database
• copy the current controlfile to a safe location. (You must have a copy of the controlfile that was taken after the resetlogs was performed - it does not have to be the current controlfile.
• restore the whole database and the controlfile from the backups before the resetlogs (point A)
• recover the database using "recover database using backup controlfile until change xxx" found in the first step.
• shutdown the database
• copy the controlfile from after the resetlogs to all locations in the CONTROL_FILES parameter in the init.ora
• startup mount the database
• use the appropriate recover database command and recover normally. Oracle should now ask for archive log sequence number 1.

Hints :

• always take a backup of your controlfile after resetlogs
• always backup all archivelogs before attempting this operation

Danger :

It is possible (but not likely) that you have conflicting archive log sequence numbers (i.e the same log sequence number from before the resetlogs point and after the resetlogs). You must be very careful not to overwrite one with other, especially if they are not backed up.

.... well ... "Bonne chance" (Good luck)

And tell methe result ... and send a champagne bottle if it succeeds ;-)

"Au revoir" from Paris
Christophe Brault.

John Darrah a écrit :

> I seem to remember something in the Rama Velpuri Backup and Recovery
> book discussing recovery through resetlogs. Do you or does anyone
> else have this book available to confirm this? I can't find my copy
> anywhere and its been driving me crazy not to know one way or the
> other whether this type of recovery is possible. I thought the steps
> went something like this:
> recover until blah,
> open database resetlogs,
> shutdown database,
> replace current controlfile with a backup controlfile from before
> resetlogs was issued,
> open database mount,
> recover database using backup control file.
> I could having a false memory here but I remember reading about this
> somewhere and I thought it was that book.
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<997985206.4879.0.pluto.d4ee154e_at_news.demon.nl>...
> > "Sven Morling" <sven.morling_at_ivz.de> wrote in message
> > news:3B7BA947.26D4C14B_at_ivz.de...
> > > Hello NG,
> > >
> > > there is a big problem in our company. I hope, one of you can help us.
> > > At first I will describe the problem.
> > >
> > > In our database occured a ORA-01578 "ORACLE data block corrupted". This
> > > is failure which came through a disk failure.
> > >
> > > After this we made an RECOVER UNTIL .... with a ALTER DATABASE OPEN
> > > RESETLOGS. Then the database ran one week. Now there are the problems
> > > in the datafiles(blocks).
> > >
> > > So we have probably an safe backup two weeks ago. And there is our
> > > problem. Between now and then is an RESETLOGS.
> > >
> > > Now my question: Is it possible to restore the safe backup and roll
> > > forward until the RESETLOGS-time and then roll again forward with the
> > > new offline redologs and the new SCN´s.
> > >
> > > If there is somebody who can help us, i would thank him, if he contact
> > > me personally.
> > >
> > > sven morling
> > >
> > > germany
> > >
> >
> > Sorry but you can't get beyond a resetlogs
> >
> > Period.
> >
> > Regards,
> >
> > Sybrand Bakker, Senior Oracle DBA
Received on Fri Aug 17 2001 - 10:34:42 CDT