ORA-1555 snapshot too old: why?

Are you the only one using the undo space? Maybe the undo is full with
data that cannot be expired yet. What kind of retention policy have you
set up?

Valentin

Are you the only one using the undo space? Maybe the undo is full with
data that cannot be expired yet. What kind of retention policy have you
set up?

Valentin

hpuxrac 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.
>
> Any chance you are committing inside a loop?

Please correct me if I'm wrong here but he's running an export
with consistent=y. How do you loop in an export?

thanks.
--
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

hpuxrac 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.
>
> Any chance you are committing inside a loop?

Please correct me if I'm wrong here but he's running an export
with consistent=y. How do you loop in an export?

thanks.
--
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

Another example of ageism
:-(

....you can also get ORA-1555 with a corruption. Maybe try
ANALYZE...VALIDATE STRUCTURE?

Another example of ageism
:-(

....you can also get ORA-1555 with a corruption. Maybe try
ANALYZE...VALIDATE STRUCTURE?

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 Database before exporting
without setting consistent, but that's reasonable in my environment.
YMMV.

jg
--
@home.com is bogus.
http://catless.ncl.ac.uk/Risks/24.51.html#subj1

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 Database before exporting
without setting consistent, but that's reasonable in my environment.
YMMV.

jg
--
@home.com is bogus.
http://catless.ncl.ac.uk/Risks/24.51.html#subj1

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

because undo_retention is not guaranteed in 9.2 and therefore cannot be
relied upon. Sad but true.

--
Niall Litchfield
Oracle DBA
http://www.orawin.info/services

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

because undo_retention is not guaranteed in 9.2 and therefore cannot be
relied upon. Sad but true.

--
Niall Litchfield
Oracle DBA
http://www.orawin.info/services