DB Wiki

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/oracle/backup/%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u03/oracle/backup/%U';

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

DELETE ARCHIVELOG ALL;
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'sysdate -7';

SET linesize 120
col name format a20
SELECT name, space_limit/1024/1024 SPACE_LIMIT_MB, 
             space_used/1024/1024 SPACE_USED_MB,
             space_reclaimable/1024/1024 SPACE_RECLAIMABLE_MB,
             NUMBER_OF_FILES FROM V$RECOVERY_FILE_DEST;
 
SELECT * FROM V$RECOVERY_AREA_USAGE;

-- Active RMAN Job
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM  V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR <> TOTALWORK
 

-- Last RMAN Operation
col object_type format a15
ALTER SESSION SET nls_date_format='DD-MON-YYYY hh24:mi:ss';
 
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;

<code SQL> set lines 300 set pages 1000 col cf for 99 col df for 99 col elapsed_seconds heading “ELAPSED|SECONDS” col i0 for 99 col i1 for 99 col l for 99 col output_mbytes for 9,999,999 heading “OUTPUT|MBYTES” col session_recid for 9999999 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 9999 heading “OUT|INST”

col start_time for a25 col end_time for a25 col START_DOW for a10

col L for 999

         col INPUT_BYTES_DISPLAY for a9 heading "INPUT|SIZE"
         col OUTPUT_BYTES_DISPLAY for a9 heading "OUTPUT|SIZE"
         col INPUT_BYTES_PER_SEC_DISPLAY for a9 heading "INPUT|PERSEC"
         col OUTPUT_BYTES_PER_SEC_DISPLAY for a9 heading "OUTPUT|PERSEC"

select

 j.session_recid, j.session_stamp,
 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,
 to_char(j.start_time, 'DAY') START_DOW,

– j.elapsed_seconds, j.time_taken_display,

                                   INPUT_BYTES_DISPLAY,
                                   OUTPUT_BYTES_DISPLAY,
                                   INPUT_BYTES_PER_SEC_DISPLAY,
                                   OUTPUT_BYTES_PER_SEC_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 = 'I0' 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 order by j.start_time; <code>