DBA Tips Archive for Oracle

  


Using the LIST Command

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. All Backup Sets, Backup Pieces, and Proxy Copies
  3. EXPIRED
  4. List Backup by File
  5. List all Archived Log Files
  6. Summary Lists



Overview

The LIST command will query the RMAN repository (control file or recovery catalog) to produce a record of its contents. Use this command to obtain data about:

RMAN records the output of the LIST command to either standard output or the message log, but not to both at the same time. You can also control how the output is organized as well as the level of detail in the output.

NOTE: In most cases, running the LIST command is preferable to querying V$ or recovery catalog views.

The LIST command displays the same files that the CROSSCHECK and DELETE commands operate on. Consequently, you can issue LIST to see what is in the repository, and then run CROSSCHECK to ensure that these files exist on disk or tape.

You can control how the output is displayed by using the BY BACKUP and BY FILE options of the LIST command and choosing between the SUMMARY and VERBOSE options. Examples will be provided below.

The primary purpose of the LIST command is to determine which backups or copies are available. Note that only backups and copies that completed successfully are stored in the repository.

Let's take at how to the different ways in which to use the LIST command.



All Backup Sets, Backup Pieces, and Proxy Copies

To list all backup sets, backup pieces, and proxy copies:
RMAN> list backup;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
257     Incr 0  326M       DISK        00:00:59     22-JAN-04
        BP Key: 259   Status: AVAILABLE   Tag: TARGDB_CLOSED_LEVEL0
        Piece Name: /db1/orabackup/TARGDB_closed_35fc6tup_1_1
  List of Datafiles in backup set 257
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2    0  Incr 1167221    22-JAN-04 /u06/app/oradata/TARGDB/undotbs01.dbf
  7    0  Incr 1167221    22-JAN-04 /u10/app/oradata/TARGDB/users01.dbf
  8    0  Incr 1167221    22-JAN-04 /u10/app/oradata/TARGDB/tools01.dbf
  9    0  Incr 1167221    22-JAN-04 /u10/app/oradata/TARGDB/perfstat01.dbf
  10   0  Incr 1167221    22-JAN-04 /u09/app/oradata/TARGDB/indx01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
258     Incr 0  477M       DISK        00:01:36     22-JAN-04
        BP Key: 260   Status: AVAILABLE   Tag: TARGDB_CLOSED_LEVEL0
        Piece Name: /db1/orabackup/TARGDB_closed_34fc6tup_1_1
  List of Datafiles in backup set 258
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 1167221    22-JAN-04 /u08/app/oradata/TARGDB/system01.dbf
  3    0  Incr 1167221    22-JAN-04 /u10/app/oradata/TARGDB/cwmlite01.dbf
  4    0  Incr 1167221    22-JAN-04 /u10/app/oradata/TARGDB/drsys01.dbf
  5    0  Incr 1167221    22-JAN-04 /u10/app/oradata/TARGDB/odm01.dbf
  6    0  Incr 1167221    22-JAN-04 /u10/app/oradata/TARGDB/xdb01.dbf
  11   0  Incr 1167221    22-JAN-04 /u10/app/oradata/TARGDB/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
273     Full    7M         DISK        00:00:02     22-JAN-04
        BP Key: 274   Status: AVAILABLE   Tag:
        Piece Name: /db1/orabackup/c-2498101982-20040122-00
  SPFILE Included: Modification time: 17-JAN-04

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
278     Incr 0  326M       DISK        00:00:56     22-JAN-04
        BP Key: 280   Status: AVAILABLE   Tag: TARGDB_CLOSED_LEVEL0
        Piece Name: /db1/orabackup/TARGDB_closed_38fc6vjv_1_1
  List of Datafiles in backup set 278
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2    0  Incr 1171233    22-JAN-04 /u06/app/oradata/TARGDB/undotbs01.dbf
  7    0  Incr 1171233    22-JAN-04 /u10/app/oradata/TARGDB/users01.dbf
  8    0  Incr 1171233    22-JAN-04 /u10/app/oradata/TARGDB/tools01.dbf
  9    0  Incr 1171233    22-JAN-04 /u10/app/oradata/TARGDB/perfstat01.dbf
  10   0  Incr 1171233    22-JAN-04 /u09/app/oradata/TARGDB/indx01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
