OraFAQ Forum: Server Utilities » ORA-01555: snapshot too old during export.

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01555: snapshot too old during export. [message #430509]

Tue, 10 November 2009 15:06

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

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #430515 is a reply to message #430509]

Tue, 10 November 2009 15:40

Mahesh Rajendran
Messages: 10707
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

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #430521 is a reply to message #430509]

Tue, 10 November 2009 17:25

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #430555 is a reply to message #430509]

Wed, 11 November 2009 00:55

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Specify a large BUFFER parameter, the default one is really tiny.

Regards
Michel

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #430655 is a reply to message #430509]

Wed, 11 November 2009 08:16

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

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #430661 is a reply to message #430655]

Wed, 11 November 2009 09:32

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #430988 is a reply to message #430509]

Fri, 13 November 2009 09:51

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

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #430996 is a reply to message #430988]

Fri, 13 November 2009 10:20

Michel Cadot
Messages: 68648
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

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #430999 is a reply to message #430509]

Fri, 13 November 2009 10:33

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

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #431007 is a reply to message #430999]

Fri, 13 November 2009 11:15

Michel Cadot
Messages: 68648
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

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

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #431014 is a reply to message #430509]

Fri, 13 November 2009 11:37

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

Try selecting all the columns apart from the blob column.

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #431018 is a reply to message #431011]

Fri, 13 November 2009 11:58

Michel Cadot
Messages: 68648
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

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Did you try the solution I posted?

Regards
Michel

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #431028 is a reply to message #430509]

Fri, 13 November 2009 13:01

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

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #431031 is a reply to message #431028]

Fri, 13 November 2009 13:04

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

quoting myself.
>>First check if the LOB segment is corrupted.

Report message to a moderator

Re: ORA-01555: snapshot too old during export. [message #431035 is a reply to message #431028]

Fri, 13 November 2009 13:12

Michel Cadot
Messages: 68648
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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 02 00:02:14 CDT 2024