How many active/users/transactions you have in the database? I am running
an OLTP database and in my case, it's not an exaggeration, at least not
by much. Setting the transaction read-only ensures that all reads are
consistent with respect to the certain point in time, usually the moment
just before the transaction has begun. If there are any other transactions
on the database, you need a consistent get, ie a get from the undo segment.
If somebody wants to modify block you're reading, he or she has to copy it
to an undo block. If you have any long-running transactions, as is the
case with billing, you will get a huge amount of undo blocks. Of course, if
your database is mostly read-only, you really can have just few blocks of
undo space. For the most part, doing export with consistent=yes is inviting
those lovely 1555 "snapshot too old" thingies that we all know and love.
Of course, to add insult to the injury, there is a bug in 9.2.0.4 which
can render your export unusable:
2666174 Export in direct path can product a corrupt export file if
ORA-1555 occurs
So, as far as I am concerned, "consistent=y" amounts to shooting myself in
both feet. You go ahead and use it. Go ahead, make my data.
--
Mladen Gogala
Ext. 121
-----Original Message-----
From: Mark Bole [mailto:makbo_at_pacbell.net]
Sent: Wednesday, September 28, 2005 1:26 PM
To: ORACLE-L_at_freelists.org
Subject: Re: A special way of migration
You stated that "The whole export will be read into undo segments". You
further stated that setting consistent=y makes "the whole export
'repeatable'". These statements are mis-information.
An export, with or without consistent=y, does not consume previously
unused undo space. (In fact, individual tables are always exported
consistently regardless of the setting). Undo will be read as needed to
create a read-consistent view of the data, just as they would for any
transaction. If there is very little update activity in the database,
then very little undo will need to be read.
I just started up a database and began a consistent=y export.
The export file is over 3 GB in size so far (still running).
Here is the undo usage, clearly it has nothing to do with the size of
the export:
select begin_time, end_time, undoblks from v$undostat;
BEGIN_TIME END_TIME UNDOBLKS
------------------- ------------------- ----------
2005/09/28 10:14:11 2005/09/28 10:23:06 3
2005/09/28 10:04:11 2005/09/28 10:14:11 18
Gogala, Mladen wrote:
> Unfortunately, it's not a misinformation. With "consistent=y", you will
> have
> SET TRANSACTION READ ONLY as the first statement in the export. What
> does this statement do you can read for yourself in the manual. It
> wasn't an overuse of sarcasm.
>
> As far as logical backup goes, I would tend not to disagree, but it has
> nothing to do with CONSISTENT=YES. This "read consistent export" can only
> ensure consistency with respect to certain point in time if it builds a
> read-consistent image of the database with respect to that point in time.
> You have three guesses to guess which structures are used for building
> such a consistent image.
> So Mark, please read the fine manual before you accuse me of being
> overly sarcastic.
>
> --
> Mladen Gogala
> Ext. 121
>
--
Mark Bole
http://www.bincomputing.com
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 28 2005 - 13:30:16 CDT
