Revised RMAN Backup Size

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

CVE-2015-7547 for Oracle Database

I am sure by now you have heard about the bug.

Reboot Required!

Need to update to release el6_7.7 if you are on OEL 6

glibc-2.12-1.166.el6_7.7.x86_64.rpm
glibc-common-2.12-1.166.el6_7.7.x86_64.rpm
glibc-devel-2.12-1.166.el6_7.7.i686.rpm
glibc-devel-2.12-1.166.el6_7.7.x86_64.rpm
glibc-headers-2.12-1.166.el6_7.7.x86_64.rpm
nscd-2.12-1.166.el6_7.7.x86_64.rpm
glibc-devel-2.12-1.166.el6_7.7.i686.rpm
glibc-2.12-1.166.el6_7.7.i686.rpm

http://linux.oracle.com/errata/ELSA-2016-0175.html

You can also use the following commands to verify RPMs for CVE_2015_7547

There are two dashes (- -) for cve.

$ yum list –cve=2015_7547|egrep ‘^glibc|^nscd’|sort

Skipping security plugin, other command
glibc-common.x86_64                  2.12-1.166.el6_7.7       @public_ol6_latest
glibc-devel.i686                     2.12-1.166.el6_7.7       @public_ol6_latest
glibc-devel.x86_64                   2.12-1.166.el6_7.7       @public_ol6_latest
glibc-headers.x86_64                 2.12-1.166.el6_7.7       @public_ol6_latest
glibc.i686                           2.12-1.166.el6_7.7       @public_ol6_latest
glibc-static.i686                    2.12-1.166.el6_7.7       public_ol6_latest
glibc-static.x86_64                  2.12-1.166.el6_7.7       public_ol6_latest
glibc-utils.x86_64                   2.12-1.166.el6_7.7       public_ol6_latest
glibc.x86_64                         2.12-1.166.el6_7.7       @public_ol6_latest
nscd.x86_64                          2.12-1.166.el6_7.7       public_ol6_latest

Note that it is not necessary to relink any binaries after this update.

Reference: glibc vulnerability (CVE-2015-7547) patch availability for Oracle Exadata Database Machine (Doc ID 2108582.1)

Assumption being made if relink is not required for Exadata, then it is not required for Non-Exadata as well.

Be safe and verify with Oracle support and let me know too please.

$ cat /etc/oracle-release

Oracle Linux Server release 6.6

$ rpm -qa –queryformat=”%{name}-%{version}-%{release}.%{arch}\n” | egrep ‘glibc|nscd’

glibc-devel-2.12-1.149.el6_6.9.i686
glibc-common-2.12-1.149.el6_6.9.x86_64
glibc-2.12-1.149.el6_6.9.i686
glibc-headers-2.12-1.149.el6_6.9.x86_64
glibc-devel-2.12-1.149.el6_6.9.x86_64
glibc-2.12-1.149.el6_6.9.x86_64

[root@arrow ~]# yum update glibc

