Restore e Recovery Control file

Oracle 11g
Supponiamo di usare la FRA e che il control file sia perso.
Un control file non essere recuperato con il database open perché i control file sono bloccati dalle istanze, quindi occorre chiudere il db e aprirlo in modalità nomount. Dopo il restore dei control file il db deve essere aperto in modalità RESETLOGS a causa dei cambiamenti del control file.


RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area    1536602112 bytes

Fixed Size                     2213616 bytes
Variable Size               1056966928 bytes
Database Buffers             469762048 bytes
Redo Buffers                   7659520 bytes


RMAN> restore controlfile from autobackup;

Starting restore at 15-JAN-19
using channel ORA_DISK_1
recovery area destination: /u01/app/oracle/fast_recovery_area/TEST1
database name (or database unique name) used for search: TEST1
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/15/2019 11:36:33
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece


Se si conosce il valore di DBID eseguire il comando
RMAN > set DBID 4247218851;
executing command: SET DBID


RMAN>  restore controlfile  from autobackup;
Starting restore at 15-JAN-19
using channel ORA_DISK_1
recovery area destination: /u01/app/oracle/fast_recovery_area/TEST1
database name (or database unique name) used for search: TEST1
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190115
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190114
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190113
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190112
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190111
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190110
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190109
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/15/2019 12:22:51
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece


Verifichiamo quale è l'ultimo backup del control file.

RMAN> list backup of controlfile;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22      Full    9.48M      DISK        00:00:00     15-JAN-19
        BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20190115T125924
        Piece Name: /u01/app/oracle/fast_recovery_area/S11TEST1/S11TEST1/backupset/2019_01_15/o1_mf_ncnnf_TAG20190115T125924_g3vlrxc1_.bkp
  Control File Included: Ckp SCN: 1303271      Ckp time: 15-JAN-


RMAN>  restore controlfile from  '/u01/app/oracle/fast_recovery_area/S11TEST1/S11TEST1/backupset/2019_01_15/o1_mf_ncnnf_TAG20190115T125924_g3vlrxc1_.bkp';

Starting restore at 15-JAN-19
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/S11TEST1/S11TEST1/control/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/S11TEST1/S11TEST1/control/control02.ctl
Finished restore at 15-JAN-19

RMAN> alter database mount;
database mounted


RMAN> recover database;
Starting recover at 15-JAN-19
Starting implicit crosscheck backup at 15-JAN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
Crosschecked 9 objects
Finished implicit crosscheck backup at 15-JAN-19
Starting implicit crosscheck copy at 15-JAN-19
using channel ORA_DISK_1
Crosschecked 7 objects
Finished implicit crosscheck copy at 15-JAN-19
searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/TEST1/TEST1/datafile/o1_mf_ts_pept__g3vcc4r3_.dbf
File Name: /u01/app/oracle/fast_recovery_area/TEST1/TEST1/datafile/o1_mf_ts_pept__g3vcc5vh_.dbf
File Name: /u01/app/oracle/fast_recovery_area/TEST1/TEST1/datafile/o1_mf_users_g3vcc3ns_.dbf
File Name: /u01/app/oracle/fast_recovery_area/TEST1/TEST1/controlfile/o1_mf_TAG20190115T105148_g3vcc2jy_.ctl
File Name: /u01/app/oracle/fast_recovery_area/TEST1/TEST1/backupset/2019_01_15/o1_mf_nnsnf_TAG20190115T105148_g3vcc6yw_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 121 is already on disk as file /u01/app/oracle/oradata/TEST1/TEST1/redo/redo01.log
archived log for thread 1 with sequence 122 is already on disk as file /u01/app/oracle/oradata/TEST1/TEST1/redo/redo02.log
archived log file name=/u01/app/oracle/oradata/TEST1/TEST1/redo/redo01.log thread=1 sequence=121
archived log file name=/u01/app/oracle/oradata/TEST1/TEST1/redo/redo02.log thread=1 sequence=122
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-JAN-19

RMAN> alter database open resetlogs;

database opened