I recently restored (from full, level 0 cold backup) an ARCHIVELOG database & recovered it to a point in time about 24 hours after backup. I ran RMAN validate and numerous blocks turned up in V$DATABASE_BLOCK_CORRUPTION with corruption of NOLOGGING. This was anticipated as database has frequent NOLOGGING load operations. The game-plan was to map V$DATABASE_BLOCK_CORRUPTION blocks to segments (table partitions), truncate partition, and replay the loads of those data sets from source files.

In most cases, this worked perfectly; most entries in V$DATABASE_BLOCK_CORRUPTION mapped directly to NOLOGGING table partitions which would've been loaded via INSERT /*+APPEND*/ after the cold backup.

Only wrench: V$DATABASE_BLOCK_CORRUPTION shows about 100 blocks with NOLOGGING corruption that do not match any entries in DBA_EXTENTS (they are, presumably, unused blocks).

1) Any idea what caused these blocks to become corrupt?
2) As they do not map to any extents in use, can I safely ignore them?
3) The OCD question: if these blocks aren't problematic, is there a way to clear them from V$DATABASE_BLOCK_CORRUPTION?

  1. Presumably there was data in them at one point, which has since been deleted.

  2. If the corrupted blocks are in free space, there is no need to take any action. Oracle will reformat the block when it attempts to use the space. So you can ignore the alerts.

  3. If you want to clear the corrupt blocks to prevent more alerts, create a nologging table in the relevant tablespace. Insert a lot of rows until the corrupt blocks disappear, then drop the table.

  • 1
    That was my take as well, glad to see a matching opinion. The ghost blocks seem "widely sprinkled" across several large datafiles, so I imagine I'd wind up running quite a mass of I/O to fill them all. Definitely doesn't seem worth the effort. – KevinKirkpatrick Aug 2 '16 at 15:49
  • Yeah if it's a production environment the cure may be worse than the disease. If you have access to Oracle support, check out Doc ID 336133.1 which has a lot more info and useful measures. – Andrew Brennan Aug 2 '16 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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