RMAN : utiliser l'advisor RMAN quand on a perdu tous les fichiers de contrôle - RMAN advisor and loss of all control files

16 juin 2020

RMAN : utiliser l'advisor RMAN quand on a perdu tous les fichiers de contrôle - RMAN advisor and loss of all control files

Introduction
Le fichier de contrôle d'une base Oracle doit être ABSOLUMENT sauvegardé et multiplexé (exister en plusieurs copies). Ces copies doivent en outre résider sur des disques durs différents, gérés par des contrôleurs de disque distincts, sur des serveurs différents etc etc. Pourquoi? Si vous perdez vos fichiers de contrôle, l'instance Oracle ne peut plus accéder à la base car c'est dans ce ctrl file que se trouvent les chemins en dur des fichiers de la base de données; en résumé, le fichier de contrôle contrôle l'accès à la base de données.

Quand vous êtes débutant, que vous vous connectez à une base qui refuse de démarrer et que vous recevez en pleine face un message disant que TOUS les ctrl files ont été supprimés, c'est très stressant, bien plus que si il en restait au moins un.

Mais tout n'est pas perdu, si vous avez une sauvegarde RMAN de ce fichier, on va voir comment le récupérer via l'advisor RMAN, un outil hyper puissant en cas d'échec (Failure) sur votre base.

Points d'attention
N/A.

Base de tests
Une base Oracle 18c multi-tenants.

Exemples

============================================================================================
Configuration de la base
============================================================================================
Il faut modifier, sous Linux, la variable NLS_DATE_FORMAT pour que les sauvegardes RMAN incorporent l'heure.
[oracle@vbgeneric ~]$ export NLS_DATE_FORMAT="dd-month-yyyy hh:mi:ss am"

On positionne les variables d'environnement Oracle.
[oracle@vbgeneric ~]$ . oraenv
ORACLE_SID = [orcl12c] ?
...

Pour me connecter au CDB$ROOT, j'utilise le service orcl12c de ma base de test.
[oracle@vbgeneric ~]$ sqlplus SYS@orcl12c as sysdba

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

La base est bien en mode ARCHIVE LOG : hyper important pour vos bases de prod.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

Lancement de RMAN : la base cible est le CDB$ROOT donc j'utilise le service orcl12c.
[oracle@vbgeneric ~]$ rman target SYS@orcl12c
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 14 14:34:36 2020
target database Password:
connected to target database: ORCL12C (DBID=768045447)

On est bien dans le CDB$ROOT car le DBID affiché lors de la connexion RMAN est 768045447, qui est le DBID stocké dans V$databse et pas celui d'une PDB présent dans dba_pdbs. En outre, si on est dans une PDB, RMAN affiche à la connexion les deux noms de services, par exemple ORCL12C/ORCL au lieu de simplement ORCL12C.
RMAN> select dbid, name from v$database;
DBID NAME
---------- ---------
768045447 ORCL12C

RMAN> select dbid, pdb_name from dba_pdbs order by 1;
DBID PDB_NAME
-----------------------
1016842785 PDB$SEED
2846920952 ORCL

Affichons maintenant la configuration RMAN : la sauvegarde automatique du ctrl file est activée, mais que cela ne vous empêche pas d'en faire une explicitement, pour être sur! On note le commentaire default, ce qui permet de voir si un paramètre a été modifié par un dba.
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL12C are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2/db_1/dbs/snapcf_orcl12c.f'; # default

============================================================================================
Gestion des sauvegardes RMAN
============================================================================================
Pour mon test, je supprime toutes les sauvegardes déjà existantes, pour partir de zéro.
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 14-JUN-20 1 1 NO TAG20200614T064236
2 B F A DISK 14-JUN-20 1 1 NO TAG20200614T064236
3 B F A DISK 14-JUN-20 1 1 NO TAG20200614T064236
4 B F A DISK 14-JUN-20 1 1 NO TAG20200614T064236
5 B F A DISK 14-JUN-20 1 1 NO TAG20200614T064415
6 B F A DISK 14-JUN-20 1 1 NO TAG20200614T065802

