joel garry wrote:
> DA Morgan wrote:
>> Chuck wrote:
>>> hpuxrac wrote:
>>>> Chuck wrote:
>>>>> Oracle 9.2.0.7
>>>>>
>>>>> I got an ORA-1555 snapshot too old today on a export where consistent=y
>>>>> was set. The export failed at 6 hrs even though undo_retention was set
>>>>> to 8 hours. Querying v$undostat shows that during the time when the
>>>>> export was running a total of only 150m of undo was written. The undo
>>>>> tablespace is 4g. Why would I get an ora-1555 when undo_retention is set
>>>>> plenty high enough, and the undoblks written weren't anywhere near
>>>>> maxing out the undo tablespace?
>>>>>
>>>>> TIA
>>>> Tom Kyte has this all covered. Try looking for 1555 in
>>>> http://asktom.oracle.com
>>>>
>>> I've already been there and searched for 1555. I didn't find anything
>>> related to ora-1555 that matches this case. All the responses related to
>>> manual rbs management, cases where undo_retention was insufficient, or
>>> cases where the undo tablespace size was insufficient. My case does not
>>> fit any of these.
>> I agree. I followed HPUXRAC's links too thinking there was something I
>> had missed and found nothing like what you are reporting.
>>
>> I couldn't find an example on metalink so I'd suggest opening an SR.
>> I'll be interested in what you discover.
>
> They would likely find Note:174782.1, tell him someone else was
> updating an object so Oracle wouldn't be able to make a read-consistent
> view of the data for exp, and to use consistent=n as a workaround.
>
> Remember Chuck, the key to most ORA-155x errors is to think of what
> else is happening to the data. It is easy to think "well, no one else
> is on" when oracle still hasn't cleaned up things, or indeed, people
> shut off their client pc's and go home. Of course, if you are using
> consistent=y, that means you already know there will be other people
> updating after the exp starts - have you considered those that have
> started updating before the exp starts? George updates a row in
> big_transaction_table at 4PM, bunch of others update much stuff from
> 4PM to 1AM overwriting George's undo, export starts at 11PM, needs to
> construct view of big_transaction_table at 5AM, barfs. By the time you
> look at v$undostat at 5:10AM, it's all gone. Did the ORA- leave a
> query in the alert log? Do you have big-George batch jobs?
>
> Personally I like to kill off any attaches to the db before exporting
> without setting consistent, but that's reasonable in my environment.
> YMMV.
>
> jg
I looked for that and didn't find it. You obviously used better
search criteria than I.
--
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 Mon Dec 18 2006 - 19:01:07 CST
