Trying to drop old undo tablespace that needs recovery

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Trying to drop old undo tablespace that needs recovery

Trying to drop old undo tablespace that needs recovery

2005-10-26 - By Marquez, Chris

Reply: 1 2 3 4 5 6 7

My notes below.
Also look/confirm here;

Doc ID: Note:1013221.6
Subject: RECOVERING FROM A LOST DATAFILE IN A ROLLBACK TABLESPACE

Doc ID: Note:28812.1
Subject: Rollback Segment Needs Recovery

Chris Marquez
Oracle DBA

====================================
Drop Rollback or UNDO Tablspace With Active / Corrupt / "NEEDS RECOVERY"
Segments
====================================
-- ---- ---- ---- ---- ---- ---- ---
The Issue:
-- ---- ---- ---- ---- ---- ---- ---

---SQL*PLUS
SQL> alter database mount;
Database altered.

SQL> alter database open;
*
ERROR at line 1:
ORA-03113 (See ORA-03113.ora-code.com): end-of-file on communication channel

__OR__

---alert.log
Errors in file /o01/app/oracle/admin/report/bdump/report_smon_1295.trc:
ORA-01578 (See ORA-01578.ora-code.com): ORACLE data block corrupted (file # 2, block # 192423)
ORA-01110 (See ORA-01110.ora-code.com): data file 2: '/o01/oradata/report/undotbs01.dbf'
*OR*
Tue May 31 13:56:41 2005
Errors in file /o01/app/oracle/admin/report/bdump/report_smon_1646.trc:
ORA-01595 (See ORA-01595.ora-code.com): error freeing extent (16) of rollback segment (4))
ORA-00607 (See ORA-00607.ora-code.com): Internal error occurred while making a change to a data block
ORA-00600 (See ORA-00600.ora-code.com): internal error code, arguments: [4193], [1088], [992], [], [], [],
[], []
*OR EVEN*
Sun Jul 17 01:25:56 2005
Errors in file /oracle//bdump/orcl_j001_115070.trc:
ORA-00603 (See ORA-00603.ora-code.com): ORACLE server session terminated by fatal error
ORA-00600 (See ORA-00600.ora-code.com): internal error code, arguments: [kteuPropTime-2], [], [], [], [], [
], [], []

+++++++++++++++++++++++++++++++++++++
A. IF YOU CAN STILL OPEN THE DATABASE
+++++++++++++++++++++++++++++++++++++
-- ---- ---- ---- ---- ---- ---- ---
UNDO/RBS Seem OK!?
-- ---- ---- ---- ---- ---- ---- ---
col segment_name format a15
select segment_name, status from dba_rollback_segs;
SEGMENT_NAME STATUS
-- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- -----
SYSTEM ONLINE
_SYSSMU1$ ONLINE
_SYSSMU2$ ONLINE
...

-- ---- ---- ---- ---- ---- ---- ---
Edit init.ora to Comment UNDO/RBS parameters
-- ---- ---- ---- ---- ---- ---- ---
---vi init.ora
#undo_management=AUTO
#undo_tablespace=UNDOTBS
#undo_retention = 18000

-- ---- ---- ---- ---- ---- ---- ---
UNDO/RBS Issue Obvious now!
-- ---- ---- ---- ---- ---- ---- ---
shutdown
startup
col segment_name format a15
select segment_name, status from dba_rollback_segs;
SEGMENT_NAME STATUS
-- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- -----
SYSTEM ONLINE
_SYSSMU1$ PARTLY AVAILABLE
_SYSSMU2$ OFFLINE
...

+++++++++++++++++++++++++++++++++++++
B. IF YOU CAN *NOT* OPEN THE DATABASE
+++++++++++++++++++++++++++++++++++++

-- ---- ---- ---- ---- ---- ---- ---
Edit init.ora to Comment UNDO/RBS parameters & ADD "_smu_debug_mode", event
10015
-- ---- ---- ---- ---- ---- ---- ---
---vi init.ora
#undo_management=AUTO
#undo_tablespace=UNDOTBS
#undo_retention = 18000

# _smu_debug_mode simply collects diagnostic information for support purposes
_smu_debug_mode=1
# Event 10015 is the undo segment recovery tracing event.
# Use this to identify corrupted rollback/undo segments when a database cannot
be started.
event="10015 trace name context forever, level 10"

