By Franck Pachot

.
Do you know the RMAN Recovery advisor? It detects the problems, and then you:


RMAN> list failure;
RMAN> advise failure;
RMAN> repair failure;

You need to have a failure detected. You can run Health Check if it was not detected automatically (see https://www.dbi-services.com/blog/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one/). In 12.2 you can run the repair directly, by specifying what you want to repair.

Syntax

There is no online help on RMAN but you can list which keywords are expected by supplying a wrong one:

RMAN> repair xxx;
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "failure"
RMAN-01008: the bad identifier was: xxx
RMAN-01007: at line 1 column 8 file: standard input

This is 12.1.0.2 where the only option is REPAIR FAILURE. In 12.2 we have a lot more:


RMAN> repair xxx
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "database, database root, datafile, failure, pluggable, tablespace, ("
RMAN-01008: the bad identifier was: xxx
RMAN-01007: at line 1 column 8 file: standard input

When you know what is broken, you can repair it without having to know what to restore and what to recover. You can repair:

  • database: the whole database
  • database root: the CDB$ROOT container, which means all its tablespaces
  • pluggable database: it means all the PDB tablespaces
  • a specific datafile

Repair pluggable database

I corrupt one datafile from PDB01:


RMAN> host "> /u01/oradata/CDB2/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/datafile/o1_mf_system_d8k2t4wj_.dbf";
host command complete

And I repair the pluggable database:


RMAN> repair pluggable database PDB01;
 
Starting restore at 23-APR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=203 device type=DISK
Executing: alter database datafile 21 offline
Executing: alter database datafile 22 offline
Executing: alter database datafile 23 offline
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00021 to /u01/oradata/CDB2/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/datafile/o1_mf_system_d8k2t4wj_.dbf
channel ORA_DISK_1: restoring datafile 00022 to /u01/oradata/CDB2/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/datafile/o1_mf_sysaux_d8k2t4wn_.dbf
channel ORA_DISK_1: restoring datafile 00023 to /u01/oradata/CDB2/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/datafile/o1_mf_users_d8kbmy6w_.dbf
channel ORA_DISK_1: reading from backup piece /u90/fast_recovery_area/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/backupset/2017_04_23/o1_mf_nnndf_B_dht2d4ow_.bkp
channel ORA_DISK_1: piece handle=/u90/fast_recovery_area/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/backupset/2017_04_23/o1_mf_nnndf_B_dht2d4ow_.bkp tag=B
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 23-APR-17
 
Starting recover at 23-APR-17
using channel ORA_DISK_1
 
starting media recovery
media recovery complete, elapsed time: 00:00:00
 
Executing: alter database datafile 21 online
Executing: alter database datafile 22 online
Executing: alter database datafile 23 online
Finished recover at 23-APR-17

The good thing is that it automatically restores and recovers the datafiles with only one command.
But we see here that all datafiles have been restored. AsI knew that only one datafile was corrupted, it would have been faster to use REPAIR DATAFILE for it.

However, doing the same and calling the recovery advisor is not better: it advises to:


1      Restore and recover datafile 21; Restore and recover datafile 23; Recover datafile 22

When dealing with recovery, you need to understand how it works, what was the scope of the failure, and how to repair it. The advisors or automatic actions can help but do not alleviate the need to understand.