Pythian Blog: Technical Track

How to determine RMAN backup size

by Michael Dinh on Feb 1, 2019 12:00:00 AM

A time long ago, I wrote a SQL query to determine RMAN backup size. Here is another validation for the SQL query. Why is this important? Because, as part of a backup review, it's good to know the growth for the backup size in order to allocate space for backup and timing for times when there might be a performance degradation with backup. 
 SQL> SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min 2 FROM 3 ( 4 SELECT 5 CASE 6 WHEN s.backup_type='L' THEN 'ARCHIVELOG' 7 WHEN s.controlfile_included='YES' THEN 'CONTROLFILE' 8 WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0' 9 WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1' 10 END type, 11 TRUNC(s.completion_time) completion_time, p.bytes, s.elapsed_seconds 12 FROM v$backup_piece p, v$backup_set s 13 WHERE p.status='A' AND p.recid=s.recid 14 UNION ALL 15 SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details 16 ) 17 GROUP BY TO_CHAR(completion_time, 'YYYY-MON-DD'), type 18 ORDER BY 1 ASC,2,3 19 ; COMPLETION_TIME TYPE MB MIN -------------------- ------------ ---------- ---------- 2019-JAN-20 ARCHIVELOG 212 0 2019-JAN-20 CONTROLFILE 512 0 2019-JAN-20 LEVEL0 25943 53 2019-JAN-21 ARCHIVELOG 446 1 2019-JAN-21 CONTROLFILE 607 1 2019-JAN-21 LEVEL1 109 1 2019-JAN-22 ARCHIVELOG 484 1 2019-JAN-22 CONTROLFILE 607 1 2019-JAN-22 LEVEL1 185 1 2019-JAN-23 ARCHIVELOG 509 1 2019-JAN-23 CONTROLFILE 607 1 2019-JAN-23 LEVEL1 190 1 2019-JAN-24 ARCHIVELOG 464 1 2019-JAN-24 CONTROLFILE 607 1 2019-JAN-24 LEVEL1 190 1 2019-JAN-25 ARCHIVELOG 474 1 2019-JAN-25 CONTROLFILE 558 0 2019-JAN-26 ARCHIVELOG 247 0 2019-JAN-26 CONTROLFILE 607 1 2019-JAN-26 LEVEL1 338 2 2019-JAN-27 ARCHIVELOG 235 0 2019-JAN-27 CONTROLFILE 607 1 2019-JAN-27 LEVEL0 26036 53 2019-JAN-28 ARCHIVELOG 406 1 2019-JAN-28 CONTROLFILE 490 0 2019-JAN-28 LEVEL1 108 1 26 rows selected. SQL>
We will be looking at LEVEL0 for 2019-JAN-27. We can use the following commands to find info about backup: 
 list backup of database summary; list backup tag LV0BKP; (change the tag accordingly)
  Here is an example: 
