Long, long ago, I posted https://mdinh.wordpress.com/2013/10/31/whats-rman-backup-size/ and recently discovered the SQL was incorrect.
Current SQL does not include backup_copy as it got a little complicated to create an all inclusive test case – being sick and lazy.
oracle@arrow:hawklas:/media/sf_working/rman
$ sysdba @bkupinfo.sql
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 27 10:51:17 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options COMPLETION_TIME BS_KEY TYPE BP_KEY BP_MB PIECES SET_COUNT PIECE# COM DEVICE_TYPE ------------------- ------ ----------- ------ ------ ------ --------- ------ --- ----------------- HANDLE ---------------------------------------------------------------------------------------------------- 2016-02-24 18:43:02 1723 FULL 2058 25.6 1 1800 1 YES DISK /oradata/backup/HAWK_3130551611_20160224_o8quog1h_1_1_s1800_p1 2016-02-24 18:43:12 1724 FULL 2059 40.88 1 1802 1 YES DISK /oradata/backup/HAWK_3130551611_20160224_oaquog1p_1_1_s1802_p1 2016-02-24 18:43:22 1725 FULL 2060 109.32 1 1801 1 YES DISK /oradata/backup/HAWK_3130551611_20160224_o9quog1i_1_1_s1801_p1 2016-02-24 18:43:25 1726 FULL 2061 8.48 1 1804 1 YES DISK /oradata/backup/HAWK_3130551611_20160224_ocquog2b_1_1_s1804_p1 2016-02-24 18:43:33 1727 FULL 2062 59.28 1 1803 1 YES DISK /oradata/backup/HAWK_3130551611_20160224_obquog2a_1_1_s1803_p1 2016-02-24 18:43:39 1728 ARCHIVELOG 2063 .02 1 1805 1 YES DISK /oradata/backup/HAWK_3130551611_20160224_odquog2r_1_1_s1805_p1 2016-02-24 18:43:44 1729 CONTROLFILE 2064 11.23 1 1806 1 NO DISK /oradata/backup/HAWK_c-3130551611-20160224-0e 2016-02-27 09:57:49 1730 ARCHIVELOG 2065 3.39 1 1807 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_ofquvecs_1_1_s1807_p1 2016-02-27 09:57:55 1731 ARCHIVELOG 2066 29.52 1 1808 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_ogquvecs_1_1_s1808_p1 2016-02-27 09:57:55 1732 ARCHIVELOG 2067 13.55 1 1809 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_ohquvecv_1_1_s1809_p1 2016-02-27 09:59:03 1733 LEVEL0 2068 25.78 1 1810 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_oiquved7_1_1_s1810_p1 2016-02-27 09:59:35 1734 LEVEL0 2069 30.08 4 1811 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_ojquved7_1_1_s1811_p1 2016-02-27 09:59:35 1734 LEVEL0 2070 30.04 4 1811 2 YES DISK /oradata/backup/HAWK_3130551611_20160227_ojquved7_2_1_s1811_p2 2016-02-27 09:59:35 1734 LEVEL0 2071 30.09 4 1811 3 YES DISK /oradata/backup/HAWK_3130551611_20160227_ojquved7_3_1_s1811_p3 2016-02-27 09:59:35 1734 LEVEL0 2072 21.52 4 1811 4 YES DISK /oradata/backup/HAWK_3130551611_20160227_ojquved7_4_1_s1811_p4 2016-02-27 09:59:46 1735 LEVEL0 2073 30.3 2 1812 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_okquvefb_1_1_s1812_p1 2016-02-27 09:59:46 1735 LEVEL0 2074 11.87 2 1812 2 YES DISK /oradata/backup/HAWK_3130551611_20160227_okquvefb_2_1_s1812_p2 2016-02-27 09:59:57 1736 LEVEL0 2075 8.51 1 1814 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_omquvego_1_1_s1814_p1 2016-02-27 10:00:02 1737 LEVEL0 2076 30.44 2 1813 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_olquveg9_1_1_s1813_p1 2016-02-27 10:00:02 1737 LEVEL0 2077 29.42 2 1813 2 YES DISK /oradata/backup/HAWK_3130551611_20160227_olquveg9_2_1_s1813_p2 2016-02-27 10:00:09 1738 ARCHIVELOG 2078 .07 1 1815 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_onquveh9_1_1_s1815_p1 2016-02-27 10:00:14 1739 CONTROLFILE 2079 11.23 1 1816 1 NO DISK /oradata/backup/HAWK_c-3130551611-20160227-00 2016-02-27 10:02:49 1740 ARCHIVELOG 2080 11.42 1 1817 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_opquvem5_1_1_s1817_p1.bus 2016-02-27 10:02:53 1741 ARCHIVELOG 2081 1.77 1 1819 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_orquvemc_1_1_s1819_p1.bus 2016-02-27 10:02:57 1742 ARCHIVELOG 2082 14.07 3 1818 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_oqquvem5_1_1_s1818_p1.bus 2016-02-27 10:02:57 1742 ARCHIVELOG 2083 14.07 3 1818 2 YES DISK /oradata/backup/HAWK_3130551611_20160227_oqquvem5_2_1_s1818_p2.bus 2016-02-27 10:02:57 1742 ARCHIVELOG 2084 5.38 3 1818 3 YES DISK /oradata/backup/HAWK_3130551611_20160227_oqquvem5_3_1_s1818_p3.bus 2016-02-27 10:04:15 1743 LEVEL0 2085 14.15 2 1820 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_osquvemj_1_1_s1820_p1.bus 2016-02-27 10:04:15 1743 LEVEL0 2086 12.41 2 1820 2 YES DISK /oradata/backup/HAWK_3130551611_20160227_osquvemj_2_1_s1820_p2.bus 2016-02-27 10:05:04 1744 LEVEL0 2087 14.17 4 1822 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_ouquvep1_1_1_s1822_p1.bus 2016-02-27 10:05:04 1744 LEVEL0 2088 14.15 4 1822 2 YES DISK /oradata/backup/HAWK_3130551611_20160227_ouquvep1_2_1_s1822_p2.bus 2016-02-27 10:05:04 1744 LEVEL0 2089 14.02 4 1822 3 YES DISK /oradata/backup/HAWK_3130551611_20160227_ouquvep1_3_1_s1822_p3.bus 2016-02-27 10:05:04 1744 LEVEL0 2090 1.7 4 1822 4 YES DISK /oradata/backup/HAWK_3130551611_20160227_ouquvep1_4_1_s1822_p4.bus 2016-02-27 10:05:07 1745 LEVEL0 2091 14.01 9 1821 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_otquvemj_1_1_s1821_p1.bus 2016-02-27 10:05:07 1745 LEVEL0 2092 14.05 9 1821 2 YES DISK /oradata/backup/HAWK_3130551611_20160227_otquvemj_2_1_s1821_p2.bus 2016-02-27 10:05:07 1745 LEVEL0 2093 14.01 9 1821 3 YES DISK /oradata/backup/HAWK_3130551611_20160227_otquvemj_3_1_s1821_p3.bus 2016-02-27 10:05:07 1745 LEVEL0 2094 14.13 9 1821 4 YES DISK /oradata/backup/HAWK_3130551611_20160227_otquvemj_4_1_s1821_p4.bus 2016-02-27 10:05:07 1745 LEVEL0 2095 14.11 9 1821 5 YES DISK /oradata/backup/HAWK_3130551611_20160227_otquvemj_5_1_s1821_p5.bus 2016-02-27 10:05:07 1745 LEVEL0 2096 14.11 9 1821 6 YES DISK /oradata/backup/HAWK_3130551611_20160227_otquvemj_6_1_s1821_p6.bus 2016-02-27 10:05:07 1745 LEVEL0 2097 14.05 9 1821 7 YES DISK /oradata/backup/HAWK_3130551611_20160227_otquvemj_7_1_s1821_p7.bus 2016-02-27 10:05:07 1745 LEVEL0 2098 14.02 9 1821 8 YES DISK /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus 2016-02-27 10:05:07 1745 LEVEL0 2099 3.46 9 1821 9 YES DISK /oradata/backup/HAWK_3130551611_20160227_otquvemj_9_1_s1821_p9.bus 2016-02-27 10:05:20 1746 LEVEL0 2100 8.51 1 1824 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_p0quveqo_1_1_s1824_p1.bus 2016-02-27 10:05:57 1747 LEVEL0 2101 14.17 5 1823 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_ovquveqn_1_1_s1823_p1.bus 2016-02-27 10:05:57 1747 LEVEL0 2102 14.2 5 1823 2 YES DISK /oradata/backup/HAWK_3130551611_20160227_ovquveqn_2_1_s1823_p2.bus 2016-02-27 10:05:57 1747 LEVEL0 2103 14.09 5 1823 3 YES DISK /oradata/backup/HAWK_3130551611_20160227_ovquveqn_3_1_s1823_p3.bus 2016-02-27 10:05:57 1747 LEVEL0 2104 14.23 5 1823 4 YES DISK /oradata/backup/HAWK_3130551611_20160227_ovquveqn_4_1_s1823_p4.bus 2016-02-27 10:05:57 1747 LEVEL0 2105 5.6 5 1823 5 YES DISK /oradata/backup/HAWK_3130551611_20160227_ovquveqn_5_1_s1823_p5.bus 2016-02-27 10:06:07 1748 ARCHIVELOG 2106 5.01 1 1825 1 YES DISK /oradata/backup/HAWK_3130551611_20160227_p1quvese_1_1_s1825_p1.bus 2016-02-27 10:06:14 1749 CONTROLFILE 2107 11.23 1 1826 1 NO DISK /oradata/backup/HAWK_c-3130551611-20160227-01 ------ sum 880.92 50 rows selected. ARROW:(SYS@hawklas):PRIMARY> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Let’t take a look at backupset 1745 and backuppiece 2098 from RMAN.
Note BS Key is not the same as SET_COUNT (which is used to label backuppiece)
BS Key = 1745 while SET_COUNT = s1821
oracle@arrow:hawklas:/media/sf_working/rman $ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 27 10:51:22 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: HAWK (DBID=3130551611) RMAN> list backup 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 ------- -- -- - ----------- -------------------- ------- ------- ---------- --- 1723 B F A DISK 2016-FEB-24 18:43:02 1 1 YES FULL 1724 B F A DISK 2016-FEB-24 18:43:12 1 1 YES FULL 1725 B F A DISK 2016-FEB-24 18:43:22 1 1 YES FULL 1726 B F A DISK 2016-FEB-24 18:43:25 1 1 YES FULL 1727 B F A DISK 2016-FEB-24 18:43:33 1 1 YES FULL 1728 B A A DISK 2016-FEB-24 18:43:39 1 1 YES ARCHIVELOG 1729 B F A DISK 2016-FEB-24 18:43:44 1 1 NO TAG20160224T184341 1730 B A A DISK 2016-FEB-27 09:57:49 1 1 YES ARCHIVELOG 1731 B A A DISK 2016-FEB-27 09:57:55 1 1 YES ARCHIVELOG 1732 B A A DISK 2016-FEB-27 09:57:55 1 1 YES ARCHIVELOG 1733 B 0 A DISK 2016-FEB-27 09:59:03 1 1 YES LEVEL0 1734 B 0 A DISK 2016-FEB-27 09:59:35 4 1 YES LEVEL0 1735 B 0 A DISK 2016-FEB-27 09:59:46 2 1 YES LEVEL0 1736 B 0 A DISK 2016-FEB-27 09:59:57 1 1 YES LEVEL0 1737 B 0 A DISK 2016-FEB-27 10:00:02 2 1 YES LEVEL0 1738 B A A DISK 2016-FEB-27 10:00:09 1 1 YES ARCHIVELOG 1739 B F A DISK 2016-FEB-27 10:00:14 1 1 NO TAG20160227T100010 1740 B A A DISK 2016-FEB-27 10:02:49 1 1 YES ARCHIVELOG 1741 B A A DISK 2016-FEB-27 10:02:53 1 1 YES ARCHIVELOG 1742 B A A DISK 2016-FEB-27 10:02:57 3 1 YES ARCHIVELOG 1743 B 0 A DISK 2016-FEB-27 10:04:15 2 1 YES LEVEL0 1744 B 0 A DISK 2016-FEB-27 10:05:04 4 1 YES LEVEL0 1745 B 0 A DISK 2016-FEB-27 10:05:07 9 1 YES LEVEL0 1746 B 0 A DISK 2016-FEB-27 10:05:20 1 1 YES LEVEL0 1747 B 0 A DISK 2016-FEB-27 10:05:57 5 1 YES LEVEL0 1748 B A A DISK 2016-FEB-27 10:06:07 1 1 YES ARCHIVELOG 1749 B F A DISK 2016-FEB-27 10:06:14 1 1 NO TAG20160227T100609 RMAN> list backupset 1745; using target database control file instead of recovery catalog List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------- 1745 Incr 0 115.94M DISK 00:02:08 2016-FEB-27 10:05:07 List of Datafiles in backup set 1745 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- -------------------- ---- 2 0 Incr 7405326 2016-FEB-27 10:02:59 /oradata/HAWKLAS/datafile/o1_mf_sysaux_c711xx75_.dbf Backup Set Copy #1 of backup set 1745 Device Type Elapsed Time Completion Time Compressed Tag ----------- ------------ -------------------- ---------- --- DISK 00:02:08 2016-FEB-27 10:05:06 YES LEVEL0 List of Backup Pieces for backup set 1745 Copy #1 BP Key Pc# Status Piece Name ------- --- ----------- ---------- 2091 1 AVAILABLE /oradata/backup/HAWK_3130551611_20160227_otquvemj_1_1_s1821_p1.bus 2092 2 AVAILABLE /oradata/backup/HAWK_3130551611_20160227_otquvemj_2_1_s1821_p2.bus 2093 3 AVAILABLE /oradata/backup/HAWK_3130551611_20160227_otquvemj_3_1_s1821_p3.bus 2094 4 AVAILABLE /oradata/backup/HAWK_3130551611_20160227_otquvemj_4_1_s1821_p4.bus 2095 5 AVAILABLE /oradata/backup/HAWK_3130551611_20160227_otquvemj_5_1_s1821_p5.bus 2096 6 AVAILABLE /oradata/backup/HAWK_3130551611_20160227_otquvemj_6_1_s1821_p6.bus 2097 7 AVAILABLE /oradata/backup/HAWK_3130551611_20160227_otquvemj_7_1_s1821_p7.bus 2098 8 AVAILABLE /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus 2099 9 AVAILABLE /oradata/backup/HAWK_3130551611_20160227_otquvemj_9_1_s1821_p9.bus RMAN> list backuppiece 2098; List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 2098 1745 8 1 AVAILABLE DISK /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus RMAN> exit Recovery Manager complete.
Note the size for backup piece is 15MB versus 14.02MB from SQL – good enough.
$ du -m /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus
15 /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus
$ sysdba @bkupsize.sql
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 27 10:52:45 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options COMPLETION_TIME TYPE MB MIN ------------------- ----------- ---------- ---------- 2016-02-24 00:00:00 ARCHIVELOG .02 0 2016-02-24 00:00:00 CONTROLFILE 11.23 .05 2016-02-24 00:00:00 FULL 243.56 .82 2016-02-27 00:00:00 ARCHIVELOG 98.25 .9 2016-02-27 00:00:00 CONTROLFILE 22.46 .13 2016-02-27 00:00:00 LEVEL0 505.4 38.52 ---------- sum 880.92 6 rows selected. GRP TYPE TOTAL_MB ---------- ----------- ---------- 0 ARCHIVELOG 98.27 0 CONTROLFILE 33.69 0 FULL 243.56 0 LEVEL0 505.4 1 880.92 ARROW:(SYS@hawklas):PRIMARY> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Let’s compare against file system.
oracle@arrow:hawklas:/media/sf_working/rman
$ du -m /oradata/backup/
882 /oradata/backup/
oracle@arrow:hawklas:/media/sf_working/rman
$ ll /oradata/backup/
total 902412
-rw-r-----. 1 oracle oinstall 26853376 Feb 24 18:43 HAWK_3130551611_20160224_o8quog1h_1_1_s1800_p1
-rw-r-----. 1 oracle oinstall 114638848 Feb 24 18:43 HAWK_3130551611_20160224_o9quog1i_1_1_s1801_p1
-rw-r-----. 1 oracle oinstall 42868736 Feb 24 18:43 HAWK_3130551611_20160224_oaquog1p_1_1_s1802_p1
-rw-r-----. 1 oracle oinstall 62169088 Feb 24 18:43 HAWK_3130551611_20160224_obquog2a_1_1_s1803_p1
-rw-r-----. 1 oracle oinstall 8904704 Feb 24 18:43 HAWK_3130551611_20160224_ocquog2b_1_1_s1804_p1
-rw-r-----. 1 oracle oinstall 19968 Feb 24 18:43 HAWK_3130551611_20160224_odquog2r_1_1_s1805_p1
-rw-r-----. 1 oracle oinstall 3551744 Feb 27 09:57 HAWK_3130551611_20160227_ofquvecs_1_1_s1807_p1
-rw-r-----. 1 oracle oinstall 30955520 Feb 27 09:57 HAWK_3130551611_20160227_ogquvecs_1_1_s1808_p1
-rw-r-----. 1 oracle oinstall 14206976 Feb 27 09:57 HAWK_3130551611_20160227_ohquvecv_1_1_s1809_p1
-rw-r-----. 1 oracle oinstall 27041792 Feb 27 09:59 HAWK_3130551611_20160227_oiquved7_1_1_s1810_p1
-rw-r-----. 1 oracle oinstall 31547392 Feb 27 09:58 HAWK_3130551611_20160227_ojquved7_1_1_s1811_p1
-rw-r-----. 1 oracle oinstall 31506432 Feb 27 09:58 HAWK_3130551611_20160227_ojquved7_2_1_s1811_p2
-rw-r-----. 1 oracle oinstall 31563776 Feb 27 09:59 HAWK_3130551611_20160227_ojquved7_3_1_s1811_p3
-rw-r-----. 1 oracle oinstall 22568960 Feb 27 09:59 HAWK_3130551611_20160227_ojquved7_4_1_s1811_p4
-rw-r-----. 1 oracle oinstall 31776768 Feb 27 09:59 HAWK_3130551611_20160227_okquvefb_1_1_s1812_p1
-rw-r-----. 1 oracle oinstall 12451840 Feb 27 09:59 HAWK_3130551611_20160227_okquvefb_2_1_s1812_p2
-rw-r-----. 1 oracle oinstall 31924224 Feb 27 09:59 HAWK_3130551611_20160227_olquveg9_1_1_s1813_p1
-rw-r-----. 1 oracle oinstall 30859264 Feb 27 10:00 HAWK_3130551611_20160227_olquveg9_2_1_s1813_p2
-rw-r-----. 1 oracle oinstall 8929280 Feb 27 09:59 HAWK_3130551611_20160227_omquvego_1_1_s1814_p1
-rw-r-----. 1 oracle oinstall 76288 Feb 27 10:00 HAWK_3130551611_20160227_onquveh9_1_1_s1815_p1
-rw-r-----. 1 oracle oinstall 11970560 Feb 27 10:02 HAWK_3130551611_20160227_opquvem5_1_1_s1817_p1.bus
-rw-r-----. 1 oracle oinstall 14755840 Feb 27 10:02 HAWK_3130551611_20160227_oqquvem5_1_1_s1818_p1.bus
-rw-r-----. 1 oracle oinstall 14749184 Feb 27 10:02 HAWK_3130551611_20160227_oqquvem5_2_1_s1818_p2.bus
-rw-r-----. 1 oracle oinstall 5646848 Feb 27 10:02 HAWK_3130551611_20160227_oqquvem5_3_1_s1818_p3.bus
-rw-r-----. 1 oracle oinstall 1853952 Feb 27 10:02 HAWK_3130551611_20160227_orquvemc_1_1_s1819_p1.bus
-rw-r-----. 1 oracle oinstall 14843904 Feb 27 10:03 HAWK_3130551611_20160227_osquvemj_1_1_s1820_p1.bus
-rw-r-----. 1 oracle oinstall 13025280 Feb 27 10:04 HAWK_3130551611_20160227_osquvemj_2_1_s1820_p2.bus
-rw-r-----. 1 oracle oinstall 14696448 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_1_1_s1821_p1.bus
-rw-r-----. 1 oracle oinstall 14737408 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_2_1_s1821_p2.bus
-rw-r-----. 1 oracle oinstall 14696448 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_3_1_s1821_p3.bus
-rw-r-----. 1 oracle oinstall 14827520 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_4_1_s1821_p4.bus
-rw-r-----. 1 oracle oinstall 14802944 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_5_1_s1821_p5.bus
-rw-r-----. 1 oracle oinstall 14802944 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_6_1_s1821_p6.bus
-rw-r-----. 1 oracle oinstall 14737408 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_7_1_s1821_p7.bus
-rw-r-----. 1 oracle oinstall 14704640 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus
-rw-r-----. 1 oracle oinstall 3637248 Feb 27 10:05 HAWK_3130551611_20160227_otquvemj_9_1_s1821_p9.bus
-rw-r-----. 1 oracle oinstall 14868480 Feb 27 10:04 HAWK_3130551611_20160227_ouquvep1_1_1_s1822_p1.bus
-rw-r-----. 1 oracle oinstall 14843904 Feb 27 10:04 HAWK_3130551611_20160227_ouquvep1_2_1_s1822_p2.bus
-rw-r-----. 1 oracle oinstall 14712832 Feb 27 10:04 HAWK_3130551611_20160227_ouquvep1_3_1_s1822_p3.bus
-rw-r-----. 1 oracle oinstall 1785856 Feb 27 10:05 HAWK_3130551611_20160227_ouquvep1_4_1_s1822_p4.bus
-rw-r-----. 1 oracle oinstall 14868480 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_1_1_s1823_p1.bus
-rw-r-----. 1 oracle oinstall 14893056 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_2_1_s1823_p2.bus
-rw-r-----. 1 oracle oinstall 14786560 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_3_1_s1823_p3.bus
-rw-r-----. 1 oracle oinstall 14925824 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_4_1_s1823_p4.bus
-rw-r-----. 1 oracle oinstall 5881856 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_5_1_s1823_p5.bus
-rw-r-----. 1 oracle oinstall 8929280 Feb 27 10:05 HAWK_3130551611_20160227_p0quveqo_1_1_s1824_p1.bus
-rw-r-----. 1 oracle oinstall 5254144 Feb 27 10:06 HAWK_3130551611_20160227_p1quvese_1_1_s1825_p1.bus
-rw-r-----. 1 oracle oinstall 11796480 Feb 24 18:43 HAWK_c-3130551611-20160224-0e
-rw-r-----. 1 oracle oinstall 11796480 Feb 27 10:00 HAWK_c-3130551611-20160227-00
-rw-r-----. 1 oracle oinstall 11796480 Feb 27 10:06 HAWK_c-3130551611-20160227-01
oracle@arrow:hawklas:/media/sf_working/rman
$
bkupinfo.sql
col BP_MB for 9,999,999.99
break on report
COMPUTE sum of BP_MB on report
set numw 6 lines 120
col handle for a100
SELECT
s.completion_time,
s.recid BS_KEY,
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'
WHEN s.backup_type='D' AND s.incremental_level IS NULL THEN 'FULL'
END type,
p.recid BP_KEY,
round(p.bytes/1048576,2) BP_MB,
s.pieces,
s.set_count,
p.piece#,
p.compressed,
p.device_type,
p.handle
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND s.set_stamp (+) = p.set_stamp
AND s.set_count (+) = p.set_count
order by s.recid, p.piece#
;
bkupsize.sql
col MB for 9,999,999.99
col min for 9,999,999.99
col TOTAL_MB for 9,999,999.99
break on report
COMPUTE sum of MB on report
SELECT TRUNC(completion_time) completion_time, type, round(sum(MB),2) MB, round(sum(elapsed_seconds)/60,2) 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'
WHEN s.backup_type='D' AND s.incremental_level IS NULL THEN 'FULL'
END type,
TRUNC(s.completion_time) completion_time,
round(p.bytes/1048576,2) MB,
s.elapsed_seconds
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND s.set_stamp = p.set_stamp
AND s.set_count = p.set_count
-- UNION ALL
-- SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
)
GROUP BY TRUNC(completion_time), type
ORDER BY 1 asc,2,3
;
SELECT grouping(type) grp, type, round(sum(MB),2) TOTAL_MB
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'
WHEN s.backup_type='D' AND s.incremental_level IS NULL THEN 'FULL'
END type,
TRUNC(s.completion_time) completion_time,
round(p.bytes/1048576,2) MB,
s.elapsed_seconds
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND s.set_stamp = p.set_stamp
AND s.set_count = p.set_count
-- UNION ALL
-- SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
)
GROUP BY ROLLUP(type)
ORDER BY 1 asc,2,3
;