RE: RMAN restore/recover problem - end result (sort of)

From: Holvoet, Jo <jo.holvoet_at_thomascook.be>
Date: Fri, 30 Aug 2013 10:33:06 +0200
Message-ID: <CF9A39CD0F65EA49ADF70FCBF9BC2FF704952ADF_at_SW-GNETCW-MBX02.tcads.thomascook.com>



Hi Mark,
Good point about the online redo. The mantra "never back up your online redo logs" kind of makes you forget about them in these situations. I hadn't attempted a resetlogs though - never got that far.

In the meantime Oracle Support have come back with the expected answer : we can help you open the database with various underscore parameters, but only to allow you to then export the data and rebuild the database. I've gone to the business people with that answer and told them what export and import of 1.2 TB of data entails, both resource and time-wise.

They were requesting this PITR because of "logical" data corruption they were seeing (and had been seeing for a couple of weeks before they came to me - sigh). Given the alternatives they have decided to try manual cleanup instead of going the exp/imp route. That's the safer route for me but of course the least interesting outcome for all of you :-)

Finally : many thanks again to everyone who responded. The way so many listers here step up time and again to help out complete strangers never ceases to amaze me.

Mvg/regards
Jo

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: donderdag 29 augustus 2013 12:32
To: oracle-l_at_freelists.org
Subject: RE: RMAN restore/recover problem

Remember that *this* is the reason that making a copy of the ONLINE redo logs is the first step of recovery. (See archaic threads for the debate about whether to ever put them on a backup set. Snagging a copy of them before you do anything else at recovery time and stashing them where Oracle cannot see them provides a safety net against both PEBKAC and software running into unforeseen situations.)

Once you have attempted an open resetlogs, you may have trashed the online redo logs. If there was activity in the online redo logs that was not yet archived that is beyond the point in time to which you want to recover (which you may have gone beyond already, but that is a different snag), then information from the online redo logs will be required. You then need a source from which to get an unmolested copy of the online redo logs to try again using backup controlfile until cancel.

If you used a too recent controlfile and already let it proceed past the point in time you want using automatic recovery (instead of until cancel or certain time or certain SCN), then you're going to need to restore older datafiles again. (This is the "different" snag.)

Finally, if the only problem is UNDO, then deactivate that UNDO tablespace and try manual undo, which should rely on the system rollback segment for the recovery. IF this works, then of course create a new UNDO tablespace and shutdown to restart with automatic undo management using the newly created tablespace.

Now, if blocks in some files were already written which need to be rolled back to reach the point in time you desire and the only source of that data before a commit you're undoing by going back in time is the existing UNDO tablespace, I'm thinking you're out of luck as far as normal recovery is concerned. (I'm not sure who gave you the idea you did not need to have UNDO on your backup: rollback can often be the longest phase of a recovery if some batch update is interrupted or has to be reversed in service of point in time recovery.)

The other thing that has crept into my mind is the RETENTION GUARANTEE clause of the UNDO tablespace. I suppose it is possible that recovery would insist on the existence of bits of UNDO that have been overwritten already by the effects of time travel in a point in time recovery situation. Without UNDO on your backup, I don't see how that is going to happen and I'm thinking an older vintage than you would suspect of the UNDO tablespace might be needed for some combinations of point in time recovery, retention time, and wrapping the undo. *Sigh*

If you're out of luck regarding normal recovery, then see a recent thread "Re: Non-Sql data extraction (recovery tools)" about DUDE. (I see Kurt is already aware of the current thread. Kurt and Tim Gorman know all about DUDE, and both will probably tell you it is only for a last resort for valuable data.

Maybe support can work some magic, but if you really need some UNDO to reach your desired point in time you'll need an old enough copy and all the interceding redo. If you can isolate which tablespaces need missing UNDO, you might be able to get everything except those tablespaces back by taking the ones which need UNDO offline. Possibly they can be recovered by a separate recovery operation to a different point in time or complete recovery if no inflight transactions need to be reversed. Whether or not those bits and the point in time bits can be remerged into a logically sound whole is a different story about knowing the interrelationships amongst your applications.

