ORA-01555: snapshot too old from a remote database.
George Barbour
Hi, has anybody got suggestions for this prob.
I copy large tables every night from a remote database. By remote, I mean I
have no access other than SELECT. I cannot communicate with the remote
database DBA in any way.
I create the tables as 'create table emp as select * from e...@foo.world I
cannot cut the table into little chunks either.
If this copy happens during the night, fine no problems. If for any reason
it fails, then I find myself trying to copy the files during the day,
against the tide. I spend many happy hours repeating copies that have
ORA-01555: snapshot too old: against them.
I wish to set the ROLLBACK SEGMENT to their RBS of rbig, I cannot. Oracle
support tell me that you cannot SET a ROLLBACK SEGMENT for a query over a
DATABASE LINK.
It is the session on the INSTANCE at the remote site that is causing the
snapshot too old errors.
I am kinda at a loss here.
George Barbour.
P.S. Please don't ask why I cannot talk to the DBA at the remote site. It
involves the words 'manager', 'brewery' and 'cannot organise a .... in one.
And judging from some of this forums responses I know it will only make you
angry. ;-)
Dave Wotton
>Hi, has anybody got suggestions for this prob.
> I copy large tables every night from a remote database. By remote, I mean I
>have no access other than SELECT. I cannot communicate with the remote
>database DBA in any way.
>I create the tables as 'create table emp as select * from e...@foo.world I
>cannot cut the table into little chunks either.
>
>If this copy happens during the night, fine no problems. If for any reason
>it fails, then I find myself trying to copy the files during the day,
>against the tide. I spend many happy hours repeating copies that have
>ORA-01555: snapshot too old: against them.
>
You're stuck. You (or rather, your management) have tied your hands
to make your task impossible.
The one little loophole is the "I cannot cut the table into little
chunks either". Why not? - that's under your control surely? You
could write a PL/SQL script to copy the table(s) over in chunks,
BUT you wouldn't be able to guarantee that the data would be
consistent. (ie. if it's being updated whilst you're copying it,
you can't guarantee that you'll get all the updates copied across
OK. If you need that, you'll have to get the rollback segments on
the remote machine sorted out. Full stop.)
If you are able to disregard consistency then take a look at my
web-page http://home.clara.net/dwotton/dba/snapshot.htm which
describes the causes of snapshot too old and contains a sample program
which you could use as a model for your copy program.
If you can't or won't take that approach, simply refuse to spend
hours trying to copy your data during the day, you're just wasting
your time and the company's money. If the copy fails, just shrug
your shoulders and say "Leave it, it'll copy down tonight".
If any manager objects, just point out that there's nothing else
you can do, your company procedures prevent you solving the problem.
Put the ball back in their court. Feel free to quote this reply if
you want.
Dave.
--
If you reply to this posting by email, remove the "nospam" from my email
address first.
George Barbour
When I ask a question on the forum, there is always this scary nagging doubt
that I have missed something obvious. It is reassuring to have your answer,
confiming my doubts about our set-up here.
Thanks again.
"Dave Wotton" <Dave....@dwotton.nospam.clara.co.uk> wrote in message
news:LXNq5.4$Eq6...@nnrp3.clara.net...
Howard J. Rogers
rollback segments are too small. Transactions have wrapped round the
segment, and overwritten a block which is no longer needed for transaction
rollback, but which is needed for read-consistent images of data.
Make your segments bigger. Preferably by recreating the segments with
larger extents. Or you could add extra extents to the segment.
Regards
HJR
"George Barbour" <george....@gecm.com> wrote in message
news:39ab96d7$1...@pull.gecm.com...
> All,
> Hi, has anybody got suggestions for this prob.
> I copy large tables every night from a remote database. By remote, I mean
I
> have no access other than SELECT. I cannot communicate with the remote
> database DBA in any way.
> I create the tables as 'create table emp as select * from e...@foo.world I
> cannot cut the table into little chunks either.
>
> If this copy happens during the night, fine no problems. If for any reason
> it fails, then I find myself trying to copy the files during the day,
> against the tide. I spend many happy hours repeating copies that have
> ORA-01555: snapshot too old: against them.
>
> I wish to set the ROLLBACK SEGMENT to their RBS of rbig, I cannot. Oracle
> support tell me that you cannot SET a ROLLBACK SEGMENT for a query over a
> DATABASE LINK.
> It is the session on the INSTANCE at the remote site that is causing the
> snapshot too old errors.
>
> I am kinda at a loss here.
Howard J. Rogers
then you will discover a couple of scripts called something like
'prevent_1555.sql' which will guarantee prevention of a 1555 error. The
trick is to raise a blocking transaction as part of your copy procedures
which has the happy benefit of preventing over-writing of the rollback block
contaiing the key information required for read-consistency generation.
Suggestions that you can't do anything about the problem are entirely wrong.
Don't go to management shrugging your shoulders... you can fix this problem
very easily provided you have enough disk space to allow the various
rollback segments you have to grow as a result of protecting the
read-consistent block(s) with a blocking transaction.
Regards
HJR
"Howard J. Rogers" <howa...@iprimus.com> wrote in message
news:39acf3cf$1...@news.iprimus.com.au...
Dave Wotton
Howard J. Rogers wrote in message <39ac...@news.iprimus.com.au>...
[snip]
>Suggestions that you can't do anything about the problem are entirely wrong.
>Don't go to management shrugging your shoulders... you can fix this problem
>very easily provided you have enough disk space to allow the various
>rollback segments you have to grow as a result of protecting the
>read-consistent block(s) with a blocking transaction.
Whilst accepting that creating a blocking transaction *may* solve the
problem, I fundamentally disagree about using this sort of subterfuge to
get around ignorance within an organisation.
I'll admit that I hadn't thought about this approach, but it is a
messy solution and it's unclear that it'll work for George: It relies on
him (a) having access to the view sys.rollback_segs (to see what rollback
segments he needs to block), (b) having the privilege to issue "alter
rollback segment ... shrink" commands (optional) and (c) having update
access to the remote database.
My understanding of his posting was that he is not the DBA of the remote
database and so won't be able to fulfill (a) or (b) and, if we take his
assertion that "I have no access other than SELECT" at face value, he
hasn't got (c) either.
His basic problem is not technical, solvable by a technical "fix", "bodge"
or whatever else you might call it, but procedural: he is trying to deal
with a DBA and management who have placed hurdles in his way on the one
hand and are asking him to produce a result on the other. The solution is
not to spend hours, days or weeks either repeating the download until it
works or finding obscure solutions to overcome the hurdles, which no-one
will understand when George moves on to another job, but to address the
fundamental problems within the organisation.
Howard J. Rogers
I sort of agree with you Dave: the cause of this problem is doing things in
the middle of a transactional day which should more properly be done at the
dead of night.
That said, the proposed solution is not a 'bodge', nor is it particularly
messy. The 1555 error message, when queried, tells you (as a proposed
action) to recereate your rollback segments with larger and/or more extents.
Creation of a blocking transaction simply causes the rollback segments to
increase in size on a dynamic basis -if you'd followed the error message's
advice, they would have been originally created bigger anyway.
If you use the scripts suggested on Steve Adams' web site, you'll discover
that George does not need access to dba_rollback_segments to discover which
segments need to be blocked: the scripts require (from memory) a blocking
transaction to be raised in *all* rollback segments. There is also an
argument that he does not need privileges to shrink rollback segments: since
his particular circumstances require larger rollback segments, then there is
a case for leaving a rollback segment that has grown as a result of the
blocking transaction at the size it swells to -clearly, such a size would be
that required for his procedures to complete effectively.
I do however agree that 1555 inevitably arises when you are doing things for
which your setup has not been prepared (ie, rollback segments which are too
small or inadequate privileges), and of course that is the fundamental
problem that needs to be addressed.
Regards
HJR
George Barbour
"Howard J. Rogers" <howa...@iprimus.com> wrote in message
news:39acf3cf$1...@news.iprimus.com.au...
> The short answer to any 1555 Snapshot Too Old error message is that your
> rollback segments are too small. Transactions have wrapped round the
> segment, and overwritten a block which is no longer needed for transaction
> rollback, but which is needed for read-consistent images of data.
With all respect I don't think you understand my problem.
> Make your segments bigger. Preferably by recreating the segments with
> larger extents. Or you could add extra extents to the segment.
No, you certainly do not understand it.
Dave Wotton answered the question on 29/08/2000 13:30. His answer is worth a
read, as is the article he wrote, that is mentioned in the text.
I know people are only trying to help when they reply to questions in this
forum.
But I have noticed on many occasions people giving answers to questions that
were not asked, and totally missing the point of the question, which has
obviously happened in this case.
I am not a beginner DBA by any means, also I not a guru (whatever that is)
I use this and other forums to ask questions about Oracle which is a vast
subject, and I believe, now more than ever, that no one person can have all
the answers.
I very much appreciate the feedback I get from the people like Dave Wotton
but sometimes, get a bit saddened having to seperate wheat from chaff.
George Barbour
> >
> "George Barbour" <george....@gecm.com> wrote in message
> news:39ab96d7$1...@pull.gecm.com...
> > All,
> > Hi, has anybody got suggestions for this prob.
> > I copy large tables every night from a remote database. By remote, I
mean
> I
Dave Wotton
[snip]
>If you use the scripts suggested on Steve Adams' web site, you'll discover
>that George does not need access to dba_rollback_segments to discover which
>segments need to be blocked: the scripts require (from memory) a blocking
>transaction to be raised in *all* rollback segments.
I did read the scripts before posting my earlier reply. Did you? They
definitely require read access to the sys.dba_rollback_segs view.
> There is also an argument that he does not need privileges to shrink
> rollback segments
Noted: that's why I put the word "optional" against this requirement in
my previous posting.
Dave.
Howard J. Rogers
>
> "Howard J. Rogers" <howa...@iprimus.com> wrote in message
> news:39acf3cf$1...@news.iprimus.com.au...
> > The short answer to any 1555 Snapshot Too Old error message is that your
> > rollback segments are too small. Transactions have wrapped round the
> > segment, and overwritten a block which is no longer needed for
transaction
> > rollback, but which is needed for read-consistent images of data.
>
> With all respect I don't think you understand my problem.
I understand you have a snapshot too old error because you are being asked
to do something with limited privileges and insufficient support. Have I
missed something?
>
> > Make your segments bigger. Preferably by recreating the segments with
> > larger extents. Or you could add extra extents to the segment.
>
> No, you certainly do not understand it.
>
No, the cause of 1555's is well understood. The fact that you are not in a
position to recreate segments, or add extents, doesn't negate the fact that
the size of your rollback segments or the lack of extents (which amount to
the same thing in this debate) is the root cause of your problem. 1555's are
*only* caused by an inadequate rollback segment. Period.
And since you go on to state you are NOT a 'beginner DBA', you would be
familiar with this fact.
Dave Wooton's answer was to say that you are being asked the impossible (I
hope I am not mischaracterising his response). My answer is the same, if
one accepts the parameters of your question without demur. My only point is
that 1555 is easily curable by *someone* -if not by you, then someone else.
I realise the parameters of your question rule out talking to the remote
DBA, but I rather think it should be known that 1555 is curable, in general.
> Dave Wotton answered the question on 29/08/2000 13:30. His answer is worth
a
> read, as is the article he wrote, that is mentioned in the text.
> I know people are only trying to help when they reply to questions in this
> forum.
> But I have noticed on many occasions people giving answers to questions
that
> were not asked, and totally missing the point of the question, which has
> obviously happened in this case.
> I am not a beginner DBA by any means, also I not a guru (whatever that is)
> I use this and other forums to ask questions about Oracle which is a vast
> subject, and I believe, now more than ever, that no one person can have
all
> the answers.
> I very much appreciate the feedback I get from the people like Dave Wotton
> but sometimes, get a bit saddened having to seperate wheat from chaff.
>
Dave's answer addresses the facts of your particular case; it would be
incorrect to characterise that as a feature of Oracle. His answer, in other
words, says more about your specifics than it does "about Oracle".
I don't criticise it for that at all, and I appreciate the point you are
making. However: you asked originally for "suggestions" -and the
suggesstion that 1555 should never happen (and can be prevented from
happening) is a fair one.
Your point that "no one person can have all the answers" is utterly valid.
I would hope that no-one thinks that could ever be the case.
Regards
HJR
Howard J. Rogers
> Howard J. Rogers wrote in message <39ae...@news.iprimus.com.au>...
> [snip]
> >If you use the scripts suggested on Steve Adams' web site, you'll
discover
> >that George does not need access to dba_rollback_segments to discover
which
> >segments need to be blocked: the scripts require (from memory) a blocking
> >transaction to be raised in *all* rollback segments.
>
> I did read the scripts before posting my earlier reply. Did you? They
> definitely require read access to the sys.dba_rollback_segs view.
I didn't read them before posting (hence the 'from memory' comment). It's
true that the script as published selects from dba_rollback_segs, so as far
as that goes, I was wrong. Completely and utterly.
On the other hand, the script only uses that view to generate the list of
rollback segments in which to create the blocking transactions. It would be
perfectly possible to use the technique without access to
dba_rollback_segments, provided you *once* found out what rollback segments
existed -the list could be hard-coded, rather than generated on the fly.
And that was my essential point.
On yet another hand, however, I see that it is also necessary to create a
segment in the SYSTEM schema if you use the technique as Steve describes,
which again means rather more privileges for the User involved than would
appear to be available in this particular case.
But on the final hand (seems this one's a bit of a handful!), that SYSTEM
segment is only needed if you are going to automate this process, and
automatically de-protect the rollback segments after a certain amount of
time etc. Once again, if you were simply to manually raise a transaction in
each (known) rollback segment before undertaking the particular task in
hand, and then manually rollback the relevant transactions at the end of the
process, I don't see off the top of my head that any special privileges
would be needed. "Set transaction use rollback segment Y" doesn't require
any system privileges that I can think of(?).
It all seems rather academic now, since George is happy with your 'can't do
it if you don't give me appropriate privileges' approach, and that's fair
enough. It just strikes me that Steve Adam's *approach* (messy as you seem
to find it) could be followed -in principle- by anyone, once the list of
rollback segments is known.
Anyway,
Regards
HJR