Skip to Main Content
  • Questions

  • Protection from database server crashes using DB_ULTRA_SAFE

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: March 02, 2017 - 12:39 pm UTC

Last updated: March 04, 2017 - 1:49 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Good Morning,

In the last four years, all of our block corruptions have occurred after the database server has crashes. In total, this has occurred three times, we have about 12 prod databases and each database has experienced about 4 crashes each. I am considering using db_ultra_safe, but i think that the name is a bit deceiving. It gives me the impression that it provides a way to prevent a block corruption. If I had DB_ULTRA_SAFE=DATA_AND_INDEX, would it have prevented a significant amount of those block corruptions we have experienced over the past four years? I am trying to determine if it is worth adding up to a 15% overhead to the database systems.

Thanks,

John

and Connor said...

DB_ULTRA_SAFE is not a "new" function as such, more just a shorthand for existing checking technologies implemented by DB_BLOCK_CHECKING, DB_LOST_WRITE_PROTECT, and DB_BLOCK_CHECKSUM.

But a database crash should *never* corrupt a block. The database is architected precisely to avoid that scenario. The only way you get a corruption is if your OS and/or storage layer has problems, or there is some issue between the Oracle layer and the underlying layers. In that sense, we will always struggle to detect a corruption "right there and then" - the various levels of checking just "improve our chances" and/or automate if possible the resolution.

For the latter, the main parameter in play here is DB_LOST_WRITE_PROTECT, which will use an available standby database to try fix up the block on the primary. If you dont have a standby, then there isn't a great deal of benefit in that instance.

There's a thread here where we've got some numbers of potential redo overhead you might be interested in.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2851924800346010643

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

John cantu, March 03, 2017 - 1:02 pm UTC

Thanks, Connor.

Is the database also architected to avoid a database crash if the server crashed first?

Connor McDonald
March 04, 2017 - 1:49 am UTC

Yes, the caveat being the integrity of storage writes. It's one of the motivations for ASM, engineered systems etc...greater control over the whole stack.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.