SNAPSHOTS and Exports
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SNAPSHOTS and Exports

  1. #1
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    HP 11.0
    Oracle 8.0.6.0.0

    I have the following issue and I'm trying to determine why it occurs.

    Export a schema that owns the SNAPSHOTS. i.e SNAP$_ tables.

    The snapshots are refreashed and this process completes at 7am.

    The OWNER export of the snapshots starts at 7:30. Using CONSISTENT=N.

    But the process ends up getting

    EXP-00008: ORACLE error 1555 encountered
    ORA-01555: snapshot too old: rollback segment number 4 with name "R03" too small
    EXP-00000: Export terminated unsuccessfully

    Now, I'm trying to determine why the ROLLBACK is being used. Surely, once the SNAP$_ refresh process has finished and consistent read from the rollback segments wouldn't be needed during the export.

    Any ideas,

    Cheers
    OCP 8i, 9i DBA
    Brisbane Australia

  2. #2
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Hi,

    As far as I know any table being exported needs rollback segment for read consistency. Hope I understood it right.

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi.

    The following thread is identical to yours and answers your question.

    http://www.dbasupport.com/forums/sho...threadid=16920

    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Thanks, but the other thread talks about changes being made to the tables at the same time as the export occurs, or the making use of the CONSISTENT=Y. In my case, the tables are not changing, they are READONLY access to users AFTER the snapshot refreash has occured and I'm not using the CONSISTENT paramerter in the par file. Therefore, what I'm wondering is, just becasue the REFREASH process has completeted, is there still data needed to make a consistent read of the data.

    I've also exported large GB database without any problems with rollbacks, and when the rollback segments have been tiny and the RBS tablesapce being about 500 MB, so I don't believe the ROLLBACK SEGMENTS are used in every export function unless of course they are used for a consistent read.

    Any more ideas peps.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Hi ,

    There might be some users accessing the table. Try restricting
    access and export it as the only user in the database.

  6. #6
    Join Date
    Aug 2001
    Posts
    111
    Check to see who is using the rbs whilst performing the export. I would expect that this will be the user doing the export.
    The next thing to do would be to pass a table list (via the export parfile) and therefore only export the tables not the snapshots, see if this stops the error.
    I am not sure if this is a valid workaround or not, but the snapshots are just copies of data elsewhere so why should they be backed up???

    Have Fun
    Performance... Push the envelope!

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    roobaron,

    Getting back to it. Basically the Origional tables is from another system, and the snapshot are refreased via a DBLINK. After the the snapshots are refreashed the clients wants us to maintain exports of the SNAPSHOT data for historical purposes, therefore we then export the snapshots from our systemTherefore, after the snapshot refrash process completes the export process starts, and then we have the rollback issue. I'm going to have to set up a script to capture the rollback activity during the export.

    I mean nobody is quering the data at the time, and the Refreash has completed, I can't understand why the export would be using a rollback segment for read consistency if the data in the SNAP$ table is consistent.

    Hmmm, anyway.

    Cheers,


    [Edited by grjohnson on 11-07-2001 at 11:59 PM]
    OCP 8i, 9i DBA
    Brisbane Australia

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by grjohnson
    I mean nobody is quering the data at the time, and the Refreash has completed, I can't understand why the export would be using a rollback segment for read consistency if the data in the SNAP$ table is consistent.
    Try performing a full scan of those snapshots before you do your export. One of the frequent causes of ORA-1555 is "delayed block cleanout", which means that even only reading the block might change the block content.

    Are you sure nobody is actualy reading your snapshots while export is going on? Are only snapshots exported, no other table? Are you sure you are getting ORA-1555 while exporting snapshot?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width