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
