Skip to Main Content
  • Questions

  • Manual block level recovery steps on standby database using backup from primary database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: March 04, 2021 - 8:58 pm UTC

Last updated: March 10, 2021 - 5:20 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Greetings,

As I as was reading the 19c Data Guard manual, I came across a topic in about being able to use a backup taken at the primary to perform a block level recovery at it's standby database. I read that block level recovery can automatically be done if we have purchased an active data guard license. It does also state that it can do it manually, too, but it doesn't give the steps on how to perform it.

From Data Guard Manual: 10.2.1.6 Manual Block Media Recovery

Are you able to provide the steps to perform a manual block level recovery steps on standby database using backup from primary database without a Catalog? I would be nice if we could simply use the "FOR SERVICE" clause.

Thanks,

John

and Connor said...

MOS notes 1428823.1 and 144911.1 have a full example of doing block recovery.

Snippets pasted here showing the main points

Complete database check

RMAN>run {
       allocate channel d1 type disk;
       backup check logical validate database;
       release channel d1;
     }

Specific Datafile check

RMAN>run {
       allocate channel d1 type disk;
       backup check logical validate datafile 1;
       release channel d1;
     }

select * from V$DATABASE_BLOCK_CORRUPTION;

RMAN>run {
      allocate channel d1 type disk;
      blockrecover corruption list;
      release channel d1;
     }

or

<code>
RMAN> validate check logical database;
Starting validate at 20-SEP-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/<path>/sysaux01.dbf
input datafile file number=00001 name=/<path>/system01.dbf
input datafile file number=00003 name=/<path>/undotbs01.dbf
input datafile file number=00004 name=/<path>/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:01:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              74562        98560           2634075   
  File Name: /<path>/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
........
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    FAILED 0              1            22882           2633107   
  File Name: /<path>/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              685             
  Index      0              0               
  Other      1              22194           

validate found one or more corrupt blocks

RMAN> list failure;
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
441        HIGH     OPEN      20-SEP-13     Datafile 4: '/<path>/users01.dbf' contains one or more corrupt blocks



Prerequisites for using Advice Failure :-
In the current release, Data Recovery Advisor only supports single-instance databases. Oracle Real Application Clusters (Oracle RAC) databases are not supported.
The target database must be a single-instance database and must not be a physical standby database, although it can be a logical standby database.



RMAN> advise failure;
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
441        HIGH     OPEN      20-SEP-13     Datafile 4: '/<path>/users01.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Perform block media recovery of block 520 in file 4  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /<path>/hm/reco_4024241654.hm

RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /<path>/hm/reco_4024241654.hm

contents of repair script:
   # block media recovery
   recover datafile 4 block 520;

RMAN> repair failure noprompt;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /<path>/hm/reco_4024241654.hm

contents of repair script:
   # block media recovery
   recover datafile 4 block 520;

executing repair script

Starting recover at 20-SEP-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece=/<path>/backupset/2013_09_20/o1_mf_nnndf_TAG20130920T092224_93rm0khr_.bkp
channel ORA_DISK_1: piece handle=/<path>/backupset/2013_09_20/o1_mf_nnndf_TAG20130920T092224_93rm0khr_.bkp tag=TAG20130920T092224
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 187 is already on disk as file /<path>/archivelog/2013_09_20/o1_mf_1_187_93rmnqwg_.arc
archived log for thread 1 with sequence 188 is already on disk as file /<path>/archivelog/2013_09_20/o1_mf_1_188_93rmq47g_.arc
archived log for thread 1 with sequence 189 is already on disk as file /<path>archivelog/2013_09_20/o1_mf_1_189_93rn7994_.arc
archived log for thread 1 with sequence 190 is already on disk as file /<path>/archivelog/2013_09_20/o1_mf_1_190_93rnymmf_.arc
media recovery complete, elapsed time: 00:00:03
Finished recover at 20-SEP-13
repair failure complete


</code>

Rating

  (1 rating)

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

Comments

A reader, March 05, 2021 - 11:40 am UTC

Thank you, Connor, for providing the block recovery steps.

If the backup took place on the primary database, what is needed to perform those block recovery steps on the standby database? My assumption would be that we could:

1.) Catalog an RMAN backup taken at the primary; then, follow the block level recovery steps you provided.
or
2.) Maybe just connect to the primary using connect target @primary and connect auxiliary @standby; then, follow the block level recovery steps you provided.
or
3.) Maybe there is a way to use the recent "FOR SERVICE" option
?

Thanks
Connor McDonald
March 10, 2021 - 5:20 am UTC

My understanding is that you would cancel managed recovery to have the standby in a consistent state, then do block recovery as per normal with the standby connected to as the target.

The backups/archives must be accessible to the standby node.
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.

More to Explore

Backup/Recovery

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