Fix Undo Block Corruption

************************************************************

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-06512: at line 19

Errors in file /export/home/oracle/admin/df4/dwnon/bdump/dwnon_smon_26175.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 238 cannot be read at this time

ORA-01110: data file 238: '/u2/df4/oradata/dwnon/undotbs2_dwnon_01.dbf'

************************************************************

Dbv:

The Database Verify utility (dbv) provides a mechanism to validate the structure of Oracle data files at the operating system level.  It should be used on a regular basis to inspect data files for signs of corruption.  

Although it can be used against open data files, the primary purpose of dbv is to verify the integrity of cold datafiles that would be used for a backup.  If used against online datafiles, intermittent errors can occur and the utility should be executed again against the same file to verify accuracy.  The utility can only be used against datafiles however, not control files or archived redo logs. 

bash-2.05$ dbv file=/u2/df4/oradata/dwnon/undotbs2_dwnon_01.dbf blocksize=16384

DBVERIFY: Release 10.2.0.2.0 - Production on Tue Feb 7 12:56:11 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBVERIFY - Verification starting: FILE = /u2/df4/oradata/dwnon/undotbs2_dwnon_01.dbf

DBV-00200: Block, dba 998244473, already marked corrupted

DBVERIFY - Verification complete

Total Pages Examined         : 125440

Total Pages Processed (Data) : 0

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 121776

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 3664

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Highest block SCN            : 2468459942 (17.2468459942)

bash-2.05$

Mount Stage:

Step 1:

SQL> select FILE#, NAME, STATUS, ERROR, RECOVER from v$datafile_header

where status <> 'ONLINE';

FILE#       NAME                                      STATUS          ERROR REC

--------   ----------------------------------------  -----------     -----

238       /u2/df4/oradata/dwnon/undotbs2_dwnon_01.dbf  OFFLINE            NO

SQL> alter database recover datafile '/u2/df4/oradata/dwnon/undotbs2_dwnon_01.dbf'

Media Recovery Complete.

SQL> alter database datafile '/u2/df4/oradata/dwnon/sysaux_dwnon_01.dbf' online;

Database altered.

SQL> alter database open;

Note: Database is in open mode and able to connect to schemas but we are not able to perform any DDL and DML operation.

SQL> connect sukku/sukku

Connected.

SQL> Create table test (n number);

Error : 

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-06512: at line 19

Errors in file /export/home/oracle/admin/df4/dwnon/bdump/dwnon_smon_26175.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 238 cannot be read at this time

ORA-01110: data file 238: '/u2/df4/oradata/dwnon/undotbs2_dwnon_01.dbf'

SQL> Select segment_name, status from dba_rollback_segs where tablespace_name='UNDOTBS2'

And status = 'NEEDS RECOVERY'; 

SEGMENT_NAME                   STATUS

------------------------------ ----------------

_SYSSMU4$                      NEEDS RECOVERY

_SYSSMU5$                      NEEDS RECOVERY

_SYSSMU6$                      NEEDS RECOVERY

_SYSSMU7$                      NEEDS RECOVERY

_SYSSMU8$                      NEEDS RECOVERY

_SYSSMU9$                      NEEDS RECOVERY

_SYSSMU10$                    NEEDS RECOVERY

7 rows selected.

SQL> Shut Immediate

Note:   If the old segments are online, then they must be taken offline. Once these segments are offline it will be easy to drop old undo tablespace without any exceptions.

SQL>alter rollback segment “_SYSSMU4$” offline;

Step 2:

SQL> Startup nomount;   

SQL> Create pfile=' initdwnon.ora' from spfile;
SQL> Shutdown immediate;

Step 3:

Modify parameter file

*.undo_management='MANUAL'

#*.undo_tablespace='UNDOTBS1'

*._OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

SQL> Startup mount pfile=' initdwnon.ora'
SQL> Alter database open ;

SQL> drop rollback segment "_SYSSMU4$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU5$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU6$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU7$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU8$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU9$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU10$";

Rollback segment dropped.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

SQL> CREATE UNDO TABLESPACE "UNDOTBS4"

  DATAFILE'/u2/df4/oradata/dwnon/undotbs04.dbf' SIZE 1024M; 

Tablespace created.

Step 4 :

Modify parameter file

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS4'

Remove hidden parameter

*._OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

SQL> Shutdown immediate;
SQL> Startup nomount; ---> Using spfile

SQL> Create Spfile=’/export/home/oracle/product/10.2/dbs/spfiledwnon.ora’ from

Pfile=’ initdwnon.ora’
SQL> Shutdown immediate;
SQL> Startup