Forum Stats

  • 3,826,323 Users
  • 2,260,624 Discussions
  • 7,896,896 Comments

Discussions

What are the on-going RMAN backup jobs ?

Peter77
Peter77 Member Posts: 10 Green Ribbon
edited Sep 29, 2021 7:30PM in Recovery Manager (RMAN)

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>



Answers

  • Smohib
    Smohib Member Posts: 221 Blue Ribbon

    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
    Nikhil Kotak Member Posts: 5 Red Ribbon

    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;