RMAN backups


Find DB in RMAN catalog

set linesize 230
set pagesize 9999
select dbid, db_key, name,  
to_char(RESETLOGS_TIME, 'dd/mm/yyyy hh24:mi:ss') resetlogs 
from rc_database where upper(name) like upper('%&DBNAME%');

Find backup in RMAN catalog (DBName and days back) by dbname

SET LINESIZE 200
SET PAGESIZE 9999
SET VERIFY   OFF

COLUMN name		       		FORMAT a12                HEADING 'DB Name'
COLUMN start_time           FORMAT a20    			  HEADING 'Start time'
COLUMN completion_time      FORMAT a20    			  HEADING 'End time'
COLUMN backup_type     		FORMAT a3                 HEADING 'BType'
COLUMN incremental_level    FORMAT 9				  HEADING 'ILevel'
COLUMN device_type  		FORMAT a15                HEADING 'Device Type'
COLUMN media	    		FORMAT a15        		  HEADING 'Media'
COLUMN handle        		FORMAT a20	     		  HEADING 'Handle'
COLUMN tag        			FORMAT a30	     		  HEADING 'Tag'

select db.name, bp.START_TIME, bp.completion_time, bp.BACKUP_TYPE, bp.INCREMENTAL_LEVEL,
	bp.DEVICE_TYPE, bp.MEDIA, bp.handle, bp.TAG
	from rc_backup_piece bp, rc_database db
	where upper(name) = '&DBNAME'
	and bp.db_key = db.db_key
	and start_time > sysdate - &days_back
	order by start_time;

Find backup in RMAN catalog (DBName and days back) by dbid

SET LINESIZE 200
SET PAGESIZE 9999
SET VERIFY   OFF

COLUMN name		       		FORMAT a12                HEADING 'DB Name'
COLUMN start_time           FORMAT a20    			  HEADING 'Start time'
COLUMN completion_time      FORMAT a20    			  HEADING 'End time'
COLUMN backup_type     		FORMAT a3                 HEADING 'BType'
COLUMN incremental_level    FORMAT 9				  HEADING 'ILevel'
COLUMN device_type  		FORMAT a15                HEADING 'Device Type'
COLUMN media	    		FORMAT a15        		  HEADING 'Media'
COLUMN handle        		FORMAT a20	     		  HEADING 'Handle'
COLUMN tag        			FORMAT a30	     		  HEADING 'Tag'

select db.dbid, db.name, bp.START_TIME, bp.completion_time, bp.BACKUP_TYPE, bp.INCREMENTAL_LEVEL,
	bp.DEVICE_TYPE, bp.MEDIA, bp.handle, bp.TAG
	from rc_backup_piece bp, rc_database db
	where db.dbid = &dbid
	and bp.db_key = db.db_key
	and start_time > sysdate - &days_back
	order by start_time;

Find backup in RMAN catalog (DBNAME, start_date and end_date)

SET LINESIZE 200
SET PAGESIZE 9999
SET VERIFY   OFF

COLUMN name                 FORMAT a12                HEADING 'DB Name'
COLUMN start_time           FORMAT a20  	    HEADING 'Start time'
COLUMN completion_time      FORMAT a20    	    HEADING 'End time'
COLUMN backup_type          FORMAT a3                 HEADING 'BType'
COLUMN incremental_level    FORMAT 9		    HEADING 'ILevel'
COLUMN device_type          FORMAT a15                HEADING 'Device Type'
COLUMN media	        FORMAT a15        	    HEADING 'Media'
COLUMN handle               FORMAT a20	     	    HEADING 'Handle'
COLUMN tag        	        FORMAT a30	     	    HEADING 'Tag'

select db.name, bp.START_TIME, bp.completion_time, bp.BACKUP_TYPE, bp.INCREMENTAL_LEVEL,
	bp.DEVICE_TYPE, bp.MEDIA, bp.handle, bp.TAG
	from rc_backup_piece bp, rc_database db
	where upper(name) = '&DBNAME'
	and bp.db_key = db.db_key
	and start_time between to_date('&START_DATE', 'dd/mm/yyyy hh24:mi') and to_date('&END_DATE', 'dd/mm/yyyy hh24:mi')
	order by start_time;

List of INCR1 backups with break up by day of week (Sun - Sat)

SET LINESIZE 230
SET PAGESIZE 9999
SET VERIFY   OFF

alter session set NLS_DATE_FORMAT='dd/mm/yyyy';

