RE: Trying to drop old undo tablespace that needs recovery
- From: "Marquez, Chris" <cmarquez@xxxxxxxxxxxxxxxx>
- To: <mark.strickland@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Oct 2005 18:41:02 -0400
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: end-of-file on communication channel
__OR__
---alert.log
Errors in file /o01/app/oracle/admin/report/bdump/report_smon_1295.trc:
ORA-01578: ORACLE data block corrupted (file # 2, block # 192423)
ORA-01110: 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: error freeing extent (16) of rollback segment (4))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: 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: ORACLE server session terminated by fatal error
ORA-00600: 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: 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@util1 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@util1 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
Other related posts:
