Short Version:
alter database open
fails with ORA-00600.alert.log
anddbv
both point out that one data block (type: 6
) is corrupted.- I cannot select from
dba_extents
as that requires an opened database - I cannot use
RMON
with thebackup validate check logical
command as the database is inNOARCHIVELOG
mode. - I cannot set the database to
ARCHIVELOG
mode 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?
Detailed Version:
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.
Using the 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
So 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
:
RMAN> backup validate check logical database;
which unfortunately results in the new errors RMAN-03009
and ORA-19602
which basically mean that a backup (even a validation check) is not possible in NOARCHIVELOG
mode.
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 shutdown
or shutdown immediate
.
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?