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?
Best Answer
Well, I guess the database is expendable since there is no proper backup. So just drop and recreate the database.
OK, seriously there are not many options left. Before you do anything shutdown the database and copy all files which belong to the database somewhere else! Because everything you do can make it worse.
Once you did that you have 2 options:
This database is not in archivelog mode so there is no option to restore the block from a backup. If you still have an rman backup of datafile 1 you might be able to restore this file and then open the database with
_allow_resetlogs_corruption
(Don't use this option on your own!). Because you cannot fully recover the block, you have to tell Oracle that you don't care that the datafile do not have the same SCN. See details here: https://practicalappsdba.wordpress.com/2008/04/01/how-to-recover-and-open-the-database-if-the-archive-log-required-for-recovery-is-missing/If this is a block which contains standard objects you might be able to extract the block from a healthy database and inject it on this block.
Good luck!