Re: shutdown abort and database consistency

"Howard J. Rogers" <dba_at_hjrdba.com> wrote:

>There's your problem, right there... ORA-600.

OK, I am not expecting to get a solution to this particular problem. My concern was to better understand the relationship between the effects of a "shutdown abort", the nologging option and a long running transaction.

>A complete waste of time, if this job was doing regular 'delete from table
>where some_condition...'. Deletes *always* log, no matter whether you tell
>them to or not. They are regular DML statements, and regular DML pay no heed
>to the nologging attribute. So that's not the problem.

Well, thanks. I didn't know...

>> Now I am wondering if an Oracle DB is able to recover from uncommited
>> transactions that are not being logged in redo logs.
>
>The short answer is "no", since if there's nothing in the redo logs, how do
>you expect Oracle to "redo" them?

Now let's assume that the operation was of the kind that if set to nologging then indeed no logging would occur. When I say "to recover from uncommited transactions" I don't mean to get these redone, but to recover at all, that is, to be opened in a consistent state. Here again the question: Could Orable be able to come up in a consistent state after issuing a "shutdown abort" during the execution of operations that weren't being logged?

>>As I understand, a "shutdown abort" should be the last
>> thing to do, it is like a provoked crash.
>
>There is a lot of old horse manure talked about shutdown aborts. They are
>perfectly and utterly safe. They do no more to damage your data than a
>shutdown immediate. The *only* risk they incur is that if you lose your
>current redo log right after a shutdown abort, you won't be able to re-open
>the database until you've performed an incomplete recovery, and you'll lose
>committed data as a result. But assuming your online logs are suitably
>multiplexed and mirrored, that's not going to be an issue, is it?

I have indeed read what you call old horse manure.

>>By the way, the size and
>> number of the Rollback Segments are huge in this DB.
>
>Ah hah. I think we might take a wild stab in the dark at this point and say
>that we put this information with your ORA-600 and conclude that you've
>encountered an Oracle bug relating to the number of extents for a rollback
>segment (or something similar).

Thanks for the hint.

>My opinion is that you mustn't jump at shadows. Shutdown aborts are
>perfectly OK. Deletes always generate redo, so recoverability is not an
>issue. But huge rollbacks might be.

Now I think that the problem was caused by a kind of overflow of the rollback segments.

What I did today: I reloaded all deleted data from a dump file. Since the DB would not react to a "shutdown immediate" command, I had to use the "shutdown abort", but before doing that, I issued an "alter system checkpoint" command. Should not harm. After that, the DB came up and no problems have arised sofar.

The problem was solved, and this time the guy is issuing a commit every 1000 deletes, so the rollback segment won't overflow.

Thanks a lot.

Rick Denoire Received on Tue Jul 09 2002 - 17:56:53 CDT