Rman backup status from sqlplus> ..

Rman backup status 

Rman backup output via sql  

v$rman_output to review  backups

for status ... 

http://anuj-singh.blogspot.com/2017/05/rman-sql-to-find-backup-details.html?m=1

-- v$rman_output goes away with a restart

set linesize 200  pagesize 200

select output

from v$rman_output where session_recid = (select max(session_recid) from v$rman_status)

order by recid ;

OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

connected to target database: GRAINUAT (DBID=2803380037, not open)

using target database control file instead of recovery catalog

old RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

allocated channel: c1

channel c1: SID=134 device type=DISK

or 

set linesize 200  pagesize 200

select output

from v$rman_output where session_recid in (select session_recid from v$rman_status WHERE operation = 'BACKUP' AND status != 'COMPLETED' AND status != 'RUNNING')

order by recid ;

====

set linesize 200  pagesize 200

select output

from v$rman_output where session_recid in (select session_recid from v$rman_status

where start_time > sysdate -7)

order by recid ;

set linesize 200  pagesize 200

select output

from v$rman_output where session_recid in (select session_recid from v$rman_status

where start_time > sysdate -7)

order by recid ;

OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

connected to target database: GRAINUAT (DBID=2803380037, not open)

using target database control file instead of recovery catalog

old RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

allocated channel: c1

channel c1: SID=134 device type=DISK

allocated channel: c2

channel c2: SID=167 device type=DISK

alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';

set  linesize 200

select object_type,mbytes_processed, start_time, end_time,status

from v$rman_status

where session_recid = (select max(session_recid) from v$rman_status) and operation !='RMAN'

order by recid

OBJECT_TYPE     MBYTES_PROCESSED START_TIME           END_TIME             STATUS

--------------- ---------------- -------------------- -------------------- -----------------------

ARCHIVELOG                    15 03-SEP-2017 14:54:43 03-SEP-2017 14:54:48 COMPLETED

DB INCR               199589.594 03-SEP-2017 14:54:48 03-SEP-2017 18:17:23 RUNNING

=============

set linesize 300 pagesize 300

-- TTITLE LEFT '% Completed. Aggregate is the overall progress:'

SET LINE 300

col SKILL for a15

SELECT 

''''||sid ||','|| serial#||',@'||inst_id ||'''' Skill,opname, round(sofar/totalwork*100) "% Complete"   FROM gv$session_longops

WHERE opname LIKE 'RMAN%'

AND totalwork != 0

AND sofar <> totalwork

 ORDER BY 1;

set linesize 300 pagesize 300

col info for a120