COLUMN DOW		       		FORMAT a12                HEADING 'Day of week'
COLUMN start_date           FORMAT a10    			  HEADING 'Start date'
COLUMN run_time      		FORMAT 999.99			  HEADING 'Run time (min)'

break on report on DOW skip 1

select to_char(bp.START_TIME, 'D') DOW, trunc(start_time) start_date, max(round((bp.completion_time - bp.START_TIME)*24*60, 2)) run_time
	from rc_backup_piece bp, rc_database db
	where db.db_key = &db_key
	and bp.db_key = db.db_key
	and start_time > sysdate - &days_back
	and bp.INCREMENTAL_LEVEL = 1
	group by to_char(bp.START_TIME, 'D'), trunc(start_time)
	order by to_char(bp.START_TIME, 'D'), trunc(start_time);

Find all databases where used_change_tracking = 'YES' (last 30 days)

set linesize 230
set pagesize 999

select distinct db_key, db_name
from rc_backup_datafile  
where completion_time > sysdate - 30  
and used_change_tracking = 'YES';

Find archivelogs between specified dates

SET LINESIZE 200
SET PAGESIZE 9999
SET VERIFY   OFF

COLUMN start_time           FORMAT a20    			  HEADING 'Start time'
COLUMN completion_time      FORMAT a20    			  HEADING 'End time'
COLUMN handle        		FORMAT a20	     		  HEADING 'Handle'
COLUMN tag        			FORMAT a30	     		  HEADING 'Tag'
	  
select r.sequence#, p.handle, p.tag, p.start_time, p.completion_time
from RC_BACKUP_PIECE p, RC_BACKUP_REDOLOG r
where r.set_stamp = p.set_stamp 
and r.set_count = p.set_count
and p.start_time between to_date('&begin_date', 'dd/mm/yyyy hh24:mi') and to_date('&end_date', 'dd/mm/yyyy hh24:mi')
and p.db_key = &db_key
order by sequence#;

Latest backup jobs (from controlfile)

set linesize 230
set pagesize 9999
COLUMN INPUT_TYPE      		FORMAT a15		  		HEADING 'Input Type'
COLUMN RMAN_Status 			FORMAT a10		  		HEADING 'Status'
COLUMN Start_time			FORMAT a17		  		HEADING 'Start Time'
COLUMN End_time				FORMAT a17		  		HEADING 'End Time'
COLUMN Runtime_Min			FORMAT 999,999.99		HEADING 'Run Time (min)'
COLUMN Input_MB				FORMAT 999,999,999.99	HEADING 'Input size (MB)'
COLUMN Output_MB			FORMAT 999,999,999.99	HEADING 'Output size (MB)'
COLUMN compression_ratio	FORMAT 999.99		  	HEADING 'Compr. Ratio'

SELECT INPUT_TYPE,
          STATUS                                    RMAN_Status,
           TO_CHAR(START_TIME,'dd/mm/yyyy hh24:mi') Start_Time,
           TO_CHAR(END_TIME,'dd/mm/yyyy hh24:mi')   end_Time,
       ELAPSED_SECONDS/60                  Runtime_Min,
	   Input_bytes/1024/1024	Input_MB,
	   Output_bytes/1024/1024	Output_MB,
	   compression_ratio
        FROM V$RMAN_BACKUP_JOB_DETAILS
    ORDER BY SESSION_KEY desc
/

Latest backup jobs (from RMAN catalog)

set linesize 230
set pagesize 9999
COLUMN INPUT_TYPE      		FORMAT a15		  		HEADING 'Input Type'
COLUMN RMAN_Status 			FORMAT a10		  		HEADING 'Status'
COLUMN Start_time			FORMAT a17		  		HEADING 'Start Time'
COLUMN End_time				FORMAT a17		  		HEADING 'End Time'
COLUMN Runtime_Min			FORMAT 999,999.99		HEADING 'Run Time (min)'
COLUMN Input_MB				FORMAT 999,999,999.99	HEADING 'Input size (MB)'
COLUMN Output_MB			FORMAT 999,999,999.99	HEADING 'Output size (MB)'
COLUMN compression_ratio	FORMAT 999.99		  	HEADING 'Compr. Ratio'

SELECT INPUT_TYPE,
          STATUS                                    RMAN_Status,
           TO_CHAR(START_TIME,'dd/mm/yyyy hh24:mi') Start_Time,
           TO_CHAR(END_TIME,'dd/mm/yyyy hh24:mi')   end_Time,
       ELAPSED_SECONDS/60                  Runtime_Min,
	   Input_bytes/1024/1024	Input_MB,
	   Output_bytes/1024/1024	Output_MB,
	   compression_ratio
        FROM RC_RMAN_BACKUP_JOB_DETAILS
	where db_key = &db_key and start_time > sysdate - &days_back
    ORDER BY SESSION_KEY desc
