不写了,从文库转搜了一篇记录一下
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
- 查询过去和现在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的。