sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle RMAN 通过sql语句查询rman的备份信息

时间:2016-07-25 22:39   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 分享一篇关于查看Oracle RMAN备份集的案例,通过sql语句查询rman的备份信息

1.查看所有备份集

    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;

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

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#;

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

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;

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

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#;

6.查看某个备份集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;

rman配置信息

SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle RMAN 通过sql语句查询rman的备份信息

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/893.html

Oracle研究中心

关键词:

Oracle RMAN

通过sql语句查询rman的备份信息

SQL语句查看备份的数据文件