Recovering ORACLE db after system crash
Brad Keifer
We are currently running ORACLE Version 6.0.31.0.1 on an RS/6000, running
AIX Version 3.2. A power failure took out the machine the other day while
ORACLE was being used. When the machine was brought up again, we had
trouble starting ORACLE. The error message we got was ORA-1545 (see below).
So to get ORACLE started I commented out the rollback_segments statement in
the init.ora file and started ORACLE.
Using SQLDBA to look at the rollback segments table indicated that one of
the rollback segments had a status of "NEEDS RECOVERY".
I couldn't drop the table because ORACLE told me to put it offline, I
couldn't alter it offline either, neither could I recover tablespace
"rollback_segment".
In the end we were desperate so we restored from tape.
What should I have done?
Oerr: Version 6.0.31.0.1 - Production on Wed Oct 07 08:47:21 1992
Copyright (c) Oracle Corporation 1989. All rights reserved.
01545, 00000, "rollback segment '%s' specified not available"
// *Cause: 1) Try to acquire offline rollback segment during startup.
// 2) Try to drop a rollback segment that contains active transactions.
// *Action: 1) Bring the tablespace containing the rollback segment online or
// do not specify the rollback segment in init.ora.
// 2) If the rollback segment is in use, shutdown the instance using
// rollback segment, else if the rollback segment needs recovery,
// find out the errors that are holding back the rolling back
// of the transactions and take appropriate actions.
--
Brad Keifer, BHP Information Technology, Newcastle Region, Australia
PO Box 216. Hamilton. NSW. 2303
internet: br...@cerberus.bhpese.oz.au Phone: +61 49 402126
Fax: +61 49 402165
Hans Plambeck
: "rollback_segment".
You are right, you can not restore a rollback segment, only ordinary tablespaces
Where would you restore it from ? Redo logs contain roll forward information,
rollback segments contain data to roll backward, thus making sure a database
contains only commited transactions. If redo log is stuffed, you can not
recover or skip it either.
:
: In the end we were desperate so we restored from tape.
:
Wise decision.
: What should I have done?
:
If you are sure (how would you?) that there are no transactions, you might
want to try ALTER DATABASE DATAFILE file-spec OFFLINE, and then forget
about it.
I believe in V7 you can 'mirror' these tablespaces and redo log files,
adding more security. Anyone to comment on this?
--
Hans-J Plambeck Mincom Pty Ltd ha...@mincom.oz.au +61 (7) 364 9999
Kent Palm
is:
First, if you want to be able to recover to the point-in-time when the
system crashed, you needed to be running in archive log mode. This means
you would be doing online (hot) backups as well.
Then,
To recover, copy your latest hot backup of the data files that make up the
tablespace that contained the invalid rollback segment. After that, use
the command
RECOVER TABLESPACE tablespace
and roll forward your log files. If the tablespace was the system
tablespace, then you would probably have to use the RECOVER DATABASE
command.
It's in the DBA guide and online document, recovery.doc.
Wayne Amisano
>: I couldn't drop the table because ORACLE told me to put it offline, I
>: couldn't alter it offline either, neither could I recover tablespace
>: "rollback_segment".
[...]
>: What should I have done?
>:
>If you are sure (how would you?) that there are no transactions, you might
> want to try ALTER DATABASE DATAFILE file-spec OFFLINE, and then forget
> about it.
>I believe in V7 you can 'mirror' these tablespaces and redo log files,
> adding more security. Anyone to comment on this?
In a recent DBA course, they gave an talk on what is in v7. Adding
redundant database files, similar to allowing more than 1 control file,
was mentioned.
cul Wayne!
--
Wayne Amisano (VK4KT) _-_|\\ E-Mail - wa...@gbrmpa.gov.au
Great Barrier Reef / \\ Phone - +61 77 81 8861 (GMT+1000)
Marine Park Authority. \_.-._/ Fax - +61 77 72 6093
Our's to use Wisely! v Mail - GPO Box 1379, Townsville QLD 4810
Neil Greene
(Wayne Amisano) writes:
> ha...@iris.mincom.oz.au (Hans Plambeck) writes:
>
[stuff deleted]
I was wondering if some knowledgeable oracle dba with experience in
recovering from several system crashes (instance, power, media, etc.)
could post a short message outlining the things to consider and steps to
follow in order to perform successful database recoveries.
I have read the DBA manual, but it can be complicated at times and nowhere
is there a section that just really says do this, do that.
Thanks in advance. Then again, proabaly the best way to learn would be
the day I actually have to recover from some crashes. :-)
--
Neil Greene
President, Kentucky NeXT User Group, Inc.
deepthought!ne...@ms.uky.edu [NeXTMail]
--
Neil Greene
President, Kentucky NeXT User Group, Inc.
deepthought!ne...@ms.uky.edu [NeXTMail]
Hari Seldon psychohistorian
>We are currently running ORACLE Version 6.0.31.0.1 on an RS/6000, running
>AIX Version 3.2. A power failure took out the machine the other day while
^^^^^
>What should I have done?
very simply you should be using a ups. it should have sw to do a shutdown
when the power goes away (for a specifiec period of time) and it should wait
a bit after power coes back prior to restarting the machine.
it's cheep, its simple, it's good insurance.
bill pociengel
--
llbi locgeenpi " i'm a little confused right now"
Joel Garry
>
> >We are currently running ORACLE Version 6.0.31.0.1 on an RS/6000, running
> >AIX Version 3.2. A power failure took out the machine the other day while
> ^^^^^
> >What should I have done?
>
> very simply you should be using a ups. it should have sw to do a shutdown
> when the power goes away (for a specifiec period of time) and it should wait
> a bit after power coes back prior to restarting the machine.
> it's cheep, its simple, it's good insurance.
Yes, ours only cost $20,000 plus a diesel generator, concrete block
building and new transformers on the pole. Then it blew up when the
electricians installed it and turned the lever that says "do not turn".
--
INTERNET: tumi...@netlink.cts.com (Joel Garry)
UUCP: ...!ryptyde!netlink!tumidity
NetLink Online Communications * Public Access in San Diego, CA (619) 453-1115
Hari Seldon ... psychohistorian
>bi...@chaos.cs.umn.edu ( Hari Seldon psychohistorian ) writes:
>>
>> >We are currently running ORACLE Version 6.0.31.0.1 on an RS/6000, running
>> >AIX Version 3.2. A power failure took out the machine the other day while
>> ^^^^^
>> >What should I have done?
>>
>> very simply you should be using a ups. it should have sw to do a shutdown
>> when the power goes away (for a specifiec period of time) and it should wait
>> a bit after power coes back prior to restarting the machine.
>> it's cheep, its simple, it's good insurance.
>Yes, ours only cost $20,000 plus a diesel generator, concrete block
>building and new transformers on the pole. Then it blew up when the
>electricians installed it and turned the lever that says "do not turn".
pity. but that had nothing to do with the rs/6000, oracle, or even (sigh)
aix. for an rs/6000 you should be able to get a ups for <$3k that can
provide for an orderly shutdown of the system. thats what i was pointing
out. not that you could spend mondo bucks trying to keep the system *up*.
that is already a given.
bill pociengel
ps all that for $20k? installed? thats really not a bad price. probably
a bit much for the systems i run at my home office tho;-)
Tony Jambu
br...@cerberus.bhpese.oz.au (Brad Keifer) writes:
>
.
.
> Using SQLDBA to look at the rollback segments table indicated that one of
> the rollback segments had a status of "NEEDS RECOVERY".
> couldn't alter it offline either, neither could I recover tablespace
> "rollback_segment".
>
>
There is an undocumented init.ora parameter that will help you. I am not
exactly sure of its name but I think it is '_OFFLINE_ROLLBACK_SEGMENT'.
What it does is force the DBMS to ignore or offlines the RBS. This
would have saved you the trouble of recovering the DB.
Warning: Seek Oracle support before using any '_init_ora_parameter'.
tony
-----------------------------------------------------------------------------
_____ ________ / ____ |Tony Jambu, Database Administrator
/_ __ /_ __ / |Colonial Mutual Life Australia. (ACN 004021809)
/(_)/ ((_/ \_/(///(/_)/_( |EMAIL: TJa...@cmutual.com.au
______ / |PHONE: +61-3-6076448
\/ | FAX: +61-3-6076198
--
-----------------------------------------------------------------------------
_____ ________ / ____ |Tony Jambu, Database Administrator
/_ __ /_ __ / |Colonial Mutual Life Australia. (ACN 004021809)
/(_)/ ((_/ \_/(///(/_)/_( |EMAIL: TJa...@cmutual.com.au