Cacher is the code snippet organizer for pro developers

export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss';

rman target=/

report obsolete;

delete obsolete;

CROSSCHECK ARCHIVELOG ALL;

DELETE EXPIRED ARCHIVELOG ALL;

CROSSCHECK BACKUP;

DELETE EXPIRED BACKUP;

delete archivelog all completed before 'sysdate -35';

list backup summary;

show retention policy;

report schema;

show all;

list backup;

rman catalog rman/rman@rman target=/

TTITLE LEFT '% Completed. Aggregate is the overall progress:'
SET LINE 132
SELECT opname, round(sofar/totalwork*100) "% Complete"
  FROM gv$session_longops
 WHERE opname LIKE 'RMAN%'
   AND totalwork != 0
   AND sofar <> totalwork
 ORDER BY 1;

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COL compression_ratio for 99999 head Cmprsn|Ratio
COL elapsed for 9999.99 head Elapsed|Mins
COL end_time for a18 head "End Time"
COL hrs for 999,999
COL input_type head "Input Type"
COL session_key for 9999999 head Session|Key
COL start_time for a18 Head "Start Time"
COL status for a23 head "Job Status"
 
select session_key,input_type,status,
        to_char(start_time,'dd/mm/yy hh24:mi:ss') start_time,
        to_char(end_time,'dd/mm/yy hh24:mi:ss') end_time,
        (elapsed_seconds/60) elapsed,COMPRESSION_RATIO
from v$rman_backup_job_details
order by session_key
/

export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss';

LIST BACKUP OF ARCHIVELOG TIME BETWEEN "to_date('20-FEB-2020 09:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('20-FEB-2020 18:00:00','DD-MON-YYYY HH24:MI:SS')";

LIST BACKUP SUMMARY COMPLETED BETWEEN "to_date('20-MAR-2020 05:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('20-MAR-2020 07:00:00','DD-MON-YYYY HH24:MI:SS')";

SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
  FROM
  (
  SELECT
  CASE
  WHEN s.backup_type='L' THEN 'ARCHIVELOG'
  WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
  WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
  WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
END type,
TRUNC(s.completion_time) completion_time, p.bytes, s.elapsed_seconds
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A' AND p.recid=s.recid
UNION ALL
SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
)
GROUP BY TO_CHAR(completion_time, 'YYYY-MON-DD'), type
ORDER BY 1 ASC,2,3 
/

SELECT TO_CHAR(completion_time, 'YYYY-MON-DD hh24:mm:ss') completion_time, type, bytes/1024/1024 AS MB
  FROM
  (
  SELECT
  CASE
  WHEN s.backup_type='L' THEN 'ARCHIVELOG'
END type,
s.completion_time, p.bytes
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A' AND p.recid=s.recid
);

set lines 150
set pages 300
alter session set NLS_DATE_FORMAT="DD/MM/YYYY-HH24:MI:SS";
select start_time "Start", round(input_bytes/1024/1024*10)/10 "Source MB",
       round(output_bytes/1024/1024*10)/10 "Backup MB", input_type "Type", status "Status",
       round(elapsed_seconds/60*10)/10 "Duration (min)", round(COMPRESSION_RATIO*10)/10 "Ratio",
       round(input_bytes_per_sec/(1024*1024),2) "In mb/s",
       round(output_bytes_per_sec/(1024*1024),2) "Out mb/s"
  from v$rman_backup_job_details
 where start_time >= SYSDATE-2 order by 1 desc;

SELECT
	TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time
  , type
  , round(sum(bytes)          /1048576) MB
  , round(sum(elapsed_seconds)/60)      min
FROM
	(
		SELECT
			CASE
				WHEN s.backup_type='L'
					THEN 'ARCHIVELOG'
				WHEN s.controlfile_included='YES'
					THEN 'CONTROLFILE'
				WHEN s.backup_type         ='D'
					AND s.incremental_level=0
					THEN 'LEVEL0'
				WHEN s.backup_type         ='I'
					AND s.incremental_level=1
					THEN 'LEVEL1'
			END                      type
		  , TRUNC(s.completion_time) completion_time
		  , p.bytes
		  , s.elapsed_seconds
		FROM
			v$backup_piece p
		  , v$backup_set   s
		WHERE
			p.status   ='A'
			AND p.recid=s.recid
		UNION ALL
		SELECT
			'DATAFILECOPY' type
		  , TRUNC(completion_time)
		  , output_bytes
		  , 0 elapsed_seconds
		FROM
			v$backup_copy_details
	)
GROUP BY
	TO_CHAR(completion_time, 'YYYY-MON-DD')
  , type
ORDER BY
	1 ASC
  , 2
  , 3
;