Re: Snapshot Too Old Error on Export !!!

[Bjørn Engsig]

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
[EMAIL PROTECTED] - 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
/