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:([email protected]):PRIMARY> select min_first_change#,max_next_change# from v$backup_archivelog_summary; MIN_FIRST_CHANGE# MAX_NEXT_CHANGE# ----------------- ---------------- 1013038 1048901 ARROW:([email protected]):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
6 Comments. Leave new
could you do the same thing
but avoid the <<END syntax
put the SQL commands in a SQL text file on the filesystem
like rmancmds1sql
and a 2nd one
and leave on shell script commands in the script?
Hello oracleman,
Thanks to your comment, I have made an improvement to the script.
Hi Michael,
I think restore validate archivelog … is just test if archivelogs can be restored. It might be not connected to restoration of database backup but rather just test of archived logs defined in clause.
I would use “restore database until … validate preview” to see that backupsets and archive logs would be tested to restore database to some point in time.
Thanks,
Andrey
Wow…
RESTORE DATABASE … VALIDATE PREVIEW;
is quite ingenious. Unfortunately, even after carefully trying to interpret the documentation (I used the one for 11gR2) I cannot find the actual results being described there. It looks as if RMAN pulls the needed backups, including those for archivelogs, from tape without actually restoring them to disk, but for me this looks like undocumented behaviour.
Unfortunately, this command does NOT pull needed INC LEVEL 1 backups from tape. They are listed in the summary, but RMAN then proceeds to only pull the LEVEL 0 backup and the archivelog-backups AFTER the LEVEL 1 backup.
i need archive log mode using rman backup
Excellent Article….