Force drop of UNDO tablespace (with online segment) in 9.2.0.8?
in [Oracle]
|
Prev: Checkpoints
Next: ORA-03135: connection lost contact
From: DG problem on 21 Sep 2009 23:52 In summary, I completed an import which took five days into a test database. The import completed successfully. I then swapped UNDOTBS1 to UNDO_TEMP tablesapce so that it would be much smaller. However, UNDOTBS1 then reported that it needed recovery :( I tried to drop UNDOTBS1 but a segment remained online and I was unable to drop it. There is no backup and the DB is in noarchivelog mode. I'll probably just restart the import process again, but I was wondering if there is any way to drop UNDOTBS1? Hopefully there are enough details below for any one with some spare time to comment on? The drop datafile and tablespace appear at the very end. I also did a full export (ROWS=N) and it completed successfully. Running on HP-UX oracle @ HM [TESTDB]:$ sqlplus '/ as sysdba' SQL*Plus: Release 9.2.0.8.0 - Production on Tue Sep 22 13:01:53 2009 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1.0957E+10 bytes Fixed Size 750280 bytes Variable Size 218103808 bytes Database Buffers 1.0737E+10 bytes Redo Buffers 1060864 bytes Database mounted. Database opened. SQL> archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination /p06/oraarch/TESTDB/ Oldest online log sequence 3484 Current log sequence 3485 SQL> show parameter undo NAME TYPE VALUE ---------------------- ----------- ---------- undo_management string AUTO undo_retention integer 3600 undo_suppress_errors boolean FALSE undo_tablespace string UNDO_TEMP SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs where status <> 'OFFLINE' order by 3; OWNER SEGMENT_NAME TABLESPACE_NAME STATUS ------ ------------ ---------------- ---------------- SYS SYSTEM SYSTEM ONLINE PUBLIC _SYSSMU5$ UNDOTBS1 NEEDS RECOVERY PUBLIC _SYSSMU61$ UNDO_TEMP ONLINE PUBLIC _SYSSMU62$ UNDO_TEMP ONLINE PUBLIC _SYSSMU63$ UNDO_TEMP ONLINE PUBLIC _SYSSMU64$ UNDO_TEMP ONLINE PUBLIC _SYSSMU65$ UNDO_TEMP ONLINE 7 rows selected. SQL> SELECT r.file# AS df#, d.name AS df_name, t.name AS tbsp_name, d.status, r.error, r.change#, r.time FROM v$recover_file r, v$datafile d, v$tablespace t WHERE t.ts# = d.ts# AND d.file# = r.file# ; DF# DF_NAME ---- --------------------------------- 2 /p01/oradata/TESTDB/undotbs01.dbf TBSP_NAME STATUS ERROR CHANGE# TIME --------- ------- ---------- ---------- --------- UNDOTBS1 RECOVER 5911128765 21-SEP-09 SQL> SHUTDOWN transactional Database closed. Database dismounted. ORACLE instance shut down. SQL> ! cat /opt/oracle/T/admin/TESTDB/bdump/alert_TESTDB.log Tue Sep 22 13:02:21 2009 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 1 Tue Sep 22 13:02:43 2009 Using log_archive_dest parameter default value SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.8.0. System parameters with non-default values: processes = 20 timed_statistics = TRUE shared_pool_size = 67108864 lock_name_space = resource_manager_plan = TMT_PLAN control_files = /p01/oradata/TESTDB/control01.ctl, /p01/ oradata/TESTDB/control02.ctl db_block_checksum = TRUE db_block_size = 8192 db_writer_processes = 2 db_cache_size = 10737418240 compatible = 9.2.0.0.0 log_archive_start = TRUE log_archive_dest_1 = LOCATION=/p06/oraarch/TESTDB/ log_archive_max_processes= 2 log_archive_min_succeed_dest= 1 log_archive_format = arch_S%S_T%T.arc archive_lag_target = 0 db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDO_TEMP undo_retention = 3600 db_block_checking = TRUE remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = TESTDB local_listener = (ADDRESS = (PROTOCOL=TCP)(HOST=SQR) (PORT=1521)) job_queue_processes = 0 hash_join_enabled = TRUE background_dump_dest = /opt/oracle/T/admin/TESTDB/bdump user_dump_dest = /opt/oracle/T/admin/TESTDB/udump core_dump_dest = /opt/oracle/T/admin/TESTDB/cdump audit_file_dest = /opt/oracle/T/admin/TESTDB/adump audit_trail = DB db_name = TESTDB open_cursors = 500 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 209715200 PMON started with pid=2, OS id=22526 DBW0 started with pid=3, OS id=22528 DBW1 started with pid=4, OS id=22530 LGWR started with pid=5, OS id=22532 CKPT started with pid=6, OS id=22534 SMON started with pid=7, OS id=22536 RECO started with pid=8, OS id=22538 Tue Sep 22 13:02:43 2009 ARCH: STARTING ARCH PROCESSES ARC0 started with pid=9, OS id=22540 ARC0: Archival started ARC1 started with pid=10, OS id=22542 Tue Sep 22 13:02:43 2009 ARCH: STARTING ARCH PROCESSES COMPLETE Tue Sep 22 13:02:43 2009 ARC1: Archival started Tue Sep 22 13:02:43 2009 ARC0: Becoming the 'no FAL' ARCH ARC0: Becoming the 'no FAL' ARCHARC0: Thread not mounted Tue Sep 22 13:02:43 2009 ARC1: Becoming the heartbeat ARCH ARC1: Becoming the heartbeat ARCHARC1: Thread not mounted Tue Sep 22 13:02:43 2009 ALTER DATABASE MOUNT Tue Sep 22 13:02:47 2009 Successful mount of redo thread 1, with mount id 2068494515 Tue Sep 22 13:02:47 2009 Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Tue Sep 22 13:02:48 2009 ALTER DATABASE OPEN Tue Sep 22 13:02:48 2009 Thread 1 opened at log sequence 3485 Current log# 5 seq# 3485 mem# 0: /p01/oradata/TESTDB/log05a.log Successful open of redo thread 1 Tue Sep 22 13:02:48 2009 ARC1: Media recovery disabled Tue Sep 22 13:02:48 2009 ARC0: Media recovery disabled Tue Sep 22 13:02:48 2009 SMON: enabling cache recovery Tue Sep 22 13:02:49 2009 Successfully onlined Undo Tablespace 26. Tue Sep 22 13:02:49 2009 SMON: enabling tx recovery Tue Sep 22 13:02:49 2009 Database Characterset is WE8ISO8859P1 Tue Sep 22 13:02:49 2009 SMON: about to recover undo segment 5 SMON: mark undo segment 5 as needs recovery Tue Sep 22 13:02:50 2009 replication_dependency_tracking turned off (no async multimaster replication found) Completed: ALTER DATABASE OPEN Tue Sep 22 13:03:43 2009 ARC1: Media recovery disabled Tue Sep 22 13:03:46 2009 Errors in file /opt/oracle/T/admin/TESTDB/bdump/TESTDB_reco_22538.trc: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/p01/oradata/TESTDB/undotbs01.dbf' Tue Sep 22 13:04:19 2009 Errors in file /opt/oracle/T/admin/TESTDB/bdump/TESTDB_reco_22538.trc: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/p01/oradata/TESTDB/undotbs01.dbf' Tue Sep 22 13:04:43 2009 ARC1: Media recovery disabled Tue Sep 22 13:05:07 2009 Errors in file /opt/oracle/T/admin/TESTDB/bdump/TESTDB_reco_22538.trc: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/p01/oradata/TESTDB/undotbs01.dbf' Tue Sep 22 13:05:43 2009 ARC1: Media recovery disabled Tue Sep 22 13:06:20 2009 Errors in file /opt/oracle/T/admin/TESTDB/bdump/TESTDB_reco_22538.trc: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/p01/oradata/TESTDB/undotbs01.dbf' Tue Sep 22 13:06:43 2009 ARC1: Media recovery disabled Tue Sep 22 13:07:43 2009 ARC0: Media recovery disabled Tue Sep 22 13:07:43 2009 ARC1: Media recovery disabled Tue Sep 22 13:07:50 2009 SMON: about to recover undo segment 5 SMON: mark undo segment 5 as needs recovery Tue Sep 22 13:08:08 2009 Errors in file /opt/oracle/T/admin/TESTDB/bdump/TESTDB_reco_22538.trc: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/p01/oradata/TESTDB/undotbs01.dbf' Tue Sep 22 13:08:14 2009 Shutting down instance: further logons disabled Shutting down instance (transactional) All transactions complete. Performing immediate shutdown License high water mark = 2 Tue Sep 22 13:08:19 2009 ALTER DATABASE CLOSE NORMAL Tue Sep 22 13:08:20 2009 SMON: disabling tx recovery SMON: disabling cache recovery Tue Sep 22 13:08:20 2009 Shutting down archive processes Archiving is disabled Tue Sep 22 13:08:20 2009 ARCH shutting down Tue Sep 22 13:08:20 2009 ARCH shutting down Tue Sep 22 13:08:20 2009 ARC0: Archival stopped Tue Sep 22 13:08:20 2009 ARC1: Archival stopped Tue Sep 22 13:08:20 2009 Thread 1 closed at log sequence 3485 Successful close of redo thread 1 Tue Sep 22 13:08:21 2009 Completed: ALTER DATABASE CLOSE NORMAL Tue Sep 22 13:08:21 2009 ALTER DATABASE DISMOUNT Completed: ALTER DATABASE DISMOUNT ARCH: Archiving is disabled Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active ARCH: Archiving is disabled Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active SQL> startup restrict mount; ORACLE instance started. Total System Global Area 1.0957E+10 bytes Fixed Size 750280 bytes Variable Size 218103808 bytes Database Buffers 1.0737E+10 bytes Redo Buffers 1060864 bytes Database mounted. SQL> Alter database datafile '/p01/oradata/TESTDB/undotbs01.dbf' offline drop; Database altered. SQL> alter database open ; Database altered. SQL> drop tablespace UNDOTBS1 including contents ; drop tablespace UNDOTBS1 including contents * ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU5$' found, terminate dropping tablespace SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.8.0 - Production oracle @ HM [TESTDB]:$
From: Mladen Gogala on 22 Sep 2009 02:51 Na Mon, 21 Sep 2009 20:52:32 -0700, DG problem napisao: > I'll probably just restart the import process again, but I was wondering > if there is any way to drop UNDOTBS1? Yes, there is. In the error message (ORA-01548) there is also the name of the rollback segment. The message looks like this: SQL> drop tablespace undotbs1 including contents and datafiles; drop tablespace undotbs1 including contents and datafiles * ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU11$' found, terminate dropping tablespace Here is the solution: startup mount; alter system set undo_management = 'MANUAL' scope=spfile; alter system set "_corrupted_rollback_segments"="_SYSSMU11$" scope=spfile; startup; drop rollback segment "_SYSSMU11$"; shutdown; startup nomount alter database open That was extracted from the ML note 28814.1. Of course, you should return to the automatic undo afterwards. -- http://mgogala.freehostia.com
From: Mladen Gogala on 23 Sep 2009 14:13 On Tue, 22 Sep 2009 06:51:54 +0000, Mladen Gogala wrote: > That was extracted from the ML note 28814.1. Of course, you should > return to the automatic undo afterwards. The original poster sent me an email because Google groups are apparently not cooperating, so he asked me to publish this note for him. Basically, the recipe has worked but the ML search is less than useful. Here is his note: "Thanks Mladen! That worked perfectly, very much appreciated. Congratulations on finding that in Metalink as I spent a couple of hours looking at how to do it and still couldn't solve it (maybe I'm getting too old :). I then went and looked at the note you reference and still couldn't find anything to do with "_corrupted_rollback_segments". I then did a ML knowledge base search (using the flash version, grumble grumble) on "_corrupted_rollback_segments" and it only returned six hits. Only two of those six hits contained the string "_corrupted_rollback_segments"! I then did an advanced search (unquoted) The Exact Phrase: _corrupted_rollback_segments And got six hits again with only two hits containing the search string! Has anyone else noticed this?" PS: --- I didn't find that in Metalink, I had the same problem and have opened a SR to fix it. That's how I learned to solve that kind of problems. -- http://mgogala.freehostia.com
From: joel garry on 23 Sep 2009 16:03 On Sep 23, 11:13 am, Mladen Gogala <mla...(a)bogus.email.invalid> wrote: > On Tue, 22 Sep 2009 06:51:54 +0000, Mladen Gogala wrote: > > That was extracted from the ML note 28814.1. Of course, you should > > return to the automatic undo afterwards. > > The original poster sent me an email because Google groups are apparently > not cooperating, so he asked me to publish this note for him. For some strange reason, they reject the free hostia. I had to add a space just to post this. jg -- @home.com is bogus. Banzai! http://www3.signonsandiego.com/stories/2009/sep/23/agents-fire-vans-running-border/?uniontrib
From: DG problem on 23 Sep 2009 19:14
I've been trying to post this all day (Google Groups) without success. Thanks Joel if this posts upon your advice. > Here is the solution: > > startup mount; > > alter system set undo_management = 'MANUAL' scope=spfile; > > alter system set "_corrupted_rollback_segments"="_SYSSMU11$" scope=spfile; > > startup; > > drop rollback segment "_SYSSMU11$"; > > shutdown; > > startup nomount > alter database open > > That was extracted from the ML note 28814.1. Of course, you should return > to the automatic undo afterwards. > > --http://mgogala.free hostia.com Thanks Mladen! That worked perfectly, very much appreciated. Congratulations on finding that in Metalink as I spent a couple of hours looking at how to do it and still couldn't solve it (maybe I'm getting too old :). I then went and looked at the note you reference and still couldn't find anything to do with "_corrupted_rollback_segments". I then did a ML knowledge base search (using the flash version, grumble grumble) on "_corrupted_rollback_segments" and it only returned six hits. Only two of those six hits contained the string "_corrupted_rollback_segments"! I then did an advanced search (unquoted) The Exact Phrase: _corrupted_rollback_segments And got six hits again with only two hits containing the search string! Has anyone else noticed this? |