From oracle-l-bounce@freelists.org Tue Jul 20 09:02:56 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6KE2Ux03269 for ; Tue, 20 Jul 2004 09:02:41 -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 i6KE2K603219 for ; Tue, 20 Jul 2004 09:02:30 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2B40A72C8A5; Tue, 20 Jul 2004 08:41:48 -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 16249-50; Tue, 20 Jul 2004 08:41:48 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 75EB772C0BD; Tue, 20 Jul 2004 08:41:47 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 20 Jul 2004 08:40:21 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 37E6272C3E2 for ; Tue, 20 Jul 2004 08:40:21 -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 15359-66 for ; Tue, 20 Jul 2004 08:40:21 -0500 (EST) Received: from smcorps0002.smcorp.speechmachines.com (mail.speechmachines.com [193.132.150.21]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 94B5D72C0BD for ; Tue, 20 Jul 2004 08:40:20 -0500 (EST) Received: by SMCORPS0002 with Internet Mail Service (5.5.2653.19) id <39V9LXF4>; Tue, 20 Jul 2004 15:05:34 +0100 Message-ID: From: Tim Onions To: "'oracle-l@freelists.org'" Subject: RE: Delayed block cleanout and changing automatic undo tablespace Date: Tue, 20 Jul 2004 15:05:33 +0100 X-Mailer: Internet Mail Service (5.5.2653.19) X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 5487 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tim.onions@speechmachines.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Jonathan Interesting thoughts indeed. The database is failsafed and it was "shutdown" via a failover so it is possible that the shutdown was not clean. I will look into that. I have no intention of dropping the tablespace until such time as I am totally satisfied this issue is fixed - I don't have the luxury of taking cold backups - until then I'll simply shrink datafile when space becomes an issue. I came to the conclusion that it was a delayed block cleanout issue myself based on previous experience but I have not absolute proof that this is really the case. Thanks T¬ -----Original Message----- From: Jonathan Lewis [mailto:jonathan@jlcomp.demon.co.uk] Sent: 20 July 2004 14:51 To: oracle-l@freelists.org Subject: Re: Delayed block cleanout and changing automatic undo tablespace That's very odd. Since you restarted the database to switch undo tablespaces, any code that attempts to do delayed block cleanout should be able to determine that the startup SCN for the database is an adequate upper bound commit, and therefore not NEED to go back any further.*** The fact that Oracle tries to go back further than necessary is arguably an error - unless someone can think of a reason why it actually IS necessary. It's probably okay to drop the tablespace - but I think I'd only do that after taking a cold backup because if there's one error in this area, there may be other worse errors waiting to happen. *** Footnote: I am assuming that your shutdown was a clean shutdown, otherwise the old undo tablespace could be needed for completing a "rollback;" rather than a cleanout. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated July 14th ----- Original Message ----- From: "Tim Onions" To: Sent: Tuesday, July 20, 2004 10:52 AM Subject: Delayed block cleanout and changing automatic undo tablespace : Dear All : : Windows 2003 (full patched)/Oracle9.2.0.5 EE : : After a recent migration to 9i where data was copied from the 8i database : rather than the migration utility used I changed the automatic undo : tablespace from a large one (coz I was migrating a LOT of data) to a smaller : one (normal transactions are quite small). The DB was restarted in between : times to ensure the new UNDO tablesapce became active. : : I want to drop the old large undo tablespace but cannot as when I offlined : it in preparation I started to get "data file cannot be read at this time" : errors in the application, where the datafiles in question were associated : with the old large undo tablesapce. I am pretty sure this is due to delayed : block cleanout with "rollback" in the old large tablespace still being : required for this period. : : When using traditional rollback you could offline the rollbacks segments and : I believe this would force a cleanout (from what I have read). I can see no : way of offlining auto undo or forcing a cleanout. AskTom says "it happens : over time" as the blocks that need cleaning are accessed. I have done FTS : queries on all tables in the application and will, if I have to, run queries : that access all index blocks. However, I need to be sure data in the old : large undo tablespace is not still needed before I drop it or even risk : taking it offline again (the application errors were not nice!). : : So eventually the questions: : - how can I tell if cleanout is still required? : or how can I tell if undo in the old large tablespace (the tablespaces that : is no longer being actively used by auto undo) is still required (for : cleanout) : Or how can I force cleanout aka offlining a rollback segment in 8i (without : having to run FTS and index scans on everything) : : DBA_SEGMENTS shows 10 auto undo segments in the old large tablespace which : may be a clue but there is no info there to help me (they were there beofre : I ran the FTS queries and are still there now). They have been there for : some time and show no inclination to go. : : Many thanks in anticipation. : ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------