Forum Stats

  • 3,840,184 Users
  • 2,262,573 Discussions
  • 7,901,162 Comments

Discussions

Categories

Block corruption with creating tables/inserting data

658887
658887 Member Posts: 3
edited Sep 9, 2008 11:21PM in General Database Discussions
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
Tagged:
· Share on TwitterShare on Facebook

Answers

  • Robert Geier
    Robert Geier Member Posts: 2,989
    edited Sep 9, 2008 11:18AM
    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
    Robert Geier
    · Share on TwitterShare on Facebook
  • 658887
    658887 Member Posts: 3
    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.
    · Share on TwitterShare on Facebook
  • Robert Geier
    Robert Geier Member Posts: 2,989
    edited Sep 9, 2008 11:30AM
    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
    Robert Geier
    · Share on TwitterShare on Facebook
  • Anand...
    Anand... Member Posts: 3,590
    Since the currupted block belongs to system tablespace, its better to create a new database and import all the objects in the new DB.
    Anand...
    · Share on TwitterShare on Facebook
  • Robert Geier
    Robert Geier Member Posts: 2,989
    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
    Robert Geier
    · Share on TwitterShare on Facebook
  • 347756
    347756 Member Posts: 130
    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.
    · Share on TwitterShare on Facebook
  • 658887
    658887 Member Posts: 3
    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.
    · Share on TwitterShare on Facebook
  • Ganadeva
    Ganadeva Member Posts: 118
    Would this be useful?
    [http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#ADMIN022]
    · Share on TwitterShare on Facebook
This discussion has been closed.