OraFAQ Forum: Server Administration » ORA-01555 during expdp

Home » RDBMS Server » Server Administration » ORA-01555 during expdp (oracle 11g r2 in cluster on m5000 machines)

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

ORA-01555 during expdp [message #536890]

Fri, 23 December 2011 13:19

janakors
Messages: 232
Registered: September 2009

Senior Member

hi,
i am getting this error during expdp ORA-01555 -snapshot too old

1. i increased undo_retention i.e 50000
2. i execute expdp during late night when almost zero user is online so no commit from any other session.

3. we have a table having BLOB data type field in a table which is 500 GB in size.complete DB size is 700 GB

Any suggestion where and why this error exists.
if corruption of BLOB so how to check that etc.

the same error is occurring in three of our data centres during
expdp

regards

Report message to a moderator

Re: ORA-01555 during expdp [message #536891 is a reply to message #536890]

Fri, 23 December 2011 13:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//	   overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments

>2. i execute expdp during late night when almost zero user is online so no commit from any other session.

ORA-01555 only occurs after some session has done DML & COMMIT!
The session that throws ORA-01555 is victim; not necessarily the culprit.
ORA-01555 means that Oracle can no longer provide a read consistent view of some table being read.
It means that the table has changed & the UNDO is no longer available because COMMIT was issued & UNDO was overwritten.

[Updated on: Fri, 23 December 2011 14:25]

Report message to a moderator

Re: ORA-01555 during expdp [message #536948 is a reply to message #536891]

Sat, 24 December 2011 11:22

janakors
Messages: 232
Registered: September 2009

Senior Member

thank you for reply. i have read older posts regarding this error and to me the bottom line is (as you said) to increase the rollback segment but question is how much and i suspect (may be i am wrong) that after some days or weeks i would be increasing again this rollback segment.so sir can you tell me how much i should increase this rollback segment and so far in last 3 yrs we receive this error during EXPDP not anywhere else.

kindly guide and thank you for your time

Janakors

Report message to a moderator

Re: ORA-01555 during expdp [message #536949 is a reply to message #536948]

Sat, 24 December 2011 11:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>to me the bottom line is (as you said) to increase the rollback segment
It depends.
How big is UNDO now?
How much available disk space exists that could be consumed by larger UNDO?
I'd increase UNDO by 10 - 15% or more.

>i suspect (may be i am wrong) that after some days or weeks i would be increasing again this rollback segment.
I agree only if the amount of data being exported increases in size.
If size of exported data remains the same then UNDO requirements should remain the same.
If size of exported data increases, then UNDO requirements will also increase.

Report message to a moderator

Re: ORA-01555 during expdp [message #537891 is a reply to message #536890]

Mon, 02 January 2012 07:46

hristo
Messages: 258
Registered: May 2007

Senior Member

Do you use PARALLEL=X when exporting? If you have, for example, 6CPS, set PARALLEL=4.
Check to see if you have tables with chained rows, a table with chained rows takes much longer to export then a table without.

[Updated on: Mon, 02 January 2012 07:47]

Report message to a moderator

Previous Topic:	password with @
Next Topic:	function with user

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 18:55:05 CST 2025