Search My Oracle Blog

Custom Search

19 February, 2016

RMAN : Unused Block Compression and Null Block Compression

To quote the 11.2 documentation,  "Although it is referred to as block compression, it might be helpful to think of block compression as block skipping".  11.2 differentiates between Unused Block Compression and Null Block Compression.
UPDATE 28-Feb-16 : Please note the list of 5 pre-requisites for Unused Block Compression.

In response to a community thread, here is a quick demo to see both Null Block Compression and Unused Block Compression.

I start with a 100MB tablespace.

[oracle@ora11204 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 19 22:37:47 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

SQL> create tablespace hemant;

Tablespace created.

SQL> alter user hemant quota unlimited on hemant;

User altered.

SQL> select file_name, bytes/1048576 from dba_data_files
  2  where tablespace_name = 'HEMANT';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
   100


SQL> 


First I take a backup of this empty tablespace.

RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp tag=TAG20160219T224255 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904257776_cdgbhkcx_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    1.03M      DISK        00:00:01     19-FEB-16      
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224255
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4806951    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN> 

This 100MB datafile backed up to 1.03MB is Null Blocm compression.

I next load data into it and  then take a backup.

[oracle@ora11204 Desktop]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 19 22:45:12 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

SQL> create table hkc_all_obj tablespace hemant
  2  as select * from dba_objects;

Table created.

SQL> insert into hkc_all_obj select * from hkc_all_obj;

28105 rows created.

SQL> /

56210 rows created.

SQL> /

112420 rows created.

SQL> /

224840 rows created.

SQL> commit;              

Commit complete.

SQL> select segment_name, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'HEMANT';

SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
HKC_ALL_OBJ
    49


SQL> 
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp tag=TAG20160219T224721 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258049_cdgbr179_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    1.03M      DISK        00:00:01     19-FEB-16      
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224255
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4806951    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27      Full    49.67M     DISK        00:00:04     19-FEB-16      
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224721
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
  List of Datafiles in backup set 27
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4807559    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN> 


So, I now have 49.67MB backup of the datafile in the tablespace. What happens if I drop all objects (in this case only the 1 table) in that tablespace, with RECYLEBIN ?

SQL> show parameter recycle

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle       string
db_recycle_cache_size       big integer 0
recyclebin        string  on
SQL> drop table hkc_all_obj;

Table dropped.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
  2  from user_recyclebin;

OBJECT_NAME         ORIGINAL_NAME
------------------------------ --------------------------------
TS_NAME        SPACE SPACE*8192/1048576
------------------------------ ---------- ------------------
BIN$LCGWc71HDSzgUwEAAH/27g==$0 HKC_ALL_OBJ
HEMANT         6272    49


SQL> 
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp tag=TAG20160219T225023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258224_cdgbxjg4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    1.03M      DISK        00:00:01     19-FEB-16      
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224255
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4806951    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27      Full    49.67M     DISK        00:00:04     19-FEB-16      
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224721
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
  List of Datafiles in backup set 27
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4807559    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29      Full    49.67M     DISK        00:00:00     19-FEB-16      
        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T225023
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp
  List of Datafiles in backup set 29
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4807909    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN> 


The latest backup (BackupSet 29) is still 49.67MB although, technically, the tablespace has no Tables/Indexes.  It does have something in the RECYCLEBIN.

Let me purge the RECYCLEBIN and then run a fresh backup.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
  2  from user_recyclebin;

OBJECT_NAME         ORIGINAL_NAME
------------------------------ --------------------------------
TS_NAME        SPACE SPACE*8192/1048576
------------------------------ ---------- ------------------
BIN$LCGWc71HDSzgUwEAAH/27g==$0 HKC_ALL_OBJ
HEMANT         6272    49


SQL> purge hkc_all_obj;
purge hkc_all_obj
      *
ERROR at line 1:
ORA-38302: invalid PURGE option


SQL> purge table hkc_all_obj;

Table purged.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
  2  from user_recyclebin;

no rows selected

SQL> 
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225323_cdgc33tc_.bkp tag=TAG20160219T225323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258404_cdgc351n_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    1.03M      DISK        00:00:01     19-FEB-16      
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224255
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4806951    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27      Full    49.67M     DISK        00:00:04     19-FEB-16      
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224721
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
  List of Datafiles in backup set 27
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4807559    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29      Full    49.67M     DISK        00:00:00     19-FEB-16      
        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T225023
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp
  List of Datafiles in backup set 29
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4807909    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31      Full    1.03M      DISK        00:00:00     19-FEB-16      
        BP Key: 31   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T225323
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225323_cdgc33tc_.bkp
  List of Datafiles in backup set 31
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4808966    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN> 
[oracle@ora11204 Desktop]$ ls -l /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
-rw-rw----. 1 oracle oracle 104865792 Feb 19 22:53 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
[oracle@ora11204 Desktop]$ 


Aha ! BackupSet 31 is now 1.03MB only.  So, Unused Block Compression has kicked in !  The blocks that belonged to that table are yet formatted and in prior versions that did not have Unused Block Compression would still be backed up even though the table has been dropped.
(Note : I did not shrink the datafile, it is still 100MB).


UPDATE 28-Feb-16 :  Testing for TRUNCATE

SQL> select segment_name, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'HEMANT';

SEGMENT_NAME           BYTES/1048576
--------------------------------------------------------------------------------- -------------
HKC_ALL_OBJ               49

SQL> 
RMAN> backup tablespace hemant;

Starting backup at 28-FEB-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf
channel ORA_DISK_1: starting piece 1 at 28-FEB-16
channel ORA_DISK_1: finished piece 1 at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp tag=TAG20160228T190655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 28-FEB-16

Starting Control File and SPFILE Autobackup at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2016_02_28/o1_mf_s_905022445_cf5o7lyq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-16

RMAN> list backup of tablespace hemant completed after "trunc(sysdate)";


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    49.55M     DISK        00:00:22     28-FEB-16      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160228T190655
        Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4776386    28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

RMAN> 
SQL> show parameter recyclebin;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
recyclebin        string  on
SQL> truncate table hkc_all_obj;

Table truncated.

SQL> select segment_name, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'HEMANT';

SEGMENT_NAME           BYTES/1048576
--------------------------------------------------------------------------------- -------------
HKC_ALL_OBJ            .0625

SQL> 
RMAN> backup tablespace hemant;

Starting backup at 28-FEB-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf
channel ORA_DISK_1: starting piece 1 at 28-FEB-16
channel ORA_DISK_1: finished piece 1 at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190946_cf5octqc_.bkp tag=TAG20160228T190946 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 28-FEB-16

Starting Control File and SPFILE Autobackup at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2016_02_28/o1_mf_s_905022589_cf5oczwq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-16

RMAN> list backup of tablespace hemant completed after "trunc(sysdate)";


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    49.55M     DISK        00:00:22     28-FEB-16      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160228T190655
        Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4776386    28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    1.53M      DISK        00:00:01     28-FEB-16      
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160228T190946
        Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190946_cf5octqc_.bkp
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4776656    28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

RMAN> 


The TRUNCATE did reduce the size of the table and the backup !
.
.
.

1 comment:

Foued said...

Thanks Hemant for this post.
Foued

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016