TIP #25: Estimate backup size (1)

Before taking a backup, it would be idea to estimate backup size.With Estimation of backup size, we can make sure that there is enough disk space for backup.
Regarding to my investigation, there is no official way to find backup size before taking backup.
However, Oracle comments is : "RMAN does not take backup from NEVER USED blocks" .
Never used blocks are blocks which have not ever been touched.It is different from empty blocks. From the following sample test which I ran, I found that empty blocks were definitely backed up.

Test
=====

== Scenario1 : Create new tablespace and backup (TS size:200M)

SQL> create tablespace rmantst datafile 'C:\ORACLE\ORADATA\ORA10GPR\rmantst.dbf' size 200M;
Tablespace created.
RMAN> connect target /
connected to target database: ORA10GPR (DBID=1394790175)
RMAN> run

2> {
3> backup tablespace rmantst format 'c:\backup_after_ts_creation_%s.bak';
4> }

Starting backup at 28-FEB-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=138 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=C:\ORACLE\ORADATA\ORA10GPR\RMANTST.DBF
channel ORA_DISK_1: starting piece 1 at 28-FEB-07
channel ORA_DISK_1: finished piece 1 at 28-FEB-07
piece handle=C:\BACKUP_AFTER_TS_CREATION_29.BAK tag=TAG20070228T225029 comment=N
ONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 28-FEB-07
Starting Control File and SPFILE Autobackup at 28-FEB-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORA10GPR\AUTOBACKUP\2007_02_28\O1_MF_
S_615768645_2YDMDPG5_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-07
RMAN>

BACKUP_AFTER_TS_CREATION_29.BAK ==> 96KB.

== Scenario2 : Fill up tablespace with data (Fill 163MB of 200MB tablespace)

SQL> create table rmantest1 tablespace rmantst as select * from dba_objects;
Table created.
SQL> create table rmantest2 tablespace rmantst as select * from SOURCE$;
Table created.
SQL> create table rmantest3 tablespace rmantst as select * from SOURCE$;
Table created.
SQL> create table rmantest4 tablespace rmantst as select * from SOURCE$ nologging;
Table created.
SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='RMANTST';

SUM(BYTES)/1024/1024
--------------------
200


SQL> select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
37.9375


SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
162


BACKUP_AFTER_TABLES_CREATION_IN_TS_31.BAK ==> 163 MB.

== Scenario3 : Delete Objects in tablespace (Remove most objects ... Only 6MB was allocated for 200MB tablespace)

SQL> truncate table rmantest2;
Table truncated.
SQL> truncate table rmantest3;
Table truncated.
SQL> truncate table rmantest4;
Table truncated.

SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
200
SQL> select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
193.75
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
6.1875 Only 6.1MB was allocated in RMANTST

RMAN> run
2> {
3> backup tablespace rmantst format 'c:\backup_after_tables_truncate_in_ts_%s.ba
k';
4> }
Starting backup at 28-FEB-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=C:\ORACLE\ORADATA\ORA10GPR\RMANTST.DBF
channel ORA_DISK_1: starting piece 1 at 28-FEB-07
channel ORA_DISK_1: finished piece 1 at 28-FEB-07
piece handle=C:\BACKUP_AFTER_TABLES_TRUNCATE_IN_TS_33.BAK tag=TAG20070228T231456
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 28-FEB-07
Starting Control File and SPFILE Autobackup at 28-FEB-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORA10GPR\AUTOBACKUP\2007_02_28\O1_MF_
S_615770131_2YDNV53F_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-07

BACKUP_AFTER_TABLES_TRUNCATE_IN_TS_33.BAK ==> 163 M <== This proves that only NEVER used blocks are not being backed up. EMPTY blocks are backed up if they have already been touched. Therefore, it is not true to say dba_segments can always give backup size .

FYI : Scenarios were run in 10.2.0.1. I will test these in 10.2.0.2

1 comment:

vaibhav said...

Hi Shervin,

I did following testcase in 10.2.0.3.0 database

Created tablespace USERS2 of size 200M

Created and populated table TEST in tablespace USERS2

SQL> select bytes/(1024*1024) from dba_free_space where tablespace_name='USERS2';
BYTES/(1024*1024)
-----------------
7.9375

SQL> select count(*) from dba_extents where segment_name='TEST';
COUNT(*)
----------
95

RMAN> backup tablespace users2 format 'c:\hot_bkp\tbs%U';

Starting backup at 02-JUL-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=C:\ORADATA\ORA10GR2DB1\USERS2.DBF
channel ORA_DISK_1: starting piece 1 at 02-JUL-07
channel ORA_DISK_1: finished piece 1 at 02-JUL-07
piece handle=C:\HOT_BKP\TBS1UILRMID_1_1 tag=TAG20070702T210205 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-JUL-07

Size of backup piece ===> 192 M

SQL> truncate table test;
Table truncated.

SQL> select count(*) from dba_extents where segment_name='TEST';
COUNT(*)
----------
1

SQL> select bytes/(1024*1024) from dba_free_space where tablespace_name='USERS2';
BYTES/(1024*1024)
-----------------
199.875

RMAN> backup tablespace users2 format 'c:\hot_bkp\tbs%U';
Starting backup at 02-JUL-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=C:\ORADATA\ORA10GR2DB1\USERS2.DBF
channel ORA_DISK_1: starting piece 1 at 02-JUL-07
channel ORA_DISK_1: finished piece 1 at 02-JUL-07
piece handle=C:\HOT_BKP\TBS20ILRN9L_1_1 tag=TAG20070702T211429 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 02-JUL-07

Size of backup piece ===> 616 KB

Thanks and Regards,
Vaibhav