Re: DISASTER RECOVERY: Our experience and questions for Gurus!

comments below

Deja User wrote in message <82h51r$sjk$1_at_nnrp1.deja.com>...
>We had to do a point-in-time recovery and ran into some unexpected
>scenerio. Your help will be appreciated. (FYI, the most important
>question is at the very end).
>
>First on What triggered a recovery:
>A table space was accidently dropped. But since users were logged in
>and actively working, the "drop tablespace" command did not complete
>when it hit a "locked" record (we use "select for update nowait" in our
>applications). So we had a tablespace with half of the tables gone.
>According to alert log/trace file, it indicated that it encountered an
>resource busy error during "tablespace drop". So, the "drop tablespace"
>didn't have a "completed" entry in the log.
>

To bypass this error in the future, make sure you take the tablespace offline before you drop it. This would have prevented your whole recovery.

>First attempt at recovery:
>Wishfull thinking led to believe that since the "drop tablespace"
>didn't complete and abnormally ended due to an error, simply shutting
>down the db and restarting it would fix the error. For some reason it
>was not attempted. FIRST QUESTION: do you think it would have worked?
>i.e., would restarting the DB would have "fixed" it? Your guess would
>do!

If by "fixed" you mean "would my tablespace be back to it's original condition?" No. Oracle has already recorded the SCN number in the control files and data files and would have performed recovery to bring you back to the point in which you shut the database down. However, you are trying to drop the tablespace anyway, so go ahead and finish the drop once the database comes back up.

>
>Second attemp at recovery:
>We took the tablespace's datafile from an old backup, copied it over
>the existing datafile and issued the command "recover tablespace".
>Again the wishfull thinking was at work that since the "drop
>tablespace" did not complete due to an exception, somewhere internally
>it would have been flagged as an "in-doubt" command/operation
>and "recover tablespace" will bring back the tablespace just prior to
>the "drop" (i.e. abended operation) command. Recover command did
>initiate the recover process where it was re-applying all operations
>from re-do logs. But when it ended, we had the same *incomplete*
>tablespace. Just like it was right after the drop command--the last
>operation executed! So much for that. Oracle, dutifully, had re-applied
>the drop command as well.

DDL operations do not respect transactional boundries. In effect, when a table is dropped, the drop is automatically "committed".

 SECOND QUESTION: From the behavoiur of "drop
>tablespace" even after re-applying re-do logs, it seems that "drop
>tablesapce" command, internally, is sort of like a macro. It first
>issues individual drop commands against all object (tables?)
Only objects that can occupy a database segment (tables, indexes, clusters, partitions, etc.)

it
>contains, and then when they all succeed, it marks itself empty. So, in
>simplest form, a drop tablespace is a very long "drop table(s)"
>command. And in the re-do logs, it doesn't record the "drop tablespace"
>as the operation, but each individual "drop table" is logged. Is that
>true? That is the only explanation I can think offor an incomplete
>tablespace after recovery even though the original "drop tablespace"
>command abended.

Yes, that is correct. DROP TABLESPACE figures out which order to drop the tables based on the RI rules you have defined.

>
>Third attemp at recovery:
>It was the plain old point-in-time recovery with datafiles copied from
>an old backup. The command used was "recover database until time "1999-
>12-03:11:00:00". Notice that we don't have milliseconds in the command.
>Should we? Anyway, we didn't have the change number prior to the "drop
>tablespace" so we only had the "time" option. THIRD QUESTION: How can a
>change-number be obtained for an operation which you are trying to
>recover upto? For example, in our case, how could we get the change-
>number for the last commit prior to the "drop tablespace" command.
>Continuing on...when the recover ended, we started the database
>with "alter database open resetlogs". FOURTH QUESTION: Why doesn't
>oracle dis-allow 'noresetlogs' as an option after an incomplete
>recovery? After a point-in-time recovery, I can't think of *ANY* reason
>to first open the database, after point-in-time recovery,
>with "noresetlogs" option. Doing so just rolls the DB forward to the
>final state and all your point-in-time recovery was for nothing! Moving
>on... When we looked at the data, three of the tables reported
>following errors when we tried to view their data:
>ORA:01578 Oracle datablock corrupt, file #7, block # 285699.
>ORA:01110 datafile 7 <...blah blah blah...>
>MOST IMPORTANT QUESTION: How do we fix this error. How can we fix such
>a corrupt datablock after a point-in-time recovery without rolling
>forward "dangrously" (I hope you know what I mean by dangrously!). Our
>concern is thatrecover tablespace and recover datafile commands don't
>have UNTIL as an option. So a simple recover will only roll them
>foward. Or not? Or, is it that at this point in time, we should have
>opened the db with "resetlogs" option (that we did) and then issued the
>recover tablespace/datafile command to only fix that corrupt block?
>WHAT IS THE WAY TO DEAL WITH IT?
>

I don't know if your corrupt block is a result of your recovery or it was just backed up that way. My guess would be that the corrupt block existed before. I'll defer to the more knowlegable Oracle Gods out there...

>Thanks very much for your help!
>Regards,
>Mike.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Mon Dec 06 1999 - 16:06:16 CST