Re:RE: Re[2]: How can I get my DB back if I lost one of my r

From: <dgoulet_at_vicr.com>
Date: Tue, 14 Aug 2001 10:48:34 -0700
Message-ID: <F001.0036A172.20010814102732@fatcity.com>

Lisa,

I believe that is why using "shutdown abort" is not truly endorsed by Oracle, except as a last resort. What I've noticed on the list is the desire by some to over use this method of shutting down the DB. Now I will admit that "shutdown normal" is a real pain in the back side since every one HAS to log off before the DB will shutdown. And "shutdown immediate" does have a tendency to hang around way too long on occasion. Using abort though as the fix all is a really bad thing to do. One item I've taken is to allow the DB to stay in the "shutdown immediate" state for at least 10 minutes before aborting it. The reason is that during that time the background processes have the opportunity to rollback/commit all of the transactions that were taking place thereby cleaning out the rollback segments. If by the time 10 minutes has expired your DB has not shutdown, you can be 99% confident that the problem is a truly dead process/client who is no longer talking to Oracle and who's transactions have been handled. It's just a matter that Oracle cannot handshake with the client anymore that's holding up the shutdown. Then one can abort the instance with every confidence that it will restart later. Experience here has proved that the startup in this case will occur 5 times faster than just firing off a "shutdown abort" without the time period. Therefore to my SA's chagrin, all of my scripts call "shutdown immediate" and "shutdown abort" is left to a manual process.

In my last response, privately, to Riyaj I stated that I had repaired a DB that lost it's rollback tablespace to a disk failure. The problem arose on db startup after a normal cold backup. Since the DB was shutdown with the immediate vs. abort option all of the rollback was cleaned out before Oracle shutdown, and it did so normally. When the restart failed it was just a matter of dumping the datafile offline and the tablespace including contents (which cleans undo$ in the process) and rebuilding the messed up rollback space else where. Had "shutdown abort" been used I have no experience on what would have happen, but I sure can imagine.

Now the original post stated that they had forgotten to backup this datafile. The reasons for doing so vary a lot, but there is a common and failsafe method to prevent this. It's just a matter of laying out your filesystem in such a way that you cannot miss a datafile. Around here we use /ora1 as Oracle's $HOME mount point and then a second directory off of the root as the starting point for each instance such as our ODS where all of the files for the instance are on /TEST/<something>/<something>.dbf. This includes the control files and on-line redo logs although I know you really should not back them up IAW Oracle's suggestions but I do anyway. Now when my SA wants to do a backup he just backups /TEST/* and we're 100% confident he got everything.

I think one of the tasks of a DBA is to try and anticipate everything that Murphy can unleash on you. To that point I truly believe in the KISS principle, namely Keep It Simple, Stupid.

Dick Goulet

____________________Reply Separator____________________
Author: "Koivu; Lisa" <lisa.koivu_at_efairfield.com>
Date:       8/14/2001 12:01 PM

Hi Dick,

I see your point. What about when the db does something similar to a shutdown abort? Then you do need your rollback segments to perform a full recovery.

