RMAN Backup File Order

RMAN BACKUP FILE ORDER

Question:
Assuming a single channel and filesperset=1, what order does RMAN choose to backup datafiles?

Answer:
The order of files is random. The File Names are read from V$DATAFILE view and due to performance bug, the ORDER BY FILE# clause is removed, hence the query can return file# in random order.

Track RMAN Job Process via gv$session_longops

Nice way to track RMAN Channel worker progress

Also useful: watch -n 10 sqlplus -s usr/pwd [this script].sql 

set linesize 120
column pct_done format '999.99'
column opname format a35
column time_left format a15
column started format a15
select
  sid,
  opname,
  to_char(start_time,'DD-MON HH24:MI') started,
  round(totalwork-sofar) blocks_left,
 (sofar/totalwork) * 100 pct_done,
  to_char(to_date(time_remaining,'sssss'),'hh24:mi:ss') time_left
from
   gv$session_longops
where
   totalwork > sofar
AND
   opname NOT LIKE '%aggregate%'
AND
   opname like 'RMAN%'
order by 2;
exit;

Safely Terminate an RMAN Job

This will help avoid leaving zombie processes

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from gv$session where MODULE like 'rman%';

or

select inst_id,sid,serial# from gv$session where MODULE like 'rman%';
alter system kill session 'sid,serial' immediate;

For Incremental backups:
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from gv$session where MODULE = 'backup incr datafile';

How to Enable and Disable Block Change Tracking

ENABLE

1. Verify BCT is disabled

   SELECT status FROM v$block_change_tracking;

2. Enable BCT with the database OPEN
   alter database enable block change tracking using file '+MY_FRA';
    Database altered.

3. Query BCT
    set lines 100
    col filename format a50
    col size_mb format 99,999
    select status,filename,bytes/1024/1024 size_mb from v$block_change_tracking;

    STATUS     FILENAME                                           SIZE_MB
    ---------- -------------------------------------------------- -------
    ENABLED    +MY_FRA/mydb/changetracking/ctf.6151.791995761        11

DISABLE

1. Verify BCT is enabled

   SELECT status FROM v$block_change_tracking;

2. Enable BCT with the database OPEN
   alter database disable block change tracking;
    Database altered.

RMAN Backup History

RMAN Backup History

This is especially helpful information for incremental RMAN backups.  This particular query returns results from Sat @ midnight forward (assuming level 0 backup is run on Sunday)

prompt
prompt #############
prompt BACKUP STATUS
prompt #############
set feedback off
set linesize 100
set pagesize 50
col start_time         heading 'Started'      format a13
col backup_type        heading 'Backup Type'  format a12
col time_taken_display heading 'Elapsed|Time' format a10
col elapsed_min        heading 'Run|Min'      format 999
col output_mbytes      heading 'Size MB'      format 9,999,999
col backup_status      heading 'Status'       format a10 trunc
col cf                 heading 'Ctrl|Files'   format 9,999
col dfiles             heading 'Data|Files'   format 9,999
col l                  heading 'Arch|Files'   format 9,999
col output_instance    heading 'Ran on|Inst'  format 9
select
  to_char(j.start_time, 'Dy hh24:mi:ss') start_time,
  decode(j.input_type,'DB INCR',decode(i0,0,'Incr Lvl 1','Incr Lvl 0'),initcap(j.input_type)) backup_type,
  j.time_taken_display,
  j.elapsed_seconds/60 elapsed_min,
  (j.output_bytes/1024/1024) output_mbytes,
  initcap(j.status) backup_status,
  x.cf,
  x.i0 + x.i1 dfiles,
  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(next_day(sysdate-6,'SUNDAY'))
order by j.start_time
/