Blockrecover in Standard Edition? Nope, but close enough.

Halloween started really scary this year – some company called us and said, that they have some problems with their Oracle Database Standard Edition.

Apparently they had a storage crash six months before, which caused some blocks to get corrupted and from that time no one has noticed that RMAN is not producing any backups.

But the system was working fine so no one was complaining at the business level… until the Halloween 2022.

It has occurred that twice a year they do some complex reporting and while doing it, they noticed corrupted blocks in some tablespaces.

Blockrecover would be a blessing but it is possible only in Enterprise Edition, so they tried to restore and recover affected database files from 6 months before, and it was not a surprise that the process of applying that amount of archivelogs lasted a very, very long time!

So they called us to verify if there is any possibility to resume their business process within a few hours and not… days.

Well, let’s give it a try!

The errors looked similar to the one above. But there were much more database blocks 🙂 In rico2 blocks were filled with some random data:

Of course waiting a few days for applying hundreds of gigabytes of archivelogs was not an option… but a thought came to me, that if no one noticed this error before, those blocks were not being touched since the storage crash!

So in theory it could be possible to just restore the database files from before the crash, find the blocks and copy them from the source datafiles to the current ones.

The only problem is, how to be sure, that a block was not changed during this whole time?

It is actually pretty easy! You have to scan all archivelogs and verify if the affected blocks were ever modified. To do that, you have to have a block id.

To calculate a valid block id, you have to construct it from relative file number and block number in that file.

So if the relative file number is 15 and corrupted block number is 205, the block id can be calculated like this: 4194304*15+205 = 62914765.

4194304 is a total number of blocks in one regular datafile.

62914765 in hex is 0x3c000cd so in little endian format will have the following structure: cd 00 c0 03

If you know that, you write a simple tool to scan binary files and look for that pattern in parallel. I modified my tool focs_vm and created a new tool: focs_file.

If given archivelog contains change vectors for specified database block address (DBA), the output will look similar to this one:

If not – there will be nothing to show:

So if there were no changes found to the affected blocks, we can just restore a datafile to the new location:

Verify the block is there and it is not corrupted:

And copy it manually to the appropriate place:

Let’s see if Oracle can handle this brutal behavior:

Now the customer has to do some tests and verify that everything works good, but the processing can go on 🙂

This is extremely risky and I wouldn’t recommend it unless you are completely fucked and have no other possibility of rescuing your data! The business has to be aware that this is not supported and that the only supported method of fixing this problem is just to wait for archivelogs to be applied! But sometimes each hour costs a lot of money and it’s worth the risk.

It was another case of Schrödinger’s Backup 🙂