Data File Backup Sets

Connect to RMAN securely

Start RMAN with an account that has the SYSBACKUP or SYSDBA administrative privilege. When local operating-system authentication is configured, the following command avoids exposing a password:

rman target /

For remote administration, use an Oracle Net service and an approved credential-management method. Do not place a production password directly in a command, shell history, lesson example, or unattended script.

If you use a recovery catalog, include the catalog connection according to your site's credential policy. The RMAN commands in this lesson work in both catalog and NOCATALOG modes, although the identifiers and amount of retained history can differ.

Identify data file backup sets with LIST

The RMAN LIST command is usually the best starting point because it formats the repository metadata for administrators. Use object-specific commands when you know which database, tablespace, or data file you need to investigate:

LIST BACKUP OF DATABASE;
LIST BACKUP OF DATAFILE 1, 3;
LIST BACKUP OF TABLESPACE USERS;
LIST BACKUPSET OF DATAFILE 1;
LIST BACKUP BY FILE;
LIST BACKUP SUMMARY;

Detailed output for a data file set contains a heading similar to List of Datafiles in backup set 42. An archived redo log set instead contains a List of Archived Logs in backup set section.

Read the detailed LIST output

The following fields help identify and evaluate a data file backup set:

• BS Key: The identifier for the backup set. With a recovery catalog, it corresponds to RC_BACKUP_SET.BS_KEY. In NOCATALOG mode, it displays the RECID from V$BACKUP_SET.
• Type: Full or Incr for a data file backup set.
• LV: The incremental level. A nonincremental full backup has no numeric level, while incremental backups use level 0 or 1.
• File: The absolute data file number.
• Ckp SCN and Ckp Time: The data file checkpoint represented by the backup.
• Status: Whether a backup piece is available, unavailable, or expired.
• Piece Name/Handle: The disk file name or media-manager handle for the physical piece.

Use completion-time and tag filters to narrow a large repository:

LIST BACKUP OF DATAFILE 1
  COMPLETED AFTER '01-AUG-2026';

LIST BACKUP TAG 'WEEKLY_FULL';

LIST BACKUP SUMMARY provides one line for each backup. In its LV column, F identifies a full data file backup, 0 or 1 identifies an incremental data file backup, and A identifies an archived redo log backup. Summary output is useful for triage, while detailed output is better when you need file names, checkpoints, and piece handles.

Identify sets with control-file views

Use the target database's V$ views when you need repeatable SQL reports without a recovery catalog. These views expose metadata currently retained in the control file.

Query backup-set headers

V$BACKUP_SET contains one row for each successfully completed backup set known to the control file:

SELECT recid,
       set_stamp,
       set_count,
       backup_type,
       incremental_level,
       controlfile_included,
       pieces,
       start_time,
       completion_time,
       multi_section,
       con_id
FROM   v$backup_set
WHERE  backup_type IN ('D', 'I')
ORDER BY completion_time DESC;

Use SET_STAMP and SET_COUNT together to identify a set across the control-file views. BACKUP_TYPE = 'D' represents a full data file backup or, in related repository views, an incremental level 0 backup. BACKUP_TYPE = 'I' represents an incremental backup, normally level 1. Always inspect INCREMENTAL_LEVEL: NULL means a nonincremental full backup, 0 means level 0, and 1 means level 1.

BACKUP_TYPE = 'L' identifies an archived redo log set and is intentionally excluded. CONTROLFILE_INCLUDED reports YES for a primary control file, SBY for a standby control file, and NO when no control file is included.

V$BACKUP_SET does not contain a STATUS column. To evaluate whether a set is usable, inspect its pieces or use a reporting view such as V$BACKUP_FILES.

Join each set to its data files

V$BACKUP_DATAFILE identifies the data file records within each set. Join it to V$BACKUP_SET by SET_STAMP and SET_COUNT:

SELECT s.recid              AS bs_recid,
       s.set_stamp,
       s.set_count,
       s.backup_type,
       s.incremental_level,
       d.file#,
       f.name               AS current_datafile_name,
       d.checkpoint_change#,
       d.checkpoint_time,

       d.blocks,
       d.block_size,
       d.section_size
FROM   v$backup_set      s
JOIN   v$backup_datafile d
       ON  d.set_stamp = s.set_stamp
       AND d.set_count = s.set_count
LEFT JOIN v$datafile     f
       ON  f.file# = d.file#
WHERE  s.backup_type IN ('D', 'I')
  AND  d.file# > 0
ORDER BY s.completion_time DESC,
         d.file#;

In V$BACKUP_DATAFILE, FILE# = 0 represents a backed-up control file. The FILE# > 0 predicate restricts the report to database data files. The join to V$DATAFILE returns the file's current name. Because backup history can outlive a file rename or removal, use catalog and backup-detail metadata when historical names are important.

Find the physical backup pieces

Join the set identifiers to V$BACKUP_PIECE to find disk paths or media-manager handles:

