Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Trying to drop old undo tablespace that needs recovery

RE: Trying to drop old undo tablespace that needs recovery

From: Marquez, Chris <cmarquez_at_collegeboard.org>
Date: Wed, 26 Oct 2005 18:41:02 -0400
Message-ID: <B30C2483766F9342B6AEF108833CC84E05BD6049@ecogenemld50.Org.Collegeboard.local> 





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_at_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_at_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





--
http://www.freelists.org/webpage/oracle-l


Received on Wed Oct 26 2005 - 17:45:15 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US