Block corruption with creating tables/inserting data

658887

I appear to have a corrupt block that I encounter when I reach a certain point in creating a table or inserting new data in an Oracle 10gR2 database. The error is

SQL Error: ORA-00604: error occurred at recursive SQL level 2
ORA-01578: ORACLE data block corrupted (file # 1, block # 59522)
ORA-01110: data file 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\IMAGES\SYSTEM01.DBF'
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.



(I only get the first two lines when inserting new data)

Is there a way to mark this block so as to not use it. There is no existing object using this block -- the issue appears to occur when something tries to use it for the first time.

Edited by: user8026807 on Sep 9, 2008 8:13 AM

Robert Geier

Are you in archivelog mode ? Do you have backups ? If so you can should be able to use rman to repair this corruption. If you look in the alert log you should be able to see this error and find out when it started, then you can work out if you have backups and archive logs available.

If you don't have the archivelogs or backup to repair this, I would strongly recommend that you create a new database, move your data, and delete the corrupt database (since the corruption is in the system tablespace). You could migrate to a new database using datapump or transportable tablespaces.

Edited by: Robert Geier on Sep 9, 2008 4:18 PM

658887

I am positive there are no backups and and I will check for archive logs although I doubt they are there. Since the original data seems to be readable, am I correct in presuming that the data can be exported out of the corrupt database and then recreated?

Thanks.

Robert Geier

If the bad blocks are not used then you can export. You can check if they are used by selecting from dba_extents.

Select should be something like :-

select owner, segment_name, segment_type from dba_extents
where file_id=1 and 59522 between block_id and block_id + blocks;

Edited by: Robert Geier on Sep 9, 2008 4:30 PM

Anand...

Since the currupted block belongs to system tablespace, its better to create a new database and import all the objects in the new DB.

Robert Geier

You may also want to run dbv over all of the data files to check for other corruption. Full export or full rman backup will also detect this.

dbv file=/usr/oracle/asg920xr/datafiles/ASG920xrsys.dbf blocksize=8192

347756

You mentioned the corrupted block is not being used by an object, you could try to resize the datafile, SYSTEM01.DBF.

This worked for me on a test server I had once; I did not have any backups so I could not use rman blockrecover. I query dba_extents and discovered the block was usused so I resized the datafile to the lowest size possible and the corruption error did not occur again.

658887

Resizing the datafile does not work. The corrupted block is not the highest block number used and resizing appears to only trim the highest block numbers.

Ganadeva

Would this be useful?
[http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#ADMIN022]