if you get ORA-00600 or the following error message on your Oracle standby database:
Fri Mar 09 10:57:49 2007
Errors in file c:\oracle\admin92\crm\bdump\crmsb_mrp0_524.trc:
ORA-00600: internal error code, arguments: [3020], [356515961], [1], [4606], [2], [16], [], []
ORA-10567: Redo is inconsistent with data block (file# 85, block# 121)
ORA-10564: tablespace UNDOTBS2
ORA-01110: data file 85: ‘K:\ORADATA\CRM\UNDOTBS02_1.DBF’
ORA-10560: block type ‘KTU SMU HEADER BLOCK’
Or your undo tablespace has grown to unmanageable size (~ 35 Gbytes), you may want to recreate unto tablespace. Here is how:
1, Make sure the database was last cleanly shut down.
sqlplus /nolog SQL>;connect sys/CHANGE@crm AS sysdba SQL>; shutdown immediate
2, mount database in RESTRICT mode, using pfile.
SQL>; STARTUP RESTRICT MOUNT pfile=C:\Oracle\job\crmstandby\initCRM_18.ora ORACLE instance started. Total System Global Area 1620126452 bytes Fixed Size 457460 bytes Variable Size 545259520 bytes DATABASE Buffers 1073741824 bytes Redo Buffers 667648 bytes DATABASE mounted.
3, Try to offline drop the bad datafile.
SQL>; ALTER DATABASE DATAFILE 'K:\ORADATA\CRM\UNDOTBS2_02.DBF' OFFLINE DROP; * ERROR at line 1: ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping tablespace
or this SQL:
DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES ; * ERROR at line 1: ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping tablespace
4, Use this query to see how many rollback segments were corrupted:
SQL>;select segment_name,STATUS,tablespace_name FROM dba_rollback_segs WHERE STATUS='NEEDS RECOVERY'; SEGMENT_NAME STATUS TABLESPACE_NAME —————————— —————- —————– _SYSSMU11$ NEEDS RECOVERY UNDOTBS2 _SYSSMU12$ NEEDS RECOVERY UNDOTBS2 _SYSSMU13$ NEEDS RECOVERY UNDOTBS2 _SYSSMU14$ NEEDS RECOVERY UNDOTBS2 _SYSSMU15$ NEEDS RECOVERY UNDOTBS2 _SYSSMU16$ NEEDS RECOVERY UNDOTBS2 _SYSSMU17$ NEEDS RECOVERY UNDOTBS2 _SYSSMU18$ NEEDS RECOVERY UNDOTBS2 _SYSSMU19$ NEEDS RECOVERY UNDOTBS2 _SYSSMU20$ NEEDS RECOVERY UNDOTBS2
5, Add the following line to pfile:
_corrupted_rollback_segments =(’_SYSSMU11$’,'_SYSSMU12$’,'_SYSSMU13$’,'_SYSSMU14$’,'_SYSSMU15$’,'_SYSSMU16$’,
‘_SYSSMU17$’,'_SYSSMU18$’,'_SYSSMU19$’,'_SYSSMU20$’)
Make sure you uncomment “undo_management=AUTO”, and specify you want to use UNDOTBS1 as undo tablespace.
#undo_management=AUTO
undo_tablespace=UNDOTBS1
6, Start the database again:
SQL>; STARTUP RESTRICT MOUNT pfile=C:\Oracle\job\crmstandby\initcrm_18.ora
7. Drop bad rollback segments
SQL>; DROP rollback segment "_SYSSMU11$"; Rollback segment dropped. … SQL>; DROP rollback segment "_SYSSMU20$"; Rollback segment dropped.
8, Check again
SQL>; SELECT segment_name,STATUS,tablespace_name FROM dba_rollback_segs; SEGMENT_NAME STATUS TABLESPACE_NAME —————————— —————- ————— SYSTEM ONLINE SYSTEM _SYSSMU2$ ONLINE UNDOTBS1 _SYSSMU3$ ONLINE UNDOTBS1 _SYSSMU4$ ONLINE UNDOTBS1 _SYSSMU5$ ONLINE UNDOTBS1 _SYSSMU6$ ONLINE UNDOTBS1 _SYSSMU7$ ONLINE UNDOTBS1 _SYSSMU8$ ONLINE UNDOTBS1 _SYSSMU9$ ONLINE UNDOTBS1 _SYSSMU10$ ONLINE UNDOTBS1 _SYSSMU21$ ONLINE UNDOTBS1
9. Now drop bad undo TABLESPACE UNDOTBS2;
SQL>; DROP TABLESPACE UNDOTBS2;
10, Recreate the undo rollback tablespace with all its rollback segments
SQL>;CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 'K:\oradata\CRM\UNDOTBS01.DBF' SIZE 2000M reuse AUTOEXTEND ON ;
11, Change undo tablespace
ALTER SYSTEM SET undo_tablespace = UNDOTBS1 ;
12. Remove the following line from pfile
_corrupted_rollback_segments =(’_SYSSMU11$’,'_SYSSMU12$’,'_SYSSMU13$’,'_SYSSMU14$’,'_SYSSMU15$’,'_SYSSMU16 $’,'_SYSSMU17$’,'_SYSSMU18$’,'_SYSSMU19$’,'_SYSSMU20$’)
and uncomment “undo_management=AUTO”
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
13, Shutdown database
SQL>;shutdown immediate;14, Edit initCRM_18.ora, make sure you change ‘undo_tablespace=UNDOTBS2″ to “undo_tablespace=UNDOTBS1″, then start oracle database:
sqlplus /nolog SQL>;connect sys/CHANGE@crm AS sysdba SQL>; STARTUP RESTRICT MOUNT pfile=C:\Oracle\job\crmstandby\initcrm_18.ora ORACLE instance started. Total System Global Area 1620126452 bytes Fixed Size 457460 bytes Variable Size 545259520 bytes DATABASE Buffers 1073741824 bytes Redo Buffers 667648 bytes DATABASE mounted. DATABASE opened.
15, Create Undo tablespace:
SQL>;CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'K:\oradata\CRM\UNDOTBS02.DBF' SIZE 2000M reuse AUTOEXTEND ON ; SQL>;DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;
16, Startup database with spfile
SQL>; startup; ORACLE instance started. Total System Global Area 1620126452 bytes Fixed Size 457460 bytes Variable Size 545259520 bytes DATABASE Buffers 1073741824 bytes Redo Buffers 667648 bytes DATABASE mounted. DATABASE opened.
Oooo! This is a point mentioned. I like when everything in place while it is understandable to mere mortals.