查看rman备份信息

alter session set nls_date_format='yyyymmdd hh24:mi:ss';
col tag for a20
set lin 200 pages 200
col handle for a30
col SIZE_BYTES_DISPLAY for a10
select BS.RECID,BS.START_TIME,BS.PIECES,BS.INCREMENTAL_LEVEL,BS.BACKUP_TYPE,BP.PIECE#,BP.HANDLE,BP.TAG,BP.STATUS,ROUND(BP.bytes/1024/1024/1024,2) gb
from v$backup_set bs,v$backup_piece bp where bs.recid=bp.recid
--and tag like 'TAG20200406%'
and bp.HANDLE not like 'c-2051169266-%' and BS.BACKUP_TYPE!='L' and BS.START_TIME>to_date('20200401 08:00:00','yyyymmdd hh24:mi:ss');
select 'change backupset '||BS.RECID||' unavailable;'
from v$backup_set bs,v$backup_piece bp where bs.recid=bp.recid
and tag like 'TAG20200407%'
and bp.HANDLE not like 'c-2051169266-%' and BS.BACKUP_TYPE!='L' and BS.START_TIME>to_date('20200405 08:00:00','yyyymmdd hh24:mi:ss');
1.查询过去和现在RMAN备份工作的详细情况
COL INPUT_TYPE FORMAT a20
COL STATUS FORMAT a40
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 99999999999
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;
4.查看未完成的RMAN备份进度
set line 9999
col opname for a35
col start_time for a19
col TOTALWORK for 999999999999999999
SELECT SID,SERIAL#,opname, to_char(start_time,'yyyy-mm-dd HH24:MI:SS') start_time, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%COMPLETE",ceil(ELAPSED_SECONDS/60) ELAPSED_MI
FROM V$SESSION_LONGOPS where opname like 'RMAN%' AND SOFAR <> TOTALWORK and TOTALWORK<>0 order by start_time asc;
--recover进度
col type format a15
select START_TIME,TYPE,ITEM,TIMESTAMP from v$recovery_progress;