Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 14 July 2014

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:\ORACLE\PRODUCT\10.1.0\ORADATA\SAD1\UNDOTBS01.DBF'
Error 1578 happened during db open, shutting down database
USER: terminating instance due to error 1578
Errors in file E:\oracle\product\10.1.0\admin\sad1\bdump\sad1_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:\ORACLE\PRODUCT\10.1.0\ORADATA\SAD1\UNDOTBS01.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:\oracle\ora92\admin\sad1\bdump/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:\f4\oradata\dwnon\undotbs201.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;

1 comments:

  1. If you need your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (even if they're dating somebody else now) you must watch this video
    right away...

    (VIDEO) Have your ex CRAWLING back to you...?

    ReplyDelete