Loaded plugins: refresh-packagekit, security
Setting up Update Process
public_ol6_UEKR3_latest                                                                                                               | 1.2 kB     00:00
public_ol6_UEKR3_latest/primary                                                                                                       |  22 MB     00:10
public_ol6_UEKR3_latest                                                                                                                              559/559
public_ol6_latest                                                                                                                     | 1.4 kB     00:00
public_ol6_latest/primary                                                                                                             |  55 MB     00:24
public_ol6_latest                                                                                                                                33290/33290
Resolving Dependencies
--> Running transaction check
---> Package glibc.i686 0:2.12-1.149.el6_6.9 will be updated
--> Processing Dependency: glibc = 2.12-1.149.el6_6.9 for package: glibc-common-2.12-1.149.el6_6.9.x86_64
--> Processing Dependency: glibc = 2.12-1.149.el6_6.9 for package: glibc-devel-2.12-1.149.el6_6.9.i686
--> Processing Dependency: glibc = 2.12-1.149.el6_6.9 for package: glibc-headers-2.12-1.149.el6_6.9.x86_64
--> Processing Dependency: glibc = 2.12-1.149.el6_6.9 for package: glibc-devel-2.12-1.149.el6_6.9.x86_64
---> Package glibc.x86_64 0:2.12-1.149.el6_6.9 will be updated
---> Package glibc.i686 0:2.12-1.166.el6_7.7 will be an update
---> Package glibc.x86_64 0:2.12-1.166.el6_7.7 will be an update
--> Running transaction check
---> Package glibc-common.x86_64 0:2.12-1.149.el6_6.9 will be updated
---> Package glibc-common.x86_64 0:2.12-1.166.el6_7.7 will be an update
---> Package glibc-devel.i686 0:2.12-1.149.el6_6.9 will be updated
---> Package glibc-devel.x86_64 0:2.12-1.149.el6_6.9 will be updated
---> Package glibc-devel.i686 0:2.12-1.166.el6_7.7 will be an update
---> Package glibc-devel.x86_64 0:2.12-1.166.el6_7.7 will be an update
---> Package glibc-headers.x86_64 0:2.12-1.149.el6_6.9 will be updated
---> Package glibc-headers.x86_64 0:2.12-1.166.el6_7.7 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================
 Package                              Arch                          Version                                   Repository                                Size
=============================================================================================================================================================
Updating:
 glibc                                i686                          2.12-1.166.el6_7.7                        public_ol6_latest                        4.3 M
 glibc                                x86_64                        2.12-1.166.el6_7.7                        public_ol6_latest                        3.8 M
Updating for dependencies:
 glibc-common                         x86_64                        2.12-1.166.el6_7.7                        public_ol6_latest                         14 M
 glibc-devel                          i686                          2.12-1.166.el6_7.7                        public_ol6_latest                        986 k
 glibc-devel                          x86_64                        2.12-1.166.el6_7.7                        public_ol6_latest                        986 k
 glibc-headers                        x86_64                        2.12-1.166.el6_7.7                        public_ol6_latest                        615 k

Transaction Summary
=============================================================================================================================================================
Upgrade       6 Package(s)