/

RMAN backup sets (from controlfile)

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN bs_key                 FORMAT 999999                 HEADING 'BS|Key'
COLUMN backup_type            FORMAT a13                    HEADING 'Backup|Type'
COLUMN device_type            FORMAT a6                     HEADING 'Device|Type'
COLUMN controlfile_included   FORMAT a11                    HEADING 'Controlfile|Included?'
COLUMN spfile_included        FORMAT a9                     HEADING 'SPFILE|Included?'
COLUMN incremental_level      FORMAT 999999                 HEADING 'Inc.|Level'
COLUMN pieces                 FORMAT 9,999                  HEADING '# of|Pieces'
COLUMN start_time             FORMAT a17                    HEADING 'Start|Time'
COLUMN completion_time        FORMAT a17                    HEADING 'End|Time'
COLUMN elapsed_seconds        FORMAT 999,999                HEADING 'Elapsed|Seconds'
COLUMN tag                    FORMAT a19                    HEADING 'Tag'
COLUMN block_size             FORMAT 9,999                  HEADING 'Block|Size'

prompt
prompt Available backup sets contained in the control file.
prompt Includes available and expired backup sets.
prompt 

SELECT
    bs.recid                                              bs_key
  , DECODE(backup_type
           , 'L', 'Archived Logs'
           , 'D', 'Datafile Full'
           , 'I', 'Incremental')                          backup_type
  , device_type                                           device_type
  , DECODE(   bs.controlfile_included
            , 'NO', null
            , bs.controlfile_included)                    controlfile_included
  , sp.spfile_included                                    spfile_included
  , bs.incremental_level                                  incremental_level
  , bs.pieces                                             pieces
  , TO_CHAR(bs.start_time, 'mm/dd/yy HH24:MI:SS')         start_time
  , TO_CHAR(bs.completion_time, 'mm/dd/yy HH24:MI:SS')    completion_time
  , bs.elapsed_seconds                                    elapsed_seconds
  , bp.tag                                                tag
  , bs.block_size                                         block_size
FROM
    v$backup_set                           bs
  , (select distinct
         set_stamp
       , set_count
       , tag
       , device_type
     from v$backup_piece
     where status in ('A', 'X'))           bp
 ,  (select distinct
         set_stamp
       , set_count
       , 'YES'     spfile_included
     from v$backup_spfile)                 sp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bs.set_stamp = sp.set_stamp (+)
  AND bs.set_count = sp.set_count (+)
ORDER BY
    bs.recid
/

RMAN backup pieces (from controlfile)

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN bs_key              FORMAT 9999          HEADING 'BS|Key'
COLUMN piece#              FORMAT 99999         HEADING 'Piece|#'
COLUMN copy#               FORMAT 9999          HEADING 'Copy|#'
COLUMN bp_key              FORMAT 9999          HEADING 'BP|Key'
COLUMN status              FORMAT a9            HEADING 'Status'
COLUMN handle              FORMAT a65           HEADING 'Handle'
COLUMN start_time          FORMAT a17           HEADING 'Start|Time'
COLUMN completion_time     FORMAT a17           HEADING 'End|Time'
COLUMN elapsed_seconds     FORMAT 999,999       HEADING 'Elapsed|Seconds'
COLUMN deleted             FORMAT a8            HEADING 'Deleted?'

BREAK ON bs_key

prompt
prompt Available backup pieces contained in the control file.
prompt Includes available and expired backup sets.
prompt 

SELECT
    bs.recid                                            bs_key
  , bp.piece#                                           piece#
  , bp.copy#                                            copy#
  , bp.recid                                            bp_key
  , DECODE(   status
            , 'A', 'Available'
            , 'D', 'Deleted'
            , 'X', 'Expired')                           status
  , handle                                              handle
  , TO_CHAR(bp.start_time, 'mm/dd/yy HH24:MI:SS')       start_time
  , TO_CHAR(bp.completion_time, 'mm/dd/yy HH24:MI:SS')  completion_time
  , bp.elapsed_seconds                                  elapsed_seconds
FROM
    v$backup_set    bs
  , v$backup_piece  bp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
ORDER BY
    bs.recid
  , piece#
/