Re: stange rollback behavior

An export does not 'use' a rollback segment in the accepted sense of the word. An export reads data, and only writes to an external file (which does not generate undo). Assigning an export session to a specific rollback segment is pointless. It has no undo to write, so why does it need this?

The reason for the ORA-1555 is identified in your first sentence, "a very active database". A consistent export requires that the data in all tables exported be read consistent as of the time the export started. If the export takes 12 hours, all data must be reconstructed as it existed 12 hours ago, no exceptions.

Example (without using Fosters or Chewy Bars): Export starts and is assigned SCN 100
Update to tableA.row1.columnA occurs and is assigned SCN 200 (the undo (data as it existed before SCN 200) is written to RBS01.slot42). Transaction committed.
Heavy activity requiring undo (insert/update/delete) all assigned to rollback segments on a round robin algorithm. Update to tableB.row42.columnZZ occurs and is assigned SCN 1000 (the undo (data as it existed before SCN 1000) is writtent to RBS01.slot42 *this overwrites the undo related to SCN 200). Transaction committed. Export reads tableA. Finds row1 has been updated with an SCN (200) that is newer than the export SCN (100). Export locates transaction information and reads RBS01.slot42. However, the information from SCN 200 does not exist. Export is unable to recreate the data as it existed at SCN 100, so an ORA-1555 is issued and the export terminates.

For more information and a different explanation, please check out 'Cats, Dogs and ORA-01555' at www.evdbt.com or my Undo Internals papers/presentations at www.optimaldba.com.

There are several possible solutions. First, perform the exports at a time when the activity is very low. Second, export the static tables (lookup tables) and then export the dynamic tables. The shorter exports may help in reducing the chance of 1555. Third, increase the number of rollback segments and the corresponding files. Fourth, question the need for an export of the whole database. Is there a better method to achieve the end result?

-- 
Daniel W. Fink
http://www.optimaldba.com


Hatzistavrou John wrote:



> Dear All,

>

> In the past I had problem taking a consistent export from a very 

> active database. At that time I had ORA-1555 error.

>

> I though if I created a big rollback and force the export use it I 

> would resolve the problem.

>

> Thus I created an after logon schema trigger on the user I was 

> specifying on the export command .

>

> I have tested it and for a number of days thinks were going as expected.

>

> Yesterday the export failed with ORA-1555 again but on different 

> rollback than the one I have set in the after logon trigger.

>

> May someone of you  explain this?

>

> Kind Regards,

>

>

> Hatzistavrou Yannis

>

> Database Administrator

>






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

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