> -----Original Message-----
> From: dgoulet_at_vicr.com [SMTP:dgoulet_at_vicr.com]
> Sent: Tuesday, August 14, 2001 12:26 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re[2]: How can I get my DB back if I lost one of my rollback
>
> Riyaj,
>
> Rollback segments are needed for recovery? I think your confused with
> the
> online redo logs. When a DB shuts down the rollback segments are emptied
> out
> either because the transactions have been rolled back or committed. Once
> you
> start the recovery process the rollback segments are no longer required as
> the
> data therein is in the on-line redo logs.
>
> Dick Goulet
>
> ____________________Reply Separator____________________
> Author: Riyaj_Shamsudeen_at_i2.com
> Date: 8/14/2001 6:41 AM
>
> Hermanto
> Rollback segment data files are *needed* for recovery. Rollback
> segments are the key component for consistent media/instance recovery. If
> you lose any of the rollback segment files and if any extents from that
> datafile are needed then the database can not do the recovery consistently
>
> and will give you errors. Is it the only data file associated with the
> rollback segment tablespace. If that is the case, there will be extents
> from the rollback segments. If there are no extents from the rollback
> segments then you could potentially offline drop the datafile, do the
> media recovery and do open resetlogs. What errors are you getting ? Stuck
> recovery ? Errors will tell you whether the recovery process is looking
> for rollback segments or the data file.
> Please contact Oracle support if there are any rollback extents in
>
> this datafile. _offline_rollback_segments and _corrupted_rollback_segments
>
> parameters can be used to hard open the database. But DO expect data
> consistency errors. In fact, only thing you should do is open this way,
> extract the data out and import in to a new database. Contact Oracle
> Support before you do this since there are serious consequences of doing
> this.
>
> Thanks
> Riyaj "Re-yas" Shamsudeen
> Certified Oracle DBA
> i2 technologies www.i2.com
>
>
>
>
> Hermanto P <hermanto_p_at_aprilasia.com>
> Sent by: root_at_fatcity.com
> 08/14/01 03:35 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: How can I get my DB back if I lost one of my
> rollback
> segment (rb
>
>
> Hi gurus,
> Recently, I have forgotten to backup one of rollback segment file
> (rbs02.dbf).
> When I want to restore..the db can not startup normally.
> Anyone can give me advise..
> Thanks in advance.
>
> Warm regards,
> Hermanto
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Hermanto P
> INET: hermanto_p_at_aprilasia.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
> Hermanto
>         Rollback
> segment
> data files are *needed* for recovery. Rollback segments are the key
> component
> for consistent media/instance recovery. If you lose any of the rollback
> segment
> files and if any extents from that datafile are needed then the database
> can not
> do the recovery consistently and will give you errors. Is it the only data
> file
> associated with the rollback segment tablespace. If that is the case,
> there will
> be extents from the rollback segments. If there are no extents from the
> rollback
> segments then you could potentially offline drop the datafile, do the
> media
> recovery and do open resetlogs. What errors are you getting ? Stuck
> recovery ?
> Errors will tell you whether the recovery process is looking for rollback
> segments or the data file.
>         Please
> contact
> Oracle support if there are any rollback extents in this datafile.
> _offline_rollback_segments and _corrupted_rollback_segments parameters can
> be
> used to hard open the database. But DO expect data consistency errors. In
> fact,
> only thing you should do is open this way, extract the data out and import
> in to
> a new database. Contact Oracle Support before you do this since there are
> serious consequences of doing this.
> 
> Thanks 
> Riyaj "Re-yas" Shamsudeen 
> Certified Oracle DBA 
> i2 technologies   www.i2.com
> 
> 
> 
> <table width=100%>
> <tr valign=top>
> <td>
> <td>Hermanto P
> <hermanto_p_at_aprilasia.com>
> Sent by: root_at_fatcity.com
> 08/14/01 03:35 AM
> Please respond to ORACLE-L
> 
> <td>        
>         To:  
>  
>    Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
>         cc:  
>  
>    
>         Subject:
>  
>      How can I get my DB back if I lost one of my rollback
> segment (rb</table>
> 
> 
> Hi gurus, 
> Recently, I have forgotten to backup one of rollback segment file 
> (rbs02.dbf). 
> When I want to restore..the db can not startup normally. 
> Anyone can give me advise.. 
> Thanks in advance. 
> 
> Warm regards, 
> Hermanto 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com 
> -- 
> Author: Hermanto P 
>  INET: hermanto_p_at_aprilasia.com 
> 
> Fat City Network Services    -- (858) 538-5051  FAX: (858)
> 538-5051 
> San Diego, California        -- Public Internet access
> /
> Mailing Lists 
> -------------------------------------------------------------------- 
> To REMOVE yourself from this mailing list, send an E-Mail message 
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
> the message BODY, include a line containing: UNSUB ORACLE-L 
> (or the name of mailing list you want to be removed from).  You
> may 
> also send the HELP command for other information (like subscribing). 
> 
> 
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: dgoulet_at_vicr.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=US-ASCII">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2653.12">
<TITLE>RE: Re[2]: How can I get my DB back if I lost one of my rollback</TITLE>
</HEAD>
<BODY>

Hi Dick,

I see your point.  What about when the db does something similar to a shutdown abort?  Then you do need your rollback segments to perform a full recovery.

<P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">LK</FONT>
</P>
<UL>
<P><FONT SIZE=1 FACE="Arial">-----Original Message-----</FONT>
<BR><B><FONT SIZE=1 FACE="Arial">From:&nbsp;&nbsp;</FONT></B> <FONT SIZE=1

FACE="Arial">dgoulet_at_vicr.com [SMTP:dgoulet_at_vicr.com] Sent:   Tuesday, August 14, 2001 12:26 PM To:     Multiple recipients of list ORACLE-L Subject:        Re[2]: How can I get my DB back if I lost one of my rollback

Riyaj,

Rollback segments are needed for recovery?  I think your confused with the online redo logs.  When a DB shuts down the rollback segments are emptied out either because the transactions have been rolled back or committed.  Once you start the recovery process the rollback segments are no longer required as the
 data therein is in the on-line redo logs.

Dick Goulet

<P><FONT SIZE=2 FACE="Arial">____________________Reply
Separator____________________</FONT>

Author: Riyaj_Shamsudeen_at_i2.com Date:       8/14/2001 6:41 AM

Hermanto         Rollback segment data files are *needed* for recovery. Rollback segments are the key component for consistent media/instance recovery. If 
 you lose any of the rollback segment files and if any extents from that 
 datafile are needed then the database can not do the recovery consistently 
 and will give you errors. Is it the only data file associated with the 
 rollback segment tablespace. If that is the case, there will be extents 
 from the rollback segments. If there are no extents from the rollback 
 segments then you could potentially offline drop the datafile, do the 
 media recovery and do open resetlogs. What errors are you getting ? Stuck 
 recovery ? Errors will tell you whether the recovery process is looking 
 for rollback segments or the data file.         Please contact Oracle support if there are any rollback extents in this datafile. _offline_rollback_segments and _corrupted_rollback_segments 
 parameters can be used to hard open the database. But DO expect data 
 consistency errors. In fact, only thing you should do is open this way, 
 extract the data out and import in to a new database. Contact Oracle 
 Support before you do this since there are serious consequences of doing 
 this.

<P><FONT SIZE=2 FACE="Arial">Thanks</FONT>
<BR><FONT SIZE=2 FACE="Arial">Riyaj &quot;Re-yas&quot; Shamsudeen</FONT>
<BR><FONT SIZE=2 FACE="Arial">Certified Oracle DBA</FONT>
<BR><FONT SIZE=2 FACE="Arial">i2 technologies&nbsp;&nbsp; www.i2.com</FONT>
</P>
<BR>
<BR>
<BR>

<P><FONT SIZE=2 FACE="Arial">Hermanto P &lt;hermanto_p_at_aprilasia.com&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">Sent by: root_at_fatcity.com</FONT>
<BR><FONT SIZE=2 FACE="Arial">08/14/01 03:35 AM</FONT>

Please respond to ORACLE-L

To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

<BR><FONT SIZE=2 FACE="Arial">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cc:
</FONT>
<BR><FONT SIZE=2 FACE="Arial">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Subject:        How can I get my DB back if I lost one of my rollback

<BR><FONT SIZE=2 FACE="Arial">segment (rb</FONT>
</P>
<BR>

Hi gurus, Recently, I have forgotten to backup one of rollback segment file
 (rbs02.dbf). When I want to restore..the db can not startup normally.

<BR><FONT SIZE=2 FACE="Arial">Anyone can give me advise..</FONT>
<BR><FONT SIZE=2 FACE="Arial">Thanks in advance.</FONT>
</P>

<P><FONT SIZE=2 FACE="Arial">Warm regards,</FONT>
<BR><FONT SIZE=2 FACE="Arial">Hermanto</FONT>

-- Please see the official ORACLE-L FAQ: <A HREF="http://www.orafaq.com" TARGET="_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=2 FACE="Arial">-- </FONT>
<BR><FONT SIZE=2 FACE="Arial">Author: Hermanto P</FONT>
<BR><FONT SIZE=2 FACE="Arial">&nbsp; INET: hermanto_p_at_aprilasia.com</FONT>
</P>

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 San Diego, California        -- Public Internet access / Mailing Lists
 <FONT SIZE=2

FACE="Arial">-------------------------------------------------------------------

-
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).

</P>
<BR>
<BR>
<BR>

Hermanto &nbsp; &nbsp; &nbsp; &nbsp; Rollback segment
 data files are *needed* for recovery. Rollback segments are the key component
 for consistent media/instance recovery. If you lose any of the rollback segment
 files and if any extents from that datafile are needed then the database can not
 do the recovery consistently and will give you errors. Is it the only data file
 associated with the rollback segment tablespace. If that is the case, there will
 be extents from the rollback segments. If there are no extents from the rollback
 segments then you could potentially offline drop the datafile, do the media
 recovery and do open resetlogs. What errors are you getting ? Stuck recovery ?

<BR><FONT SIZE=2 FACE="Arial">Errors will tell you whether the recovery process
is looking for rollback</FONT>
<BR><FONT SIZE=2 FACE="Arial">segments or the data file.&lt;/font&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;br&gt;&lt;font size=2

face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; Please contact

Oracle support if there are any rollback extents in this datafile.
 _offline_rollback_segments and _corrupted_rollback_segments parameters can be used to hard open the database. But DO expect data consistency errors. In fact,
 only thing you should do is open this way, extract the data out and import in to

<BR><FONT SIZE=2 FACE="Arial">a new database. Contact Oracle Support before you
do this since there are</FONT>
<BR><FONT SIZE=2 FACE="Arial">serious consequences of doing

this.
 Thanks Riyaj &quot;Re-yas&quot; Shamsudeen

<BR><FONT SIZE=2 FACE="Arial">Certified Oracle DBA&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">i2 technologies &amp;nbsp;
www.i2.com&lt;/font&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;table width=100%&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;tr valign=top&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;td&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;td&gt;&lt;font size=1

face="sans-serif">Hermanto P &lt;hermanto_p_at_aprilasia.com&gt;</FO NT>
 Sent by: root_at_fatcity.com 08/14/01 03:35 AM Please respond to ORACLE-L <td>&nbsp; &nbsp; &nbsp; &nbsp; 
 &nbsp; &nbsp; &nbsp; &nbsp; To: &nbsp; &nbsp;
 &nbsp; &nbsp;Multiple recipients of list ORACLE-L
 &lt;ORACLE-L_at_fatcity.com&gt; &nbsp; &nbsp; &nbsp; &nbsp; cc: &nbsp; &nbsp;
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;How can I get my DB back if I lost one of my rollback

<BR><FONT SIZE=2 FACE="Arial">segment (rb&lt;/font&gt;&lt;/table&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;br&gt;&lt;font size=2 face=&quot;Courier

New">Hi gurus, 
 Recently, I have forgotten to backup one of rollback segment file 
 (rbs02.dbf). When I want to restore..the db can not startup normally.

<BR><FONT SIZE=2 FACE="Arial">Anyone can give me advise..&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">Thanks in advance.&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">Warm regards,&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">Hermanto&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">-- &lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">Please see the official ORACLE-L FAQ: <A

HREF="http://www.orafaq.com"
TARGET="_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=2 FACE="Arial">-- &lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">Author: Hermanto P&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&nbsp;&amp;nbsp;INET:

hermanto_p_at_aprilasia.com Fat City Network Services &nbsp; &nbsp;-- (858) 538-5051 &nbsp;FAX: (858) 538-5051 San Diego, California &nbsp; &nbsp; &nbsp; &nbsp;-- Public Internet access / Mailing Lists <FONT SIZE=2

FACE="Arial">-------------------------------------------------------------------

- 
 To REMOVE yourself from this mailing list, send an E-Mail message 
 to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
 the message BODY, include a line containing: UNSUB ORACLE-L 
 (or the name of mailing list you want to be removed from). &nbsp;You may also send the HELP command for other information (like subscribing).

<BR><FONT SIZE=2 FACE="Arial">&lt;/font&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">&lt;br&gt;</FONT>
<BR><FONT SIZE=2 FACE="Arial">-- </FONT>
<BR><FONT SIZE=2 FACE="Arial">Please see the official ORACLE-L FAQ: <A

HREF="http://www.orafaq.com" TARGET="_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=2 FACE="Arial">-- </FONT>
<BR><FONT SIZE=2 FACE="Arial">Author: </FONT>
<BR><FONT SIZE=2 FACE="Arial">&nbsp; INET: dgoulet_at_vicr.com</FONT>
</P>

FACE="Arial">-------------------------------------------------------------------

</P>
</UL>
</BODY>
</HTML>

Received on Tue Aug 14 2001 - 12:48:34 CDT