-- ---- ---- ---- ---- ---- ---- ---
startup Again
-- ---- ---- ---- ---- ---- ---- ---
SQL> startup nomount pfile=/.../init.ora.UNOD_PARAM;
ORACLE instance started.
SQL> alter database mount;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113 (See ORA-03113.ora-code.com): end-of-file on communication channel

-- ---- ---- ---- ---- ---- ---- ---
View event="10015 trace file for corrupted rollback/undo segments
-- ---- ---- ---- ---- ---- ---- ---
udump/> more orcl_ora_815334.trc
....
Recovering rollback segment _SYSSMU2$
UNDO SEG (BEFORE RECOVERY): usn = 2 Extent Control Header
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --

+++++++++++++++++++++++++++++++++++++
NOW FIX CORRUPTED SEGMENTS
+++++++++++++++++++++++++++++++++++++

-- ---- ---- ---- ---- ---- ---- ---
Edit init.ora to "force" Rollback or UNDO offline
-- ---- ---- ---- ---- ---- ---- ---
SQL>select '"'||segment_name||'"'||',' from sys.dba_rollback_segs where
tablespace_name = 'UNDOTBS'

---vi init.ora
For example TRADITIONAL ROLLBACK SEGMENTS:
_OFFLINE_ROLLBACK_SEGMENTS=(rbs1,rbs2)
_CORRUPTED_ROLLBACK_SEGMENTS=(rbs1,rbs2)

