alter database openfails with ORA-00600.
dbvboth point out that one data block (
type: 6) is corrupted.
- I cannot select from
dba_extentsas that requires an opened database
- I cannot use
backup validate check logicalcommand as the database is in
- I cannot set the database to
ARCHIVELOGmode as an instance recovery is required
- I cannot recover the instance as that would require me to fully startup and normally shutdown the database.
=> Is there any way I can find out what exactly is corrupted without opening the database?
One of our Development databases (non-productive) recently doesn't start up any longer and provides the following errors when trying to connect with a regular user:
ORA-01034: ORACLE not available
ORA-27101: Shared memory realm does not exist
I can connect as
sys as sysdba, and I can successfully perform the following commands:
shutdown immediate; startup mount;
but when I try doing
alter database open;
ORA-00600 along with various internal error codes is shown.
alert.log and the
dbv command line tool, I found out a data block is corrupted:
DBVERIFY - Verification starting : FILE = D:\ORADATA\ORCL\SYSTEM01.DBF
Page 62507 is influx - most likely media corrupt
Corrupt block relative dba: 0x0040f42b (file 1, block 62507)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0040f42b
last change scn: 0x0000.0eb6f2bc seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xfaae0601
check value in block header: 0xf67f
computed block checksum: 0x376f
type: 6 apparently means it's a regular data block which usually is a table, an index or similar. In order to find out which database object was compromised, I've first tried using queries like the following:
select owner, segment_type, segment_name from dba_extents where file_id = 1 and 62507 between block_id and block_id+blocks-1;
This however fails with the same ORA-01034 as any other query. This results in a recursive problem, as I would need to open the database in order to find out why I can't open the database.
After googling for a while, I also found I could query the
v$database_block_corruption view even if the database is not opened. I do however need to populate that view with data by running e.g.
RMAN> backup validate check logical database;
which unfortunately results in the new errors
ORA-19602 which basically mean that a backup (even a validation check) is not possible in
So I tried setting archivelog mode by executing
alter database archivelog;
on the mounted database.
This however produced yet another error:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
After googling for some more time, I found out that "instance recovery" can be achieved by completely starting up the database and then shutting it down regularly using
Which leads me back to my initial problem: I cannot fully start up (i.e.
open) the database.
Is there any way I can find out which table (or index, ...) is broken, without having to open the database?