RMAN usage and commands

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)

Leave a Reply

Your email address will not be published. Required fields are marked *