For example AUM UNDO TABLESPACE (SEGMENTS):
_OFFLINE_ROLLBACK_SEGMENTS=("_SYSSMU8$", "_SYSSMU9$", "_SYSSMU10$", "_SYSSMU30
$", "_SYSSMU31$", "_SYSSMU32$", "_SYSSMU34$","_SYSSMU35$")
_CORRUPTED_ROLLBACK_SEGMENTS=("_SYSSMU8$", "_SYSSMU9$", "_SYSSMU10$", "
_SYSSMU30$", "_SYSSMU31$", "_SYSSMU32$", "_SYSSMU34$","_SYSSMU35$")

---UNDO/RBS Issue Is Real (bad)!
shutdown
startup
col segment_name format a15
select segment_name, status from dba_rollback_segs;
SEGMENT_NAME STATUS
-- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- -----
SYSTEM ONLINE
_SYSSMU1$ NEEDS RECOVERY
_SYSSMU2$ OFFLINE
...

-- ---- ---- ---- ---- ---- ---- ---
Drop Rollback or UNDO Segments:
-- ---- ---- ---- ---- ---- ---- ---
SQL>select 'drop rollback segment '||'"'||segment_name||'"'||';' from sys.dba
_rollback_segs where tablespace_name = 'UNDOTBS1'

DROP ROLLBACK SEGMENT rbs1;
DROP ROLLBACK SEGMENT _SYSSMU1$;
DROP ROLLBACK SEGMENT _SYSSMU2$;
...

---UNDO/RBS All Gone...Easy and Simple to Drop UNDO/RBS Tablespace.
shutdown
startup
col segment_name format a15
select segment_name, status from dba_rollback_segs;
SEGMENT_NAME STATUS
-- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- -----
SYSTEM ONLINE

1 rows selected.

-- ---- ---- ---- ---- ---- ---- ---
Drop The Rollback or UNDO Tablespace
-- ---- ---- ---- ---- ---- ---- ---
col FILE_NAME for a60
col BYTES for 999,999,999,999,999
select FILE_ID, BYTES, FILE_NAME from dba_data_files where TABLESPACE_NAME =
'UNDOTBS';
FILE_ID BYTES FILE_NAME
-- ---- -- -- ---- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- ----
-- ---- -----
2 6,291,456,000 /o01/oradata/report/undotbs01.dbf

SQL>DROP TABLESPACE RBS INCLUDING CONTENTS;

SQL> DROP TABLESPACE UNDOTBS INCLUDING CONTENTS and datafiles;
Tablespace dropped.

[oracle@(protected) orcl920]$ ls -ltr /o01/oradata/report/undotbs01.dbf
ls: /o01/oradata/orcl920/undotbs01.dbf: No such file or directory

-- ---- ---- ---- ---- ---- ---- ---
RE-Create The Rollback or UNDO Tablespace
-- ---- ---- ---- ---- ---- ---- ---
SQL> CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE '/o01/oradata/orcl920/undotbs01
.dbf' SIZE 500M REUSE AUTOEXTEND OFF;
Tablespace created.

[oracle@(protected) orcl920]$ ls -ltr /o01/oradata/report/undotbs01.dbf
-rw-r-- -- 1 oracle dba 1048584192 May 16 17:50 /o01/oradata/report
/undotbs01.dbf

-- ---- ---- ---- ---- ---- ---- ---
Edit init.ora to Comment _OFFLINE_ROLLBACK_SEGMENTS= and UNcomment "undo_",
"rbs" parameters.
-- ---- ---- ---- ---- ---- ---- ---
---vi init.ora
#_OFFLINE_ROLLBACK_SEGMENTS

undo_management=AUTO
undo_tablespace=UNDOTBS
undo_retention = 18000

---UNDO/RBS Issue GONE!
shutdown
startup
col segment_name format a15
select segment_name, status from dba_rollback_segs;
SEGMENT_NAME STATUS
-- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- -----
SYSTEM ONLINE
_SYSSMU11$ ONLINE
_SYSSMU12$ ONLINE
...
11 rows selected.

---alert.log
Mon May 16 17:50:02 2005
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication
found)
Completed: ALTER DATABASE OPEN

-- ---- ---- ---- ---- ---- ---- --
DOCS
-- ---- ---- ---- ---- ---- ---- --
Doc ID: Note:1013221.6
Subject: RECOVERING FROM A LOST DATAFILE IN A ROLLBACK TABLESPACE

Doc ID: Note:28812.1
Subject: Rollback Segment Needs Recovery

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7233.28">
<TITLE>RE: Trying to drop old undo tablespace that needs recovery </TITLE>
</HEAD>
<BODY>


My notes below. 
Also look/confirm here; 
 
Doc ID:  Note:1013221.6 
Subject:  RECOVERING FROM A LOST DATAFILE IN A ROLLBACK TABLESPACE 
 
Doc ID:         Note:28812.1 
Subject:        Rollback Segment Needs Recovery
 
 
Chris Marquez 
Oracle DBA 
 
 
==================================== 
Drop Rollback or UNDO Tablspace With Active / Corrupt / "NEEDS RECOVERY
" Segments 
==================================== 
-- ---- ---- ---- ---- ---- ---- --- 
The Issue: 
-- ---- ---- ---- ---- ---- ---- --- 
 
---SQL*PLUS 
SQL> alter database mount; 
Database altered. 
 
SQL> alter database open; 
* 
ERROR at line 1: 
ORA-03113 (See ORA-03113.ora-code.com): end-of-file on communication channel 
 
        __OR__ 
 
---alert.log 
        Errors in file /o01/app/oracle/admin
/report/bdump/report_smon_1295.trc: 
        ORA-01578 (See ORA-01578.ora-code.com): ORACLE data block
corrupted (file # 2, block # 192423) 
        ORA-01110 (See ORA-01110.ora-code.com): data file 2: '/o01
/oradata/report/undotbs01.dbf' 
*OR* 
        Tue May 31 13:56:41 2005 
        Errors in file /o01/app/oracle/admin
/report/bdump/report_smon_1646.trc: 
        ORA-01595 (See ORA-01595.ora-code.com): error freeing extent (16)
of rollback segment (4)) 
        ORA-00607 (See ORA-00607.ora-code.com): Internal error occurred
while making a change to a data block 
        ORA-00600 (See ORA-00600.ora-code.com): internal error code,
arguments: [4193], [1088], [992], [], [], [], [], [] 
*OR EVEN* 
        Sun Jul 17 01:25:56 2005 
        Errors in file /oracle//bdump/orcl
_j001_115070.trc: 
        ORA-00603 (See ORA-00603.ora-code.com): ORACLE server session
terminated by fatal error 
        ORA-00600 (See ORA-00600.ora-code.com): internal error code,
arguments: [kteuPropTime-2], [], [], [], [], [], [], [] 
 
 
+++++++++++++++++++++++++++++++++++++ 
A. IF YOU CAN STILL OPEN THE DATABASE 
+++++++++++++++++++++++++++++++++++++ 
-- ---- ---- ---- ---- ---- ---- --- 
UNDO/RBS Seem OK!? 
-- ---- ---- ---- ---- ---- ---- --- 
col segment_name format a15 
select segment_name, status from dba_rollback_segs; 
SEGMENT_NAME    STATUS 
-- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- ----- 
SYSTEM          ONLINE 
_SYSSMU1$       ONLINE 
_SYSSMU2$       ONLINE 
... 
 
-- ---- ---- ---- ---- ---- ---- --- 
Edit init.ora to Comment UNDO/RBS parameters 
-- ---- ---- ---- ---- ---- ---- --- 
---vi init.ora 
        #undo_management=AUTO 
        #undo_tablespace=UNDOTBS 
        #undo_retention = 18000 
 
-- ---- ---- ---- ---- ---- ---- --- 
UNDO/RBS Issue Obvious now! 
-- ---- ---- ---- ---- ---- ---- --- 
shutdown 
startup 
col segment_name format a15 
select segment_name, status from dba_rollback_segs; 
SEGMENT_NAME    STATUS 
-- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- ----- 
SYSTEM          ONLINE 
_SYSSMU1$       PARTLY AVAILABLE 
_SYSSMU2$       OFFLINE 
... 
 
 
+++++++++++++++++++++++++++++++++++++ 
B. IF YOU CAN *NOT* OPEN THE DATABASE 
+++++++++++++++++++++++++++++++++++++ 
 
-- ---- ---- ---- ---- ---- ---- --- 
Edit init.ora to Comment UNDO/RBS parameters & ADD "_smu_debug_mode
", event 10015 
-- ---- ---- ---- ---- ---- ---- --- 
---vi init.ora 
        #undo_management=AUTO 
        #undo_tablespace=UNDOTBS 
        #undo_retention = 18000 
 
        # _smu_debug_mode simply collects
diagnostic information for support purposes 
        _smu_debug_mode=1 
        # Event 10015 is the undo segment
recovery tracing event. 
        # Use this to identify corrupted
rollback/undo segments when a database cannot be started. 
        event="10015 trace name context
forever, level 10" 
 
-- ---- ---- ---- ---- ---- ---- --- 
startup Again 
-- ---- ---- ---- ---- ---- ---- --- 
SQL> startup nomount pfile=/.../init.ora.UNOD_PARAM; 
ORACLE instance started. 
SQL> alter database mount; 
Database altered. 
SQL> alter database open; 
alter database open 
* 
ERROR at line 1: 
ORA-03113 (See ORA-03113.ora-code.com): end-of-file on communication channel 
 
 
-- ---- ---- ---- ---- ---- ---- --- 
View event="10015 trace file for corrupted rollback/undo segments 
-- ---- ---- ---- ---- ---- ---- --- 
udump/> more orcl_ora_815334.trc 
.... 
Recovering rollback segment _SYSSMU2$ 
UNDO SEG (BEFORE RECOVERY): usn = 2  Extent Control Header 
  -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- 
 
 
 
+++++++++++++++++++++++++++++++++++++ 
NOW FIX CORRUPTED SEGMENTS 
+++++++++++++++++++++++++++++++++++++ 
 
-- ---- ---- ---- ---- ---- ---- --- 
Edit init.ora to "force" Rollback or UNDO offline 
-- ---- ---- ---- ---- ---- ---- --- 
        SQL>select '"'||segment_name
||'"'||',' from sys.dba_rollback_segs where tablespace_name = 'UNDOTBS' 
 
---vi init.ora 
For example TRADITIONAL ROLLBACK SEGMENTS: 
        _OFFLINE_ROLLBACK_SEGMENTS=(rbs1
,rbs2) 
        _CORRUPTED_ROLLBACK_SEGMENTS=(rbs1
,rbs2) 
 
For example AUM UNDO TABLESPACE (SEGMENTS): 
        _OFFLINE_ROLLBACK_SEGMENTS=("
_SYSSMU8$", "_SYSSMU9$", "_SYSSMU10$", "_SYSSMU30$
", "_SYSSMU31$", "_SYSSMU32$", "_SYSSMU34$",
"_SYSSMU35$") 
        _CORRUPTED_ROLLBACK_SEGMENTS=("
_SYSSMU8$", "_SYSSMU9$", "_SYSSMU10$", "_SYSSMU30$
", "_SYSSMU31$", "_SYSSMU32$", "_SYSSMU34$",
"_SYSSMU35$") 
 
---UNDO/RBS Issue Is Real (bad)! 
shutdown 
startup 
col segment_name format a15 
select segment_name, status from dba_rollback_segs; 
SEGMENT_NAME    STATUS 
-- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- ----- 
SYSTEM          ONLINE 
_SYSSMU1$       NEEDS RECOVERY 
_SYSSMU2$       OFFLINE 
... 
 
 
-- ---- ---- ---- ---- ---- ---- --- 
Drop Rollback or UNDO Segments: 
-- ---- ---- ---- ---- ---- ---- --- 
        SQL>select 'drop rollback segment
'||'"'||segment_name||'"'||';' from sys.dba_rollback_segs  where
tablespace_name = 'UNDOTBS1' 
 
DROP ROLLBACK SEGMENT rbs1;  
DROP ROLLBACK SEGMENT _SYSSMU1$; 
DROP ROLLBACK SEGMENT _SYSSMU2$; 
... 
 
---UNDO/RBS All Gone...Easy and Simple to Drop UNDO/RBS Tablespace. 
shutdown 
startup 
col segment_name format a15 
select segment_name, status from dba_rollback_segs; 
SEGMENT_NAME    STATUS 
-- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- ----- 
SYSTEM          ONLINE 
 
1 rows selected. 
 
 
 
-- ---- ---- ---- ---- ---- ---- --- 
Drop The Rollback or UNDO Tablespace 
-- ---- ---- ---- ---- ---- ---- --- 
col FILE_NAME for a60 
col BYTES for 999,999,999,999,999 
select FILE_ID, BYTES, FILE_NAME from dba_data_files where TABLESPACE_NAME =
'UNDOTBS'; 
   FILE_ID         &nbsp
;      BYTES FILE_NAME 
-- ---- -- -- ---- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- ----
-- ---- ----- 
         2    &nbsp
;   6,291,456,000 /o01/oradata/report/undotbs01.dbf 
 
 
SQL>DROP TABLESPACE RBS INCLUDING CONTENTS; 
 
SQL> DROP TABLESPACE UNDOTBS INCLUDING CONTENTS and datafiles; 
Tablespace dropped. 
 
[oracle@(protected) orcl920]$ ls -ltr /o01/oradata/report/undotbs01.dbf 
ls: /o01/oradata/orcl920/undotbs01.dbf: No such file or directory 
 
 
-- ---- ---- ---- ---- ---- ---- --- 
RE-Create The Rollback or UNDO Tablespace 
-- ---- ---- ---- ---- ---- ---- --- 
SQL> CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE '/o01/oradata
/orcl920/undotbs01.dbf' SIZE 500M REUSE AUTOEXTEND OFF; 
Tablespace created. 
 
[oracle@(protected) orcl920]$ ls -ltr /o01/oradata/report/undotbs01.dbf 
-rw-r-- --    1 oracle   dba    
  1048584192 May 16 17:50 /o01/oradata/report/undotbs01.dbf 
 
 
-- ---- ---- ---- ---- ---- ---- --- 
Edit init.ora to Comment _OFFLINE_ROLLBACK_SEGMENTS= and UNcomment "undo_
", "rbs" parameters. 
-- ---- ---- ---- ---- ---- ---- --- 
---vi init.ora 
        #_OFFLINE_ROLLBACK_SEGMENTS 
 
        undo_management=AUTO 
        undo_tablespace=UNDOTBS 
        undo_retention = 18000 
 
---UNDO/RBS Issue GONE! 
shutdown 
startup 
col segment_name format a15 
select segment_name, status from dba_rollback_segs; 
SEGMENT_NAME    STATUS 
-- ---- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- ----- 
SYSTEM          ONLINE 
_SYSSMU11$      ONLINE 
_SYSSMU12$      ONLINE 
... 
11 rows selected. 
 
---alert.log 
Mon May 16 17:50:02 2005 
Database Characterset is WE8ISO8859P1 
replication_dependency_tracking turned off (no async multimaster replication
found) 
Completed: ALTER DATABASE OPEN 
 
 
-- ---- ---- ---- ---- ---- ---- -- 
DOCS 
-- ---- ---- ---- ---- ---- ---- -- 
Doc ID:  Note:1013221.6 
Subject:  RECOVERING FROM A LOST DATAFILE IN A ROLLBACK TABLESPACE 
 
Doc ID:         Note:28812.1 
Subject:        Rollback Segment Needs Recovery
 
 



</BODY>
</HTML>