Oracle RMAN Backup and incomplete recovery (delete archievelog)

Rman Backup command

Backup Database format= '/home/oracle/backup/bak_full_%u_%t ' tag= ' bak_full '; SQL ' alter system archive log current '; Backup Archivelog all tag= ' Arc_bak ' format= '/home/oracle/backup/arch_%u_%t ' delete input; Backup current Controlfile tag= ' bak_ctlfile ' format= '/home/oracle/backup/bak_ctl_%u_%t '; Backup SPFile tag= ' spfile ' format= '/home/oracle/backup/bak_spfile_%u_%t ';

Insert test data after backup

INSERT INTO ZZ values (2); Commit

INSERT INTO QQ values (2); Commit

Then cut out multiple archive files by using ALTER system switch logfile multiple times.

O1_mf_1_12_cpb2xhg8_.arc O1_mf_1_14_cpb36dbo_.arc
O1_mf_1_13_cpb35zgs_.arc O1_mf_1_15_cpb37ff2_.arc

Delete 12 and 14 files

[Email protected] 2016_06_18]$ RM O1_mf_1_12_cpb2xhg8_.arc
[Email protected] 2016_06_18]$ RM O1_mf_1_14_cpb36dbo_.arc

Error is caused by running processing in Rman

Run {

SQL ' ALTER DATABASE datafile 6 offline ';

Restore DataFile 6;

Recover DataFile 6;

SQL ' ALTER DATABASE datafile 6 online ';

}

Error is

rman> recover datafile 6; Starting recover at 18-jun-16 Using channel Ora_disk_1 Starting Media recovery Archived log for thread 1 with sequence, already on disk as file/u01/app/oracle/flash_recovery_area/orcl/archivelog/ 2016_06_18/o1_mf_1_13_cpb35zgs_.arc Archived log for thread 1 with sequence are already on disk as file/u01/app/oracle/flash_recovery_area/orcl/archivelog/ 2016_06_18/o1_mf_1_15_cpb37ff2_.arc RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE, STACK follows =============== RMAN-00571: =========================================================== Rman-03002:failure of recover command at 06/18/2016 16:59:59 Rman-06053:unable to perform media recovery because of missing log Rman-06025:no Backup of archived log for thread 1 with sequence + starting SCN of 1105737 found to restore Rman-06025:no Backup of archived log for thread 1 with sequence and starting SCN of 1080714 found to restore

View the Archieve directory and discover that there is really only

O1_mf_1_13_cpb35zgs_.arc O1_mf_1_15_cpb37ff2_.arc

View V$log to get the last log file to be applied should be 16

Sql> select Group#,sequence#,status from V$log;

group# sequence# STATUS
---------- ---------- ----------------
1 Current
2 INACTIVE
3 INACTIVE

The SCN for the restored No. 6th file is

Sql> select file#,change# from V$recover_file;

file# change#
---------- ----------
6 1080514

See V$log_history to start recovering from file 10th

Sql> Select sequence# from V$log_history where 1080514 between first_change# and next_change#-1;

sequence#
----------
10

Rman> list Backup of Archivelog all;

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

BS Key Size Device Type Elapsed Time Completion time
------- ---------- ----------- ------------ ---------------
3 61.04M DISK 00:00:03 16-jun-16
BP key:3 status:available Compressed:no tag:tag20160616t165323
Piece Name:/u01/app/oracle/flash_recovery_area/orcl/backupset/2016_06_16/o1_mf_annnn_tag20160616t165323_ Cp4t83nk_.bkp

List of archived Logs in backup set 3
THRD Seq Low SCN low time next SCN next time
---- ------- ---------- --------- ---------- ---------
1 6 1022418 08-jun-16 1033516 16-jun-16
1 7 1033516 16-jun-16 1047305 16-jun-16

BS Key Size Device Type Elapsed Time Completion time
------- ---------- ----------- ------------ ---------------
4 61.05M DISK 00:00:02 16-jun-16
BP key:4 status:available Compressed:no tag:tag20160616t165445
Piece Name:/u01/app/oracle/flash_recovery_area/orcl/backupset/2016_06_16/o1_mf_annnn_tag20160616t165445_ Cp4tbogp_.bkp

List of archived Logs in backup set 4
THRD Seq Low SCN low time next SCN next time
---- ------- ---------- --------- ---------- ---------
1 6 1022418 08-jun-16 1033516 16-jun-16
1 7 1033516 16-jun-16 1047305 16-jun-16
1 8 1047305 16-jun-16 1047358 16-jun-16

BS Key Size Device Type Elapsed Time Completion time
------- ---------- ----------- ------------ ---------------
7 61.02M DISK 00:00:03 18-jun-16
BP key:7 status:available Compressed:no tag:arc_back
Piece Name:/home/oracle/backup/arch_07r8fcti_1_1_20160618