select  'Session info  '||skill||info Rman_Session_info  from (SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' Skill,'Throughput: '||  ROUND(SUM(v.value/1024/1024),1) || ' Meg so far @ ' || ROUND(SUM(v.value /1024/1024)/NVL((SELECT MIN(elapsed_seconds) info

                FROM gv$session_longops

                WHERE opname  LIKE 'RMAN: aggregate input'

                AND sofar != TOTALWORK

                AND elapsed_seconds IS NOT NULL

           ),SUM(v.value /1024/1024)),2) || ' Meg/sec' info

     FROM gv$sesstat v, v$statname n, gv$session s

  WHERE v.statistic# = n.statistic#

  AND n.name       = 'physical write total bytes'

  AND v.sid        = s.sid

  AND v.inst_id    = s.inst_id

  AND s.program LIKE 'rman@%'

  GROUP BY ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''', n.name)

;

-- TTITLE LEFT 'Channels waiting...'

COL client_info FORMAT A15 TRUNC

COL event FORMAT A30 

COL state FORMAT A30

COL wait FORMAT 999.90 HEAD "Min waiting"

col SKILL for a15

SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' Skill, p.spid, s.client_info, status, event, state, seconds_in_wait/60 wait   FROM gv$process p, gv$session s

WHERE p.addr = s.paddr

AND client_info LIKE 'rman%';

-- TTITLE LEFT 'Files currently being written to...'

set numf 9999999999999

COL filename FORMAT a80

SELECT filename, bytes, io_count FROM gv$backup_async_io

 WHERE status='IN PROGRESS'

/

=====

define _start prompt='sysdate-1'

define _errors_only='y'

col command_id  for a19

col start_time  for a10

col end_time    for a10

COL RECID           NEW_VAL P_RECID       NOPRINT

COL COMMAND_ID      NEW_VAL P_COMMAND_ID  NOPRINT

COL START_TIME      NEW_VAL P_START_TIME  NOPRINT

COL END_TIME        NEW_VAL P_END_TIME    NOPRINT

COL OPERATION       NEW_VAL P_OPERATION   NOPRINT

COL STATUS          NEW_VAL P_STATUS      NOPRINT

COL LINE#       NOPRINT;

break on recid on command_id on start_time on end_time on operation on status skip page;

set pause off;

ttitle left -

     '###############################################################################################################' skip 1-

     '# RECID      : ' P_RECID       skip 1-

     '# COMMAND_ID : ' P_COMMAND_ID  skip 1-

     '# START_TIME : ' P_START_TIME  skip 1-

     '# END_TIME   : ' P_END_TIME    skip 1-

     '# OPERATION  : ' P_OPERATION   skip 1-

     '# STATUS     : ' P_STATUS      skip 2;

with 

 st as (

   select/*+ materialize */

     --s.sid,

     --s.parent_recid,

     s.recid,command_id

    ,to_char(start_time,'hh24:mi:ss') start_time

    ,to_char(end_time  ,'hh24:mi:ss') end_time

    ,operation||' '||object_type      operation

    ,status                      status

   from v$rman_status s 

   where start_time >= case 

                            when '&_start' is null then trunc(sysdate-3) 

                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d') then to_date('&_start','yyyy-mm-dd hh24:mi:ss')

                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d') then to_date('&_start','yyyy-mm-dd')

                            when regexp_like('&_start','^\d+$') then sysdate-to_number('&_start')

                            else trunc(sysdate)

                        end

   and s.status not in ('RUNNING','COMPLETED') 

 )

   select --distinct command_id||' '||operation||' '||object_type||' '||status  errors 

     st.*

    --,o.recid as line#

    ,o.output

   from st

       ,v$rman_output o

   where 

     o.RMAN_STATUS_RECID=st.recid

     and (  o.output not like 'Backup Set%'

        and o.output not like '  Backup Piece%'

        and o.output not like 'input%'

        and o.output not like 'channel%'

        and o.output not like 'piece%'

     )

     and (

           lower('&_errors_only')!='y' 

            or 

          (o.output like 'RMAN-%' 

            and substr(o.output,1,10) not in (

                                        'RMAN-06210', 

                                        'RMAN-06211', 

                                        'RMAN-06212', 

                                        'RMAN-06213', 

                                        'RMAN-06214')

         ))

   order by --command_id,start_time,

     st.recid,o.recid;

undef _start

clear break

====

define _start=''

select recid, parent_recid, row_type, command_id, operation, object_type, status

from v$rman_status 

where start_time >= case 

                        when '&_start' is null then trunc(sysdate-3) 

                        when regexp_like('&_start','\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d') then to_date('&_start','yyyy-mm-dd hh24:mi:ss')

                        when regexp_like('&_start','\d\d\d\d-\d\d-\d\d') then to_date('&_start','yyyy-mm-dd')

                        when regexp_like('&_start','^\d+$') then sysdate-to_number('&_start')

                        else trunc(sysdate)

                    end

order by start_time desc;

undef _start

###############################################################################

# RECID      :      48091

# COMMAND_ID : 2022-04-11T21:02:29

# START_TIME : 21:19:28

# END_TIME   : 21:21:19

# OPERATION  : RMAN

# STATUS     : COMPLETED

PARENT_RECID ROW_TYPE            OBJECT_TYPE

------------ ------------------- -------------

             SESSION

===

define _start=''

col command_id  for a19

col start_time  for a10

col end_time    for a10

COL RECID           NEW_VAL P_RECID       NOPRINT

COL COMMAND_ID      NEW_VAL P_COMMAND_ID  NOPRINT

COL START_TIME      NEW_VAL P_START_TIME  NOPRINT

COL END_TIME        NEW_VAL P_END_TIME    NOPRINT

COL OPERATION       NEW_VAL P_OPERATION   NOPRINT

COL STATUS          NEW_VAL P_STATUS      NOPRINT

COL LINE#       NOPRINT

break on recid on command_id on start_time on end_time on operation on status skip page;

set pause on;

ttitle left -

     '###############################################################################################################' skip 1-

     '# RECID      : ' P_RECID       skip 1-

     '# COMMAND_ID : ' P_COMMAND_ID  skip 1-

     '# START_TIME : ' P_START_TIME  skip 1-

     '# END_TIME   : ' P_END_TIME    skip 1-

     '# OPERATION  : ' P_OPERATION   skip 1-

     '# STATUS     : ' P_STATUS      skip 2;

with 

 st as (

   select/*+ materialize */

     --s.sid,

     --s.parent_recid,

     s.recid,command_id

    ,to_char(start_time,'hh24:mi:ss') start_time

    ,to_char(end_time  ,'hh24:mi:ss') end_time

    ,operation||' '||object_type      operation

    ,status                      status

   from v$rman_status s 

   where start_time >= case 

                            when '&_start' is null then trunc(sysdate-1) 

                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d') then to_date('&_start','yyyy-mm-dd hh24:mi:ss')

                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d') then to_date('&_start','yyyy-mm-dd')

                            when regexp_like('&_start','^\d+$') then sysdate-to_number('&_start')

                            else trunc(sysdate)

                        end

   and s.status not in ('RUNNING','COMPLETED') 

 )

   select --distinct command_id||' '||operation||' '||object_type||' '||status  errors 

     st.*

    --,o.recid as line#

    ,o.output

   from st

       ,v$rman_output o

   where 

     o.RMAN_STATUS_RECID=st.recid

     and (  o.output not like 'Backup Set%'

        and o.output not like '  Backup Piece%'

        and o.output not like 'input%'

        and o.output not like 'channel%'

        and o.output not like 'piece%'

     )

   order by --command_id,start_time,

     st.recid,o.recid;

undef _start

################################################################################

# RECID      :      48105

# COMMAND_ID : 2022-04-13T21:17:28

# START_TIME : 21:17:28

# END_TIME   : 21:17:32

# OPERATION  : RMAN

# STATUS     : COMPLETED WITH ERRORS

OUTPUT

--------------------------------------------------------------------------------

 unavailable;

==========

define NUMBER_OF_DAYS=15

set lines 300 pages 1000

col cf for 9,999

col df for 9,999

col elapsed_seconds heading "ELAPSED|SECONDS"

col i0 for 9,999

col i1 for 9,999

col l for 9,999

col output_mbytes for 999,999,999,999,999,999 heading "OUTPUT|MBYTES"

col session_recid for 999999 heading "SESSION|RECID"

col session_stamp for 99999999999 heading "SESSION|STAMP"

col status for a10 trunc

col time_taken_display for a10 heading "TIME|TAKEN"

col output_instance for 999999999 heading "OUT|INST"

col START_TIME  for a24

col END_TIME for a24

col Sday for a14

col eday for a14

select

j.session_recid, j.session_stamp,

 (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,

to_char(j.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,

to_char(j.end_time, 'dd-mm-yyyy hh24:mi:ss') end_time

,to_char(j.start_time,'DAY','nls_date_language=ENGLISH') Sday

,to_char(j.end_time,'DAY','nls_date_language=ENGLISH') Eday

 --, j.elapsed_seconds

 , j.time_taken_display,

  x.cf, x.df, x.i0, x.i1, x.l,

  ro.inst_id output_instance

from V$RMAN_BACKUP_JOB_DETAILS j

  left outer join (select

                     d.session_recid, d.session_stamp,

                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,

                     sum(case when d.controlfile_included = 'NO'

                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,

                     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,

                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L

                   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

  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id

                   from GV$RMAN_OUTPUT o

                   group by o.session_recid, o.session_stamp)

    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp

where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS

--and j.input_type='DB FULL'  ------------<<<<<<<<<<<<<<<<<

--and j.output_bytes/1024/1024 > 19000000   --- for Big backup only

-- and j.output_bytes/1024/1024 > 566579

order by j.start_time;

https://anuj-singh.blogspot.com/search?q=gv%24backup_sync_io