RMAN - Validating Archivelog Backup

11 min read
Mar 21, 2014 12:00:00 AM
There are many posts out there about validating backup. However, none seem to address some recent concerns a client of mine had. Currently, backup validation is performed once a week and the question asked, "How to validate all archivelog backup?" List Backups - [D}atafile and [A]rchivelog backup from Incremental Level 0/1
RMAN> list backup summary;
 
 using target database control file instead of recovery catalog
 
 List of Backups
 ===============
 Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
 ------- -- -- - ----------- -------------------- ------- ------- ---------- ---
 43 B A A DISK 20-MAR-2014 21:02:30 1 1 YES AINC0_THU20
 44 B 0 A DISK 20-MAR-2014 21:02:39 1 1 YES DINC0_THU20
 45 B 0 A DISK 20-MAR-2014 21:02:51 1 1 YES DINC0_THU20
 46 B 0 A DISK 20-MAR-2014 21:02:56 1 1 YES DINC0_THU20
 47 B 0 A DISK 20-MAR-2014 21:02:59 1 1 YES DINC0_THU20
 48 B 0 A DISK 20-MAR-2014 21:03:00 1 1 YES DINC0_THU20
 49 B 0 A DISK 20-MAR-2014 21:03:04 1 1 YES DINC0_THU20
 50 B A A DISK 20-MAR-2014 21:03:07 1 1 YES AINC0_THU20
 51 B F A DISK 20-MAR-2014 21:03:11 1 1 NO TAG20140320T210309
 60 B F A DISK 21-MAR-2014 07:02:53 1 1 NO TAG20140321T070249
 61 B A A DISK 21-MAR-2014 11:27:47 1 1 YES AINC1_FRI21
 62 B 1 A DISK 21-MAR-2014 11:27:54 1 1 YES DINC1_FRI21
 63 B 1 A DISK 21-MAR-2014 11:27:55 1 1 YES DINC1_FRI21
 64 B 1 A DISK 21-MAR-2014 11:27:59 1 1 YES DINC1_FRI21
 65 B 1 A DISK 21-MAR-2014 11:28:00 1 1 YES DINC1_FRI21
 66 B 1 A DISK 21-MAR-2014 11:28:01 1 1 YES DINC1_FRI21
 67 B 1 A DISK 21-MAR-2014 11:28:06 1 1 YES DINC1_FRI21
 68 B A A DISK 21-MAR-2014 11:28:08 1 1 YES AINC1_FRI21
 69 B F A DISK 21-MAR-2014 11:28:14 1 1 NO TAG20140321T112810
 
 RMAN>
List Backups for Archivelog All : sequence 53 - 63 / scn 1010308 - 1048901
RMAN> list backup of archivelog all;
 
 List of Backup Sets
 ===================
 
 BS Key Size Device Type Elapsed Time Completion Time
 ------- ---------- ----------- ------------ --------------------
 43 2.49M DISK 00:00:00 20-MAR-2014 21:02:30
  BP Key: 43 Status: AVAILABLE Compressed: YES Tag: AINC0_THU20
  Piece Name: /oradata/backup/DB01_1470673955_20140320_21p3m7b6_1_1.inc0
 
  List of Archived Logs in backup set 43
  Thrd Seq Low SCN Low Time Next SCN Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1 53 1013038 20-MAR-2014 16:31:34 1019638 20-MAR-2014 20:58:44
  1 54 1019638 20-MAR-2014 20:58:44 1019722 20-MAR-2014 20:59:53
  1 55 1019722 20-MAR-2014 20:59:53 1019884 20-MAR-2014 21:01:19
  1 56 1019884 20-MAR-2014 21:01:19 1019955 20-MAR-2014 21:02:29
 
 BS Key Size Device Type Elapsed Time Completion Time
 ------- ---------- ----------- ------------ --------------------
 50 10.00K DISK 00:00:00 20-MAR-2014 21:03:07
  BP Key: 50 Status: AVAILABLE Compressed: YES Tag: AINC0_THU20
  Piece Name: /oradata/backup/DB01_1470673955_20140320_28p3m7cb_1_1.inc0
 
  List of Archived Logs in backup set 50
  Thrd Seq Low SCN Low Time Next SCN Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1 57 1019955 20-MAR-2014 21:02:29 1019981 20-MAR-2014 21:03:05
 
 BS Key Size Device Type Elapsed Time Completion Time
 ------- ---------- ----------- ------------ --------------------
 61 3.27M DISK 00:00:01 21-MAR-2014 11:27:47
  BP Key: 61 Status: AVAILABLE Compressed: YES Tag: AINC1_FRI21
  Piece Name: /oradata/backup/DB01_1470673955_20140321_2jp3nq1i_1_1.inc1
 
  List of Archived Logs in backup set 61
  Thrd Seq Low SCN Low Time Next SCN Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1 58 1019981 20-MAR-2014 21:03:05 1020108 20-MAR-2014 21:04:05
  1 59 1020108 20-MAR-2014 21:04:05 1040311 21-MAR-2014 06:48:37
  1 60 1040311 21-MAR-2014 06:48:37 1041387 21-MAR-2014 07:01:35
  1 61 1041387 21-MAR-2014 07:01:35 1041425 21-MAR-2014 07:02:46
  1 62 1041425 21-MAR-2014 07:02:46 1048879 21-MAR-2014 11:27:44
 
 BS Key Size Device Type Elapsed Time Completion Time
 ------- ---------- ----------- ------------ --------------------
 68 2.00K DISK 00:00:00 21-MAR-2014 11:28:08
  BP Key: 68 Status: AVAILABLE Compressed: YES Tag: AINC1_FRI21
  Piece Name: /oradata/backup/DB01_1470673955_20140321_2qp3nq28_1_1.inc1
 
  List of Archived Logs in backup set 68
  Thrd Seq Low SCN Low Time Next SCN Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1 63 1048879 21-MAR-2014 11:27:44 1048901 21-MAR-2014 11:28:07
 
 RMAN>
