Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I tried using set time until.. and mis-typed the date. Can anyone help me understand how to get my backups into a manageable state?

After the accidental recover, most of my backupset disappeared.

I recovered them and used 'catalog recovery area' .. and they're listed in 'list backupset'. But something still isn't right.

When I do a PITR now, I get messages that my dbf files aren't available

and... the 'list backupset' seems to show backupsets. But they are listed differently than the files which weren't included in the 'bad' recovery.

Gists with the error and the list of backupsets are here

share|improve this question

closed as off-topic by Paul White, Phil, dezso, Max Vernon, RolandoMySQLDBA Apr 29 '15 at 14:47

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Phil, dezso, Max Vernon, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

A quick glance suggests you're stuffed, as there's only incremental backups and no full backups. One suggestion - copy the datafiles to another directory, then (I quote the documentation) "Record a datafile copy as a level 0 incremental backup in the RMAN repository, which enables you to use it as part of an incremental backup strategy.". Catalog the copies, then try the restore again. Have you checked what SCN the current datafiles are at? – Phil May 11 '13 at 20:58
Another thing, do a "LIST INCARNATION OF DATABASE yourdb" to see if there's been a resetlogs. – Phil May 11 '13 at 21:00
Phil: I'm obviously missing something. The backup set files show that they're full (or appear to me). But all the datafiles show incr. I thought the backupset was supposed to include everything necessary for recovery? – Greg May 11 '13 at 21:59
And only one incarnation.. at least I did a full export before mucking around with this. – Greg May 11 '13 at 22:00
I thought they looked too small to be full - any idea hiw big the data is? I only had a quick skim read - on the iPhone from the pub :) – Phil May 11 '13 at 22:10

So after working through the info that Phil gave me.. and doing some research after the fact..

It appeared that the Set Until with a future date screwed things up by "removing, or de-cataloging my backups". Even if that's possible (I don't know enough about Oracle to say) it wasn't the case.

What had happened was that my initial Full Backups were gone due to some poor data-retention. So when I put a future date, it appeared like my PITR would work because it had a Full backup from a day ago. However, I never did have a Full backup from a week ago. I had taken one a couple months ago. but it had been deleted.

I'm using EM Console for my backups and haven't yet figured out how to tell it to do incrementals every day and fulls once a week. I'll get there though.

Thanks for all the help.

share|improve this answer
Sorry for your data :-/ – Andrew Brennan Apr 9 '14 at 9:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.