Ben wrote:
> DA Morgan wrote:
>> Ben wrote:
>>> 9.2.0.5 EntEd AIX5L
>>>
>>> We are using rollback segments due to the fact that our compatible
>>> parameter is still set to 8.1.0
>>>
>>> On to the problem.
>>> A user calls and ooops, they've updated 2000 records that shouldn't
>>> have been updated. I get a call wanting me to recover/restore that
>>> table from last night's backup from tape. I don't want to so I tell
>>> them try to use a flashback query.
>>>
>>> I ran the query and it returns the first portion 50% of the rows but
>>> then gives me an ORA-01555: snapshot too old: rollback segment number
>>> 121 with name "RBS204" too small.
>>> Is that due to the fact that her update or another transaction aged out
>>> the portion of the data that is giving this, or is due to the RBS204
>>> rollback seg not being large enough to give me all the data that is
>>> being returned?
>>>
>>> Is there something I can do to get it back, short of restore from tape?
>> It could be either. But the solution is straight forward.
>>
>> 1. Patch to 8.1.7.4
>> 2. Enlarge those rollback segments
>>
>> You might want to then work on management to move to 10g before your
>> resume petrifies. <g>
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
>
>
> We are actually on 9.2.0.5 with a compatible parameter set to 8.1.0 but
> point taken.
>
> So but if I enlarge that rollback seg, will it then allow the flashback
> query to work? The rows in question shouldn't require 200M of rollback
> and that is what the segment has as optimal.
If you are on 9.2.0.5 my recommendation would still be to apply more
recent patches and to change the compatible parameter so that you can
move from ROLLBACK to UNDO. Then Oracle will likely solve the problem
and you can find something else to focus on. <g>
If for some reason you can't ... then yes enlarge the rollback segments:
A lot. If you have disk ... use it.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Dec 07 2006 - 14:40:52 CST