Total download size: 25 M
Is this ok [y/N]: y
Downloading Packages:
(1/6): glibc-2.12-1.166.el6_7.7.i686.rpm                                                                                              | 4.3 MB     00:02
(2/6): glibc-2.12-1.166.el6_7.7.x86_64.rpm                                                                                            | 3.8 MB     00:02
(3/6): glibc-common-2.12-1.166.el6_7.7.x86_64.rpm                                                                                     |  14 MB     00:05
(4/6): glibc-devel-2.12-1.166.el6_7.7.i686.rpm                                                                                        | 986 kB     00:00
(5/6): glibc-devel-2.12-1.166.el6_7.7.x86_64.rpm                                                                                      | 986 kB     00:00
(6/6): glibc-headers-2.12-1.166.el6_7.7.x86_64.rpm                                                                                    | 615 kB     00:00
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                        1.8 MB/s |  25 MB     00:13
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Updating   : glibc-common-2.12-1.166.el6_7.7.x86_64                                                                                                   1/12
  Updating   : glibc-2.12-1.166.el6_7.7.x86_64                                                                                                          2/12
  Updating   : glibc-headers-2.12-1.166.el6_7.7.x86_64                                                                                                  3/12
  Updating   : glibc-2.12-1.166.el6_7.7.i686                                                                                                            4/12
  Updating   : glibc-devel-2.12-1.166.el6_7.7.i686                                                                                                      5/12
  Updating   : glibc-devel-2.12-1.166.el6_7.7.x86_64                                                                                                    6/12
  Cleanup    : glibc-devel-2.12-1.149.el6_6.9                                                                                                           7/12
  Cleanup    : glibc-devel-2.12-1.149.el6_6.9                                                                                                           8/12
  Cleanup    : glibc-2.12-1.149.el6_6.9                                                                                                                 9/12
  Cleanup    : glibc-headers-2.12-1.149.el6_6.9.x86_64                                                                                                 10/12
  Cleanup    : glibc-2.12-1.149.el6_6.9                                                                                                                11/12
  Cleanup    : glibc-common-2.12-1.149.el6_6.9.x86_64                                                                                                  12/12
  Verifying  : glibc-2.12-1.166.el6_7.7.i686                                                                                                            1/12
  Verifying  : glibc-devel-2.12-1.166.el6_7.7.i686                                                                                                      2/12
  Verifying  : glibc-headers-2.12-1.166.el6_7.7.x86_64                                                                                                  3/12
  Verifying  : glibc-devel-2.12-1.166.el6_7.7.x86_64                                                                                                    4/12
  Verifying  : glibc-2.12-1.166.el6_7.7.x86_64                                                                                                          5/12
  Verifying  : glibc-common-2.12-1.166.el6_7.7.x86_64                                                                                                   6/12
  Verifying  : glibc-headers-2.12-1.149.el6_6.9.x86_64                                                                                                  7/12
  Verifying  : glibc-2.12-1.149.el6_6.9.x86_64                                                                                                          8/12
  Verifying  : glibc-common-2.12-1.149.el6_6.9.x86_64                                                                                                   9/12
  Verifying  : glibc-devel-2.12-1.149.el6_6.9.x86_64                                                                                                   10/12
  Verifying  : glibc-2.12-1.149.el6_6.9.i686                                                                                                           11/12
  Verifying  : glibc-devel-2.12-1.149.el6_6.9.i686                                                                                                     12/12

Updated:
  glibc.i686 0:2.12-1.166.el6_7.7                                              glibc.x86_64 0:2.12-1.166.el6_7.7

Dependency Updated:
  glibc-common.x86_64 0:2.12-1.166.el6_7.7              glibc-devel.i686 0:2.12-1.166.el6_7.7             glibc-devel.x86_64 0:2.12-1.166.el6_7.7
  glibc-headers.x86_64 0:2.12-1.166.el6_7.7

Complete!
[root@arrow ~]#
[root@arrow ~]# init 6
[root@arrow ~]#

$ rpm -qa –queryformat=”%{name}-%{version}-%{release}.%{arch}\n” | egrep ‘glibc|nscd’

glibc-devel-2.12-1.166.el6_7.7.i686
glibc-headers-2.12-1.166.el6_7.7.x86_64
glibc-2.12-1.166.el6_7.7.x86_64
glibc-devel-2.12-1.166.el6_7.7.x86_64
glibc-common-2.12-1.166.el6_7.7.x86_64
glibc-2.12-1.166.el6_7.7.i686

$ rpm -qa –queryformat=”%{name}-%{release}.%{arch}\n” | egrep ‘glibc|nscd’

oracle@arrow:hawklas:/home/oracle
glibc-devel-1.166.el6_7.7.i686
glibc-headers-1.166.el6_7.7.x86_64
glibc-1.166.el6_7.7.x86_64
glibc-devel-1.166.el6_7.7.x86_64
glibc-common-1.166.el6_7.7.x86_64
glibc-1.166.el6_7.7.i686

Create DB with minimum parameters

Just a quick post to create database with minimum parameters.

Note: for the control_files parameter to be updated, you must create spfile from pfile.

oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ls *test*
inittest.ora  orapwtest
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ cat inittest.ora
db_name=test
db_create_file_dest='/oradata'
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 21 18:02:12 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@test> create spfile from pfile;

File created.

SYS@test> startup nomount;
ORACLE instance started.

