TIP #41: Estimate backup size (2)

I posted a blog on March 3rd, 2007 about estimation of RMAN backup size (TIP #25) .One of my collegue suggested me to run test for 10gR2.
I ran the following steps in 10.2.0.2 on AIX5 64bit. It seems that RMAN behavior in 10.2.0.2 is closer to reality however it is not 100% precise to database size .
To sum up I would say that Before 10.2.0.2 , backup size can not be the same size as dba_segments (RMAN only does not backup never touched blocks), while in 10.2.0.2 backup size of full database is roughly the same as database.

Sample test in 10gR2. (Same scenario as 10.1).

=== Create new tablespace
SQL> create tablespace rmantst datafile '/o022/test/rmantst.dbf' size 200M;
Tablespace created.

-- Taking RMAN backup from empty tablespace
run{
backup tablespace rmantst format '/o022/test/backup_after_ts_creation_%s.bak';
}

Starting backup at 15-APR-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=533 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00028 name=/o022/test/rmantst.dbf
channel ORA_DISK_1: starting piece 1 at 15-APR-07
channel ORA_DISK_1: finished piece 1 at 15-APR-07
piece handle=/o022/test/backup_after_ts_creation_4.bak tag=TAG20070415T112434 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-APR-07

Starting Control File and SPFILE Autobackup at 15-APR-07
piece handle=/o022/backup/control_back_c-1721358348-20070415-01 comment=NONE
Finished Control File and SPFILE Autobackup at 15-APR-07

-- Check backup size

oracle(/o022/test):ls -l

total 410064
-rw-r----- 1 oracle dba 209723392 Apr 15 11:24 rmantst.dbf
-rw-r----- 1 oracle dba 106496 Apr 15 11:24 backup_after_ts_creation_4.bak (Backup of empty tablespace is only 106KB).

---- Create some objects in rmantst tablespace

create table rmantest1 tablespace rmantst as select * from dba_objects;
create table rmantest2 tablespace rmantst as select * from dba_objects;
create table rmantest3 tablespace rmantst as select * from dba_objects;
create table rmantest4 tablespace rmantst as select * from dba_objects;
create table rmantest5 tablespace rmantst as select * from dba_objects union all select * from dba_objects union all select * from dba_objects union all select * from dba_objects;
create table rmantest6 tablespace rmantst as select * from dba_objects union all select * from dba_objects union all select * from dba_objects union all select * from dba_objects;
create table rmantest7 tablespace rmantst as select * from dba_objects union all select * from dba_objects union all select * from dba_objects union all select * from dba_objects;

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_segments where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
112 <== 112MB of 200MB was allocated.

--- Take tablespace backup

RMAN> run
2> {
backup tablespace rmantst format '/o022/test/backup_after_tbl_creation_%s.bak';
}
3> 4>
Starting backup at 15-APR-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=488 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00028 name=/o022/test/rmantst.dbf
channel ORA_DISK_1: starting piece 1 at 15-APR-07
channel ORA_DISK_1: finished piece 1 at 15-APR-07
piece handle=/o022/test/backup_after_tbl_creation_6.bak tag=TAG20070415T113143 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-APR-07
Starting Control File and SPFILE Autobackup at 15-APR-07
piece handle=/o022/backup/control_back_c-1721358348-20070415-02 comment=NONE
Finished Control File and SPFILE Autobackup at 15-APR-07
RMAN> exit

Recovery Manager complete.
oracle(/o022/test):ls -l
total 634320
-rw-r----- 1 oracle dba 114786304 Apr 15 11:31 backup_after_tbl_creation_6.bak (Backup size is ~ 114M)
-rw-r----- 1 oracle dba 106496 Apr 15 11:24 backup_after_ts_creation_4.bak
-rw-r----- 1 oracle dba 209723392 Apr 15 11:31 rmantst.dbf

-- Remove data from all objects in tablespace.

SQL> truncate table rmantest2;
Table truncated.
SQL> truncate table rmantest1;
Table truncated.
SQL> truncate table rmantest3;
Table truncated.
SQL> truncate table rmantest4;
Table truncated.
SQL> truncate table rmantest5 ;
Table truncated.
SQL> truncate table rmantest6;
Table truncated.
SQL> truncate table rmantest7;
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_segments where tablespace_name='RMANTST';
SUM(BYTES)/1024/1024
--------------------
.4375 <== only 400K was allocated.

-- Take RMAN backup

run
{
backup tablespace rmantst format '/o022/test/backup_after_tbl_truncate_%s.bak';
}

oracle(/o022/test):ls -ltr
total 641792
-rw-r----- 1 oracle dba 106496 Apr 15 11:24 backup_after_ts_creation_4.bak
-rw-r----- 1 oracle dba 114786304 Apr 15 11:31 backup_after_tbl_creation_6.bak
-rw-r----- 1 oracle dba 209723392 Apr 15 11:35 rmantst.dbf
-rw-r----- 1 oracle dba 3825664 Apr 15 11:35 backup_after_tbl_truncate_8.bak ( Backup after truncate is only 3MB. It is not exactly the same as dba_segments but it is not as bad as 10.2.0.1).