March 04, 2009

Dealing with Corruption in the SYS.HIST_HEAD$ Table

After a series of error messages in the alert_log, we identified a corrupt block in the sys.hist_head$ table.

In a normal situation, corruption in sys tables generally will require an export/import (or a expdp/impdp) to fix. However, when we attempted to run expdp or exp on certain tables, it would try to pull information from hist_head$, and fail.

ORA-31693: Table data object "BB_BB60"."COURSE_USERS" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-02373: Error parsing insert statement for table "BB_BB60"."COURSE_USERS".
ORA-00604: error occurred at recursive SQL level 3
ORA-01578: ORACLE data block corrupted (file # 1, block # 28237)
ORA-01110: data file 1: '/home/oracle/dbf/ORACLE/system_file1.dbf'

Fortunately, there is a solution. The hist_head$ table is a repository for column statistics, and therefore something that seems like it can be truncated. But because it is a critical system table, Oracle will not allow you to do it. In order to get around Oracle's defences, you need to put the database in upgrade mode before truncating. This relaxes Oracle's ordinary protections over the SYS objects.

shutdown immediate;
startup upgrade;
truncate table sys.hist_head$;
shutdown immediate;
startup;

We were then able to export the database users, recreate the database, and import.

Posted by jeffreyb at March 4, 2009 01:20 PM
Comments

thanks bro, it helped me.

Posted by: alamgir shaikh at January 6, 2014 01:18 AM

Thank you very much sir. This exactly what i want and i have relaxing after few days of head ache....
Once again thank you...

Bala

Posted by: Bala at February 4, 2015 08:21 AM
Post a comment









Remember personal info?