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.