RMAN> list backup of database summary; using target database control file instead of recovery catalog List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- -------------------- ------- ------- ---------- --- 95525 B 0 A DISK 2019-JAN-20 02:53:18 1 1 YES LV0BKP 95578 B 1 A DISK 2019-JAN-21 02:01:22 1 1 YES LV1BKP 95631 B 1 A DISK 2019-JAN-22 02:01:50 1 1 YES LV1BKP 95684 B 1 A DISK 2019-JAN-23 02:01:51 1 1 YES LV1BKP 95737 B 1 A DISK 2019-JAN-24 02:01:59 1 1 YES LV1BKP 95838 B 1 A DISK 2019-JAN-26 02:02:36 1 1 YES LV1BKP 95891 B 0 A DISK 2019-JAN-27 02:53:37 1 1 YES LV0BKP 95944 B 1 A DISK 2019-JAN-28 02:01:24 1 1 YES LV1BKP RMAN> list backup tag LV0BKP; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------- 95525 Incr 0 25.34G DISK 00:52:31 2019-JAN-20 02:53:18 BP Key: 95525 Status: AVAILABLE Compressed: YES Tag: LV0BKP Piece Name: /pub/ora-backupfs/oracle/nyprod/L0_NYPROD_t998013647_s95894_p1 List of Datafiles in backup set 95525 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- -------------------- ---- 1 0 Incr 7749128727 2019-JAN-20 02:00:49 +DATA/ 31 0 Incr 7749128727 2019-JAN-20 02:00:49 +DATA/ BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ -------------------- 95528 4.10M DISK 00:00:01 2019-JAN-20 02:53:35 BP Key: 95528 Status: AVAILABLE Compressed: YES Tag: LV0BKP Piece Name: /pub/ora-backupfs/oracle/nyprod/L0_NYPROD_t998016814_s95899_p1 List of Archived Logs in backup set 95528 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- -------------------- ---------- --------- 1 199219 7749128684 2019-JAN-20 02:00:42 7749133741 2019-JAN-20 02:15:39 1 199220 7749133741 2019-JAN-20 02:15:39 7749138859 2019-JAN-20 02:30:41 1 199221 7749138859 2019-JAN-20 02:30:41 7749145564 2019-JAN-20 02:45:42 1 199222 7749145564 2019-JAN-20 02:45:42 7749148473 2019-JAN-20 02:53:32 2 197761 7749128688 2019-JAN-20 02:00:44 7749133746 2019-JAN-20 02:15:41 2 197762 7749133746 2019-JAN-20 02:15:41 7749138865 2019-JAN-20 02:30:43 2 197763 7749138865 2019-JAN-20 02:30:43 7749145560 2019-JAN-20 02:45:41 2 197764 7749145560 2019-JAN-20 02:45:41 7749148462 2019-JAN-20 02:53:30 2 197765 7749148462 2019-JAN-20 02:53:30 7749148614 2019-JAN-20 02:53:33 ================================================================================ BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------- 95891 Incr 0 25.43G DISK 00:52:51 2019-JAN-27 02:53:37 BP Key: 95891 Status: AVAILABLE Compressed: YES Tag: LV0BKP Piece Name: /pub/ora-backupfs/oracle/nyprod/L0_NYPROD_t998618446_s96260_p1 List of Datafiles in backup set 95891 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- -------------------- ---- 1 0 Incr 7761624701 2019-JAN-27 02:00:48 +DATA/ 31 0 Incr 7761624701 2019-JAN-27 02:00:48 +DATA/ BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------- 95892 Incr 0 1.78M DISK 00:00:01 2019-JAN-27 02:53:43 BP Key: 95892 Status: AVAILABLE Compressed: YES Tag: LV0BKP Piece Name: /pub/ora-backupfs/oracle/nyprod/L0_NYPROD_t998621622_s96263_p1 Control File Included: Ckp SCN: 7761648225 Ckp time: 2019-JAN-27 02:53:42 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ -------------------- 95894 4.94M DISK 00:00:00 2019-JAN-27 02:53:54 BP Key: 95894 Status: AVAILABLE Compressed: YES Tag: LV0BKP Piece Name: /pub/ora-backupfs/oracle/nyprod/L0_NYPROD_t998621634_s96265_p1 List of Archived Logs in backup set 95894 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- -------------------- ---------- --------- 1 199960 7761624430 2019-JAN-27 02:00:43 7761631279 2019-JAN-27 02:15:43 1 199961 7761631279 2019-JAN-27 02:15:43 7761637156 2019-JAN-27 02:30:42 1 199962 7761637156 2019-JAN-27 02:30:42 7761643528 2019-JAN-27 02:45:42 1 199963 7761643528 2019-JAN-27 02:45:42 7761648290 2019-JAN-27 02:53:51 2 198522 7761623633 2019-JAN-27 02:00:39 7761631270 2019-JAN-27 02:15:37 2 198523 7761631270 2019-JAN-27 02:15:37 7761637145 2019-JAN-27 02:30:36 2 198524 7761637145 2019-JAN-27 02:30:36 7761643329 2019-JAN-27 02:45:33 2 198525 7761643329 2019-JAN-27 02:45:33 7761648280 2019-JAN-27 02:53:49 2 198526 7761648280 2019-JAN-27 02:53:49 7761648431 2019-JAN-27 02:53:53 RMAN> exit
Here's a view from filesystem: 
 $ ls -lrth L0_NYPROD_t998618446_s96260_p1 -rw-r----- 1 oracle asmadmin 26G Jan 27 02:53 L0_NYPROD_t998618446_s96260_p1 $ ls -lrth L0_NYPROD_t998621622_s96263_p1 -rw-r----- 1 oracle asmadmin 1.8M Jan 27 02:53 L0_NYPROD_t998621622_s96263_p1 $ ls -lrth L0_NYPROD_t998621634_s96265_p1 -rw-r----- 1 oracle asmadmin 5.0M Jan 27 02:53 L0_NYPROD_t998621634_s96265_p1$
Topics: Oracle Technical Track

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.