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.
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 126.96.36.199 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.