V$RMAN-BACKUP-JOB-DETAILS视图详解

不写了，从文库转搜了一篇记录一下

V$RMAN_BACKUP_JOB_DETAILS

在Oracle 数据库很多的情况下，可以利用V$RMAN_BACKUP_JOB_DETAILS+DB_LINK的方式实现集中监控所有数据库的Rman备份情况，而无需登录到数据库所在主机查看备份日志，减轻了DBA的工作量。

SQL> desc v$rman_backup_job_details

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

SESSION_KEY                                        NUMBER

SESSION_RECID                                      NUMBER

SESSION_STAMP                                      NUMBER

COMMAND_ID                                         VARCHAR2(33)

START_TIME                                         DATE

END_TIME                                           DATE

INPUT_BYTES                                        NUMBER

OUTPUT_BYTES                                       NUMBER

STATUS_WEIGHT                                      NUMBER

OPTIMIZED_WEIGHT                                   NUMBER

OBJECT_TYPE_WEIGHT                                 NUMBER

OUTPUT_DEVICE_TYPE                                 VARCHAR2(17)

AUTOBACKUP_COUNT                                   NUMBER

BACKED_BY_OSB                                      VARCHAR2(3)

AUTOBACKUP_DONE                                    VARCHAR2(3)

STATUS                                             VARCHAR2(23)

INPUT_TYPE                                         VARCHAR2(13)

OPTIMIZED                                          VARCHAR2(3)

ELAPSED_SECONDS                                    NUMBER

COMPRESSION_RATIO                                  NUMBER

INPUT_BYTES_PER_SEC                                NUMBER

OUTPUT_BYTES_PER_SEC                               NUMBER

INPUT_BYTES_DISPLAY                                VARCHAR2(4000)

OUTPUT_BYTES_DISPLAY                               VARCHAR2(4000)

INPUT_BYTES_PER_SEC_DISPLAY                        VARCHAR2(4000)

OUTPUT_BYTES_PER_SEC_DISPLAY                       VARCHAR2(4000)

TIME_TAKEN_DISPLAY                                 VARCHAR2(4000)

几个重要的列状态值：

INPUT_TYPE：备份的方式，比如全库备份、数据库增量备份、归档备份等等

DB FULL

RECVR AREA

DB INCR

DATAFILE FULL

DATAFILE INCR

ARCHIVELOG

CONTROLFILE

SPFILE

STATUS:RMAN备份job的结果状态，如正在备份但是有警告（RUNNING WITH WARNINGS），正常完成（COMPLETED）、完成但是有错误（COMPLETED WITH ERRORS）等

RUNNING WITH WARNINGS

RUNNING WITH ERRORS

COMPLETED

COMPLETED WITH WARNINGS

COMPLETED WITH ERRORS

FAILED

SQL> select INPUT_TYPE,STATUS from v$rman_backup_job_details;

INPUT_TYPE    STATUS

------------- -----------------------

DB FULL       COMPLETED

DB FULL       COMPLETED    

DB FULL       FAILED

1. 查询过去和现在RMAN备份工作的详细情况

SQL> COL STATUS FORMAT a9

SQL> COL hours FORMAT 999.999

SQL> SELECT SESSION_KEY,

   INPUT_TYPE,

STATUS,

   TO_CHAR(START_TIME,'yyyy-mm-dd hh24:mi') start_time,

   TO_CHAR(END_TIME,'yyyy-mm-dd hh24:mi') end_time,

   ELAPSED_SECONDS/3600 hours

FROM V$RMAN_BACKUP_JOB_DETAILS

ORDER BY SESSION_KEY;

SESSION_KEY INPUT_TYPE    STATUS    START_TIME       END_TIME            HOURS

----------- ------------- --------- ---------------- ---------------- --------

      1 DB FULL       FAILED    2014-12-07 19:30 2014-12-07 19:30     .001

      3 DB FULL       COMPLETED 2014-12-07 19:49 2014-12-07 19:52     .043

     12 DB FULL       COMPLETED 2014-12-07 20:02 2014-12-07 20:05     .045

