Skip to Main Content
  • Questions

  • RMAN unused block compression against TABLE drop vs TABLE with lob column compression.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ram.

Asked: January 26, 2017 - 12:19 am UTC

Last updated: February 19, 2017 - 9:50 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi Tom,

I am just trying to understand RMAN unused block compression against TABLE with LOB column compression vs TABLE drop.
Consider LOB data for column 'DATA' in a table 'EMPLOYEES' under SCOTT schema.

- lob segment
SELECT SEGMENT_NAME FROM DBA_LOBS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = 'DATA';
SEGMENT_NAME
--------------------------
LOBD_EMPLOYEES_DATA

CASE 1 : RMAN unused block compression against TABLE with LOB column compression
=================================================================================
SECTION 1 : PRE-LOB COMPRESS INFO
=================================
1- Storage size of the LOBSEGMENT
SELECT SUM(BYTES)/1024/1024 "STORAGE_MB" FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'LOBD_EMPLOYEES_DATA';
STORAGE_MB
----------
183420
2- Determine the size of the LOB DATA
select sum(dbms_lob.getlength(DATA))/1024/1024 sizeMB from SCOTT.EMPLOYEES;
SIZEMB
----------
16324.356
3- database RMAN backup size - 1.5 TB
RMAN backup scirpt used is as below

run {
configure backup optimization on;
configure device type disk parallelism 2 BACKUP TYPE TO BACKUPSET;
configure channel device type disk maxpiecesize 8G;

crosscheck archivelog all ;
delete force noprompt expired archivelog all ;
backup
filesperset 1
full
format '/backup/<directory name>/xxxx.set%s.piece%p'
(database) plus archivelog ;
}


SECTION 2 : POST-LOB COMPRESS INFO [ we used gzip algorithem to compress the lob data ]
===================================
1- Storage size of the LOBSEGMENT remains the same after the LOB compression.
SELECT SUM(BYTES)/1024/1024 "STORAGE_MB" FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'LOBD_EMPLOYEES_DATA';
STORAGE_MB
----------
183420

2- size of the LOB DATA got reduced.
select sum(dbms_lob.getlength(DATA))/1024/1024 sizeMB from SCOTT.EMPLOYEES;
SIZEMB
----------
6093.90531

3- size of the data that is not LOB DATA.this tells us the Undo Data size (expired + unexpired OR pctversion) + Unused space
183420 - 6093 = 177327 MB

4- To reclaim the unused space, I have used the shrink space on the lob segment.
alter table SCOTT.EMPLOYEES modify lob(DATA) (shrink space);
Table altered.
Elapsed: 05:44:20.71

5- Storage size of the LOBSEGMENT got reduced from 183 GB to 8.9 GB
SELECT SUM(BYTES)/1024/1024 "STORAGE_MB" FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'LOBD_EMPLOYEES_DATA';
STORAGE_MB
----------
8970

There have been many empty blocks [ wihch were used earlier ] created during the shrink space process as it reduced the size from 183 GB to 8.9 GB.
6 - I was under the impression that database RMAN backup should be reduced by ~170GB [ 183-8.9] as RMAN uses unused block compression, which should skip the empty blocks created due to shrink space.
FYI . our environment satisfy all the conditions for the RMAN to use unused block compression. I have performed the tests as well by referring https://hemantoracledba.blogspot.com/2016/02/rman-unused-block-compression-and-null.html
.
7- Have taken the fresh database RMAN using the script mentioned in section 1.3 .To my surprise database RMAN backup remain the same i.e 1.5TB.

Could you please throw some lihgt on what am I missing here ? why is database RMAN backup size not getting reduced ?


CASE 2 : RMAN unused block compression against TABLE drop
===========================================================
- I could see the difference in RMAN backup of the tablespace 'TESTLOB' size with respect to PRE and POST table 'EMPLOYEES' drop . Hence RMAN is using the unused block compression here.

Does RMAN behave differently against lob compression vs table drop ?

Thanks,
Ram

and Connor said...

I tried various permutations of

- securefile, modify to compress
- basicfile, delete rows and shrink space

I'm seeing what I would expect from all situations here, ie, a DROP, a MOVE or a SHRINK SPACE reduces the segment size and the backups reduce accordingly

SQL> create tablespace DEMO datafile 'C:\ORACLE\ORADATA\NP12\DEMO.DBF' size 200m;

Tablespace created.

SQL> create table t (
  2    pk int,
  3    c clob )
  4  lob(c) store as securefile ( nocompress)
  5  tablespace demo;

Table created.

