RMAN Report Commands

RMAN provides some commands that help us get information about backupsets, those that have passed the retention policy, etc. Here we look at some of them.

List datafiles

To see list of all datafiles and tempfiles, use report schema

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DOSA

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               ***     /u2/dosa/system01.dbf
2    600      SYSAUX               ***     /u2/dosa/sysaux01.dbf
3    340      UNDOTBS1             ***     /u2/dosa/undotbs01.dbf
4    5        USERS                ***     /u2/dosa/users01.dbf
5    2355     SOE                  ***     /u2/dosa/soe.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    66       TEMP                 32767       /u2/dosa/temp01.dbf
Backupset Lists

To see all the backupsets and their backup pieces, use list backupset command

RMAN> list backupset;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1       1.03G      DISK        00:00:37     13-AUG-16
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160813T134342
        Piece Name: /u2/dosa/fra/DOSA/backupset/2016_08_13/o1_mf_annnn_TAG20160813T134342_ctxoopy2_.bkp

  List of Archived Logs in backup set 1
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    26      466387     20-JUL-16 475581     20-JUL-16
  1    27      475581     20-JUL-16 475750     20-JUL-16
  1    28      475750     20-JUL-16 475932     20-JUL-16
  1    29      475932     20-JUL-16 476102     20-JUL-16
  1    30      476102     20-JUL-16 476284     20-JUL-16
  1    31      476284     20-JUL-16 476441     20-JUL-16
  1    32      476441     20-JUL-16 476609     20-JUL-16
  1    33      476609     20-JUL-16 476761     20-JUL-16
  1    34      476761     20-JUL-16 476933     20-JUL-16
  1    35      476933     20-JUL-16 477099     20-JUL-16
  1    36      477099     20-JUL-16 477272     20-JUL-16
  1    37      477272     20-JUL-16 477437     20-JUL-16
  1    38      477437     20-JUL-16 477608     20-JUL-16
  1    39      477608     20-JUL-16 477758     20-JUL-16
  1    40      477758     20-JUL-16 477922     20-JUL-16
  1    41      477922     20-JUL-16 478074     20-JUL-16
  1    42      478074     20-JUL-16 478294     20-JUL-16
  1    43      478294     20-JUL-16 478492     20-JUL-16
  1    44      478492     20-JUL-16 478752     20-JUL-16
  1    45      478752     20-JUL-16 479230     20-JUL-16
  1    46      479230     20-JUL-16 495684     13-AUG-16
  1    47      495684     13-AUG-16 496347     13-AUG-16
  1    48      496347     13-AUG-16 535122     13-AUG-16

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    2.10G      DISK        00:01:01     13-AUG-16
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20160813T134428
        Piece Name: /u2/dosa/fra/DOSA/backupset/2016_08_13/o1_mf_nnndf_TAG20160813T134428_ctxoq57w_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 535146     13-AUG-16 /u2/dosa/system01.dbf
  2       Full 535146     13-AUG-16 /u2/dosa/sysaux01.dbf
  3       Full 535146     13-AUG-16 /u2/dosa/undotbs01.dbf
  4       Full 535146     13-AUG-16 /u2/dosa/users01.dbf
  5       Full 535146     13-AUG-16 /u2/dosa/soe.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.36M      DISK        00:00:01     13-AUG-16
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160813T134428
        Piece Name: /u2/dosa/fra/DOSA/backupset/2016_08_13/o1_mf_ncsnf_TAG20160813T134428_ctxos867_.bkp
  SPFILE Included: Modification time: 13-AUG-16
  SPFILE db_unique_name: DOSA
  Control File Included: Ckp SCN: 535169       Ckp time: 13-AUG-16

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4       4.00K      DISK        00:00:00     13-AUG-16
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20160813T134537
        Piece Name: /u2/dosa/fra/DOSA/backupset/2016_08_13/o1_mf_annnn_TAG20160813T134537_ctxos9hr_.bkp

  List of Archived Logs in backup set 4
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    49      535122     13-AUG-16 535174     13-AUG-16
To list a specific backupset

To get details of a specific backupset and its backup pieces, use list backupset <backupset number> command

The backupset number is obtained from list backupset command

RMAN> list backupset 3;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.36M      DISK        00:00:01     13-AUG-16
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160813T134428
        Piece Name: /u2/dosa/fra/DOSA/backupset/2016_08_13/o1_mf_ncsnf_TAG20160813T134428_ctxos867_.bkp
  Control File Included: Ckp SCN: 535169       Ckp time: 13-AUG-16
  SPFILE Included: Modification time: 13-AUG-16
  SPFILE db_unique_name: DOSA
To list obsolete backupsets

Backupsets become obsolete based on retention policy specified in RMAN configuration

A backupset that has passed the retention policy is marked obsolete and can be deleted from backup

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           1      13-AUG-16
  Backup Piece       1      13-AUG-16          /u2/dosa/fra/DOSA/backupset/2016_08_13/o1_mf_annnn_TAG20160813T134342_ctxoopy2_.bkp
Setting Date Format for RMAN report

RMAN report shows date in DD-MON-YY format. To view reports in a specific format for date, RMAN depends on NLS_DATE_FORMAT environment variable. This environment variable must be set before connecting to RMAN.

[oracle@orcl admin]$ export NLS_DATE_FORMAT="DD/Mon/YYYY HH:MI:SS AM"

The following table shows valid format specifiers that can be used for NLS_DATE_FORMAT variable

Format SepcifierDescription
DDDay in two digit format
MMMonth in two digit format
MonMonth name displaying only the first three letters
MonthMonth name in full
YYYYFor four digit year

