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 V ARCHAR2(33)

START_TIME DATE

END_TIME DA TE

INPUT_BYTES NUMBER

OUTPUT_BYTES NUMBER

STATUS_WEIGHT NUMBER

OPTIMIZED_WEIGHT NUMBER

OBJECT_TYPE_WEIGHT NUMBER

OUTPUT_DEVICE_TYPE V ARCHAR2(17) AUTOBACKUP_COUNT NUMBER

BACKED_BY_OSB V ARCHAR2(3) AUTOBACKUP_DONE V ARCHAR2(3) STATUS V ARCHAR2(23)

INPUT_TYPE V ARCHAR2(13) OPTIMIZED V ARCHAR2(3)

ELAPSED_SECONDS NUMBER COMPRESSION_RATIO NUMBER

INPUT_BYTES_PER_SEC NUMBER

OUTPUT_BYTES_PER_SEC NUMBER

INPUT_BYTES_DISPLAY V ARCHAR2(4000) OUTPUT_BYTES_DISPLAY V ARCHAR2(4000) INPUT_BYTES_PER_SEC_DISPLAY V ARCHAR2(4000) OUTPUT_BYTES_PER_SEC_DISPLAY V ARCHAR2(4000) TIME_TAKEN_DISPLAY V ARCHAR2(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

F AILED

SQL> SELECT INPUT_TYPE,STA TUS FROM V$RMAN_BACKUP_JOB_DETAILS;

INPUT_TYPE STA TUS

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

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_RA TIO 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_RA TIO 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