模拟测试
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;