Home » RDBMS Server » Server Utilities » ORA-01555: snapshot too old during export. (10.2.0.3.0 - 64bit Production, Server 2003 R2 Standard x64 Service Pack 2)
ORA-01555: snapshot too old during export. [message #430509] Tue, 10 November 2009 15:06 Go to next message
nielsentyler
Messages: 6
Registered: November 2009
Location: United States
Junior Member
Upon using this command during an export of my database

exp user/password@sid file=z:\Migrate\wc8m050.dmp log=z:\Migrate\wc8m050_exp.log owner=owner compress=y statistics=none

I am getting this error message.

EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old

I have searched around and tried all the recommended fixes, identified my longest running query and used a script to find what my optimal undo_retention value should be. I increased undo_retention to 10800, which is what the scripts recommend and it is still failing. I significantly increased the size of my undo tablespace as well as broke the datafiles into 4 - 5GB datafiles, added a number of large redo log groups, and added additional, large, rollback segments. I also tried setting undo_management to MANUAL with the same results and then back to AUTO. We were able to isolate the table that is causing the issue, in this case, the table is called STREAMDATA which has a data type of BLOB, field name LOBLOC. By adding the TABLES option to isolate the STREAMDATA table, along with FEEDBACK=1, were are able to see that the export stops in the same location each time, regardless of database changes. I am looking for some help now, any suggestions. Also if this is not the correct forum for this post I do apologize.

Thanks,
Tyler
Re: ORA-01555: snapshot too old during export. [message #430515 is a reply to message #430509] Tue, 10 November 2009 15:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There are specific issues with exporting LOBs in 10.2.0.3.
First check if the LOB segment is corrupted.
Most probably, there could be heavy DML activity during export or just before export and could be a by-product of delayed block cleanout.
Did you try to export when there is less activity?

Quote:
undo_retention to 10800

Maybe you just need a bigger retention.
By default, exp parameter consistent is N.
And unrelated to the current issue you are facing, you may actually want to use compress=N
Re: ORA-01555: snapshot too old during export. [message #430521 is a reply to message #430509] Tue, 10 November 2009 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>are able to see that the export stops in the same location each time, regardless of database changes.

some/many/most times ORA-01555 occurs when one session is doing a long running
SELECT against a specific table while a different session does DML against same
table while issuing frequent COMMIT.


Re: ORA-01555: snapshot too old during export. [message #430555 is a reply to message #430509] Wed, 11 November 2009 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Specify a large BUFFER parameter, the default one is really tiny.

Regards
Michel
Re: ORA-01555: snapshot too old during export. [message #430655 is a reply to message #430509] Wed, 11 November 2009 08:16 Go to previous messageGo to next message
nielsentyler
Messages: 6
Registered: November 2009
Location: United States
Junior Member
First to correct a mistake that I noticed I made in my initial statement, I posted what my undo_retention parameter was at default here is what it is set at now 152123760.

This database is not being used at all during the exp and has not had any actions on it for hours or even possibly days when we are attempting the export.

Thanks,
Tyler
Re: ORA-01555: snapshot too old during export. [message #430661 is a reply to message #430655] Wed, 11 November 2009 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>This database is not being used at all during the exp and has not had any actions on it for hours or even possibly days when we are attempting the export.

Accepting above as true, then this could be a case of delayed block clean out.

Prior to starting exp, then issue
SELECT * FROM PROBLEM_TABLE;

allow it to complete before starting exp again.
Re: ORA-01555: snapshot too old during export. [message #430988 is a reply to message #430509] Fri, 13 November 2009 09:51 Go to previous messageGo to next message
nielsentyler
Messages: 6
Registered: November 2009
Location: United States
Junior Member
I attempted to run the command and received back an SP2-0678 error. The problem table is a blob table. Sorry for the slow response back on what occurred, been a busy week.

Thanks,
Tyler
Re: ORA-01555: snapshot too old during export. [message #430996 is a reply to message #430988] Fri, 13 November 2009 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SP2-0678 is an SQL*Plus error. export (exp) is an OS command.
So execute it at OS level.

Regards
Michel
Re: ORA-01555: snapshot too old during export. [message #430999 is a reply to message #430509] Fri, 13 November 2009 10:33 Go to previous messageGo to next message
nielsentyler
Messages: 6
Registered: November 2009
Location: United States
Junior Member
I received the SP2-0678 when running the SELECT * FROM PROBLEM_TABLE; command.

Thanks,
Tyler
Re: ORA-01555: snapshot too old during export. [message #431007 is a reply to message #430999] Fri, 13 November 2009 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't COPY AND PASTE your session, how could we when you REALLY did?
The only thing we can say is: you make an error.

Please carefully read OraFAQ Forum Guide.

Regards
Michel

[Updated on: Fri, 13 November 2009 11:15]

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #431011 is a reply to message #430509] Fri, 13 November 2009 11:25 Go to previous messageGo to next message
nielsentyler
Messages: 6
Registered: November 2009
Location: United States
Junior Member
I'll make sure to post my sessions, I do have a tendency to reply to vaguely following a reply. Here is my session, if this was not how that command was intended to be ran please let me know.

SQL> SELECT * FROM streamdata;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL>

Thanks,
Tyler
Re: ORA-01555: snapshot too old during export. [message #431014 is a reply to message #430509] Fri, 13 November 2009 11:37 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try selecting all the columns apart from the blob column.

Re: ORA-01555: snapshot too old during export. [message #431018 is a reply to message #431011] Fri, 13 November 2009 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SP2-00678: Column or attribute type can not be displayed by SQL*Plus\n
 *Cause:  The type specified is not supported.
 *Action: Rewrite the query to select the data with types that
          SQL*Plus supports.

desc streamdata

Regards
Michel

[Updated on: Fri, 13 November 2009 12:04]

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #431019 is a reply to message #431011] Fri, 13 November 2009 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try the solution I posted?

Regards
Michel
Re: ORA-01555: snapshot too old during export. [message #431028 is a reply to message #430509] Fri, 13 November 2009 13:01 Go to previous messageGo to next message
nielsentyler
Messages: 6
Registered: November 2009
Location: United States
Junior Member
I was able to modify the query as suggested to select everything except for the blobs. Unfortunately it failed at the same place. I thinking that i have some corrupt data in my blobs.

Here is the select statement i ran

SQL> SELECT REVAULTSESSIONID, streamid, CREATESTAMPA2, MARKFORDELETEA2, MODIFYSTAMPA2, CLASSNAMEA2A2, IDA2A2, UPDATECOUNT
A2, UPDATESTAMPA2 FROM streamdata;

That is every column except for the blob one.

I let it complete and then I ran the export again and it failed at the same place it always does.

Thanks,
Tyler
Re: ORA-01555: snapshot too old during export. [message #431031 is a reply to message #431028] Fri, 13 November 2009 13:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
quoting myself.
>>First check if the LOB segment is corrupted.
Re: ORA-01555: snapshot too old during export. [message #431035 is a reply to message #431028] Fri, 13 November 2009 13:12 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 13 November 2009 18:58
desc streamdata

Regards
Michel

Michel Cadot wrote on Fri, 13 November 2009 19:05
Did you try the solution I posted?

Regards
Michel

Michel Cadot wrote on Fri, 13 November 2009 18:15
If you don't COPY AND PASTE your session, how could we when you REALLY did?
The only thing we can say is: you make an error.

Please carefully read OraFAQ Forum Guide.

Regards
Michel


Previous Topic: Loading data via external table -when csv file doesn't exactly map to table
Next Topic: SQL Loader loads all fields with double quotes into staging table
Goto Forum:
  


Current Time: Thu Dec 08 02:38:35 CST 2016

Total time taken to generate the page: 0.10752 seconds