RMAN recovery using until SCN

Recovering the DB after the clone.


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: '/app/database/dev/data01/system01.dbf'
SQL> recover database using backup controlfile;
ORA-00279: change 461885697981 generated at 02/03/2017 15:39:21 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_113_934902581.dbf
ORA-00280: change 461885697981 for thread 1 is in sequence #113


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/app/database/dev/archive_logs/1_113_934902581.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/app/database/dev/archive_logs/1_113_934902581.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


Check whether we have the required archives in the backup.

RMAN> list backuppiece '/app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hlrrm59a_1_1_196149.rman';


List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
74      74      1   1   AVAILABLE   DISK        /app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hlrrm59a_1_1_196149.rman


RMAN> list backupset 74;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
74      2.95M      DISK        00:00:01     03-FEB-17
        BP Key: 74   Status: AVAILABLE  Compressed: YES  Tag: TAG20170203T201850
        Piece Name: /app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hlrrm59a_1_1_196149.rman

  List of Archived Logs in backup set 74
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    114     461885718790 03-FEB-17 461885734590 03-FEB-17


We don't have the file requried i.e. 113. Get it from the tapes and apply.
 
SQL> recover database using backup controlfile;
ORA-00279: change 461885697981 generated at 02/03/2017 15:39:21 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_113_934902581.dbf
ORA-00280: change 461885697981 for thread 1 is in sequence #113


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 461885718790 generated at 02/03/2017 17:00:22 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_114_934902581.dbf
ORA-00280: change 461885718790 for thread 1 is in sequence #114
ORA-00278: log file '/app/database/dev/archive_logs/1_113_934902581.dbf'
no longer needed for this recovery


ORA-00279: change 461885734590 generated at 02/03/2017 18:00:20 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_115_934902581.dbf
ORA-00280: change 461885734590 for thread 1 is in sequence #115
ORA-00278: log file '/app/database/dev/archive_logs/1_114_934902581.dbf'
no longer needed for this recovery


ORA-00279: change 461885734598 generated at 02/03/2017 18:00:20 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_116_934902581.dbf
ORA-00280: change 461885734598 for thread 1 is in sequence #116
ORA-00278: log file '/app/database/dev/archive_logs/1_115_934902581.dbf'
no longer needed for this recovery


ORA-00279: change 461885734606 generated at 02/03/2017 18:00:23 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_117_934902581.dbf
ORA-00280: change 461885734606 for thread 1 is in sequence #117
ORA-00278: log file '/app/database/dev/archive_logs/1_116_934902581.dbf'
no longer needed for this recovery


ORA-00279: change 461885734614 generated at 02/03/2017 18:00:23 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_118_934902581.dbf
ORA-00280: change 461885734614 for thread 1 is in sequence #118
ORA-00278: log file '/app/database/dev/archive_logs/1_117_934902581.dbf'
no longer needed for this recovery


ORA-00279: change 461885767294 generated at 02/03/2017 20:18:45 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_119_934902581.dbf
ORA-00280: change 461885767294 for thread 1 is in sequence #119
ORA-00278: log file '/app/database/dev/archive_logs/1_118_934902581.dbf'
no longer needed for this recovery


ORA-00279: change 461885767302 generated at 02/03/2017 20:18:45 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_120_934902581.dbf
ORA-00280: change 461885767302 for thread 1 is in sequence #120
ORA-00278: log file '/app/database/dev/archive_logs/1_119_934902581.dbf'
no longer needed for this recovery


ORA-00279: change 461885767310 generated at 02/03/2017 20:18:47 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_121_934902581.dbf
ORA-00280: change 461885767310 for thread 1 is in sequence #121
ORA-00278: log file '/app/database/dev/archive_logs/1_120_934902581.dbf'
no longer needed for this recovery


ORA-00279: change 461885767325 generated at 02/03/2017 20:18:48 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_122_934902581.dbf
ORA-00280: change 461885767325 for thread 1 is in sequence #122
ORA-00278: log file '/app/database/dev/archive_logs/1_121_934902581.dbf'
no longer needed for this recovery


ORA-00279: change 461885767339 generated at 02/03/2017 20:18:50 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_123_934902581.dbf
ORA-00280: change 461885767339 for thread 1 is in sequence #123
ORA-00278: log file '/app/database/dev/archive_logs/1_122_934902581.dbf'
no longer needed for this recovery


ORA-00308: cannot open archived log
'/app/database/dev/archive_logs/1_123_934902581.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3



SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/app/database/dev/data01/system01.dbf'

 
You still need archive log file with sequence 123, as we don't have it. We are going with incomplete recovery by using, until SCN. This SCN we can get from the last archive backup we have.

 
RMAN> list backuppiece '/app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hprrm59b_1_1_196153.rman';

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
73      73      1   1   AVAILABLE   DISK        /app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hprrm59b_1_1_196153.rman

RMAN> list backupset 73;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
73      2.50K      DISK        00:00:00     03-FEB-17
        BP Key: 73   Status: AVAILABLE  Compressed: YES  Tag: TAG20170203T201850
        Piece Name: /app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hprrm59b_1_1_196153.rman

  List of Archived Logs in backup set 73
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    121     461885767310 03-FEB-17 461885767325 03-FEB-17
  1    122     461885767325 03-FEB-17 461885767339 03-FEB-17


RMAN> recover database until scn=461885767339;


SQL> alter database open resetlogs;

Database altered.


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DEV      READ WRITE

SQL>