RMAN> delete backupset 1;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T064236_h gczrxjf_.bkp

Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T0 64236_hgczrxjf_.bkp RECID=1 STAMP=1043044957
Deleted 1 objects
...

C'est bon, j'ai tout supprimé.
RMAN> list backup;
specification does not match any backup in the repository

Sauvegarde des bases
Dans le CDB$ROOT, je fais une sauvegarde de toutes les bases AVEC, en plus, le control file. Je ne mets pas les archived redo logs car je viens de créer ma base mais cela eut été plus propre... à noter qu'on ne peut pas, dans une même sauvegarde, sauver le ctrl file et les redo logs. RMAN va donc sauvegarder le CDB$ROOT, la base de test orcl, la base PDB$SEED et les ctrl files et le fichier .ora.
RMAN> backup database include current controlfile;
Starting backup at 14-JUN-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
input datafile file number=00014 name=/u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
channel ORA_DISK_1: starting piece 1 at 14-JUN-20
channel ORA_DISK_1: finished piece 1 at 14-JUN-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T144448_hgdw119x_.bkp tag=TAG20200614T144448 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl12c/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl12c/sysaux01.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/orcl12c/undotbs2.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-JUN-20
channel ORA_DISK_1: finished piece 1 at 14-JUN-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T144448_hgdw2s5l_.bkp tag=TAG20200614T144448 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 14-JUN-20
channel ORA_DISK_1: finished piece 1 at 14-JUN-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T1 44448_hgdw45k9_.bkp tag=TAG20200614T144448 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 14-JUN-20
channel ORA_DISK_1: finished piece 1 at 14-JUN-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_06_14/o1_mf_ncnnf_TAG20200614T144448_hgdw4oqy_.bkp tag=TAG20200614T144448 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-JUN-20
Starting Control File and SPFILE Autobackup at 14-JUN-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_06_14/o1_mf_s_1043074006_hgdw4q3g_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-JUN-20

Liste des backups
Alors, si on liste les backups, qu'est-ce que RMAN a sauvegardé?
RMAN> list backup;
List of Backup Sets
===================

ICI RMAN A SAUVEGARDE LA PDB ORCL : son nom est orcl et ses fichiers sont dans le répertoire ORCL12C.
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 1.33G DISK 00:00:37 14-JUN-20
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20200614T144448
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T144448_h gdw119x_.bkp
List of Datafiles in backup set 7
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
9 Full 2270760 14-JUN-20 NO /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 Full 2270760 14-JUN-20 NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 Full 2270760 14-JUN-20 NO /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 Full 2270760 14-JUN-20 NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 Full 2270760 14-JUN-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 Full 2270760 14-JUN-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf

ICI RMAN A SAUVEGARDE LE CDB$ROOT : son nom est ORCL12C.
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 1.01G DISK 00:00:32 14-JUN-20
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20200614T144448
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T144448_hgdw2s5l_.bkp
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2270811 14-JUN-20 NO /u01/app/oracle/oradata/orcl12c/system01.dbf
3 Full 2270811 14-JUN-20 NO /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
7 Full 2270811 14-JUN-20 NO /u01/app/oracle/oradata/orcl12c/users01.dbf
15 Full 2270811 14-JUN-20 NO /u01/app/oracle/oradata/orcl12c/undotbs2.dbf

ICI RMAN A SAUVEGARDE LA PDB$SEED.
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 512.80M DISK 00:00:06 14-JUN-20
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20200614T144448
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T144448_h gdw45k9_.bkp
List of Datafiles in backup set 9
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
6 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
8 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

