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.
First I take a backup of this empty tablespace.
This 100MB datafile backed up to 1.03MB is Null Blocm compression.
I next load data into it and then take a backup.
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 ?
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.
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
The TRUNCATE did reduce the size of the table and the backup !
.
.
.
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:
Thanks Hemant for this post.
Foued
Post a Comment