Good luck.
(If I mis-remembered some detail or voiced an error, please someone correct me. I haven't had my coffee yet.)

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kurt Van Meerbeeck Sent: Thursday, August 29, 2013 4:40 AM
To: jo holvoet
Cc: jo holvoet; Guillermo Alan Bort; oracle_at_dunbar-it.co.uk; oracle-l-freelists; Mark Bobak; Dimitre Radoulov Subject: Re: RMAN restore/recover problem

Did you try to recreate the controlfile and then use recover database using backup controlfile until cancel ? (sorry if this was already suggested - didn't read the whole thread)

kind regards,
Kurt

  • Original Message ----- From: "Jo Holvoet" <jo.holvoet_at_thomascook.be> To: "Mark Bobak" <Mark.Bobak_at_proquest.com>, "Dimitre Radoulov" <cichomitiko_at_gmail.com> Cc: "jo holvoet" <jo.holvoet_at_gmail.com>, "Guillermo Alan Bort" <cicciuxdba_at_gmail.com>, oracle_at_dunbar-it.co.uk, "oracle-l-freelists" <oracle-l_at_freelists.org> Sent: Thursday, August 29, 2013 9:51:29 AM Subject: RE: RMAN restore/recover problem

I most certainly will. It's the least I can do for everything this list has given me the last 10+ years :-)

-----Original Message-----
From: Mark Bobak [mailto:Mark.Bobak_at_proquest.com] Sent: donderdag 29 augustus 2013 9:49
To: Holvoet, Jo; Radoulov, Dimitre
Cc: jo.holvoet_at_gmail.com; Guillermo Alan Bort; oracle_at_dunbar-it.co.uk; oracle-l-freelists Subject: Re: RMAN restore/recover problem

I was going to recommend opening an SR, as the next step.

There are some undocumented parameters that support may ask you to set.

When you get it recovered, and get caught up on some sleep, I hope you'll follow up here. I'd be interested in hearing the final resolution.

Good luck!

-Mark

On 8/29/13 3:26 AM, "Holvoet, Jo" <jo.holvoet_at_thomascook.be> wrote:

>Mark,
>
>This also asks for archive log sequence 1 ...
>
>Anyway, after a (not-so-) good night's sleep, the plot thickens.
>The level 0 backup I'm starting from does NOT contain the undo tbs ...
>Upon restore one is created with a checkpoint time way in the past (I
>think DB creation time) and so the request for arch seq 1.
>This is a data warehouse and the point-in-time I need shouldn't have
any
>open transactions at that time.
>
>I've got an SR open because I think this is heading toward some
>not-so-supported actions ...
>
>Mvg/regards
>Jo
>
>-----Original Message-----
>From: Mark Bobak [mailto:Mark.Bobak_at_proquest.com]
>Sent: woensdag 28 augustus 2013 22:05
>To: Holvoet, Jo; Radoulov, Dimitre
>Cc: jo.holvoet_at_gmail.com; Guillermo Alan Bort; oracle_at_dunbar-it.co.uk;
>oracle-l-freelists
>Subject: Re: RMAN restore/recover problem
>
>Have you tried 'recover database using backup control file until
>cancel'?
>
>http://docs.oracle.com/cd/B12037_01/server.101/b10734/rcmrecov.htm#1006
2
>47
>
>
>I think someone previously suggested that, but I don't recall seeing
>that you tried it?
>
>-Mark
>
>On 8/28/13 3:48 PM, "Holvoet, Jo" <jo.holvoet_at_thomascook.be> wrote:
>
>>Hi Dimitre,
>>
>>The only backups listed are all newer than the point-in-time I want.
>>
>>-----Original Message-----
>>From: Radoulov, Dimitre [mailto:cichomitiko_at_gmail.com]
>>Sent: woensdag 28 augustus 2013 21:00
>>To: Holvoet, Jo
>>Cc: jo.holvoet_at_gmail.com; Guillermo Alan Bort; oracle_at_dunbar-it.co.uk;
>>oracle-l-freelists
>>Subject: Re: RMAN restore/recover problem
>>
>>Connect to catalog and execute:
>>
>>list backup of controlfile;
>>
>>If you find the backup in some backupset, try (I'm not sure about the
>>exact syntax):
>>
>>restore controlfile from 'backupset_name';
>>
>>Regards
>>Dimitre
>>
>>On 28/08/2013 20:56, Holvoet, Jo wrote:
>>> Hi Dimitre,
>>>
>>> Since I registered the backupsets in our rman catalog, shouldn't I
>see
>>> this in the RC_BACKUP_CONTROLFILE view ? It's not there.
>>>
>>> Mvg/regards
>>> Jo
>>>
>>> -----Original Message-----
>>> From: Radoulov, Dimitre [mailto:cichomitiko_at_gmail.com]
>>> Sent: woensdag 28 augustus 2013 20:14
>>> To: jo.holvoet_at_gmail.com; Holvoet, Jo
>>> Cc: Guillermo Alan Bort; oracle_at_dunbar-it.co.uk; oracle-l-freelists
>>> Subject: Re: RMAN restore/recover problem
>>>
>>> Just to add that RMAN automatically includes the control file and
>>> server parameter file in backups of data file 1, so I believe that
>you
>>> should have
>>> a backup of the controlfile in the backupset of datafile 1
>(tablespace
>>> system).
>>>
>>> Regards
>>> Dimitre
>>>
>>> On 28/08/2013 19:54, Guillermo Alan Bort wrote:
>>>> [...] (ever since I had to
>>>> restore a database without the correct controlfile and learned to
>>> check the
>>>> controlfiles are being backed up properly).
>>
>>--
>>http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 30 2013 - 10:33:06 CEST

Original text of this message