ORA-01113: file # needs media recovery

Oracle 12c

Supponiamo di voler aprire un pluggable database e termina in errore.

SQL> alter pluggable database S12AVA1 open;
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/u01/app/oracle/oradata/CDB12S01/s12ava1/system01.dbf'

SQL> recover datafile 9;
ORA-00279: change 3747608 generated at 12/17/2018 10:54:49 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive/CDB12S01/1_92_993571574.dbf
ORA-00280: change 3747608 for thread 1 is in sequence #92

Controllare se la sequence 92 e' presente nel Backup, o in locale o nelle directory dei redolog file.

Aprire un secondo terminale:
RMAN>  list backup of archivelog all;
starting full resync of recovery catalog
full resync complete
List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
98      105.00M    SBT_TAPE    00:00:05     28-NOV-18
        BP Key: 106   Status: AVAILABLE  Compressed: NO  Tag: TAG20181128T124704
        Handle: CDB12S01_03tjboa8_1_1   Media: avtar007

  List of Archived Logs in backup set 98
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2       1426225    28-NOV-18 1450355    28-NOV-18

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
99      256.00K    SBT_TAPE    00:00:00     28-NOV-18
        BP Key: 107   Status: AVAILABLE  Compressed: NO  Tag: TAG20181128T124704
        Handle: CDB12S01_04tjboaf_1_1   Media: avtar007
  List of Archived Logs in backup set 99
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    7       1450367    28-NOV-18 1450743    28-NOV-18
  ..........................................
.....................................

  List of Archived Logs in backup set 3553
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    119     3926778    18-DEC-18 3927039    18-DEC-18


La seq#92 e' presente negli ArchiveLog, quindi occorre effettuare il restore dal backup degli archive log.

RMAN>
run {
allocate channel c0 type sbt PARMS=     "SBT_LIBRARY=/usr/local/avamar/lib/libobk_avamar64.so" format '%d_%U';
send channel 'c0' ' "--ctlusessl=true" "--cacheprefix=CDB12S01_c0" "--sysdir=/usr/local/avamar/etc" "--bindir=/usr/local/avamar/bin" "--vardir=/usr/local/ava
mar/var/clientlogs" "--logfile=/u01/app/oracle/admin/scripts/rman/LOG/restore_tbs_c0.log" "--flagfile=/usr/local/avamar/lib/CDB12S01-flags.txt" ';
allocate channel c1 type sbt PARMS=     "SBT_LIBRARY=/usr/local/avamar/lib/libobk_avamar64.so" format '%d_%U';
......................
restore archivelog from sequence=92 until sequence=119;
}

Controllare se sotto il path sono presenti i file restorati.

cd /u01/app/oracle/archive/CDB12S01
-rw-r----- 1 oracle oinstall 70656 Dec 18 10:53 1_92_993571574.dbf

Ritornare al primo terminale sotto slqplus:

SQL> recover datafile 10;
ORA-00279: change 3747602 generated at 12/17/2018 10:54:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive/CDB12S01/1_92_993571574.dbf
ORA-00280: change 3747602 for thread 1 is in sequence #92
-- La seq #92 è stata recuperata precedentemente con RMAN

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO   
-- scrivere AUTO cosi in automatico cerca negli archive i log mancanti

ORA-00279: change 3747628 generated at 12/17/2018 10:55:13 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive/CDB12S01/1_93_993571574.dbf
ORA-00280: change 3747628 for thread 1 is in sequence #93
La seq #93 è stata recuperata precedentemente con RMAN

............
ORA-00279: change 3901933 generated at 12/18/2018 01:30:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive/CDB12S01/1_113_993571574.dbf
ORA-00280: change 3901933 for thread 1 is in sequence #113

Log applied.
Media recovery complete.

SQL> alter pluggable database s12ava1 open;
alter pluggable database s12ava1 open
*
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: '/u01/app/oracle/oradata/CDB12S01/s12ava1/users01.dbf'

SQL> recover datafile 11;
ORA-00279: change 3747615 generated at 12/17/2018 10:54:58 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive/CDB12S01/1_92_993571574.dbf
ORA-00280: change 3747615 for thread 1 is in sequence #92

-- La seq #92 è stata recuperata precedentemente con RMAN
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
-- scrivere AUTO cosi in automatico cerca negli archive i log mancanti
ORA-00279: change 3747628 generated at 12/17/2018 10:55:13 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive/CDB12S01/1_93_993571574.dbf
ORA-00280: change 3747628 for thread 1 is in sequence #93

-- La seq #93 è stata recuperata precedentemente con RMAN
................
Log applied.
Media recovery complete.

SQL> alter pluggable database s12ava1 open;

Pluggable database altered.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 S12AVA1                        READ WRITE NO
         4 S12AVA1_CLONE                  READ WRITE NO

Potrebbe succedere che una seq non sia presente negli "archive log" allora in questo caso occorre effettuare il recover passando il path dei redolog perche' li deve trovarsi la seq segnalata come mancante.

Se non c'è neanche lì, allora  non si puo' recuperare il file e quindi occorre effettuare il restore full.