SELECT p.set_stamp,
       p.set_count,
       p.piece#,
       p.copy#,
       p.handle,
       p.device_type,
       p.status,
       p.bytes,
       p.completion_time
FROM   v$backup_piece p
WHERE  EXISTS (
         SELECT 1
         FROM   v$backup_set s
         WHERE  s.set_stamp = p.set_stamp
           AND  s.set_count = p.set_count
           AND  s.backup_type IN ('D', 'I')
       )
ORDER BY p.set_stamp DESC,
         p.set_count DESC,
         p.piece#,
         p.copy#;

A piece can be available, unavailable, expired, or deleted, depending on the view and retained repository history. A set is only useful for restore when its required pieces are accessible. Run CROSSCHECK BACKUP according to your maintenance policy so repository status reflects the actual storage.

Separate backup sets from image and proxy copies

V$BACKUP_DATAFILE_DETAILS includes restorable data files from backup sets, image copies, and proxy copies. Filter BTYPE when the report must include only backup sets:

SELECT btype,
       btype_key,
       id1                    AS set_stamp,
       id2                    AS set_count,
       file#,
       incremental_level,
       checkpoint_time,
       filesize,
       con_id
FROM   v$backup_datafile_details
WHERE  btype = 'BACKUPSET'
ORDER BY checkpoint_time DESC;

The possible container values are BACKUPSET, IMAGECOPY, and PROXYCOPY. Only BACKUPSET identifies the format discussed in this lesson.

V$BACKUP_FILES offers another reporting option. For rows where BACKUP_TYPE = 'BACKUP SET', BS_TYPE = 'DATAFILE' identifies data file sets and BS_TYPE = 'ARCHIVED LOG' identifies archived redo log sets. BS_STATUS reports AVAILABLE, UNAVAILABLE, EXPIRED, or OTHER.

Identify sets with recovery-catalog views

A recovery catalog stores metadata independently of the target control file. It is especially useful for historical reports, Data Guard environments, and inventories that span many databases or incarnations.

Query recovery-catalog set headers

RC_BACKUP_SET corresponds to V$BACKUP_SET. It contains DB_KEY and DB_ID, but it does not contain a DB_NAME column. The corrected query is:

SELECT db_key,
       db_id,
       bs_key,
       backup_type,
       incremental_level,
       pieces,
       start_time,
       completion_time,
       status
FROM   rc_backup_set
WHERE  backup_type IN ('D', 'I')
ORDER BY completion_time DESC;

BS_KEY is the catalog's primary key for the set. STATUS can be A when all pieces are available, D when all pieces are deleted, or O when only some pieces are available and the set is unusable.

If a report specifically needs DB_NAME, use a view that exposes it, such as RC_BACKUP_SET_DETAILS, or join through an appropriate database key. Remember that RC_BACKUP_SET_DETAILS reports currently available sets, so its STATUS is always A.

Join sets to cataloged data files

RC_BACKUP_DATAFILE includes DB_NAME, PDB_KEY, and the file-level catalog key. Join it to RC_BACKUP_SET with BS_KEY:

SELECT d.db_name,
       s.db_key,
       s.bs_key,
       d.bdf_key,
       d.file#,
       d.backup_type,
       d.incremental_level,
       d.checkpoint_change#,
       d.checkpoint_time,
       d.pdb_key
FROM   rc_backup_set      s
JOIN   rc_backup_datafile d
       ON d.bs_key = s.bs_key
WHERE  s.backup_type IN ('D', 'I')
  AND  d.file# > 0
ORDER BY s.completion_time DESC,
         d.file#;

Important recovery-catalog identifiers include BS_KEY for a backup set, BDF_KEY for a backed-up data file record, BP_KEY for a backup piece, and PDB_KEY for the pluggable database associated with the metadata.

Practical identification rules

Do not classify BACKUP_TYPE = 'L' or BS_TYPE = 'ARCHIVED LOG' as a data file backup set.

Identify multisection data file backups

A multisection backup divides one large data file into contiguous sections. Multiple channels can process those sections in parallel, and each section is written into a separate piece of the same multisection backup set.

SELECT recid,
       set_stamp,
       set_count,
       backup_type,
       incremental_level,
       pieces,
       multi_section,
       completion_time
FROM   v$backup_set
WHERE  backup_type IN ('D', 'I')
  AND  multi_section = 'YES'
ORDER BY completion_time DESC;

For multisection records, V$BACKUP_DATAFILE.SECTION_SIZE is greater than zero and represents the section size in blocks. Multiple records can refer to sections of the same file and set:

SELECT set_stamp,
       set_count,
       file#,
       COUNT(*)          AS section_records,
       MAX(section_size) AS section_size_blocks
FROM   v$backup_datafile
WHERE  section_size > 0
GROUP BY set_stamp,
         set_count,
         file#
ORDER BY set_stamp DESC,
         set_count DESC,
         file#;

Back up data files by number, path, or tablespace

REPORT SCHEMA;

Parallelism and data files