Home » RDBMS Server » Server Utilities » Snapshot error during export (Oracle 10g, Sun OS)
Snapshot error during export [message #463432] Thu, 01 July 2010 05:52 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi, i am getting "snapshot too old " error while takein export backup of a database(with CONSISTENT=y), it actualy runs for 3 hours.

it always fails for table1 with snapshot errror

i pulled the awr for that 3 hours, to see any long running SQL hitting table1 . and i found 3 , Two SELECT and one INSERT.

I assume it is INSERT not letting me take a consistent export backup of Table1 . Can some one suggest me if i am troubleshooting in the right direction and any tips if you had similar issues ?

Thanks

Report message to a moderator

Re: Snapshot error during export [message #463435 is a reply to message #463432] Thu, 01 July 2010 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Either enlarge your undo tablespace and increase undo retention, or do you export when there is no concurrent session.

Regards
Michel

Report message to a moderator

Re: Snapshot error during export [message #463436 is a reply to message #463432] Thu, 01 July 2010 06:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Export is not backup.
You can use CONSISTENT=N.
You can start exporting when there is no activity in database.
You can try increasing BUFFER value during export session.
You can try increasing the UNDO_RETENTION in database.

Report message to a moderator

Re: Snapshot error during export [message #464715 is a reply to message #463432] Fri, 09 July 2010 00:32 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi, Mike&Mahesh,

Forgot to mention ,UNDO management,UNDO auto tune is TRUE .So i dont think increasing RETENTION will help.

I have one basic question.Is it because of high activity on my table1 or its due to high concurrency at database level.

thanks

Report message to a moderator

Re: Snapshot error during export [message #464728 is a reply to message #464715] Fri, 09 July 2010 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It is due to commits in concurrent transactions.

I don't understand your remark about undo_retention, if you increase it and above all if you set it to guarantee then you will not have this error.

Regards
Michel

Report message to a moderator

Re: Snapshot error during export [message #464735 is a reply to message #464728] Fri, 09 July 2010 02:24 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
hi Mike,
_undo_autotune is TRUE, and undo_retention is 0 , means Oracle decides best value for undo_retention.

Report message to a moderator

Re: Snapshot error during export [message #464740 is a reply to message #464735] Fri, 09 July 2010 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It does this based on an average on the whole workload and so can't know what you know for a specific job.
Don't think this is a magic parameter that will solve all your problems (and the proof is your current error).
So do as we said or live with the error.

Regards
Michel

Report message to a moderator

Re: Snapshot error during export [message #464742 is a reply to message #464740] Fri, 09 July 2010 02:52 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
I did observe another schema export running at same time, with CONSISTENT=Y. i pushed it to a later time. Hope this would solve the errr. I will update you.

Report message to a moderator

Re: Snapshot error during export [message #464744 is a reply to message #464742] Fri, 09 July 2010 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Waiting for your feedback but if this export may concurrently consumme the undo space it is not the origin of the problem; the root is concurrent transactions modifying the tables.

Regards
Michel

Report message to a moderator

Re: Snapshot error during export [message #466346 is a reply to message #463432] Mon, 19 July 2010 02:44 Go to previous message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Export ran fine for 4 days after i pushed the other export job to later time.

But this export again start failing from today when it reach this particular table.Its confirmed its a highly active table.

I am taking it to Oracle support .

Report message to a moderator

Previous Topic: sqlldr disable truncate
Next Topic: HOW TO PROCESS ASCII CHARACTER 29 WHILE LOADING DATA WITH SQL LOADER
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 14:03:56 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.