通过sql查询rman备份信息

来源：互联网发布：wei护肤品怎么样知乎编辑：程序博客网时间：2024/05/18 03:08

链接：http://www.xifenfei.com/2582.html

标题：通过sql查询rman备份信息

查看所有备份集

    SELECTA.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"
    FROMGV$BACKUP_PIECE A, GV$BACKUP_SET B
   WHEREA.SET_STAMP = B.SET_STAMP ANDA.DELETED = 'NO'
ORDER BY A.COMPLETION_TIME DESC;

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

SELECT distinct c.file#,A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIME
  FROMV$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE D
 WHEREA.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
  FROMV$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$CONTROLFILE D
 WHEREA.SET_STAMP = C.SET_STAMP
   ANDC.FILE# = 0
   ANDA.DELETED = 'NO'
   ANDC.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#
  FROMV$BACKUP_REDOLOG B, V$BACKUP_PIECE A
 WHEREA.SET_STAMP = B.SET_STAMP
   ANDA.DELETED = 'NO'
   ANDB.SET_STAMP = &SET_STAMP
 ORDERBY THREAD#, SEQUENCE#;

查看某个备份集SPFILE

SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLE
  FROMV$BACKUP_SPFILE B, V$BACKUP_PIECE A
 WHEREA.SET_STAMP = B.SET_STAMP
   ANDA.DELETED = 'NO'
   ANDB.SET_STAMP = &SET_STAMP;

rman配置信息

SELECT NAME,VALUE FROMV$RMAN_CONFIGURATION;

oracle查看object对象使用空间
ORACLE 12C dbms_utility.expand_sql_text 查看SQL视图基表
通过sql语句获取awr/statspack逻辑读/物理读
fckeditor使用说明
shell编程——基本语法
v$和gv$来源
找出调用DBLINK的SESSION信息
oracle 中如何定位重要(消耗资源多)的SQL

0 0