What are the on-going RMAN backup jobs ?

Peter77

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>

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;