From oracle-l-bounce@freelists.org Fri Sep 17 04:13:43 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i8H9Dgl14588 for ; Fri, 17 Sep 2004 04:13:43 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i8H9DgI14581 for ; Fri, 17 Sep 2004 04:13:42 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C2CCC72C628; Fri, 17 Sep 2004 04:19:40 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32381-76; Fri, 17 Sep 2004 04:19:40 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3159F72C61E; Fri, 17 Sep 2004 04:19:40 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Subject: RE: Undo Tablespace question Date: Fri, 17 Sep 2004 10:17:39 +0100 Message-ID: <1C6E45ADB2EC324F9553E468ABFE0F6301C5CED7@UKWMXM04> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [OT] gmail invitations Thread-Index: AcScDFiIcI3tFwOaTkmKQSN/9aXXLQABqZfAAB3gbGAAAyljgA== From: "Hallas, John, Tech Dev" To: X-OriginalArrivalTime: 17 Sep 2004 09:17:38.0722 (UTC) FILETIME=[2D4CD020:01C49C97] X-archive-position: 9672 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: John.Hallas@gb.vodafone.co.uk Precedence: normal Reply-To: John.Hallas@gb.vodafone.co.uk X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Paul, The transition from RBS to UNDO is pretty easy I would suggest retaining the same size UNDO as you have RBS at the = moment, especially if you have no special (longer) retention period = requirements. The init.ora (or hopefully spfile) should be amended to remove the = following lines TRANSACTIONS_PER_ROLLBACK_SEGMENT and ROLLBACK_SEGMENTS.=20 The following parameters should be added UNDO_MANAGEMENT=3DAUTO UNDO_SUPRESS_ERROR=3DTRUE (to stop errors occuring if the commands = referring to rollback segments are used (online/offline/use etc) UNDO_TABLESPACE (multiple UNDO tablespace can be created (as normal T/S = - use LMT) but only 1 can be 'active' Drop the RBS tablespace once all sessions using it have been removed (it = can always be re-created if necessary) Experiment with the flashback_query facility, you might not need it but = it is a pretty neat thing and well worth playing with. The main view to use for monitoring information is V$UNDOSTAT where you = can determine the maximum query length and the number of undo blocks = used amongst other things. In my view this is one of the easiest of the 9i features to implement = and you should not have many problems HTH=20 John -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Paul Vincent Sent: Friday, September 17, 2004 9:16 AM To: oracle-l@freelists.org Subject: Undo Tablespace question Now that we've finally got some 9i databases, I'm quite keen to switch = =3D from Manual to Automatic Undo Management. It all looks pretty =3D straightforward, but I do have a couple of questions that the "How To" = =3D guides don't really address... 1. If I don't have any requirement for long-term retention of Undo data = =3D after transactions commit, so that the default retention of 900 seconds = =3D is quite sufficient, then what multiplier should I use when calculating = =3D the size of my Undo Tablespace relative to the size of the old RBS =3D tablespace? Say I currently have 500Mb RBS tablespace, then should a =3D 500Mb undo tablespace be adequate, or should I allow double (1Gb) or =3D more? 2. What monitoring practices are advisable to check the adequacy of the = =3D undo tablespace provision? Can anyone point me at any webpages with =3D articles about monitoring AUM activity, for instance? 3. Once I've switched from Manual to Auto, does the RBS tablespace then = =3D become completely superfluous? Can it be dropped? And what of the =3D "rollback_segments" initialisation parameter? Do I remove this from the = =3D parameter file, or is it best left there "just in case"? Before I go diving in, in cavalier manner, I always like to check for = =3D "gotchas"! Regards, Paul Vincent DBA University of Central England -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l