ICI RMAN A SAUVEGARDE LE CONTROL FILE : c'est la sauvegarde explicite.
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Full 17.91M DISK 00:00:01 14-JUN-20
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20200614T144448
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_06_14/o1_mf_ncnnf_TAG20200614T144448_hgdw4oqy_.bkp
Control File Included: Ckp SCN: 2270842 Ckp time: 14-JUN-20

ICI RMAN A SAUVEGARDE LE CONTROL FILE ET LE SPFILE : c'est la sauvegarde implicite liée aux paramètres de configuration car les deux ont été sauvegardés en même temps.
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11 Full 17.94M DISK 00:00:01 14-JUN-20
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20200614T144646
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_06_14/o1_mf_s_1043074006_hgdw4q3g_.bkp
SPFILE Included: Modification time: 14-JUN-20
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 2270848 Ckp time: 14-JUN-20

============================================================================================
Suppression des fichiers de contrôle : la base ne démarre plus
============================================================================================
Maintenant on supprime les deux fichiers de contrôle sous Linux. Je fais l'opération sous SQL*Plus; les commandes ! ou HOST passent sous RMAN si on les entoure par des quotes.
RMAN> select name from v$controlfile;
NAME
----------------------------------------------------------
/u01/app/oracle/oradata/orcl12c/control01.ctl
/u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl

SQL> ! rm /u01/app/oracle/oradata/orcl12c/control01.ctl /u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl

SQL> ! ls /u01/app/oracle/oradata/orcl12c/control01.ctl
ls: cannot access /u01/app/oracle/oradata/orcl12c/control01.ctl: No such file or directory
SQL> ! ls /u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl
ls: cannot access /u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl: No such file or directory

Lors de l'arrêt relance de la base, celle-ci refuse de se démonter puisque les control files n'existent plus et qu'il faut y accéder pour démonter la base.
SQL> shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl12c/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

La base est encore montée.
SQL> select status from v$instance;
STATUS
------------
MOUNTED

A priori pas possible de la démonter mais finalement les messages ne sont pas parlant car l'instance a bien été arrêtée.
SQL> alter database dismount;
alter database dismount
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4620
Session ID: 29 Serial number: 44074

SQL> select status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Le démarrage est impossible : normal!
[oracle@vbgeneric ~]$ sqlplus SYS@orcl12c as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 360714136 bytes
Database Buffers 461373440 bytes
Redo Buffers 7974912 bytes
ORA-00205: error in identifying control file, check alert log for more info

