Home » RDBMS Server » Backup & Recovery » incremental 0/1 and full backup status
incremental 0/1 and full backup status [message #537310] Tue, 27 December 2011 12:23 Go to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
All:

I am using the following query to determine if my rman backup
succeeded for failed. I look for "COMPLETED WITH ERRORS"


col input_type format a10
   col bck_hrs format 99.9 heading "Run|Time"
   col status format a21
   col end_dt format a20 heading "End|Time"
   col mbytes_per_sec format 9,999 heading "Output|Rate|MB/sec"
   col gb_out format 999,999.9 heading "Output|Size GB"

   select input_type, status, to_char(end_time,'YYYY-MM-DD HH24:MI:SS') end_dt,
        (end_time - start_time) *24 bck_hrs,
        output_bytes/1024/1024/1024 gb_out,
        output_bytes_per_sec/1024/1024 mbytes_per_sec
        from v$rman_backup_job_details
        where
          end_time >= to_date('&start_backup_time','MM-DD-YYYY HH24:MI:SS') AND
          end_time <= to_date('&end_backup_time','MM-DD-YYYY HH24:MI:SS')
order by end_dt desc;


output
======

                                 End                    Run     Output   Rate
INPUT_TYPE STATUS                Time                  Time    Size GB MB/sec
---------- --------------------- -------------------- ----- ---------- ------
ARCHIVELOG COMPLETED             2011-12-24 06:03:54     .5      189.4    106
DB INCR    COMPLETED             2011-12-24 05:33:05    9.3    3,392.6    103
ARCHIVELOG COMPLETED             2011-12-23 10:12:27     .2       73.3    105




I know that the DB INCR is an INCR 0 backup but
is there some way query I can join with my example above to
tell me this is INCR 0 or FULL BKUP?

I was thinking maybe setting "COMMAND_ID" some text like
INCR 0 or INCR 1 or FULL BKUP. Does that sound feasible

Thanks to all who answer

Thanks to all who answer
Re: incremental 0/1 and full backup status [message #537318 is a reply to message #537310] Tue, 27 December 2011 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65258
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fro a full the type is "DB FULL", for an incremental (level 0 or 1) the type is "DB INCR".

Regards
Michel
Re: incremental 0/1 and full backup status [message #537326 is a reply to message #537318] Tue, 27 December 2011 13:14 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
I know that... let me re-phrase how can I tell if its a INCR 0 or INCR 1
Re: incremental 0/1 and full backup status [message #537342 is a reply to message #537326] Tue, 27 December 2011 14:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10679
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Not from database views.
You can get some info from recovery catalog views like RC_BACKUP_SET_DETAILS
Re: incremental 0/1 and full backup status [message #537472 is a reply to message #537342] Wed, 28 December 2011 14:33 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
How do I tie the two tables together to get what I need?
Re: incremental 0/1 and full backup status [message #537475 is a reply to message #537472] Wed, 28 December 2011 14:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65258
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Join on SESSION_KEY but there are not in the same database.

Regards
Michel
Re: incremental 0/1 and full backup status [message #659760 is a reply to message #537310] Sun, 29 January 2017 22:55 Go to previous message
anuragjec
Messages: 1
Registered: January 2017
Junior Member
You can use V$BACKUP_SET_DETAILS view to identify whether its INCR0 or INCR1. Try this query:

set lines 220
set 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 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  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)-7
order by j.start_time;
Previous Topic: Restoring Unencrypted Backups to a TDE Encrypted Database?
Next Topic: how get 12c data when windows 7 crashed
Goto Forum:
  


Current Time: Mon Dec 18 13:41:39 CST 2017

Total time taken to generate the page: 0.03979 seconds