Table of Contents
Configuration
- FRA in use
- FRA is storage for ONLINE REDO
- No RMAN catalog
Validate your backup with RESTORE DATABASE PRESERVE
- Simple command to test database files and related archive logs
Switch all RAC ONLINE REDO logs and archive them SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; System altered. SQL> !date Sun Jul 13 10:48:27 CEST 2014 Try to restore/recover database to 1 minute before RMAN> run { set until time "to_date('2014-13-07:10:47:00','yyyy-dd-mm:hh24:mi:ss')"; restore database preview; } executing command: SET until clause Starting restore at 13-JUL-14 using channel ORA_DISK_1 List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 64 Incr 0 686.25M DISK 00:07:02 13-JUL-14 BP Key: 64 Status: AVAILABLE Compressed: NO Tag: TAG20140713T091718 Piece Name: +FRA2/grac4/backupset/2014_07_13/nnndn0_tag20140713t091718_0.332.852801449 List of Datafiles in backup set 64 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 0 Incr 43106520 13-JUL-14 +DATA/grac4/datafile/system.256.826111425 6 0 Incr 43106520 13-JUL-14 +DATA/grac4/datafile/undotbs3.268.826220083 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 65 Incr 0 3.08G DISK 00:22:14 13-JUL-14 BP Key: 65 Status: AVAILABLE Compressed: NO Tag: TAG20140713T091718 Piece Name: +FRA2/grac4/backupset/2014_07_13/nnndn0_tag20140713t091718_0.334.852801443 List of Datafiles in backup set 65 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 2 0 Incr 43106516 13-JUL-14 +DATA/grac4/datafile/sysaux.257.826111425 3 0 Incr 43106516 13-JUL-14 +DATA/grac4/datafile/undotbs1.258.826111425 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 66 Incr 0 4.53G DISK 00:24:59 13-JUL-14 BP Key: 66 Status: AVAILABLE Compressed: NO Tag: TAG20140713T091718 Piece Name: +FRA2/grac4/backupset/2014_07_13/nnndn0_tag20140713t091718_0.348.852801439 List of Datafiles in backup set 66 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 4 0 Incr 43106514 13-JUL-14 +DATA/grac4/datafile/users.259.826111427 5 0 Incr 43106514 13-JUL-14 +DATA/grac4/datafile/undotbs2.264.826111755 List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 67 671.00K DISK 00:00:01 13-JUL-14 BP Key: 67 Status: AVAILABLE Compressed: NO Tag: TAG20140713T094233 Piece Name: +FRA2/grac4/backupset/2014_07_13/annnf0_tag20140713t094233_0.304.852802955 List of Archived Logs in backup set 67 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 2 23 43106179 13-JUL-14 43108607 13-JUL-14 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 68 412.00K DISK 00:00:01 13-JUL-14 BP Key: 68 Status: AVAILABLE Compressed: NO Tag: TAG20140713T094233 Piece Name: +FRA2/grac4/backupset/2014_07_13/annnf0_tag20140713t094233_0.296.852802955 List of Archived Logs in backup set 68 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 53 43106182 13-JUL-14 43108603 13-JUL-14 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 69 206.50K DISK 00:00:02 13-JUL-14 BP Key: 69 Status: AVAILABLE Compressed: NO Tag: TAG20140713T094233 Piece Name: +FRA2/grac4/backupset/2014_07_13/annnf0_tag20140713t094233_0.289.852802955 List of Archived Logs in backup set 69 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 3 25 43106185 13-JUL-14 43108600 13-JUL-14 List of Archived Log Copies for database with db_unique_name GRAC4 ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - --------- 963 1 54 A 13-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_13/thread_1_seq_54.301.852803207 966 1 55 A 13-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_13/thread_1_seq_55.259.852803279 970 1 56 A 13-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_13/thread_1_seq_56.329.852806897 964 2 24 A 13-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_13/thread_2_seq_24.307.852803213 967 2 25 A 13-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_13/thread_2_seq_25.347.852803285 969 2 26 A 13-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_13/thread_2_seq_26.336.852806895 965 3 26 A 13-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_13/thread_3_seq_26.282.852803213 968 3 27 A 13-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_13/thread_3_seq_27.337.852803285 971 3 28 A 13-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_13/thread_3_seq_28.324.852806897 Media recovery start SCN is 43106514 Recovery must be done beyond SCN 43107799 to clear datafile fuzziness Finished restore at 13-JUL-14
Cleanup expired RMAN backups and archivlog
RMAN> crosscheck archivelog all; RMAN> delete noprompt expired archivelog all; RMAN> list archivelog all; List of Archived Log Copies for database with db_unique_name GRAC4 ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - --------- ... 879 2 2 A 10-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_10/thread_2_seq_2.266.852576547 876 3 4 A 10-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_10/thread_3_seq_4.263.852576359 880 3 5 A 10-JUL-14 Name: +FRA2/grac4/archivelog/2014_07_10/thread_3_seq_5.267.852576547 RMAN> crosscheck backup; RMAN> delete expired backup; RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 43 Full 18.86M DISK 00:00:04 10-JUL-14 BP Key: 43 Status: AVAILABLE Compressed: NO Tag: TAG20140710T182005 Piece Name: +FRA2/grac4/autobackup/2014_07_10/s_852574805.262.852574807 SPFILE Included: Modification time: 10-JUL-14 SPFILE db_unique_name: GRAC4 Control File Included: Ckp SCN: 42170803 Ckp time: 10-JUL-14
Backup RAC database and monitor progress
Shell script #!/bin/bash logloc=`date '+rman_backup_lvl0_grac4_%d%m%y%H%M%S'`.log $ORACLE_HOME/bin/rman target=/ log=$logloc << EOF run { allocate channel ch1 type Disk connect sys/sys@grac41; allocate channel ch2 type Disk connect sys/sys@grac42; allocate channel ch3 type Disk connect sys/sys@grac43; backup incremental level=0 database plus archivelog delete input; release channel ch1; release channel ch2; release channel ch3; } delete noprompt obsolete; EOF cat $logloc [oracle@grac41 RMAN]$ ./do_backup.sh Monitor progress SQL> SELECT inst_id, SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM GV$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK order by inst_id; INST_ID SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 54 179 1 115838 639360 18.12 2 62 25 1 117886 475520 24.79 3 61 13 1 136446 167040 81.68 SQL> / INST_ID SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 54 179 1 187646 639360 29.35 2 62 25 1 189822 475520 39.92
Display Backup details
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 F A DISK 10-JUL-14 1 1 NO TAG20140710T182005 44 B A A DISK 10-JUL-14 1 1 NO TAG20140710T190511 45 B A A DISK 10-JUL-14 1 1 NO TAG20140710T190511 46 B A A DISK 10-JUL-14 1 1 NO TAG20140710T190511 47 B A A DISK 10-JUL-14 1 1 NO TAG20140710T190511 48 B 0 A DISK 10-JUL-14 1 1 NO TAG20140710T190521 49 B 0 A DISK 10-JUL-14 1 1 NO TAG20140710T190521 50 B 0 A DISK 10-JUL-14 1 1 NO TAG20140710T190521 51 B A A DISK 10-JUL-14 1 1 NO TAG20140710T192216 52 B A A DISK 10-JUL-14 1 1 NO TAG20140710T192216 53 B A A DISK 10-JUL-14 1 1 NO TAG20140710T192216 54 B F A DISK 10-JUL-14 1 1 NO TAG20140710T192220 RMAN> list backup of database tag TAG20140713T091718; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 64 Incr 0 686.25M DISK 00:07:02 13-JUL-14 BP Key: 64 Status: AVAILABLE Compressed: NO Tag: TAG20140713T091718 Piece Name: +FRA2/grac4/backupset/2014_07_13/nnndn0_tag20140713t091718_0.332.852801449 List of Datafiles in backup set 64 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 0 Incr 43106520 13-JUL-14 +DATA/grac4/datafile/system.256.826111425 6 0 Incr 43106520 13-JUL-14 +DATA/grac4/datafile/undotbs3.268.826220083 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 65 Incr 0 3.08G DISK 00:22:14 13-JUL-14 BP Key: 65 Status: AVAILABLE Compressed: NO Tag: TAG20140713T091718 Piece Name: +FRA2/grac4/backupset/2014_07_13/nnndn0_tag20140713t091718_0.334.852801443 List of Datafiles in backup set 65 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 2 0 Incr 43106516 13-JUL-14 +DATA/grac4/datafile/sysaux.257.826111425 3 0 Incr 43106516 13-JUL-14 +DATA/grac4/datafile/undotbs1.258.826111425 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 66 Incr 0 4.53G DISK 00:24:59 13-JUL-14 BP Key: 66 Status: AVAILABLE Compressed: NO Tag: TAG20140713T091718 Piece Name: +FRA2/grac4/backupset/2014_07_13/nnndn0_tag20140713t091718_0.348.852801439 List of Datafiles in backup set 66 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 4 0 Incr 43106514 13-JUL-14 +DATA/grac4/datafile/users.259.826111427 5 0 Incr 43106514 13-JUL-14 +DATA/grac4/datafile/undotbs2.264.826111755
Validate backup with RESTORE DATABASE VALIDATE and RESTORE ARCHIVELOG ALL VALIDATE
- ALTER SYSTEM ARCHIVE LOG CURRENT; ( save all current REDO logs for all instances – waits untill all REDOs are archived )
- RESTORE DATABASE VALIDATE;
- RESTORE ARCHIVELOG ALL VALIDATE;
Note RESTORE ARCHIVELOG ALL VALIDATE could be tricky depending on RENTITION period and deleted archive logs
RMAN> restore database validate; Starting restore at 10-JUL-14 using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of datafile backup set channel ORA_DISK_1: reading from backup piece +FRA2/grac4/backupset/2014_07_10/nnndn0_tag20140710t190521_0.269.852577543 channel ORA_DISK_1: piece handle=+FRA2/grac4/backupset/2014_07_10/nnndn0_tag20140710t190521_0.269.852577543 tag=TAG20140710T190521 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: validation complete, elapsed time: 00:00:26 channel ORA_DISK_1: starting validation of datafile backup set channel ORA_DISK_1: reading from backup piece +FRA2/grac4/backupset/2014_07_10/nnndn0_tag20140710t190521_0.271.852577541 channel ORA_DISK_1: piece handle=+FRA2/grac4/backupset/2014_07_10/nnndn0_tag20140710t190521_0.271.852577541 tag=TAG20140710T190521 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: validation complete, elapsed time: 00:01:25 channel ORA_DISK_1: starting validation of datafile backup set channel ORA_DISK_1: reading from backup piece +FRA2/grac4/backupset/2014_07_10/nnndn0_tag20140710t190521_0.270.852577541 channel ORA_DISK_1: piece handle=+FRA2/grac4/backupset/2014_07_10/nnndn0_tag20140710t190521_0.270.852577541 tag=TAG20140710T190521 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: validation complete, elapsed time: 00:02:15 Finished restore at 10-JUL-14 List backup details RMAN> list backup of database tag TAG20140710T190521; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 48 Incr 0 686.13M DISK 00:04:44 10-JUL-14 BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20140710T190521 Piece Name: +FRA2/grac4/backupset/2014_07_10/nnndn0_tag20140710t190521_0.269.852577543 List of Datafiles in backup set 48 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 0 Incr 42190036 10-JUL-14 +DATA/grac4/datafile/system.256.826111425 6 0 Incr 42190036 10-JUL-14 +DATA/grac4/datafile/undotbs3.268.826220083 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 49 Incr 0 3.02G DISK 00:13:29 10-JUL-14 BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20140710T190521 Piece Name: +FRA2/grac4/backupset/2014_07_10/nnndn0_tag20140710t190521_0.271.852577541 List of Datafiles in backup set 49 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 2 0 Incr 42190035 10-JUL-14 +DATA/grac4/datafile/sysaux.257.826111425 3 0 Incr 42190035 10-JUL-14 +DATA/grac4/datafile/undotbs1.258.826111425 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 50 Incr 0 4.53G DISK 00:16:48 10-JUL-14 BP Key: 50 Status: AVAILABLE Compressed: NO Tag: TAG20140710T190521 Piece Name: +FRA2/grac4/backupset/2014_07_10/nnndn0_tag20140710t190521_0.270.852577541 List of Datafiles in backup set 50 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 4 0 Incr 42190032 10-JUL-14 +DATA/grac4/datafile/users.259.826111427 5 0 Incr 42190032 10-JUL-14 +DATA/grac4/datafile/undotbs2.264.826111755 RMAN-06025 could indicate that we could have a problem Following commands may work: RMAN> restore archivelog from time='SYSDATE-1/24' validate; RMAN>restore archivelog from time='SYSDATE-2/24' validate; RMAN>restore archivelog from time='SYSDATE-3/24' validate; But the following may fail RMAN> restore archivelog from time='SYSDATE-6/24' validate; Starting restore at 13-JUL-14 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 07/13/2014 11:03:31 RMAN-06026: some targets not found - aborting restore RMAN-06025: no backup of archived log for thread 3 with sequence 23 and starting SCN of 42849074 found to restore RMAN-06025: no backup of archived log for thread 2 with sequence 21 and starting SCN of 42849089 found to restore --> The question here is do need this delete REDOs for restore and recover ? The command restore archive log doesn't not check the latest available backup If the above error really indicates that a archive REDO log was lost delete your backup and archive logs and backup your data again See : RMAN "RESTORE ARCHIVELOG ALL VALIDATE" fails with RMAN-06025: no backup of log (Doc ID 1391263.1)
Delete backups and archive logs without expire switch
Note you should not run these commands until - your backup becomes invalid - you are losing your FRA RMAN> delete backup; RMAN> delete archivelog all; Verify that all objects where sucessfully deleted RMAN> list backup summary; specification does not match any backup in the repository RMAN> list archivelog all; specification does not match any archived log in the repository
Reference
- RMAN “RESTORE ARCHIVELOG ALL VALIDATE” fails with RMAN-06025: no backup of log (Doc ID 1391263.1)