All date fields are now shows date in DD/Mon/YYYY HH:MI:SS AM format as seen below

    
[oracle@orcl admin]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Aug 27 17:31:24 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DOSA (DBID=2763133831)

RMAN> list backupset;

using target database control file instead of recovery catalog

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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -----------------------
1       1.03G      DISK        00:00:37     13/Aug/2016 01:44:19 PM
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160813T134342
        Piece Name: /u2/dosa/fra/DOSA/backupset/2016_08_13/o1_mf_annnn_TAG20160813T134342_ctxoopy2_.bkp

  List of Archived Logs in backup set 1
  Thrd Seq     Low SCN    Low Time                Next SCN   Next Time
  ---- ------- ---------- ----------------------- ---------- ---------
  1    26      466387     20/Jul/2016 10:05:21 PM 475581     20/Jul/2016 10:26:44 PM
  1    27      475581     20/Jul/2016 10:26:44 PM 475750     20/Jul/2016 10:26:52 PM
  1    28      475750     20/Jul/2016 10:26:52 PM 475932     20/Jul/2016 10:27:00 PM
  1    29      475932     20/Jul/2016 10:27:00 PM 476102     20/Jul/2016 10:27:08 PM
  1    30      476102     20/Jul/2016 10:27:08 PM 476284     20/Jul/2016 10:27:21 PM
  1    31      476284     20/Jul/2016 10:27:21 PM 476441     20/Jul/2016 10:27:33 PM
  1    32      476441     20/Jul/2016 10:27:33 PM 476609     20/Jul/2016 10:27:44 PM
  1    33      476609     20/Jul/2016 10:27:44 PM 476761     20/Jul/2016 10:27:54 PM
  1    34      476761     20/Jul/2016 10:27:54 PM 476933     20/Jul/2016 10:28:06 PM
  1    35      476933     20/Jul/2016 10:28:06 PM 477099     20/Jul/2016 10:28:17 PM
  1    36      477099     20/Jul/2016 10:28:17 PM 477272     20/Jul/2016 10:28:28 PM
  1    37      477272     20/Jul/2016 10:28:28 PM 477437     20/Jul/2016 10:28:39 PM
  1    38      477437     20/Jul/2016 10:28:39 PM 477608     20/Jul/2016 10:28:50 PM
  1    39      477608     20/Jul/2016 10:28:50 PM 477758     20/Jul/2016 10:29:00 PM
  1    40      477758     20/Jul/2016 10:29:00 PM 477922     20/Jul/2016 10:29:11 PM
  1    41      477922     20/Jul/2016 10:29:11 PM 478074     20/Jul/2016 10:29:21 PM
  1    42      478074     20/Jul/2016 10:29:21 PM 478294     20/Jul/2016 10:29:28 PM
  1    43      478294     20/Jul/2016 10:29:28 PM 478492     20/Jul/2016 10:29:34 PM
  1    44      478492     20/Jul/2016 10:29:34 PM 478752     20/Jul/2016 10:29:41 PM
  1    45      478752     20/Jul/2016 10:29:41 PM 479230     20/Jul/2016 10:29:48 PM
  1    46      479230     20/Jul/2016 10:29:48 PM 495684     13/Aug/2016 10:49:40 AM
  1    47      495684     13/Aug/2016 10:49:40 AM 496347     13/Aug/2016 10:49:53 AM
  1    48      496347     13/Aug/2016 10:49:53 AM 535122     13/Aug/2016 01:43:41 PM

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------
2       Full    2.10G      DISK        00:01:01     13/Aug/2016 01:45:30 PM
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20160813T134428
        Piece Name: /u2/dosa/fra/DOSA/backupset/2016_08_13/o1_mf_nnndf_TAG20160813T134428_ctxoq57w_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time                Name
  ---- -- ---- ---------- ----------------------- ----
  1       Full 535146     13/Aug/2016 01:44:29 PM /u2/dosa/system01.dbf
  2       Full 535146     13/Aug/2016 01:44:29 PM /u2/dosa/sysaux01.dbf
  3       Full 535146     13/Aug/2016 01:44:29 PM /u2/dosa/undotbs01.dbf
  4       Full 535146     13/Aug/2016 01:44:29 PM /u2/dosa/users01.dbf
  5       Full 535146     13/Aug/2016 01:44:29 PM /u2/dosa/soe.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------
3       Full    9.36M      DISK        00:00:01     13/Aug/2016 01:45:36 PM
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160813T134428
        Piece Name: /u2/dosa/fra/DOSA/backupset/2016_08_13/o1_mf_ncsnf_TAG20160813T134428_ctxos867_.bkp
  SPFILE Included: Modification time: 13/Aug/2016 01:40:57 PM
  SPFILE db_unique_name: DOSA
  Control File Included: Ckp SCN: 535169       Ckp time: 13/Aug/2016 01:45:34 PM

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -----------------------
4       4.00K      DISK        00:00:00     13/Aug/2016 01:45:37 PM
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20160813T134537
        Piece Name: /u2/dosa/fra/DOSA/backupset/2016_08_13/o1_mf_annnn_TAG20160813T134537_ctxos9hr_.bkp

  List of Archived Logs in backup set 4
  Thrd Seq     Low SCN    Low Time                Next SCN   Next Time
  ---- ------- ---------- ----------------------- ---------- ---------
  1    49      535122     13/Aug/2016 01:43:41 PM 535174     13/Aug/2016 01:45:37 PM
Comments