RMAN - Validating Archivelog Backup
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
List Backups for Archivelog All : sequence 53 - 63 / scn 1010308 - 1048901RMAN> 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>
Validate using archivelog all does not work since RMAN does not recognize deleted backups.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 arbitrary date and time does not meet the requirements.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 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 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>
View v$backup_archivelog_summary will provide the information needed.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>
Run validate_archivelog.shARROW:(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>
Script 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.
#!/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
Script 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.
#!/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
