snapshot too old error
zigzagdna
I am doing an expdp and get following error
expdp system/password directory=expdp_dir dumpfile=expdp.dmp
logfile=texpdp.log schemas=PQMS PARALLEL=1 exclude=statistics
compression=all
ORA-31693: Table data object ""COMPLAINT_LTR_ORIG" failed to load/
unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 6 with name
"_SYSSMU6_1265054678$"
My undo size can grow to 8G, and I see it initial size as 2G; so most
likely rest of undo was not used and increasing undo size will not
help.
I have a 24x7 system, and I want to do an export data pump without
error. How can I overcome this error? Will adding a flashback_time to
expdp help. Based on my knowledge of exp, it will make things worse as
for as ORA-1555 is concerned.
phil_h...@yahoo.com.au
delayed block cleanout, a fairly complex issue that Tom Kyte explains
here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923
-- Phil
Carlos
>
> -- Phil
Ah! The good old ORA-01555!
It's good to know that some things never change. Even in the all-new
Oracle 11g with all these buzz and whistles!
Cheers.
Carlos.
magicwand
zigzagdna,
This error occures, when at least 2 conditions are met:
1.) A long running Query (q)
2.) At least 1 short transaction (t) that modifies AND commits data
which will be selected by (q)
When (q) starts, Oracle guarantees read consistency, meaning you can
be sure that the result set of (q) reflects the state the db was in at
the start of (q).
If transaction (t) - started AFTER you started (q) - modifies data
that will be selected by (q) later on (because (q) is a long running
query), (q) will read the old, unmodified data out of the undo
segments and everythig is as it should be.
The problem now is, that after (t) commits, the undo segments are
still holding the consistent data block(s), but are released and may
be used (and overwritten) by other transactions.
When (q) discovers the situation above, it will terminate with
ORA-1555.
There are basically 2 solutions to this problem:
a.) Dont do transactions while running such an extended query (which
will not be possible in real environments)
b.) Change the undo_retention - pararameter of your database.
UNDO_RETENTION (the default is 900 seconds) tells the system how
long to wait before reusing the undo - segments after they have been
released by the transaction.
So if your pump-export needs i.e. 1 hour you should set this parameter
to 3600 or higher.
Note however, that undo_retention will be set for ALL undo - segments
in your system, so - depending on your transaction structure - you
might need a bigger undo-tablespace.
Mladen Gogala
> I have a 24x7 system, and I want to do an export data pump without
> error. How can I overcome this error? Will adding a flashback_time to
> expdp help. Based on my knowledge of exp, it will make things worse as
> for as ORA-1555 is concerned.
Impose a shared lock on the table.
joel garry
There's a bug if you plug in a transportable tablespace and then
create a controlfile. If you've done that, see MOS 1066229.1 Updated
blocks in the tablespace confuse the issue.
Also, if you want something frightening, copy the table with something
like http://oracle-randolf.blogspot.com/2009/04/read-consistency-ora-01555-snapshot-too.html
and export that. If the problem is delayed block cleanout, you then
perhaps could export the original table.
In the 10g docs it mentions that this can happen with a lot of
metadata, google "Data Pump Export and Import consume more undo
tablespace than original Export and Import." I would expect it to be
fixed in 11g, but... it does bring up the issue of whether the error
message is being honest about which object is the cause of the error.
The error message is saying the result of the error. Kinda like
shooting the messenger - the cause isn't necessarily anything the
messenger did wrong.
You might want to let us know your undo parameters.
jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2010/jun/01/hewlett-packard-to-cut-9k-jobs-see-1b-in-charges/
zigzagdna
> On May 31, 1:43 pm, zigzagdna <zigzag...@yahoo.com> wrote:
>
>
>
>
>
> > I am on Oracle 11.1.0.7 and HP UNIX 11.23i.
> > I am doing an expdp and get following error
> > expdp system/password directory=expdp_dir dumpfile=expdp.dmp
> > logfile=texpdp.log schemas=PQMS PARALLEL=1 exclude=statistics
> > compression=all
> > ORA-31693: Table data object ""COMPLAINT_LTR_ORIG" failed to load/
> > unload and is being skipped due to error:
> > ORA-02354: error in exporting/importing data
> > ORA-01555: snapshot too old: rollback segment number 6 with name
> > "_SYSSMU6_1265054678$"
> > My undo size can grow to 8G, and I see it initial size as 2G; so most
> > likely rest of undo was not used and increasing undo size will not
> > help.
> > I have a 24x7 system, and I want to do an export data pump without
> > error. How can I overcome this error? Will adding a flashback_time to
> > expdp help. Based on my knowledge of exp, it will make things worse as
> > for as ORA-1555 is concerned.
>
> There's a bug if you plug in a transportable tablespace and then
> create a controlfile. If you've done that, see MOS 1066229.1 Updated
> blocks in the tablespace confuse the issue.
>
> Also, if you want something frightening, copy the table with something
> and export that. If the problem is delayed block cleanout, you then
> perhaps could export the original table.
>
> In the 10g docs it mentions that this can happen with a lot of
> metadata, google "Data Pump Export and Import consume more undo
> tablespace than original Export and Import." I would expect it to be
> fixed in 11g, but... it does bring up the issue of whether the error
> message is being honest about which object is the cause of the error.
> The error message is saying the result of the error. Kinda like
> shooting the messenger - the cause isn't necessarily anything the
> messenger did wrong.
>
> You might want to let us know your undo parameters.
>
> jg
> --
>
> - Show quoted text -
I changed default 800 seconds undo_retention to 1 day. I got past that
error, but now another error came on a different table:
ORA-00600: internal error code, arguments: [b], [23786], [121640375],
[121640408],
Could not find anything on kdliSyncRead on google or metalink.
Looked at trace file, seems like some core dump.
Oracle 11.1.0.7.1 has all kind sof bugs; problem is now I am in
production so cannot back out of this release.
I
Noons
> I changed default 800 seconds undo_retention to 1 day. I got past that
> error, but now another error came on a different table:
> ORA-00600: internal error code, arguments: [b], [23786], [121640375],
> [121640408],
> Could not find anything on kdliSyncRead on google or metalink.
> Looked at trace file, seems like some core dump.
>
> Oracle 11.1.0.7.1 has all kind sof bugs; problem is now I am in
> production so cannot back out of this release.
Narh! Not possible!
So let me see: those of us who Oracle called "dinossaurs" because we
refused to upgrade - knowing the problems with new releases of Oracle
- were right after all?
Wonders will never cease...
Steve Howard
This is really scary. The problem is, everyone will just say, "well,
you should be on 11.2.0.1, anyway". In a year or so when another post
comes through talking about how buggy that release is, the cycle will
repeat itself.
John Hurley
# I changed default 800 seconds undo_retention to 1 day. I got past
that error, but now another error came on a different table:
> ORA-00600: internal error code, arguments: [b], [23786], [121640375], [121640408], Could not find anything on kdliSyncRead on google or metalink. Looked at trace file, seems like some core dump.
... Oracle 11.1.0.7.1 has all kind sof bugs; problem is now I am in
production so cannot back out of this release.
Pretty darn stable for me so far all things considered.
Seems like your plan for testing out your planned release level before
moving into production was missing some really big pieces.
Why on earth would one put in a very large undo tablespace with a very
small value set for undo_retention unless one had missed many of the
important concepts involved here in this very important piece of
system architecture?
John Hurley
> > Oracle 11.1.0.7.1 has all kind sof bugs; problem is now I am in
> > production so cannot back out of this release.
>
> Narh! Not possible!
> So let me see: those of us who Oracle called "dinossaurs" because we
> refused to upgrade - knowing the problems with new releases of Oracle
> - were right after all?
> Wonders will never cease...
Well actually some of the dinosaurs are running that release and
finding it pretty good. Some of the dinosaurs though actually do a
systematic job of planning and testing things.
You have to remember the OP in this thread did not seem to know where
to find resources and information on snapshot too older ... so take
some of the complaints from the OP with a large degree of skepticism.
There is a large difference between refusing to upgrade because one
has tested and found things in their environment and and their
applications which cause serious ( or critical ) errors and refusing
to upgrade just because something is new. Ya gotta remember that 11.1
has been out now for a pretty long time eh kimosabe?
We will be starting the phase of seriously checking out 11.2 just as
soon as the first patchset is available although it might be sooner.
zigzagdna
Every release has soem issues. I tested Oracle 11g for 6 months; yet
many of the problems found in production never showd in testing. I
ahev following major issues with 11.1.0.7.1 on HP UNIX 11i which I
used:
1. db control does not work. I had problem in creating db control in
QA, but in production I did not get any errors during database
instance creaion. BUt then I found in laert.log, it is generating core
dmp every minute. I removed db control from databas eand problem went
away.
2. Export dump has errors as in this thread.
3. rman has bugs sometimes it ends up in an infinite loop during
archived log backup and my lof file keeps growing and fills my file
system.
4. Once I restarted the database and it showd it mounte dbut never
showd db opened, eventhough db was opened.
Prem
Mladen Gogala
> I changed default 800 seconds undo_retention to 1 day. I got past that
> error, but now another error came on a different table: ORA-00600:
> internal error code, arguments: [b], [23786], [121640375], [121640408],
> Could not find anything on kdliSyncRead on google or metalink. Looked
> at trace file, seems like some core dump.
No, it's more like the blue screen of death.
Noons
> This is really scary. The problem is, everyone will just say, "well,
> you should be on 11.2.0.1, anyway". In a year or so when another post
> comes through talking about how buggy that release is, the cycle will
> repeat itself.
It's been repeating itself for the last 15 years, on every single major release.
Noons
> Well actually some of the dinosaurs are running that release and
> finding it pretty good. Some of the dinosaurs though actually do a
> systematic job of planning and testing things.
Or just plain do not stress out the relevant bits of code?
> You have to remember the OP in this thread did not seem to know where
> to find resources and information on snapshot too older ... so take
> some of the complaints from the OP with a large degree of skepticism.
Absolutely. They seem remarkably similar to what I've been seeing in the lists
of fixed bugs in the patch releases, though...
> There is a large difference between refusing to upgrade because one
> has tested and found things in their environment and and their
> applications which cause serious ( or critical ) errors and refusing
> to upgrade just because something is new.
Or refusing to upgrade because there is simply nothing in that release that is
relevant to the applications being used? Or even supporting those applications?
> Ya gotta remember that 11.1
> has been out now for a pretty long time eh kimosabe?
You gotta remember that 9.1 has been out for a pretty long time eh kimosabe?
> We will be starting the phase of seriously checking out 11.2 just as
> soon as the first patchset is available although it might be sooner.
Good. Is the cost of that testing and the upgrades being accounted for to the
business?
joel garry
Is the cost of being on an out of support release being accounted for
to the business? eh kimo-sahib-bwanna-massa? :-) *
jg
--
@home.com is bogus.
Wandered through my local Westfield Mall yesterday about 2:00PM. It
was eerily like the old SNL routine with the dying mall, where the pet
shop's puppies had puppies.
* Reference from "Congress of Wonders" skit _The Stoned Ranger_
http://hedtke.blogspot.com/2009/07/congress-of-wonders.html
Noons
>
> Is the cost of being on an out of support release being accounted for
> to the business? eh kimo-sahib-bwanna-massa? :-) *
LOL! Wouldn't have a clue: we have everything in 10.2.0.3.
Last time I looked that was under support
eh, kimo-bwanna-muzungo-estaleka-maningwe?
(!) >¦)