SQL>
SQL> insert into t
  2  select rownum, rpad('x',32000,'x')
  3  from dual
  4  connect by level <= 5000;

5000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, bytes
  2  from dba_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                  327680
SYS_IL0000203091C00002$$            65536
SYS_LOB0000203091C00002$$       184745984

RMAN> backup tablespace demo format 'c:\temp\demo.1';

Starting backup at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=403 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=C:\ORACLE\ORADATA\NP12\DEMO.DBF
channel ORA_DISK_1: starting piece 1 at 19-FEB-17
channel ORA_DISK_1: finished piece 1 at 19-FEB-17
piece handle=C:\TEMP\DEMO.1 tag=TAG20170219T173436 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-FEB-17

C:\Users\comcdona>dir c:\temp\DEMO.1
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of c:\temp

19/02/2017  05:34 PM       166,674,432 DEMO.1

SQL> drop table t purge;

Table dropped.

RMAN> backup tablespace demo format 'c:\temp\demo.2';

Starting backup at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=403 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=C:\ORACLE\ORADATA\NP12\DEMO.DBF
channel ORA_DISK_1: starting piece 1 at 19-FEB-17
channel ORA_DISK_1: finished piece 1 at 19-FEB-17
piece handle=C:\TEMP\DEMO.2 tag=TAG20170219T173645 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-17


C:\temp>dir DEMO.2
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of C:\temp

19/02/2017  05:36 PM         1,089,536 DEMO.2

SQL> alter table T modify lob(c) (compress high);

Table altered.

SQL> select segment_name, bytes
  2  from dba_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                  786432
SYS_IL0000203094C00002$$            65536
SYS_LOB0000203094C00002$$       184745984

RMAN> backup tablespace demo format 'c:\temp\demo.3';

Starting backup at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=403 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=C:\ORACLE\ORADATA\NP12\DEMO.DBF
channel ORA_DISK_1: starting piece 1 at 19-FEB-17
channel ORA_DISK_1: finished piece 1 at 19-FEB-17
piece handle=C:\TEMP\DEMO.3 tag=TAG20170219T173938 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 19-FEB-17

RMAN> quit


Recovery Manager complete.

C:\temp>dir demo.3
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of C:\temp

19/02/2017  05:39 PM       169,918,464 DEMO.3
               1 File(s)    169,918,464 bytes
               0 Dir(s)  47,581,122,560 bytes free
               
SQL> alter table T move lob ( c ) store as securefile (compress high);

Table altered.

SQL> select segment_name, bytes
  2  from dba_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                  655360
SYS_IL0000203094C00002$$            65536
SYS_LOB0000203094C00002$$          131072


SQL> create table t (
  2    pk int,
  3    c clob )
  4  lob(c) store as basicfile
  5  tablespace demo;

Table created.

SQL>
SQL> insert into t
  2  select rownum, rpad('x',32000,'x')
  3  from dual
  4  connect by level <= 5000;

5000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> select segment_name, bytes
  2  from dba_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                  393216
SYS_IL0000203103C00002$$            65536
SYS_LOB0000203103C00002$$       167772160

SQL> delete from t
  2  where rownum < 4000;

3999 rows deleted.

SQL> commit;

Commit complete.


RMAN> backup tablespace demo format 'c:\temp\demo.4';

Starting backup at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=207 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=C:\ORACLE\ORADATA\NP12\DEMO.DBF
channel ORA_DISK_1: starting piece 1 at 19-FEB-17
channel ORA_DISK_1: finished piece 1 at 19-FEB-17
piece handle=C:\TEMP\DEMO.4 tag=TAG20170219T174611 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 19-FEB-17

C:\temp>dir DEMO.4
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of C:\temp

19/02/2017  05:46 PM       169,910,272 DEMO.4

SQL> alter table t modify lob (c) (shrink space);

Table altered.

SQL> select segment_name, bytes
  2  from dba_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                  393216
SYS_IL0000203103C00002$$           458752
SYS_LOB0000203103C00002$$        33488896

RMAN> backup tablespace demo format 'c:\temp\demo.5';

Starting backup at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=403 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=C:\ORACLE\ORADATA\NP12\DEMO.DBF
channel ORA_DISK_1: starting piece 1 at 19-FEB-17
channel ORA_DISK_1: finished piece 1 at 19-FEB-17
piece handle=C:\TEMP\DEMO.5 tag=TAG20170219T174811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-17

C:\temp>dir DEMO.5
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of C:\temp

19/02/2017  05:48 PM        35,692,544 DEMO.5



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here