Total System Global Area  229683200 bytes
Fixed Size                  2251936 bytes
Variable Size             171967328 bytes
Database Buffers           50331648 bytes
Redo Buffers                5132288 bytes
SYS@test> CREATE DATABASE
CHARACTER SET AL32UTF8
  2    3  EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 256M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  4    5  sysaux DATAFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  6  LOGFILE GROUP 1 SIZE 100M,GROUP 2 SIZE 100M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  7    8  DEFAULT TABLESPACE users DATAFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  9  UNDO TABLESPACE undotbs DATAFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
; 10

Database created.

SYS@test> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata/TEST/controlfile/o1_mf_cdnv3bjv_.ctl

SYS@test> 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
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ strings spfiletest.ora
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.control_files='/oradata/TEST/controlfile/o1_mf_cdnv3bjv_.ctl'#Oracle managed file
*.db_create_file_dest='/oradata'
*.db_name='test'


oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 21 18:09:48 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

ARROW:(SYS@test):PRIMARY> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfiletest.ora
ARROW:(SYS@test):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
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$

Many years ago, I had learned about drop database.
https://mdinh.wordpress.com/?s=drop+database

It does not do a complete job for cleanup.

What I did not know then which I now know, it does drop spfile as spfile is considered as part of the database, may be because it can get backup from RMAN just like the database?

oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ll *test*
-rw-rw----. 1 oracle oinstall 1544 Feb 21 18:14 hc_test.dat
-rw-r--r--. 1 oracle oinstall   44 Feb 21 18:01 inittest.ora
-rw-r-----. 1 oracle oinstall 2048 Feb 21 17:54 orapwtest
-rw-r-----. 1 oracle oinstall 1536 Feb 21 18:03 spfiletest.ora
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 21 18:15:21 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@test> startup restrict force mount;
ORACLE instance started.

Total System Global Area  229683200 bytes
Fixed Size                  2251936 bytes
Variable Size             171967328 bytes
Database Buffers           50331648 bytes
Redo Buffers                5132288 bytes
Database mounted.
SYS@test> drop database;

Database dropped.

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
SYS@test> exit
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ll *test*
-rw-rw----. 1 oracle oinstall 1544 Feb 21 18:17 hc_test.dat
-rw-r--r--. 1 oracle oinstall   44 Feb 21 18:01 inittest.ora
-rw-r-----. 1 oracle oinstall 2048 Feb 21 17:54 orapwtest
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sysresv

IPC Resources for ORACLE_SID "test" :
Shared Memory
ID              KEY
No shared memory segments used
Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid "test"
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ll /oradata/
total 56
drwxr-xr-x. 2 oracle oinstall 16384 Feb  1 11:59 backup
-rw-r--r--. 1 oracle oinstall    60 Nov  5  2014 bkup.rman
-rwxr-xr-x. 1 oracle oinstall   783 Nov  5  2014 bkup.sh
-rwxr-xr-x. 1 oracle oinstall   443 Nov  5  2014 bkup.sh.bak
drwxr-xr-x. 4 oracle oinstall  4096 Jan  9 15:08 fra
-rw-r--r--. 1 oracle oinstall   287 Nov  6  2014 fra.rman
drwxr-xr-x. 5 oracle oinstall  4096 Jan 14 21:28 HAWKLAS
drwxrwxr-x. 5 oracle oinstall  4096 Jan 14 21:28 HAWKSAN
drwxr-xr-x. 2 oracle oinstall  4096 Jan 28 11:07 keep
-rw-r--r--. 1 oracle oinstall   230 Jul 24  2014 restore_validate.rman
-rw-r--r--. 1 oracle oinstall   164 Jul 25  2014 simple.rman
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$