List of archived Logs in backup set 7
THRD Seq Low SCN low time next SCN next time
---- ------- ---------- --------- ---------- ---------
1 9 1047358 16-jun-16 1071119 16-jun-16
1 1071119 16-jun-16 1080684 18-jun-16
1 1080684 18-jun-16 1080714 18-jun-16

Backup level there is 6,7,8,9,10,11,6 file test01.dbf need to start from 10th Archivelog, but need to recover to 16th file, currently missing 12, 14 files.

Check the location of the 12-14 file for confirmation.

rman> list Archivelog low sequence-sequence 14

2>;

List of archived Log Copies for database with Db_unique_name ORCL
=====================================================================

Key thrd Seq S Low Time
------- ---- ------- - ---------
7 1 A 18-jun-16
Name:/u01/app/oracle/flash_recovery_area/orcl/archivelog/2016_06_18/o1_mf_1_12_cpb2xhg8_.arc

8 1 A 18-jun-16
Name:/u01/app/oracle/flash_recovery_area/orcl/archivelog/2016_06_18/o1_mf_1_13_cpb35zgs_.arc

9 1 A 18-jun-16
Name:/u01/app/oracle/flash_recovery_area/orcl/archivelog/2016_06_18/o1_mf_1_14_cpb36dbo_.arc

Can only be not fully restored to 12

Shutdown abort;

Starup force Mount;

Rman> Run { 2> set until sequence 12; 3> Restore Database; 4>} Executing command:set until clause Starting restore at 18-jun-16 Using channel Ora_disk_1 Flashing back control file to SCN 1080714 Channel ora_disk_1:starting datafile backup set restore Channel ora_disk_1:specifying DataFile (s) to the restore from backup set Channel ora_disk_1:restoring datafile 00001 to/u01/app/oracle/oradata/orcl/system01.dbf Channel ora_disk_1:restoring datafile 00002 to/u01/app/oracle/oradata/orcl/sysaux01.dbf Channel ora_disk_1:restoring datafile 00003 to/u01/app/oracle/oradata/orcl/undotbs01.dbf Channel ora_disk_1:restoring datafile 00004 to/u01/app/oracle/oradata/orcl/users01.dbf Channel ora_disk_1:restoring datafile 00005 to/u01/app/oracle/oradata/orcl/example01.dbf Channel ora_disk_1:restoring datafile 00006 to/u01/app/oracle/oradata/orcl/test01.dbf Channel ora_disk_1:reading from backup piece/home/oracle/backup/back_full_05r8fcon_1_1_20160618 Channel Ora_disk_1:piece handle=/home/oracle/backup/back_full_05r8fcon_1_1_20160618 Tag=back_full Channel ora_disk_1:restored backup Piece 1 Channel Ora_disk_1:restore complete, elapsed time:00:00:35 Finished restore at 18-jun-16 Rman> Run { 2> set until sequence 12; 3> Recover database; 4>} Executing command:set until clause Starting recover at 18-jun-16 Using channel Ora_disk_1 Starting Media recovery Channel ora_disk_1:starting archived log restore to default destination Channel ora_disk_1:restoring archived Log Archived Log thread=1 sequence=10 Channel ora_disk_1:restoring archived Log Archived Log thread=1 sequence=11 Channel ora_disk_1:reading from backup piece/home/oracle/backup/arch_07r8fcti_1_1_20160618 Channel Ora_disk_1:piece handle=/home/oracle/backup/arch_07r8fcti_1_1_20160618 Tag=arc_back Channel ora_disk_1:restored backup Piece 1 Channel Ora_disk_1:restore complete, elapsed time:00:00:01 Archived log File Name=/u01/app/oracle/flash_recovery_area/orcl/archivelog/2016_06_18/o1_mf_1_10_cpb4clcf_.arc Thread=1 sequence=10 Channel default:deleting archived log (s) Archived log File Name=/u01/app/oracle/flash_recovery_area/orcl/archivelog/2016_06_18/o1_mf_1_10_cpb4clcf_.arc Recid=20 stamp=914865395 Archived log File Name=/u01/app/oracle/flash_recovery_area/orcl/archivelog/2016_06_18/o1_mf_1_11_cpb4clcl_.arc Thread=1 sequence=11 Channel default:deleting archived log (s) Archived log File Name=/u01/app/oracle/flash_recovery_area/orcl/archivelog/2016_06_18/o1_mf_1_11_cpb4clcl_.arc Recid=19 stamp=914865394 Media recovery complete, elapsed time:00:00:00 Finished recover at 18-jun-16 rman> ALTER DATABASE open resetlogs; Database opened

Log in with Scott/tiger and see that the inserted data is not there.

Oracle RMAN Backup and incomplete recovery (delete archievelog)