Voici un exercice qui prouve deux choses:

Nous pouvons mettre sur pieds une instance lors de la perte d’un segment rollback, même dans le cas de la non utilisation de ARCHIVELOG.

Il faut être en mode archivelog. Ceci vient également appuyer la thèse de la disponibilité 24/7 d’une base des données.

Histoire : Nous avons perdu un Rollback Segment

>

>When you talk about bye passing the corrupted rollback segments do you

>loose any actual data, and if so what way we can recover it?

What you may get is an inconsistent database.

Consider the following

SVRMGR> connect internal

Connected.

SVRMGR> select sal from scott.emp where ename = 'KING';

SAL

------------

5000

1 row selected.

SVRMGR> rollback;

Statement processed.

SVRMGR> set transaction use rollback segment r01;

Statement processed.

SVRMGR> update scott.emp set sal=4999 where ename = 'KING';

1 row processed.

SVRMGR> alter system checkpoint;

Statement processed.

SVRMGR> shutdown abort

---------------------------------------------------------------------

Now mark rollback segment r01 as being corrupted and restart the

database,

ORACLE instance shut down.

SVRMGR> startup

ORACLE instance started.

SVRMGR> select sal from scott.emp where ename = 'KING';

SAL

----------

4999

1 row selected.

SVRMGR> exit

Notice the uncommitted update has not rolled back. Your alert log

will also have something along the lines of

SMON: about to recover undo segment 2

SMON: mark undo segment 2 as needs recovery

SMON: about to recover undo segment 2

SMON: mark undo segment 2 as needs recovery.

What this all means is that your database is now inconsistent.

Edit the init.ora file to remove the _corrupted_rollback_segments

parameter; i.e rollback segment R01 is no longer marked as being corrupted.

SVRMGR> connect internal

Connected.

SVRMGR> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SVRMGR> startup

ORACLE instance started.

SVRMGR> select sal from scott.emp where ename = 'KING';

SAL

>----------

5000

1 row selected

I posted this a few months ago during a discussion of whether rollback

segments were needed for recovery. I have edited things down a bit,

and hope that no context has been lost.

Ian MacGregor

Stanford Linear Accelerator Center

ian@slac.stanford.edu

>I did use this command but does not know the actual implications of

>this this because I was forced to reconstruct the data (demo database)

>after a hell of problems.

>

>If I am correct Oracle TS doesnot recommend using this parameter

>yourself without consulting them. Also it is suggested to use

>_offline_rollback_segments parameter first and try to bring up the db

>and do other sort of stuff.

>

>Lakshmikanth V Bhat

>

>---Jeroen van Sluisdam wrote:

>>

>> Hi Ravi,

>>

>> I remembered having read somewhere about a similar problem and when I

>> dug into

>> my own little archive of usefull mails I found the one below.

>> Hope it can help,

>>

>> Jeroen

>> .................................

>> Jeroen,

>>

>> I think you need to leave the _corrupted_rollback_segments parameter

>in

>> your init.ora file. If R01 and R02 were the names of the corrupted

>rbs

>> segments then the line would look like:

>>

>> _corrupted_rollback_segments = (R01, R02)

>>

>> Drop the rollback segments (offline and drop) and create new segments

>> with new names. I recall not being able to use the name of the

>original

>> rollback segment that got corrupted, I used different names such as

>R03

>> and R04. Then I left the _corrupted_rollback_segments = ... parameter

>> string in the init.ora file. When the database successfully

>started, I

>> brought the new rollback segments R03 and R04 online.

>>

>> I suggest

>> (1) put into the init.ora file the _corrupted_rollback_segments =

>(xxx,

>> yyy) line, with the correct values for your database and the names of

>> the rollbacks that were initially corrupted

>>

>> (2) Offline and drop any rollback segments with those names (since

>they

>> may have been reused), and create new rollback segments with different

>> names.

>>

>> (3) Never use the corrupted rollback segments names again.

>>

>> Hope that helps ... if you've gone this long without the database,

>maybe

>> rebuilding it is a better option.

>>

>> If you're still seeing "corrupted rollback segment" error messages at

>> this point, you may have more than one corrupted rollback segment -

>> repeat steps 1-3.

>>

>> -sooz

>>

>> Jeroen van Sluisdam wrote:

>> >

>> > Hi,

>> >

>> > Last week I read your mail concerning corrupted rollback segments.

>> > How do I use this one ? Yes (default ?) use them and no, don't use

>> > them or something completely differtent ?

>> >

>> > Thanks,

>> >

>> > Jeroen

>> >

>> > On Wed, 07 Oct 1998 21:40:21 -0700, in comp.databases.oracle.server

>> > sooz wrote:

>> >

>> > >This happened to me once too. That hidden param is a life saver.

> I

>> > >found that I could not remove the _corrupted_rollback_segments

>entry

>> > >from the init file though, and therefore could not reuse the

>> corrupted

>> > >rollback segments name again.

>> > >

>> > >Maybe there is something left in the data dictionary that just

>never

>> > >allows you to actually remove the init parameter or reuse the name.

>> > Try

>> > >it.

>> > >Good luck

>> > >- sooz

>> > >

>> > >jimde@my-dejanews.com wrote:

>> > >>

>> > >> A week ago we suffered a corrupted rollback segment and after

>much

>> > >> deliberation brought the database back online using the hidden

>> > parameter

>> > >> _corrupted_rollback_segments The affected rollback segment was

>> > dropped and

>> > >> rebuilt, the hidden paremeter removed. The database then had a

>cold

>> > backup and

>> > >> restarted. Every evening since a hot backup has been taken.

>Oracle

>> > however say

>> > >> the that database is 'corrupted' and should be rebuilt. Can

>anyone

>> > explain if

>> > >> this is true and why?

>> > >>

>> > >> -----------== Posted via Deja News, The Discussion Network

>> > ==----------

>> > >> http://www.dejanews.com/ Search, Read, Discuss, or Start

>Your

>> > Own

>>

>> -----Oorspronkelijk bericht-----

>> Van: B.C.Ravi [mailto:bflbrav@bfl.soft.net]

>> Verzonden: Friday, November 06, 1998 1:03 PM

>> Aan: 'oracledba@MailingList.net'

>> Onderwerp: ORA-600 continuiation...604..

>>

>>

>> Hi ! All,

>>

>> The database starts opens correctly, while log the following

>> message.......

>>

>> ERROR: ORA-00604: error occurred at recursive SQL level 1

>> ORA-01578: ORACLE data block corrupted (file # 3, block # 80)

>> ORA-01110: data file 3: 'C:\ORANT\DATABASE\CPX_RB.ORA'

>>

>> One of the rollback segments status is 'NEEDS RECOVERY' so if try to

>> make the status to online it is not allowing.

>> Both dropping tablespace and dropping segment are not allowed when the

>> status of a segment is 'NEEDS RECOVERY'

>>

>> My idea is to drop and recreate the rollback segment... any

>solutions in

>> this regard.

>>

>>

>> Thanks,

>> Ravi

>>

>

>

>==