oracle@arrow:hawklas:/u01/app/oracle/diag
$ ll
total 8
drwxr-xr-x. 6 oracle oinstall 4096 Feb 21 17:55 rdbms
drwxr-xr-x. 3 oracle oinstall 4096 Sep 25 06:37 tnslsnr
oracle@arrow:hawklas:/u01/app/oracle/diag
$ cd rdbms/
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms
$ ll
total 16
drwxr-xr-x. 3 oracle oinstall 4096 Jan  9 16:34 hawk
drwxr-xr-x. 3 oracle oinstall 4096 Jan  9 16:31 hawklas
drwxr-xr-x. 3 oracle oinstall 4096 Jan  9 16:39 hawksan
drwxr-xr-x. 3 oracle oinstall 4096 Feb 21 18:17 test
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms
$ cd test/
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test
$ ll
total 4
-rw-r-----.  1 oracle oinstall    0 Feb 21 18:02 i_1.mif
drwxr-xr-x. 15 oracle oinstall 4096 Feb 21 18:17 test
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test
$ cd test/
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test/test
$ ll
total 52
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 alert
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 cdump
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 hm
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 incident
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 incpkg
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 ir
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:19 lck
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:19 metadata
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 metadata_dgif
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 metadata_pv
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 stage
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 sweep
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 trace
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test/test
$

Converting to Interval Paritions

Just a quick note to demonstrate how to convert date range partition into interval partition.

Create test case:

ARROW:(MDINH@hawklas):PRIMARY> @test_part.sql
ARROW:(MDINH@hawklas):PRIMARY> set echo on
ARROW:(MDINH@hawklas):PRIMARY> drop table t_part purge;

Table dropped.

ARROW:(MDINH@hawklas):PRIMARY> create table t_part (
  2  col_date DATE
  3  )
  4  PARTITION BY RANGE (col_date)
  5  (
  6  PARTITION P2014 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')),
  7  PARTITION P2015 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),
  8  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
  9  );

Table created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2014-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2015-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2016-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> commit;

Commit complete.

ARROW:(MDINH@hawklas):PRIMARY> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'T_PART',cascade=>DBMS_STATS.AUTO_CASCADE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => NULL);

PL/SQL procedure successfully completed.

Check partitions:

ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  PMAX                          1                  0 MAXVALUE

ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE                    3                      0                      1 USERS

Failed due to PMAX partition as shown above:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(numtodsinterval(1,'year'));
alter table t_part set interval(numtodsinterval(1,'year'))
*
ERROR at line 1:
ORA-14759: SET INTERVAL is not legal on this table.

Split partition to remove PMAX:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part split partition PMAX AT(TO_DATE('2017-01-01', 'YYYY-MM-DD')) into (partition P2017, partition PMAX);

Table altered.

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part drop partition pmax;

Table altered.

Check partition:

ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')


ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE                    3                      0                      1 USERS

Wrong syntax:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(numtodsinterval(1,'year'));
alter table t_part set interval(numtodsinterval(1,'year'))
                                                  *
ERROR at line 1:
ORA-14752: Interval expression is not a constant of the correct type

Correct syntax:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(NUMTOYMINTERVAL(1,'year'));

Table altered.

Check partition:

ARROW:(MDINH@hawklas):PRIMARY> @part
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE              1048575                      0                      1 USERS

Add data:

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2018-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2019-01-01','YYYY-MM-DD'));

1 row created.


ARROW:(MDINH@hawklas):PRIMARY> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'T_PART',cascade=>DBMS_STATS.AUTO_CASCADE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => NULL);

PL/SQL procedure successfully completed.

Check partition:

ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
2 FROM dba_tab_partitions i
3 WHERE table_name='T_PART'
4 ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               YES SYS_P541                      1                  0 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               YES SYS_P542                      1                  0 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')
ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
2 table_name,
3 partitioning_type,
4 subpartitioning_type,
5 partition_count,
6 def_subpartition_count,
7 partitioning_key_count,
8 def_tablespace_name
9 FROM DBA_PART_TABLES
10 WHERE partitioning_type='RANGE'
11 AND table_name='T_PART'
12 ;

OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH T_PART RANGE NONE 1048575 0 1 USERS

ARROW:(MDINH@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
oracle@arrow:hawklas:/media/sf_working/sql
$