Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

What are the on-going RMAN backup jobs ?

Peter77Sep 29 2021 — edited Sep 29 2021

Database version: 12.1

I want to know if there are any RMAN backup jobs currently running. 
And if there are any RMAN jobs running, I want to know if it is an L0, L1 or Archive log backup.

Which view can I use for that ?

I ran the following query which uses v$rman_status view.
But, the END_TIME always shows the current time !
Plus, v$rman_status does not provide info on whether the backup which is currently being run is L0, L1 or Archive log
The only useful information I got from the below query is that there is an RMAN job which started running at 29-SEP-2021 20:40:13 , nothing much

SQL> select distinct status from V$RMAN_BACKUP_JOB_DETAILS;


STATUS
-----------------------
COMPLETED
FAILED


SQL>
SQL> SELECT status,
       operation,
       To_char(start_time, 'DD-MON-YYYY HH24:MI:SS') AS START_TIME,
       To_char(end_time, 'DD-MON-YYYY HH24:MI:SS')   AS END_TIME,
       To_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') current_time,
       MBYTES_PROCESSED
FROM   v$rman_status
WHERE  start_time > SYSDATE - 1
  2    3    4    5         AND status LIKE 'RUNNING%';   6    7    8    9


STATUS                  OPERATION                         START_TIME                    END_TIME                      CURRENT_TIME                  MBYTES_PROCESSED
----------------------- --------------------------------- ----------------------------- ----------------------------- ----------------------------- ----------------
RUNNING                 RMAN                              29-SEP-2021 20:40:13          29-SEP-2021 21:28:54          29-SEP-2021 21:28:54                         0


SQL>

Comments

Smohib

check this, if it helps
select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;
SELECT sid, round(sofar/totalwork*100)"%Comp", message
FROM v$session_longops
where totalwork != 0
AND sofar <> totalwork
order by sid
/
select session_id,status,error_msg from dba_resumable
/

Nikhil Kotak

use below scripts to see if backup is Incremental L0 or L1
Backup Check 1 : backup piece info
-----------------------------------
SELECT bs.recid, DECODE(backup_type, 'L', 'Archived Logs', 'D', 'Datafile Full', 'I', 'Incremental') backup_type,
device_type "type", DECODE(bs.controlfile_included, 'NO', null, bs.controlfile_included) controlfile,
sp.spfile_included spfile, bs.incremental_level L,TO_CHAR(bs.start_time, 'dd/mm/yyyy HH24:MI:SS') start_time
, TO_CHAR(bs.completion_time, 'dd/mm/yyyy HH24:MI:SS') completion_time, bs.elapsed_seconds "ELAPSED", bp.tag, bs.block_size "BLOCK"
FROM v$backup_set bs, (select distinct set_stamp, set_count, tag, device_type from v$backup_piece where status in ('A', 'X')) bp,
(select distinct set_stamp, set_count, 'YES' spfile_included from v$backup_spfile) sp
WHERE completion_time > sysdate -1
AND bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bs.set_stamp = sp.set_stamp (+)
AND bs.set_count = sp.set_count (+)
ORDER BY completion_time desc, bs.recid;

Backup check 2 : INCR and archivelog : Categorized
----------------------------------

set linesize 200 pagesize 2000
col time_taken for a30
col START_TIME for a30
col END_TIME for a30
-- Database backups with Incremental Level
select
to_char(START_TIME,'DD/MM/YYYY HH24:MI:SS') START_TIME, to_char(END_TIME,'DD/MM/YYYY HH24:MI:SS') END_TIME,
cast (END_TIME as TIMESTAMP) - cast (START_TIME as TIMESTAMP) as TIME_TAKEN,
round(INPUT_BYTES / 1024 / 1024) as INPUT_BYTES_MB, round(OUTPUT_BYTES / 1024 / 1024) as OUTPUT_BYTES_MB,STATUS,INPUT_TYPE,
case when x.i0 = 0 then 1 else 0 end Il
from V$RMAN_BACKUP_JOB_DETAILS j
join (select
d.session_recid, d.session_stamp,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
where j.input_type='DB INCR'
order by j.start_time desc;

select to_char(START_TIME,'DD/MM/YYYY HH24:MI:SS') START_TIME, to_char(END_TIME,'DD/MM/YYYY HH24:MI:SS') END_TIME,
cast (END_TIME as TIMESTAMP) - cast (START_TIME as TIMESTAMP) as TIME_TAKEN,round(INPUT_BYTES / 1024 / 1024) as
INPUT_BYTES_MB,round(OUTPUT_BYTES / 1024 / 1024) as OUTPUT_BYTES_MB,STATUS,INPUT_TYPE
from V$RMAN_BACKUP_JOB_DETAILS
where trunc(START_TIME)=trunc(sysdate)
order by 1 desc;

1 - 2

Post Details

Added on Sep 29 2021
#performance-availability, #recovery-manager-rman
2 comments
1,552 views