RMAN Reporting Quick Reference

UNDER CONSTRUCTION

PRE-REQUISITES

  • This is a quick reference to useful RMAN Reporting commands and not an exhaustive list.
  • All example output has been shortened for readability.
  • To include hours & minutes in listings, at the UNIX prompt before starting the RMAN client, issue:
    • export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
  • The majority of commands are executed from within RMAN, denoted by RMAN>
  • Queries issued from Sql*Plus are denoted SQL>

LIST DATABASE INCARNATIONS

RMAN> list incarnation of database;

List of Database Incarnations
=============================
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ABCDBA01 2210720797       PARENT  1          14-JAN-09
2       2       ABCDBA01 2210720797       CURRENT 8178396    29-JAN-09

SUMMARISE AVAILABLE BACKUPS

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
5296    B  F  A SBT_TAPE    07-OCT-09       1       1       NO         TAG20091...
5299    B  F  A SBT_TAPE    07-OCT-09       1       1       NO         TAG20091...
5302    B  F  A SBT_TAPE    07-OCT-09       1       1       NO         TAG20091...

SUMMARISE AVAILABLE BACKUPS WITH TAG INFO

SQL> SELECT
        file#, 
        name,
        TO_CHAR( completion_time, 'DD-MON-YY' ) completed,
        tag, 
        status
     FROM
        v$datafile_copy 
     WHERE 
        deleted = 'NO'
     ORDER BY
        file#, 
        completion_time, 
        tag
      /

FILE# NAME                                 COMPLETED TAG                  S
----- ------------------------------------ --------- -------------------- -
    1 +REC10/ABCDBA01/datafile/system.7... 31-MAR-11 TAG20110331T161600   A
    1 +REC10/ABCDBA01/datafile/system.6... 01-APR-11 BACKUP_2011_13       A
    1 +REC10/ABCDBA01/datafile/system.7... 04-APR-11 BACKUP_2011_14       A
    1 +REC10/ABCDBA01/datafile/system.7... 07-APR-11 BACKUP_2011_14       A

LISTINGS BY DATE RANGE

RMAN> list copy of database completed between '07-APRIL-2011' and '09-APRIL-2011';

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
367     1    A 08-APR-11       1752281    08-APR-11       +REC10/.../.../...
364     1    A 07-APR-11       1643684    06-APR-11       +REC10/.../.../...
370     2    A 08-APR-11       1752317    08-APR-11       +REC10/.../.../...
365     2    A 07-APR-11       1643687    06-APR-11       +REC10/.../.../...

LIST IMAGE COPIES BY TAG

RMAN> list copy tag TAG20091107T102539;

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
405     1    A 26-Aug-11 12:31 3296993... 26-Aug-11 12:30 +REC10/.../.../...
420     2    A 26-Aug-11 12:54 3296993... 26-Aug-11 12:26 +REC10/.../.../...
395     3    A 26-Aug-11 12:29 3296993... 26-Aug-11 12:26 +REC10/.../.../...

LIST BACKUPSETS BY TAG

RMAN> list backup tag TAG20091107T102539; 

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6279    Incr 0  104.44G    SBT_TAPE    01:16:42     07-NOV-09
        BP Key: 6279   Status: AVAILABLE  Compressed: NO  Tag: TAG20091107T102539
        Handle: rman_ABCDBA01_071109_090000_d1ktocl4_1_1_702296740   Media:
  List of Datafiles in backup set 6279
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  44   0  Incr 3298849119182 07-NOV-09 +DATA10/.../.../...
  48   0  Incr 3298849119182 07-NOV-09 +DATA10/.../.../...

LIST DATAFILE COPIES (IMAGE COPIES)

RMAN> list copy of database;

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
372     1    A 08-APR-11       1755561    08-APR-11       +REC10/.../.../...
367     1    A 08-APR-11       1752281    08-APR-11       +REC10/.../.../...
375     2    A 08-APR-11       1755588    08-APR-11       +REC10/.../.../...
370     2    A 08-APR-11       1752317    08-APR-11       +REC10/.../.../...
373     3    A 08-APR-11       1755571    08-APR-11       +REC10/.../.../...

LIST BACKUPS OF A SPECFIC DATAFILE

