Re: Snapshot Too Old Error on Export !!!

In addition to the other comments about export not being a backup, let me add a few things. Managing the trade off between many rollback segments (good for oltp performance) and large rollback segments (necessary to avoid 1555) is often a tough one. As somebody else said, you can optimize export performance by doing direct path, but besides that, the time it takes to export each individual table (that would be the entire export if consistent=yes) must be smaller than the time it takes for any rollback segment to wrap (note, this is not the WRAP column of v$rollstat, rather you should compare the growth of the WRITES column with the RSSSIZE column).

If need an idea on a running system about how long the longest query can be without running into 1555, the attached can be used. It basically looks at v$rollstat twice with 10 seconds between them, and estimates how many hours it will take for the most rapidly used rollback segment to wrap. On a less busy system, you may want to modify the 10 seconds to something larger.

BTW, at a ct. I was working on, we had to go to 100 segments (due to OLTP requirements) of 1GB each (due to requirements to allow 4-5 hour queries to run without 1555 risk). Yes, this is 100Gb of rollback:-)

/Bjørn.

Jackson Dumas wrote:

>Hi all
>
>I have a problem when doing an export in one of ourt production
>databases. The export fails with ORA-01555, snapshot too old error.
>
>I have increased the number of rollback segments and their sizes on
>the database. Also I have went to an extent of specifying the
>parameter constent=n on my script but backups fails. The worst part is
>this export runs for a long time and then fails, more than 24 hours.
>
>The only time that this export succeed is over the weekend, because
>most of the time few people are working or not at all. Now I have
>tried to start it after hours but as I said it still takes long and
>end up failing the next day.
>
>Could somebody help me here, this is very critical to be running
>production without proper backups .....!
>
>Thanx
>
>_______________________________________________________________
> http://www.webmail.co.za the South-African free email service
>
> NetWiseGurus.Com Portal - Your Own Internet Business Today!
>
>
>

-- 
Bjørn Engsig, Miracle A/S
Member of Oak Table Network <http://www.oaktable.net>
Bjorn.Engsig@MiracleAS.dk - http://MiracleAS.dk





rem before running this, do 
rem SQL> create table temprollstat as select * from v$rollstat where 1=42
rem
variable aaaa varchar2(100);
insert into temprollstat select * from v$rollstat
/
exec :aaaa := to_char(sysdate,'DD-MON-YYYY HH24:MI:SS');
prompt hang on, waiting 10s to inspect undo bytes written...
host sleep 10
select l.usn, t.writes - l.writes byteswritten,
to_char((sysdate-to_date(:aaaa,'DD-MON-YYYY HH24:MI:SS'))*24*t.rssize/( t.writes - l.wr
ites ), '999999.9') hoursbeforewrap
from temprollstat l, v$rollstat t
where l.usn = t.usn
and   t.writes != l.writes
order by byteswritten
/
rollback
/


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?ISO-8859-15?Q?Bj=F8rn_Engsig?=
  INET: bjorn_at_miracleas.dk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).