-- --Original Message-- -- From: Lex de Haan [mailto:lex.de.haan@(protected)] Sent: Wednesday, September 28, 2005 1:06 PM To: makbo@(protected) Cc: ORACLE-L@(protected) 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 (See ORA-1555.ora-code.com) (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 (See ORA-01555.ora-code.com) 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.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii"> <META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2653.12"> <TITLE>RE: A special way of migration</TITLE> </HEAD> <BODY>
<P><FONT SIZE=2>-- --Original Message-- --</FONT> <BR><FONT SIZE=2>From: Lex de Haan [<A HREF="mailto:lex.de.haan@(protected)" >mailto:lex.de.haan@(protected)</A>] </FONT> <BR><FONT SIZE=2>Sent: Wednesday, September 28, 2005 1:06 PM</FONT> <BR><FONT SIZE=2>To: makbo@(protected)</FONT> <BR><FONT SIZE=2>Cc: ORACLE-L@(protected)</FONT> <BR><FONT SIZE=2>Subject: RE: A special way of migration</FONT> </P>
<P><FONT SIZE=2>"The whole export will be read into undo segments" is wrong; </FONT> <BR><FONT SIZE=2>also, the export will *not* result in more undo generation or retention.</FONT> <BR><FONT SIZE=2>[Mladen Gogala] </FONT> </P>
<P><FONT SIZE=2>Well, for an active database, it results in a very large quantities of undo </FONT> <BR><FONT SIZE=2>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</FONT></P>
<P><FONT SIZE=2>whole database being copied, it's just 70%. That is a huge difference on a</FONT> <BR><FONT SIZE=2>multi-TB database.</FONT> </P>
<P><FONT SIZE=2>the export job "just" risks ORA-1555 (See ORA-1555.ora-code.com) (snapshot too old) in case concurrently</FONT> <BR><FONT SIZE=2>running transactions have overwritten undo info needed to reconstruct read</FONT> <BR><FONT SIZE=2>consistent block images. only the blocks dirtied since the export job started</FONT> <BR><FONT SIZE=2>will be cloned in the buffer cache, and *only then* certain undo information is</FONT> <BR><FONT SIZE=2>needed to reconstruct older block images.</FONT> <BR><FONT SIZE=2>[Mladen Gogala] </FONT> </P>
<P><FONT SIZE=2>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 </FONT></P>
<P><FONT SIZE=2>my watch. It's a fact of life and it's non-negotiable, just like gravity.</FONT> <BR><FONT SIZE=2>ORA-01555 (See ORA-01555.ora-code.com) will cause OEM to remember me and make sure that I'm awake. Failed</FONT> <BR><FONT SIZE=2>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.</FONT></P>
<P><FONT SIZE=2>so what you need to do (as Mark suggests below) is to size your undo tablespace</FONT> <BR><FONT SIZE=2>and to set the undo retention appropriately. in 10g, you can set the retention</FONT> <BR><FONT SIZE=2>to be guaranteed; until 9i, it is based on best effort.</FONT> <BR><FONT SIZE=2>[Mladen Gogala] </FONT> </P> <BR>
<P><FONT SIZE=2>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</FONT></P >
<P><FONT SIZE=2>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< /FONT></P>
<P><FONT SIZE=2>poor veeps at Seagate, Maxtor and Fujitsu any more than I already have to. </FONT> <BR><FONT SIZE=2>You can call me cruel, I will not mind. CONSISTENT=YES is evil , period.</FONT> <BR><FONT SIZE=2> </FONT> </P>
