通过sql查询rman备份信息

最新推荐文章于 2025-01-10 11:42:11 发布

转载最新推荐文章于 2025-01-10 11:42:11 发布 · 606 阅读

备份&还原&修复专栏收录该内容

25 篇文章

订阅专栏

本文提供了一系列 Oracle RMAN 的 SQL 查询语句，用于查看备份集信息、定位特定备份集中的数据文件、控制文件、归档日志及 SPFILE，并展示了如何获取 RMAN 的配置信息。

http://www.xifenfei.com/2582.html

查看所有备份集

    SELECT
A.RECID "BACKUP SET",
         A.SET_STAMP,
         DECODE (B.INCREMENTAL_LEVEL,
                 '', DECODE (BACKUP_TYPE,
'L', 'Archivelog',
'Full'),
                 1,
'Incr-1级',
                 0,
'Incr-0级',
                 B.INCREMENTAL_LEVEL)
            "Type LV",
         B.CONTROLFILE_INCLUDED
"包含CTL",
         DECODE (A.STATUS,
                 'A',
'AVAILABLE',
                 'D',
'DELETED',
                 'X',
'EXPIRED',
                 'ERROR')
            "STATUS",
         A.DEVICE_TYPE
"Device Type",
         A.START_TIME
"Start Time",
         A.COMPLETION_TIME
"Completion Time",
         A.ELAPSED_SECONDS
"Elapsed Seconds",
         --a.BYTES/1024/1024/1024 "大小(G)",
         --a.COMPRESSED,
         A.TAG
"Tag",
         A.HANDLE
"Path"
    FROM
GV$BACKUP_PIECE A, GV$BACKUP_SET B
   WHERE
A.SET_STAMP = B.SET_STAMP AND
A.DELETED = 'NO'
ORDER 
BY A.COMPLETION_TIME DESC;

查找某个备份集中包含数据文件

SELECT 
distinct c.file#,A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIME
  FROM
V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE D
 WHERE
A.SET_STAMP = C.SET_STAMP
AND 
D.FILE# = C.FILE#
AND 
A.DELETED='NO'
AND 
c.set_stamp=&set_stamp
ORDER 
BY C.FILE#;

查询某个备份集中控制文件

SELECT 
DISTINCT A.SET_STAMP,
                D.NAME,
                C.CHECKPOINT_CHANGE#,
                C.CHECKPOINT_TIME
  FROM
V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$CONTROLFILE D
 WHERE
A.SET_STAMP = C.SET_STAMP
   AND
C.FILE# = 0
   AND
A.DELETED = 'NO'
   AND
C.SET_STAMP = &SET_STAMP;

查看某个备份集中归档日志

SELECT 
DISTINCT B.SET_STAMP,
                B.THREAD#,
                B.SEQUENCE#,
                B.FIRST_TIME,
                B.FIRST_CHANGE#,
                B.NEXT_TIME,
                B.NEXT_CHANGE#
  FROM
V$BACKUP_REDOLOG B, V$BACKUP_PIECE A
 WHERE
A.SET_STAMP = B.SET_STAMP
   AND
A.DELETED = 'NO'
   AND
B.SET_STAMP = &SET_STAMP
 ORDER
BY THREAD#, SEQUENCE#;

查看某个备份集SPFILE

SELECT 
DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLE
  FROM
V$BACKUP_SPFILE B, V$BACKUP_PIECE A
 WHERE
A.SET_STAMP = B.SET_STAMP
   AND
A.DELETED = 'NO'
   AND
B.SET_STAMP = &SET_STAMP;