Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: A special way of migration

RE: A special way of migration

From: Gogala, Mladen <>
Date: Wed, 28 Sep 2005 14:49:03 -0400
Message-ID: <>

Comments inline

Mladen Gogala
Ext. 121

-----Original Message-----
From: Lex de Haan [] 
Sent: Wednesday, September 28, 2005 1:06 PM
Subject: RE: A special way of migration

"The whole export will be read into undo segments" is wrong; 
also, the export will *not* result in more undo generation or retention.

[Mladen Gogala]
Well, for an active database, it results in a very large quantities of undo information being generated. It also results in corrupt dump file, if direct=y option is used and if your version is 9.2.0.x, x<=4. It's not the whole database being copied, it's just 70%. That is a huge difference on a multi-TB database. the export job "just" risks ORA-1555 (snapshot too old) in case concurrently running transactions have overwritten undo info needed to reconstruct read consistent block images. only the blocks dirtied since the export job started will be cloned in the buffer cache, and *only then* certain undo information is needed to reconstruct older block images.
[Mladen Gogala]
That is correct. Unfortunately, the full export is usually done by night, as are various mass update jobs. Theoretically, you are correct. Practically, I've had too many alarms caused by 1555 wake me up in the middle of the night. Nobody will be using consistent=y for the full database export on my watch. It's a fact of life and it's non-negotiable, just like gravity. ORA-01555 will cause OEM to remember me and make sure that I'm awake. Failed jobs and job re-runs have to be documented, cause all kinds of scheduling problems and force me to write unwanted reports to my boss. It's anything but harmless. so what you need to do (as Mark suggests below) is to size your undo tablespace and to set the undo retention appropriately. in 10g, you can set the retention to be guaranteed; until 9i, it is based on best effort.
[Mladen Gogala]
Not using evil things like consistent=yes is the way I like it. I will rather send my money to the victims of Katrina then to the disk manufacturers. I know that 10g has several ways of ensuring that disk manufacturers do not have to beg for food (flashback, recycle bin, guaranteed retention) but at the moment I am not in the mood to help out poor veeps at Seagate, Maxtor and Fujitsu any more than I already have to. You can call me cruel, I will not mind. CONSISTENT=YES is evil, period. --
Received on Wed Sep 28 2005 - 13:49:19 CDT

Original text of this message