RMAN> list backup of datafile 11;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5427    Incr 0  135.48G    SBT_TAPE    02:05:37     10-OCT-09
        BP Key: 5427   Status: AVAILABLE  Compressed: NO  Tag: TAG20091010T090152
        Handle: rman_ABCDBA01_101009_090000_i4kred81_1_1_699872513   Media: EW00CF
  List of Datafiles in backup set 5427
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11   0  Incr 3298806042002 10-OCT-09 +DATA10/.../.../...

LIST BACKUPS OF A SPECIFIC TABLESPACE

RMAN> list backup of tablespace USERS;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6124    Incr 0  101.60G    SBT_TAPE    01:23:50     31-OCT-09
        BP Key: 6124   Status: AVAILABLE  Compressed: NO  Tag: TAG20091031T101544
        Handle: rman_ABCDBA01_311009_090000_82kt6387_1_1_701697287   Media: EW106F
  List of Datafiles in backup set 6124
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5    0  Incr 3298838137256 31-OCT-09 +DATA10/.../.../...

LIST RECOVERY AREA BACKUPS (FRA BACKUPS)

RMAN> list backup of database device type sbt;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
48649   Incr 1  157.13M    SBT_TAPE    00:07:33     12-APR-14
        BP Key: 50617   Status: AVAILABLE  Compressed: NO  Tag: ABCDBA01_FRA_...
        Handle: rman_ABCDBA01_120414_163001_FRA_j4p5ib7q_1_2_...   Media: @aaabP
  List of Datafiles in backup set 48649
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 303281470  12-APR-14 +DATA10/.../.../...
  10   1  Incr 303281470  12-APR-14 +DATA10/.../.../...
  17   1  Incr 303281470  12-APR-14 +DATA10/.../.../...

LIST CONTROLFILE BACKUPS

RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14991   Full    26.50M     SBT_TAPE    00:01:54     23-OCT-09
        BP Key: 18366   Status: AVAILABLE  Compressed: NO  Tag: TAG20091023T170151
        Handle: c-2117661968-20091023-13   Media: EW00H8
  Control File Included: Ckp SCN: 3298999784292   Ckp time: 23-OCT-09

LIST SPFILE BACKUPS

RMAN> list backup of spfile;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15256   Full    26.50M     SBT_TAPE    00:01:57     25-OCT-09
        BP Key: 18631   Status: AVAILABLE  Compressed: NO  Tag: TAG20091025T190204
        Handle: c-2117661968-20091025-16   Media: EW002X
  SPFILE Included: Modification time: 21-OCT-09

LIST ALL ARCHIVE LOG BACKUPS

RMAN> list archivelog all;

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
24768   1    25130   A 07-NOV-09 +REC10/ABCDBA01/archivelog/2009_11_08/thread...
24769   1    25131   A 08-NOV-09 +REC10/ABCDBA01/archivelog/2009_11_08/thread...

LIST BACKUP OF A SPECIFIC ARCHIVE LOG

RMAN> list backup of archivelog sequence 64981 thread 3;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
30536   1.89G      SBT_TAPE    00:02:12     10-DEC-10
        BP Key: 31064   Status: AVAILABLE  Compressed: NO  Tag: TAG20101210T210120
        Handle: al_ABCDBA01_58799_1_737413283   Media: EW00HM

  List of Archived Logs in backup set 30536
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  3    64981   3333669778706 10-DEC-10 3333670361120 10-DEC-10

REPORT ON FILES NOT BACKED UP

RMAN> report need backup days=2;

Report of files whose recovery needs more than 2 days of archived logs
File Days  Name
---- ----- ----------------------------------------------
1    3     +DATA10/ABCDBA01/datafile/system....
2    3     +DATA10/ABCDBA01/datafile/undo....

REPORT ON UNRECOVERABLE DATAFILES

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
10   full or incremental     +DATA10/ABCDBA01/datafile/tsp1....
11   full or incremental     +DATA10/ABCDBA01/datafile/tsp1....
12   full or incremental     +DATA10/ABCDBA01/datafile/tsp1....
13   full or incremental     +DATA10/ABCDBA01/datafile/tsp1....
14   full or incremental     +DATA10/ABCDBA01/datafile/tsp1....

RMAN 10gR2 BACKUP & RECOVERY GUIDE

© copyright 2001-2014 ABCdba.com | all rights reserved