Validate using archivelog all does not work since RMAN does not recognize deleted backups.
RMAN> restore validate archivelog all;
 
 Starting restore at 21-MAR-2014 09:45:09
 using channel ORA_DISK_1
 
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of restore command at 03/21/2014 09:45:09
 RMAN-06026: some targets not found - aborting restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 52 and starting SCN of 998131 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 51 and starting SCN of 998032 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 50 and starting SCN of 997986 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 49 and starting SCN of 997779 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 48 and starting SCN of 974617 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 47 and starting SCN of 974437 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 46 and starting SCN of 974348 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 45 and starting SCN of 973251 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 44 and starting SCN of 943517 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 43 and starting SCN of 942296 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 42 and starting SCN of 942262 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 41 and starting SCN of 941967 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 40 and starting SCN of 917029 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 39 and starting SCN of 916561 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 38 and starting SCN of 908363 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 37 and starting SCN of 907850 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 36 and starting SCN of 875257 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 35 and starting SCN of 837127 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 34 and starting SCN of 791810 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 33 and starting SCN of 749949 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 32 and starting SCN of 749893 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 31 and starting SCN of 749681 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 30 and starting SCN of 712625 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 29 and starting SCN of 672466 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 28 and starting SCN of 646365 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 27 and starting SCN of 616449 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 26 and starting SCN of 581487 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 25 and starting SCN of 540184 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 24 and starting SCN of 519475 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 23 and starting SCN of 494335 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 22 and starting SCN of 470043 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 21 and starting SCN of 432577 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 20 and starting SCN of 412641 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 19 and starting SCN of 353256 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 18 and starting SCN of 306546 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 17 and starting SCN of 306515 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 16 and starting SCN of 306404 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 15 and starting SCN of 306211 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 14 and starting SCN of 280433 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 13 and starting SCN of 253917 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 12 and starting SCN of 227620 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 11 and starting SCN of 227291 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 10 and starting SCN of 226756 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 9 and starting SCN of 226342 found to restore
Validate using arbitrary date and time does not meet the requirements.
RMAN> restore archivelog from time "TRUNC(sysdate)" until time "sysdate";
 
 Starting restore at 21-MAR-2014 09:46:32
 using channel ORA_DISK_1
 
 channel ORA_DISK_1: starting archived log restore to default destination
 channel ORA_DISK_1: restoring archived log
 archived log thread=1 sequence=59
 channel ORA_DISK_1: restoring archived log
 archived log thread=1 sequence=60
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140321_2ap3naeg_1_1.inc1
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140321_2ap3naeg_1_1.inc1 tag=AINC1_FRI21
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
 channel ORA_DISK_1: starting archived log restore to default destination
 channel ORA_DISK_1: restoring archived log
 archived log thread=1 sequence=61
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140321_2hp3nagn_1_1.inc1
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140321_2hp3nagn_1_1.inc1 tag=AINC1_FRI21
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
 Finished restore at 21-MAR-2014 09:46:37
 
 RMAN> restore archivelog from time "TRUNC(sysdate-1)" until time "TRUNC(sysdate)";
 
 Starting restore at 21-MAR-2014 09:51:21
 using channel ORA_DISK_1
 
 archived log for thread 1 with sequence 59 is already on disk as file /oradata/fra/DB01/archivelog/2014_03_21/o1_mf_1_59_9lrv79h1_.arc
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of restore command at 03/21/2014 09:51:21
 RMAN-06026: some targets not found - aborting restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 52 and starting SCN of 998131 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 51 and starting SCN of 998032 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 50 and starting SCN of 997986 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 49 and starting SCN of 997779 found to restore
 RMAN-06025: no backup of archived log for thread 1 with sequence 48 and starting SCN of 974617 found to restore
 
 RMAN>
