V$RMAN_BACKUP_JOB_DETAILS

模拟测试

V$RMAN_BACKUP_JOB_DETAILS 详解

V$RMAN_BACKUP_JOB_DETAILS

V$RMAN_BACKUP_JOB_DETAILS展示了rman备份的相关细节。
比如，rman备份持续时间、rman备份的执行次数、每一次rman备份工作的状态（failed or completed and so on），
每次rman备份的起始时间等等。

SQL> desc sys.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

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

COL STATUS FORMAT a9
COL hours    FORMAT 999.999
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;

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

COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT a10
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;      

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

COL in_size  FORMAT a10
COL out_size FORMAT a10
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;

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

4. 备份情况

set pagesize 1000 linesize 200  
col command_id for a20  
col start_time for a20  
col time_taken_display for a10  
col status for a25  
col input_type for a13  
col output_device_type for a10  
col input_bytes_display for a10  
col output_bytes_display for a10
COL in_sec FORMAT a10
COL out_sec FORMAT a10  
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';  
select * from  
(select    
  SESSION_KEY
  ,start_time  
  , end_time  
  , time_taken_display  
  , status  
  , input_type  
  , output_device_type 
  ,INPUT_BYTES_PER_SEC_DISPLAY in_sec
  ,OUTPUT_BYTES_PER_SEC_DISPLAY out_sec
  , input_bytes_display 
  , output_bytes_display 
from v$rman_backup_job_details 
order by start_time DESC) 
where rownum<30
/

select to_char(START_TIME, 'yyyy-mm-dd'), STATUS, TIME_TAKEN_DISPLAY, INPUT_TYPE from v$rman_backup_job_details;