Force drop of UNDO tablespace (with online segment) in 9.2.0.8?
Date: Mon, 21 Sep 2009 20:52:32 -0700 (PDT)
Message-ID: <ad6d9c96-da7e-4939-85f8-6aa2eccc012c_at_m3g2000pri.googlegroups.com>
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 _at_ 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 bytesDatabase 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 bytesDatabase 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 _at_ HM [TESTDB]:$
Received on Mon Sep 21 2009 - 22:52:32 CDT