ORA-00604 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'


The Database Verify utility(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 - 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;


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.
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS4'


Remove hidden parameter

*._OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
*.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
SQL> Shutdown immediate;