Validate using from scn 1013038 until scn 1048901 works, but where is the information stored so that it can be automated in a shell script?
RMAN> restore validate archivelog from scn 1013038 until scn 1048901;
 
 Starting restore at 21-MAR-2014 11:34:02
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=107 device type=DISK
 
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140320_21p3m7b6_1_1.inc0
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140320_21p3m7b6_1_1.inc0 tag=AINC0_THU20
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140320_28p3m7cb_1_1.inc0
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140320_28p3m7cb_1_1.inc0 tag=AINC0_THU20
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140321_2jp3nq1i_1_1.inc1
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140321_2jp3nq1i_1_1.inc1 tag=AINC1_FRI21
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140321_2qp3nq28_1_1.inc1
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140321_2qp3nq28_1_1.inc1 tag=AINC1_FRI21
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 Finished restore at 21-MAR-2014 11:34:06
 
 RMAN>
View v$backup_archivelog_summary will provide the information needed.
ARROW:(SYS@db01):PRIMARY> select min_first_change#,max_next_change# from v$backup_archivelog_summary;
 
 MIN_FIRST_CHANGE# MAX_NEXT_CHANGE#
 ----------------- ----------------
  1013038 1048901
 
 ARROW:(SYS@db01):PRIMARY>
Run validate_archivelog.sh
$ ./validate_archivelog.sh
 
 Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 21 11:41:44 2014
 
 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
 
 RMAN>
 echo set on
 
 RMAN> connect target;
 connected to target database: DB01 (DBID=1470673955)
 
 RMAN> restore validate archivelog from scn 1013038 until scn 1048901;
 Starting restore at 21-MAR-2014 11:41:45
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=107 device type=DISK
 
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140320_21p3m7b6_1_1.inc0
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140320_21p3m7b6_1_1.inc0 tag=AINC0_THU20
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140320_28p3m7cb_1_1.inc0
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140320_28p3m7cb_1_1.inc0 tag=AINC0_THU20
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140321_2jp3nq1i_1_1.inc1
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140321_2jp3nq1i_1_1.inc1 tag=AINC1_FRI21
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140321_2qp3nq28_1_1.inc1
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140321_2qp3nq28_1_1.inc1 tag=AINC1_FRI21
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 Finished restore at 21-MAR-2014 11:41:49
 
 RMAN> exit
 
 Recovery Manager complete.
Script validate_archivelog.sh #!/bin/sh NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS" min_scn=`sqlplus -SL "/ as sysdba" <<END whenever sqlerror exit sql.sqlcode whenever oserror exit 1 set pages 0 head off veri off feed off term off echo off pause off numw 32 select min_first_change# from v\\$backup_archivelog_summary; exit END ` if [ "$?" != "0" ]; then echo "*** ERROR: $min_scn" exit 1; fi max_scn=$(sqlplus -SL "/ as sysdba" <<END whenever sqlerror exit sql.sqlcode whenever oserror exit 1 set pages 0 head off veri off feed off term off echo off pause off numw 32 select max_next_change# from v\$backup_archivelog_summary; exit END ) if [ "$?" != "0" ]; then echo "*** ERROR: $max_scn" exit 1; fi rman <<END set echo on connect target; restore validate archivelog from scn ${min_scn} until scn ${max_scn}; exit END if [ "$?" != "0" ]; then echo "*** ERROR: RMAN restore validate" exit 1; fi exit UPDATE: Based on recent comment, improve script to reduce call to database. Run validate_archivelog2.sh
$ ./validate_archivelog2.sh
 
 Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 21 13:40:35 2014
 
 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
 
 RMAN>
 echo set on
 
 RMAN> connect target;
 connected to target database: DB01 (DBID=1470673955)
 
 RMAN> restore validate archivelog from scn 1013038 until scn 1048901;
 Starting restore at 21-MAR-2014 13:40:36
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=15 device type=DISK
 
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140320_21p3m7b6_1_1.inc0
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140320_21p3m7b6_1_1.inc0 tag=AINC0_THU20
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140320_28p3m7cb_1_1.inc0
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140320_28p3m7cb_1_1.inc0 tag=AINC0_THU20
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140321_2jp3nq1i_1_1.inc1
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140321_2jp3nq1i_1_1.inc1 tag=AINC1_FRI21
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 channel ORA_DISK_1: starting validation of archived log backup set
 channel ORA_DISK_1: reading from backup piece /oradata/backup/DB01_1470673955_20140321_2qp3nq28_1_1.inc1
 channel ORA_DISK_1: piece handle=/oradata/backup/DB01_1470673955_20140321_2qp3nq28_1_1.inc1 tag=AINC1_FRI21
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 Finished restore at 21-MAR-2014 13:40:40
 
 RMAN> exit
 
 Recovery Manager complete.

Script validate_archivelog2.sh #!/bin/sh NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS" cmd=`sqlplus -SL "/ as sysdba" <<END whenever sqlerror exit sql.sqlcode whenever oserror exit 1 set pages 0 head off veri off feed off term off echo off pause off numw 32 select 'restore validate archivelog from scn '||min_first_change#||' until scn '||max_next_change# from v\\$backup_archivelog_summary; exit END ` if [ "$?" != "0" ]; then echo "*** ERROR: $cmd" exit 1; fi rman <<END set echo on connect target; ${cmd}; exit END if [ "$?" != "0" ]; then echo "*** ERROR: RMAN restore validate" exit 1; fi exit

Ready to optimize your Oracle Database for the future?

 

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.