RMAN - Validating Archivelog Backup

by Michael Dinh on 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

No Comments Yet

Let us know what you think

Share this

RMAN - Validating Archivelog Backup

Share this

No Comments Yet

Subscribe by email