Search This Blog

Total Pageviews

Sunday 3 September 2017

Rman backup status from sqlplus

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


Oracle DBA

anuj blog Archive