Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 400 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Block corruption with creating tables/inserting data

658887
Member Posts: 3
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
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:
Answers
-
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 -
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. -
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 -
Since the currupted block belongs to system tablespace, its better to create a new database and import all the objects in the new DB.
-
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 -
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. -
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.
-
Would this be useful?
[http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#ADMIN022]
This discussion has been closed.