Skip to main content

How to fix undo block corruption (UNDOTBS) of database

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;

Comments

Post a Comment

Popular posts from this blog

Tablespace Management using Toad

You can view all of your tablespace information using toad tablespace screen from Database –> Administer –> Tablespaces Here each table describes different detailed information across all the tablespaces in database. If you open this screen from the toad DBA module then you are also able to see the “space history” and “IO history” tab along with Files, Free space, object, Fragmentation. With these tab you are able to able perform space monitoring and planning steps. These tab permits you check graphically space and IO usage over time. Through this way DBA are also able to estimate ‘Object size’ based on this estimation. From the below “alter tablespace” tab you can §          Modify the tablespace and datafile size. §          Add new datafile to the tablespace.
Post a Comment
Read more

Import Excel Data into Oracle using Oracle form Button

Import Excel Data into Oracle using Oracle form Button 1. Create a button on the form with the name "IMPORT_EXCEL" and write a pl/sql on "WHEN BUTTON PRESSED" trigger. BEGIN IF :System.Cursor_Block<>'GL_DAILY_COMPOUND_HEADER' THEN   Go_Item('GL_DAILY_COMPOUND_HEADER.V_DATE'); END IF; IF :System.Mode = 'NORMAL' AND :System.Record_Status IN ('NEW','INSERT') THEN   IMPORT_EXCEL_PROC;   ---Form procedure ELSE  MESSAGE('Import allowed only for new entry!!!');  MESSAGE('Import allowed only for new entry!!!'); END IF; END; 2. Now write the procedure "IMPORT_EXCEL_PROC" into the program unit. Don't forget to create required table and folder for procedure IMPORT_EXCEL_PROC PROCEDURE IMPORT_EXCEL_PROC IS application    OLE2.Obj_Type; workbooks      OLE2.Obj_Type; workbook       OLE2.Obj_Type; worksheets      OLE2.Obj_Type; worksheet       OLE2.Obj_Type; cell      
Post a Comment
Read more

Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8 The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB.  In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB. Like the database character set, the national character set is defined when the database is initially created and can usually no longer be changed, at least not easily or without involving quite a lot of work (export, recreate database, import). Except when creating the database, where the national character set is defined explicitly, it can change implicitly even when upgrading the database from Oracle8i to Oracle9i (or Oracle10g). You require SYSDBA authorization to change the national character set. Changing the national character set means changing an Oracle Dictionary entry, but no data is changed.  $sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> Select property_value fro
Post a Comment
Read more