279     Incr 0  477M       DISK        00:01:36     22-JAN-04
        BP Key: 281   Status: AVAILABLE   Tag: TARGDB_CLOSED_LEVEL0
        Piece Name: /db1/orabackup/TARGDB_closed_37fc6vjv_1_1
  List of Datafiles in backup set 279
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 1171233    22-JAN-04 /u08/app/oradata/TARGDB/system01.dbf
  3    0  Incr 1171233    22-JAN-04 /u10/app/oradata/TARGDB/cwmlite01.dbf
  4    0  Incr 1171233    22-JAN-04 /u10/app/oradata/TARGDB/drsys01.dbf
  5    0  Incr 1171233    22-JAN-04 /u10/app/oradata/TARGDB/odm01.dbf
  6    0  Incr 1171233    22-JAN-04 /u10/app/oradata/TARGDB/xdb01.dbf
  11   0  Incr 1171233    22-JAN-04 /u10/app/oradata/TARGDB/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
294     Full    7M         DISK        00:00:01     22-JAN-04
        BP Key: 295   Status: AVAILABLE   Tag:
        Piece Name: /db1/orabackup/c-2498101982-20040122-01
  SPFILE Included: Modification time: 17-JAN-04



EXPIRED

We can also specify the EXPIRED keyword to identify those backups that were not found during a crosscheck:
RMAN> list expired backup;



List Backup by File

You can list copies of datafiles, control files, and archived logs. Specify the desired objects with the listObjList or recordSpec clause. If you do not specify an object, then RMAN displays copies of all database files and archived redo logs. By default, RMAN lists backups in verbose mode, which means that it provides extensive, multiline information.
RMAN> list backup by file;

List of Datafile Backups
========================

File Key     TY LV S Ckp SCN    Ckp Time  #Pieces #Copies Tag
---- ------- -  -- - ---------- --------- ------- ------- ---
1    279     B  0  A 1171233    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
     258     B  0  A 1167221    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
2    278     B  0  A 1171233    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
     257     B  0  A 1167221    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
3    279     B  0  A 1171233    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
     258     B  0  A 1167221    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
4    279     B  0  A 1171233    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
     258     B  0  A 1167221    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
5    279     B  0  A 1171233    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
     258     B  0  A 1167221    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
6    279     B  0  A 1171233    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
     258     B  0  A 1167221    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
7    278     B  0  A 1171233    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
     257     B  0  A 1167221    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
8    278     B  0  A 1171233    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
     257     B  0  A 1167221    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
9    278     B  0  A 1171233    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
     257     B  0  A 1167221    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
10   278     B  0  A 1171233    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
     257     B  0  A 1167221    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
11   279     B  0  A 1171233    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0
     258     B  0  A 1167221    22-JAN-04 1       1       TARGDB_CLOSED_LEVEL0

List of Controlfile Backups
===========================

CF Ckp SCN Ckp Time  BS Key  S #Pieces #Copies Tag
---------- --------- ------- - ------- ------- ---
1171233    22-JAN-04 294     A 1       1
1167221    22-JAN-04 273     A 1       1
List of SPFILE Backups
======================

Modification Time BS Key  S #Pieces #Copies Tag
----------------- ------- - ------- ------- ---
17-JAN-04         294     A 1       1
17-JAN-04         273     A 1       1



List all Archived Log Files

You can also list all archived log files:
RMAN> list archivelog all;

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
245     1    15      A 18-JAN-04 /u07/app/oradata/TARGDB/archive/arch_t1_s15.dbf
246     1    16      A 19-JAN-04 /u07/app/oradata/TARGDB/archive/arch_t1_s16.dbf
247     1    17      A 21-JAN-04 /u07/app/oradata/TARGDB/archive/arch_t1_s17.dbf



Summary Lists

By default the LIST output is highly detailed, but you can also specify that RMAN display the output in summarized form. Specify the desired objects with the listObjectList or recordSpec clause. If you do not specify an object, then LIST BACKUP displays all backups. By default, RMAN lists backups in verbose mode.
RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
257     B  0  A DISK        22-JAN-04       1       1       TARGDB_CLOSED_LEVEL0
258     B  0  A DISK        22-JAN-04       1       1       TARGDB_CLOSED_LEVEL0
273     B  F  A DISK        22-JAN-04       1       1
278     B  0  A DISK        22-JAN-04       1       1       TARGDB_CLOSED_LEVEL0
279     B  0  A DISK        22-JAN-04       1       1       TARGDB_CLOSED_LEVEL0
294     B  F  A DISK        22-JAN-04       1       1
The above listing shows that I performed two backups:



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Monday, 26-Apr-2004 00:00:00 EDT
Page Count: 10837