Undo tablespace issue....

There were some issues in HARD Diskk of Test Machine, and systems department wanted to format/replace the hard disk (F Drive, where Oracle is installed). So I closed the test database, and later systems departmentd copied all folders, including Oracle folder, where all datafiles, control files, redo logs are stored. They restored all folders after completing their activity.

After that the systems department wanted the Database team to verify whether the Oracle is working properly or not. When we started Oracle services, and Oracle Database, it was automatically shuting down the instance, after checking the alert.log file we got below error:

DEBUG: Replaying xcb 0x4e7d126c, pmd 0x4deeb4b8 for failed op 8
Errors in file f:\oracle\product\10.2.0\admin\icpora\udump\icpora_ora_4192.trc:
ORA-00600: internal error code, arguments: [4194], [49], [44], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [49], [44], [], [], [], [], []

Metalink says:

A mismatch has been detected between Redo records and rollback (Undo) records.

We are validating the Undo record number relating to the change being applied againstthe maximum undo record number recorded in the undo block.This error is reported when the validation fails.

ARGUMENTS:

Arg [a] Maximum Undo record number in Undo block
Arg [b] Undo record number from Redo block

IMPACT:PROCESS FAILUREPOSSIBLE ROLLBACK SEGMENT CORRUPTION

SUGGESTIONS:This error may indicate a rollback segment corruption.This may require a recovery from a database backup depending on the situation.

We deleted untobs01.dbf and created undotbs2 tablespace undotbs02.dbf

1. alter database datafile '/home/u01/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;
2. create undo tablespace undotbs2 datafile '/home/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 1024m;
3. ALTER SYSTEM SET undo_tablespace='UNDOTBS2'

The system started working properly, the Full Database export batch file runs in nights, when we checked the log of export file it indicated below error:

EXP-00008: ORACLE error 376 encountered
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ICPORA\UNDOTBS01.DBF'
EXP-00000: Export terminated unsuccessfully

when we checked the v$recover_file, data file 2 is not found for recovery. so when we issued the drop tablespace undotbs1 command, we got error

ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU01$’ found, terminate dropping tablespace

select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————–
_SYSSMU01$ NEEDS RECOVERY UNDOTBS1
_SYSSMU02$ NEEDS RECOVERY UNDOTBS1
_SYSSMU03$ NEEDS RECOVERY UNDOTBS1
_SYSSMU04$ NEEDS RECOVERY UNDOTBS1
_SYSSMU05$ NEEDS RECOVERY UNDOTBS1
_SYSSMU06$ NEEDS RECOVERY UNDOTBS1
_SYSSMU07$ NEEDS RECOVERY UNDOTBS1
_SYSSMU08$ NEEDS RECOVERY UNDOTBS1

We closed the, database edited init.ora file and added parameters (comment undo_management=auto and entry for corrupted_rollback_segments
#undo_management=AUTO
undo_tablespace=UNDOTBS2
_corrupted_rollback_segments =('_SYSSMU01$','_SYSSMU02$','_SYSSMU03$','_SYSSMU04$','_SYSSMU05$','_SYSSMU06$','_SYSSMU07$','_SYSSMU08$'

1. STARTUP RESTRICT MOUNT pfile=C:\Oracle\init.ora
2. Drop rollback segment "_SYSSMU01$"
3. Droped all segments one by one that we entered in initfile. (step 2 drop command)
4. drop TABLESPACE UNDOTBS1;
5. shutdown
6. deleted entry of _corrupted_rollback_segments and removed comment from undo_management
7. STARTUP MOUNT pfile=C:\Oracle\init.ora8 and later opened the database.
8. Taken Full database export, it was successfully exported.

Speciall thanks to the writer/owner of below page, It helped me alot.
http://www.my-whiteboard.com/oracle-dba/how-to-drop-and-recreate-oracle-undo-tablespace-and-its-data-files.html