1

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?

2
  • you probably can (log-in as sysdba) alter system dump datafile 1 block 62507; The block will be dumped in hex in tracefile. Then use google-fu to interpret the contents. Better yet, log a case with Oracle support, it might be best that way.
    – Raj
    Jan 20, 2015 at 17:16
  • I have in the past done recovery on databases that were not in archive log mode by doing recover database until cancel and pointing to the online redo logs for the archive logs. if you are lucky this could work. If this is a production database it needs to be in archive log mode with hot backups. In the case of data warehouses, I have seen companies run in noarchivelog mode with weekly warm backups, and they would restore and do the last few ETL loads and they were happy with that. The recovery that you need depends on what is in the database and how it is used.
    – Gandolf989
    Jan 11, 2018 at 13:52

1 Answer 1

4

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.