How to find out the segment behind a corrupted block when the database cannot be opened?

Question

Short Version:

• alter database open fails with ORA-00600.
• alert.log and dbv 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 the backup validate check logical command as the database is in NOARCHIVELOG 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?

Answer 1

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:

1. Open an Oracle Service Request. With their help try to restore the block with lots of tricks and magic.
2. Call the Oracle Consulting Team, they have unpublished/internal tools which might be helpful.

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!