How to fix undo block corruption (UNDOTBS) of database

One of users written today that he got the undo block corruption while he is trying to start the database.  The reason he explain that due to power fluctuation the database is abnormally shutdown. When he checks the alert log file then finds the following error:

ORA-01578: ORACLE data block corrupted (file # 8, block # 39)
ORA-01110: data file 8: ‘E:ORACLEPRODUCT10.1.0ORADATASAD1UNDOTBS01.DBF’

Error 1578 happened during db open, shutting down database
USER: terminating instance due to error 1578
Errors in file E:oracleproduct10.1.0adminsad1bdumpsad1_arc3_1400.trc:
The Primarily you have to check the alert log file for any type of error or corruption or you can use the utility dbv (database Verify utility) which provides a mechanism to validate the structure of oracle data files at the operating system level. You can use it on regular basis to inspect data files for sings of corruption but you can use this utility only with datafiles not with control or redolog file.

DBVERIFY – Verification starting: FILE = E:ORACLEPRODUCT10.1.0ORADATASAD1UNDOTBS01.DBF

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

DBVERIFY – Verification complete

Total Pages Examined         : 104340

Total Pages Processed (Data) : 0

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 101216

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 3124

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Highest block SCN            : 2144459844 (15.24)

Step1: If you have Cold backup

Connect with sys as sysdba

Shutdown immediate;

Startup mount;

Restore datafile 8;

Recover datafile 8;

Alter database open;

Step2: If you have RMAN backup

rman target sys/oracle@sad1.world

blockrecover datafile 8 block 22;

Step3: If you do not have backup

Create another tablespace

Create or edit pfile and change tablespace as:

Create pfile=’d:sad_pfile.sql’ from spfile’;

undo_management=manual

undo_tablespace=UNDOTBS2
Startup Nomount pfile=’d:sad_pfile.sql’;
Startup mount;
alter database open;
Create spfile from pfile=’d:sad_pfile.sql’;

Now you are able to open the database as well as connect the schema but you are not able to perform any DDL and DML operation. This is due to the old rollback segements are still active and either needs recovery  or offline or drop the those segments. If you try to create any table within the connected schema will return the following error:

SQL> connect hrms/hrms

Connected.

SQL> Create table payment_master_test (ser number);

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace ‘HRMS’

ORA-06512: at line 19

Errors in file D:oracleora92adminsad1bdump/sad1_smon_21134.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: ‘E:f4oradatadwnonundotbs201.dbf’;

Try to find the old online rollback segment which needs recovery and make it offline one by one, thus will be easier to drop it.

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

SQL>alter rollback segment “_SYSSMU4$” offline;

SQL> drop rollback segment “_SYSSMU4$”;

SQL> drop rollback segment “_SYSSMU5$”;

Once drop all the rollback segments then easily you can drop the old undo tablespace.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s