Rman backup sets

Fiche express
Type	Script SQL*Plus de diagnostic
Vues source	v$backup_set, v$backup_piece, v$backup_spfile
Voir aussi	Rman backup pieces · Rman backup sets 8i · RMAN

Rman backup sets liste tous les backup sets RMAN connus du control file (disponibles et expirés) : type de sauvegarde, type de périphérique, inclusion du control file/SPFILE, niveau incrémental, nombre de pièces, durée, tag. Voir Rman backup sets 8i pour la variante compatible Oracle8i (sans v$backup_spfile, introduite en 9i).

Script

SET TERMOUT OFF
COLUMN current_instance NEW_VALUE current_instance NOPRINT
SELECT RPAD(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON

PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Rapport   : Backup sets RMAN                                           |
PROMPT | Instance  : &current_instance                                          |
PROMPT | Note      : Jeux de sauvegarde disponibles listés dans le control      |
PROMPT |             file, y compris ceux expirés.                             |
PROMPT +------------------------------------------------------------------------+

SET ECHO      OFF
SET FEEDBACK  6
SET HEADING   ON
SET LINESIZE  180
SET PAGESIZE  50000
SET VERIFY    OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN bs_key                FORMAT 999999 HEADING 'BS|Clé'
COLUMN backup_type           FORMAT a14    HEADING 'Type de|sauvegarde'
COLUMN device_type           FORMAT a6     HEADING 'Type de|périphérique'
COLUMN controlfile_included  FORMAT a11    HEADING 'Control-|file inclus ?'
COLUMN spfile_included       FORMAT a9     HEADING 'SPFILE|inclus ?'
COLUMN incremental_level     FORMAT 999999 HEADING 'Niveau|incrémental'
COLUMN pieces                FORMAT 9,999  HEADING 'Nb de|pièces'
COLUMN start_time            FORMAT a19    HEADING 'Début'
COLUMN completion_time       FORMAT a19    HEADING 'Fin'
COLUMN elapsed_seconds       FORMAT 999,999 HEADING 'Durée|(s)'
COLUMN tag                   FORMAT a19    HEADING 'Tag'
COLUMN block_size            FORMAT 999,999 HEADING 'Taille de|bloc'

SELECT
    bs.recid                                                bs_key
  , DECODE(backup_type, 'L', 'Archived Logs', 'D', 'Datafile Full', 'I', 'Incremental') backup_type
  , device_type                                             device_type
  , DECODE(bs.controlfile_included, 'NO', NULL, bs.controlfile_included) controlfile_included
  , sp.spfile_included                                       spfile_included
  , bs.incremental_level                                     incremental_level
  , bs.pieces                                                pieces
  , TO_CHAR(bs.start_time, 'mm/dd/yyyy HH24:MI:SS')          start_time
  , TO_CHAR(bs.completion_time, 'mm/dd/yyyy HH24:MI:SS')     completion_time
  , bs.elapsed_seconds                                       elapsed_seconds
  , bp.tag                                                   tag
  , bs.block_size                                            block_size
FROM
    v$backup_set bs
  , (SELECT DISTINCT set_stamp, set_count, tag, device_type
     FROM   v$backup_piece
     WHERE  status IN ('A', 'X'))          bp
  , (SELECT DISTINCT set_stamp, set_count, 'YES' spfile_included
     FROM   v$backup_spfile)               sp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bs.set_stamp = sp.set_stamp (+)
  AND bs.set_count = sp.set_count (+)
ORDER BY
    bs.recid;

Voir aussi

• Rman backup pieces — détail des pièces physiques composant chaque jeu
• Rman backup sets 8i — variante compatible Oracle8i
• Rman controlfiles — jeux contenant spécifiquement une sauvegarde de control file
• RMAN — présentation générale de l'outil