Thinking Out Loud

February 27, 2016

Revised RMAN Backup Size

Filed under: 11g,oracle,RMAN — mdinh @ 7:25 pm

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
;

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: