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 I NPUT 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 th