Oracle-L: RE: Re[4]: undo tablespace

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Wed, 29 Jan 2003 09:39:52 -0800
Message-ID: <F001.0053D79E.20030129093952@fatcity.com>

Breno,

There is your problem. Don't try to solve it by trying to eliminate undo. I have run into that situation several times. The key is to set up the database management processes to handle the situation.

Things to think about:
1) Increase the size of the device where archive_dump_dest resides 2) NEVER, NEVER, NEVER delete archived redo logs until they are on several different backup tapes. You lose 1 of this beauties and your recovery is halted.
3) Put together a script/process to move the logs from one device to another, then purge them after several days.

Dan Fink

-----Original Message-----
Sent: Wednesday, January 29, 2003 9:55 AM To: Multiple recipients of list ORACLE-L

Dan,

Is the generation of redo (and archived logs) causing the system to halt due to the archive_dump_dest filling up? YES
I'll work to minimize this situation. Thanks.

--

Breno A. K. Magnago
mailto:breno_at_mercantilsoares.com.br

Wednesday, January 29, 2003, 1:19:50 PM, you wrote:

FD> Breno,
FD> 100mb of redo in 20 minutes is not all that high. I have seen far
FD> worse (as I am sure most on the list have as well). Why do you perceive this
FD> as a problem? Are you seeing poor performance or waits? FD> Is the generation of redo (and archived logs) causing the system to
FD> halt due to the archive_dump_dest filling up? If so, the problem is not the

FD> procedure, but rather the lack of proper process to manage your archive
FD> logs.
FD>         Even with NOLOGGING, UNDO must be generated for read

consistency,
FD> rollback and recovery. You are not inserting data into the undo tablespace.
FD> The undo entries are generated for each operation (insert/update/delete).
FD> However, the undo entry for an insert is very small and thus will consume
FD> very little undo space and redo.

FD> Don't concern yourself with trying to stop the generation of UNDO.
FD> You will end up causing yourself more problems that you will ever try to FD> solve. It is part of the Oracle kernel and not modifiable (at least in this
FD> release). Focus on performance and decide if 100mb in 20 minutes is really a
FD> problem.

FD> Dan Fink

FD> -----Original Message-----
FD> Sent: Wednesday, January 29, 2003 7:10 AM
FD> To: Multiple recipients of list ORACLE-L

FD> Mike,

FD> I asked it because I have a problem.
FD> Any insert data in UNDO tablespace generate insert in REDO Files. Is
FD> is correct ?

FD> When I execute a high procedure, many inserts in UNDO tablespace
FD> ocurres, so many inserts in REDO´s are genereate.
FD> I want to avoid this REDO´s generation.

FD> My tables and indexes are in NOLOGGING, but I high value of FD> REDO are generate (100 MB each 20 minutes). It is desnecessary.

FD> Oracle 9i / NT

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Breno A. K. Magnago
INET: breno_at_mercantilsoares.com.br

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Fink, Dan
INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------