Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Som.

Asked: March 07, 2017 - 10:23 pm UTC

Last updated: March 09, 2017 - 11:31 pm UTC

Version: 11g/12c

Viewed 1000+ times

You Asked

Hi Tom,

Suppose I have database with 100 datafiles on 20 tablespaces. I have a scheduled backup (datafiles plus archive log files) job which runs daily and weekly(any hot or cold). By the time of backup everything was fine but later after a week you encountered block corrupted.

My question is...
1. How can I find till what backup I need to restore?
2. On what date and time did block corrupt happen exactly?

Is there any views which can tell me the exact restore from the backup, instead of doing restore repeatedly till the block is rectified.

Thanks,
Shom

and Connor said...

1) You dont need to. From the docs:

"By default, RMAN first searches for good blocks in the real-time query physical standby database, then flashback logs and then blocks in full or level 0 incremental backups."

http://docs.oracle.com/database/121/BRADV/rcmblock.htm#BRADV89781

2) V$DATABASE_BLOCK_CORRUPTION gives you details on corrupted blocks. You could use the CORRUPTION_CHANGE# to approximate time.

Rating

  (1 rating)

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

Comments

Block corruption

Som Rai, March 09, 2017 - 9:31 am UTC

Yeah I am aware of RMAN utility tool but if I don't have luxury to use RMAN and and the organisation do only user manual backup, restore, and recovery..... I am just looking some fast restore and recovery in that scenario i.e. on user manual backup, restore, and recovery....

Thanks,
Shom
Connor McDonald
March 09, 2017 - 11:31 pm UTC

RMAN is indeed luxurious :-)

Without that, its going to be not much fun.

If you can dump the corrupted block, you might be able to glean some SCN information from it to assist, but lets face it - its a *corrupted* block, so trusting the contents is up for debate.

If the backups are on disk, you could run dbverify on them as well.
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.