2.RMAN备份的速度，in_sec表示每秒的input速度，out_sec表示每秒的output速度

SQL>COL in_sec FORMAT a10

SQL>COL out_sec FORMAT a10

SQL>COL TIME_TAKEN_DISPLAY FORMAT a10

SQL>SELECT SESSION_KEY,

   OPTIMIZED,

   COMPRESSION_RATIO,

   INPUT_BYTES_PER_SEC_DISPLAY in_sec,

   OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,

   TIME_TAKEN_DISPLAY

FROM  V$RMAN_BACKUP_JOB_DETAILS

ORDER BY SESSION_KEY;         

SESSION_KEY OPT COMPRESSION_RATIO IN_SEC     OUT_SEC    TIME_TAKEN

----------- --- ----------------- ---------- ---------- ----------

      1 NO                  1     0.00K      0.00K  00:00:02

      3 NO         4.38910265     7.97M      1.82M  00:02:36

     12 NO         4.40249791     7.55M      1.71M  00:02:42

3.显示备份的大小。OUT_SIZE表示备份出的大小，IN_SIZE表示输入的数据总大小。

SQL>COL in_sec FORMAT a10

SQL>COL out_sec FORMAT a10

SQL>SELECT SESSION_KEY,

  INPUT_TYPE,

  COMPRESSION_RATIO,

  INPUT_BYTES_DISPLAY in_size,

  OUTPUT_BYTES_DISPLAY out_size

FROM  V$RMAN_BACKUP_JOB_DETAILS

ORDER BY SESSION_KEY;

SESSION_KEY INPUT_TYPE    COMPRESSION_RATIO IN_SIZE    OUT_SIZE

----------- ------------- ----------------- ---------- ----------

      1 DB FULL                       1     0.00K      0.00K

      3 DB FULL              4.38910265     1.21G    283.37M

     12 DB FULL              4.40249791     1.19G    277.69M

SQL> COL INPUT_BYTES_DISPLAY for a40

SQL> set linesize 500

SQL>select OUTPUT_DEVICE_TYPE,INPUT_TYPE,ELAPSED_SECONDS,INPUT_BYTES_DISPLAY,INPUT_BYTES_PER_SEC,OUTPUT_BYTES_PER_SEC

from v$rman_backup_job_details;

OUTPUT_DEVICE_TYP INPUT_TYPE    ELAPSED_SECONDS INPUT_BYTES_DISPLAY                      INPUT_BYTES_PER_SEC OUTPUT_BYTES_PER_SEC

----------------- ------------- --------------- ---------------------------------------- ------------------- --------------------

DISK              DB FULL                   162     1.19G                                         7913152.79           1797423.41

DISK              DB FULL                   156     1.21G                                         8359991.79           1904715.49

              DB FULL                     2     0.00K                                                  0                    0

INPUT_BYTES_DISPLAY 数据量                    

ELAPSED_SECONDS 耗时

INPUT_BYTES_PER_SEC 每秒读取IO

OUTPUT_BYTES_PER_SEC 每秒写出IO

备份时对数据文件的读取和写出backup piece到备份介质上的操作是一个整体，CPU、Input IO、Output IO任何一环都可能成为备份的瓶颈；

譬如因为对数据文件的读取IO存在瓶颈，那么相应的写出IO也会慢下来；又譬如当RMAN与备份服务器之间的IO带宽存在瓶颈，那么相应的读取IO也会不得不慢下来。

具体是哪一个环节出现了问题，我们需要求助于其他的RMAN动态性能视图，如:

V$BACKUP_SYNC_IO

Displays rows when the I/O is synchronous to the process (or thread on some platforms) performing the backup.

V$BACKUP_ASYNC_IO

Displays rows when the I/O is asynchronous to the process (or thread on some platforms) performing the backup.

以上2个视图的区别在于一个汇聚了使用同步IO执行RMAN备份恢复操作的性能信息，而另一个是异步IO的。