Alors, que dit le fichier ALERT_SID.log? Hé bien qu'il manque les deux fichiers de contrôle, référencés dans le .ora.
[oracle@vbgeneric trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace

[oracle@vbgeneric trace]$ view alert_orcl12c.log
...
ALTER DATABASE MOUNT
2020-06-14T15:01:12.536672-04:00
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl12c/control01.ctl'
ORA-27037: unable to obtain file status
...

On se reconnecte à RMAN. Il dit que la base n'est pas montée.
[oracle@vbgeneric trace]$ rman target SYS@orcl12c
target database Password:
connected to target database: ORCL12C (not mounted)

Là c'est marrant, je veux voir le status de la base et RMAN me dit qu'il utilise le fichier de contrôle alors que je l'ai supprimé. Je pense qu'il détecte plutôt qu'il n'y a pas de catalogue RMAN et donc il affiche ce message par défaut.
RMAN> select status from v$instance;
using target database control file instead of recovery catalog
STATUS
------------
STARTED

============================================================================================
L'advisor RMAN - le conseiller qui fait presque tout :-)
============================================================================================
En cas de gros problème sur une base (arrêt/démarrage impossible par exemple), lancez absolument le conseiller RMAN. Attention, ce conseiller ne fonctionne pas si vous voulez restaurer un tablespace applicatif supprimé il y a un mois et dont Oracle n'a pas besoin pour fonctionner; ce conseiller ne fonctionne que pour les échecs sur la base, c'est à dire les gros problèmes.

Etape 1 : lister les failures (échecs)
C'est bon, on a bien le problème des ctrl files, qui est taggué comme CRITICAL.
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
421 CRITICAL OPEN 14-JUN-20 Control file /u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl is missing
418 CRITICAL OPEN 14-JUN-20 Control file /u01/app/oracle/oradata/orcl12c/control01.ctl is missing

Etape 2 : afficher les conseils sur les échecs
RMAN nous propose une solution automatique pour résoudre le problème. Le fichier reco_765207530.hm peut se lire avec vi, il contient juste des commandes RMAN lisibles, comme restore, recover, alter database... Vous noterez qu'il y a trois parties : les actions manuelles obligatoires, facultatives et la solution automatique.
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
421 CRITICAL OPEN 14-JUN-20 Control file /u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl is missing
418 CRITICAL OPEN 14-JUN-20 Control file /u01/app/oracle/oradata/orcl12c/control01.ctl is missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=320 device type=DISK
analyzing automatic repair options complete

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

Optional Manual Actions
=======================
1. If file /u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl was unintentionally renamed or moved, restore it
2. If file /u01/app/oracle/oradata/orcl12c/control01.ctl was unintentionally renamed or moved, restore it
3. If this is a standby database, restore the controlfile for a standby database using RESTORE STANDBY CONTROLFILE FROM AUTOBACKUP command
4. If this is a primary database and a standby database is available, then perform a Data Guard failover initiated from the standby

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore a backup control file
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/hm/reco_765207530.hm

Etape 3 : réparer les échecs.
Pour réparer une erreur, soit on tape repair failure soit on tape repair failure et un numéro de correction s'il y en a plusieurs possibles. Vous noterez que RMAN affiche le contenu du script de correction; comme ça vous apprenez de l'advisor comment réparer les erreurs sur votre base.
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/hm/reco_765207530.hm
contents of repair script:
# restore control file
restore controlfile from autobackup;
sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? Y
executing repair script
Starting restore at 14-JUN-20
using channel ORA_DISK_1
recovery area destination: /u01/app/oracle/fast_recovery_area/orcl12c
database name (or database unique name) used for search: ORCL12C
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_06_14/o1_mf_s_1043074006_hgdw4q3g_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_06_14/o1_mf_s_1043074006_hgdw4q3g_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/orcl12c/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl
Finished restore at 14-JUN-20
sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete

Est-ce que la base peut démarrer?
SQL> select status from v$instance;
STATUS
------------
MOUNTED

Etape post repair failure : relancer l'advisor
Ah, vu qu'on a restauré le control file, il faut faire un OPEN de la base avec l'option RESETLOGS. Cette option crée une nouvelle incarnation de la base Oracle, remet à 1 la séquence des redos logs online.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Argh, encore un pb... Peut-être que le fait de monter la base a créé und écalage entre le control file restauré et les fichiers actuels de la base.
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl12c/system01.dbf'

OK, on relance l'advisor RMAN. Avant cela je quitte RMAN car quand on modifie la structure de la base, il faut quitter RMAN et revenir dans l'utilitaire. Dans notre cas, ce n'est peut-être pas obligatoire mais bon, ne prenons pas de risque ;-)
RMAN> exit

[oracle@vbgeneric trace]$ rman target SYS@orcl12c
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 14 15:17:03 2020
connected to target database: ORCL12C (DBID=768045447, not open)

Ah, de nouvelles erreurs, trois critiques, une high. On continue alors à utiliser ce super advisor RMAN.
RMAN> list failure;
using target database control file instead of recovery catalog
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
487 CRITICAL OPEN 14-JUN-20 System datafile 9: '/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf' needs media recovery
472 CRITICAL OPEN 14-JUN-20 System datafile 1: '/u01/app/oracle/oradata/orcl12c/system01.dbf' needs media recovery
469 CRITICAL OPEN 14-JUN-20 Control file needs media recovery
159 HIGH OPEN 14-JUN-20 One or more non-system datafiles need media recovery

Que nous propose RMAN comme solution?
RMAN> advise failure;
Starting implicit crosscheck backup at 14-JUN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=332 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 14-JUN-20
Starting implicit crosscheck copy at 14-JUN-20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-JUN-20
searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_06_14/o1_mf_s_1043074006_hgdw4q3g_.bkp
Database Role: PRIMARY

List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
487 CRITICAL OPEN 14-JUN-20 System datafile 9: '/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf' needs media recovery
472 CRITICAL OPEN 14-JUN-20 System datafile 1: '/u01/app/oracle/oradata/orcl12c/system01.dbf' needs media recovery
469 CRITICAL OPEN 14-JUN-20 Control file needs media recovery
159 HIGH OPEN 14-JUN-20 One or more non-system datafiles need media recovery
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
=======================
1. If you have the correct version of the control file, then shutdown the database and replace the old control file
2. If you restored the wrong version of data file /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf, then replace it with the correct one
3. If you restored the wrong version of data file /u01/app/oracle/oradata/orcl12c/system01.dbf, then replace it with the correct one
4. If you restored the wrong version of data file /u01/app/oracle/oradata/orcl12c/sysaux01.dbf, then replace it with the correct one
5. If you restored the wrong version of data file /u01/app/oracle/oradata/orcl12c/users01.dbf, then replace it with the correct one
6. If you restored the wrong version of data file /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf, then replace it with the correct one
7. If you restored the wrong version of data file /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf, then replace it with the correct one
8. If you restored the wrong version of data file /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf, then replace it with the correct one
9. If you restored the wrong version of data file /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf, then replace it with the correct one
10. If you restored the wrong version of data file /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf, then replace it with the correct one
11. If you restored the wrong version of data file /u01/app/oracle/oradata/orcl12c/undotbs2.dbf, then replace it with the correct one

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Recover datafile 9; Recover datafile 1; Recover datafile 3; ...
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/hm/reco_738935833.hm

On lance la réparation automatique.
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/hm/reco_738935833.hm
contents of repair script:
# recover datafile
recover database;
alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? Y
executing repair script
Starting recover at 14-JUN-20
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/oradata/orcl12c/redo02.log
archived log file name=/u01/app/oracle/oradata/orcl12c/redo02.log thread=1 sequence=2
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-JUN-20
Statement processed
repair failure complete

Et là, bingo, dans ma session SQL, je n'ai même pas à ouvrir ma base, RMAN l'a fait pour moi dans son script :-)
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01531: a database already open by the instance

