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 01:20 PM | Comments (2)