SQL> select status from v$instance;
STATUS
------------
OPEN

On arrive bien à interroger la base de données et à la redémarrer :-)
SQL> select count(*) from dba_users;
COUNT(*)
----------
36

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 360714136 bytes
Database Buffers 461373440 bytes
Redo Buffers 7974912 bytes
Database mounted.
Database opened.

============================================================================================
Que deviennent les sauvegardes de l'ancienne incarnation?
============================================================================================
ATTENTION : avant Oracle 10g, il fallait IMPÉRATIVEMENT refaire une sauvegarde COMPLÈTE de la base de données quand on faisait un RESETLOGS. Pourquoi : cela rendait invalide les backup RMAN de l'incarnation précédente de la abse car faire un RESETLOGS crée une nouvelle incarnation de votre base. On voit que sous RMAN ils sont accessibles mais peut-on vraiment les utiliser? Je vous laisse faire le test :-)
RMAN> list backup;
using target database control file instead of recovery catalog

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
7 Full 1.33G DISK 00:00:37 14-june -2020 02:45:26 pm
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20200614T144448
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T144448_h gdw119x_.bkp
List of Datafiles in backup set 7
Container ID: 3, PDB Name: ORCL

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ----------------------------- ----------- ------ ----
9 Full 2270760 14-june -2020 02:44:49 pm NO /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 Full 2270760 14-june -2020 02:44:49 pm NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 Full 2270760 14-june -2020 02:44:49 pm NO /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 Full 2270760 14-june -2020 02:44:49 pm NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 Full 2270760 14-june -2020 02:44:49 pm NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 Full 2270760 14-june -2020 02:44:49 pm NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
8 Full 1.01G DISK 00:00:32 14-june -2020 02:46:16 pm
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20200614T144448
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T144448_hgdw2s5l_.bkp
List of Datafiles in backup set 8

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ----------------------------- ----------- ------ ----
1 Full 2270811 14-june -2020 02:45:44 pm NO /u01/app/oracle/oradata/orcl12c/system01.dbf
3 Full 2270811 14-june -2020 02:45:44 pm NO /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
7 Full 2270811 14-june -2020 02:45:44 pm NO /u01/app/oracle/oradata/orcl12c/users01.dbf
15 Full 2270811 14-june -2020 02:45:44 pm NO /u01/app/oracle/oradata/orcl12c/undotbs2.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
9 Full 512.80M DISK 00:00:06 14-june -2020 02:46:35 pm
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20200614T144448
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T144448_h gdw45k9_.bkp
List of Datafiles in backup set 9

Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ----------------------------- ----------- ------ ----
5 Full 1443131 02-march -2017 07:57:03 am NO /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
6 Full 1443131 02-march -2017 07:57:03 am NO /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
8 Full 1443131 02-march -2017 07:57:03 am NO /u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
10 Full 17.91M DISK 00:00:01 14-june -2020 02:46:45 pm
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20200614T144448
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_06_14/o1_mf_ncnnf_TAG20200614T144448_hgdw4oqy_.bkp
Control File Included: Ckp SCN: 2270842 Ckp time: 14-june -2020 02:46:44 pm

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
11 Full 17.94M DISK 00:00:01 14-june -2020 02:46:47 pm
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20200614T144646
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_06_14/o1_mf_s_1043074006_hgdw4q3g_.bkp
SPFILE Included: Modification time: 14-june -2020 02:44:38 pm
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 2270848 Ckp time: 14-june -2020 02:46:46 pm

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
12 Full 17.94M DISK 00:00:00 14-june -2020 03:19:52 pm

BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20200614T151952
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_06_14/o1_mf_s_1043075992_hgdy2r72_.bkp
SPFILE Included: Modification time: 14-june -2020 03:19:50 pm
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 2272139 Ckp time: 14-june -2020 03:19:52 pm

Validate d'un backupset
Si je fais un VALIDATE sur le backupset 7 : pas de message d'erreur donc a priori on pourrait l'utiliser. Pour rappel, un backupset est une structure logique qui renferme de 1 à N backup pieces, c'est à dire des fichiers créés par RMAN.
RMAN> validate backupset 7;
Starting validate at 14-june -2020 03:31:34 pm
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T144448_h gdw119x_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_06_14/o1_mf_nnndf_TAG20200614T1 44448_hgdw119x_.bkp tag=TAG20200614T144448
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
Finished validate at 14-june -2020 03:31:50 pm

Validate d'un restore
On peut aussi valider un restore : là encore, pas d'erreur. La commande restore ... validate ne va rien restaurer, c'est une sorte de preview.
RMAN> restore controlfile validate;
Starting restore at 14-june -2020 03:35:32 pm
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_06_14/o1_mf_s_1043075992_hgdy2r72_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_06_14/o1_mf_s_1043075992_hgdy2r72_.bkp tag=TAG20200614T151952
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 14-june -2020 03:35:33 pm

Conclusion

L'advisor RMAN est un outil très puissant, qui analyse les erreurs critiques de votre base et propose en plus, si c'est possible, un script de commandes RMAN pour les corriger... N'hésitez pas à l'utiliser, il n'y a pas de licence spéciale :-)

En outre nous avons vu que perdre TOUS les controls files d'une base n'était pas si terrible que cela; du moins si on a des backups RMAN.