结论:
首先rman备份时会备份被使用过的块。对表进行delete&shrink&move是无法提高rman的备份效率,resize可以。
delete、shrink、move不会改变备份集合大小。
首先rman备份时会备份被使用过的块。对表进行delete&shrink&move是无法提高rman的备份效率,resize可以。
delete、shrink、move不会改变备份集合大小。
下面将通过对比这个操作与初始表的备份集大小、花费时间来说明这个结论。
DB 10gR201 /OS redhat
1、创建一个1g的tbs,插入一个1g的表t
create tablespace tbs datafile '/u01/oradata/sales/tbs01' size 1024m;
create table t1 pctfree 99 pctused 0 tablespace tbs as select * from dba_objects where 1=0;
insert into t1 select * from dba_objects;
insert into t1 select * from dba_objects;
commit;
2、对表进行delete、shrink、move、resize等操作后用rman进行备份,记录备份时间以及备份集大小
SQL> col segment_name for a30
SQL> select segment_name,sum(blocks)*8/1024 from dba_extents
2 where tablespace_name = 'TBS' group by segment_name;
SEGMENT_NAME SUM(BLOCKS)*8/1024
------------------------------ ------------------
T1 792
SQL> select tablespace_name,sum(blocks)*8/1024 from dba_data_files
2 where tablespace_name = 'TBS' group by tablespace_name;
TABLESPACE_NAME SUM(BLOCKS)*8/1024
------------------------------------------------------------ ------------------
TBS 1024
SQL> select tablespace_name,sum(blocks)*8/1024 from dba_free_space
2 where tablespace_name = 'TBS' group by tablespace_name;
TABLESPACE_NAME SUM(BLOCKS)*8/1024
------------------------------------------------------------ ------------------
TBS 231.9375
SQL>
RMAN> backup tablespace tbs format='/tmp/tbs%U.bak';
Starting backup at 13-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=213 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/u01/oradata/sales/tbs01
channel ORA_DISK_1: starting piece 1 at 13-MAR-14
channel ORA_DISK_1: finished piece 1 at 13-MAR-14
piece handle=/tmp/tbs09p33hi1_1_1.bak tag=TAG20140313T190016 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 13-MAR-14
RMAN>
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:01 tbs09p33hi1_1_1.bak
2.2 delete mod(rownum,2)=0
SQL> col segment_name for a30
SQL> select segment_name,sum(blocks)*8/1024 from dba_extents
2 where tablespace_name = 'TBS' group by segment_name;
SEGMENT_NAME SUM(BLOCKS)*8/1024
------------------------------ ------------------
T1 792
SQL> select tablespace_name,sum(blocks)*8/1024 from dba_data_files
2 where tablespace_name = 'TBS' group by tablespace_name;
TABLESPACE_NAME SUM(BLOCKS)*8/1024
------------------------------------------------------------ ------------------
TBS 1024
SQL> select tablespace_name,sum(blocks)*8/1024 from dba_free_space
2 where tablespace_name = 'TBS' group by tablespace_name;
TABLESPACE_NAME SUM(BLOCKS)*8/1024
------------------------------------------------------------ ------------------
TBS 231.9375
SQL>
RMAN> backup tablespace tbs format='/tmp/tbs%U.bak';
Starting backup at 13-MAR-14
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=00006 name=/u01/oradata/sales/tbs01
channel ORA_DISK_1: starting piece 1 at 13-MAR-14
channel ORA_DISK_1: finished piece 1 at 13-MAR-14
piece handle=/tmp/tbs0ap33ia8_1_1.bak tag=TAG20140313T191312 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:37
Finished backup at 13-MAR-14
RMAN>
[oracle@laf ~]$ ls -l /tmp/tbs*
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:01 /tmp/tbs09p33hi1_1_1.bak
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:14 /tmp/tbs0ap33ia8_1_1.bak
[oracle@laf ~]$
2.3 shrink
SQL> alter table t1 shrink space;
Table altered.
SQL> col segment_name for a30
SQL> select segment_name,sum(blocks)*8/1024 from dba_extents
2 where tablespace_name = 'TBS' group by segment_name;
SEGMENT_NAME SUM(BLOCKS)*8/1024
------------------------------ ------------------
T1 .0625
SQL> select tablespace_name,sum(blocks)*8/1024 from dba_data_files
2 where tablespace_name = 'TBS' group by tablespace_name;
TABLESPACE_NAME SUM(BLOCKS)*8/1024
------------------------------------------------------------ ------------------
TBS 1024
SQL> select tablespace_name,sum(blocks)*8/1024 from dba_free_space
2 where tablespace_name = 'TBS' group by tablespace_name;
TABLESPACE_NAME SUM(BLOCKS)*8/1024
------------------------------------------------------------ ------------------
TBS 1023.875
SQL>
RMAN> backup tablespace tbs format='/tmp/tbs%U.bak';
Starting backup at 13-MAR-14
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=00006 name=/u01/oradata/sales/tbs01
channel ORA_DISK_1: starting piece 1 at 13-MAR-14
channel ORA_DISK_1: finished piece 1 at 13-MAR-14
piece handle=/tmp/tbs0bp33j66_1_1.bak tag=TAG20140313T192806 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:37
Finished backup at 13-MAR-14
RMAN>
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:01 /tmp/tbs09p33hi1_1_1.bak
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:14 /tmp/tbs0ap33ia8_1_1.bak
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:29 /tmp/tbs0bp33j66_1_1.bak
2.4 move
SQL>
SQL> alter table t1 move;
Table altered.
SQL> col segment_name for a30
SQL> select segment_name,sum(blocks)*8/1024 from dba_extents
2 where tablespace_name = 'TBS' group by segment_name;
SEGMENT_NAME SUM(BLOCKS)*8/1024
------------------------------ ------------------
T1 .0625
SQL> select tablespace_name,sum(blocks)*8/1024 from dba_data_files
2 where tablespace_name = 'TBS' group by tablespace_name;
TABLESPACE_NAME SUM(BLOCKS)*8/1024
------------------------------------------------------------ ------------------
TBS 1024
SQL> select tablespace_name,sum(blocks)*8/1024 from dba_free_space
2 where tablespace_name = 'TBS' group by tablespace_name;
TABLESPACE_NAME SUM(BLOCKS)*8/1024
------------------------------------------------------------ ------------------
TBS 1023.875
SQL>
RMAN> backup tablespace tbs format='/tmp/tbs%U.bak';
Starting backup at 13-MAR-14
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=00006 name=/u01/oradata/sales/tbs01
channel ORA_DISK_1: starting piece 1 at 13-MAR-14
channel ORA_DISK_1: finished piece 1 at 13-MAR-14
piece handle=/tmp/tbs0cp33jc1_1_1.bak tag=TAG20140313T193113 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
Finished backup at 13-MAR-14
RMAN>
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:01 /tmp/tbs09p33hi1_1_1.bak
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:14 /tmp/tbs0ap33ia8_1_1.bak
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:29 /tmp/tbs0bp33j66_1_1.bak
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:32 /tmp/tbs0cp33jc1_1_1.bak
[oracle@laf ~]$
4、resize
SQL> alter tablespace tbs resize 1m;
alter tablespace tbs resize 1m
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace TBS
SQL> alter database datafile 6 resize 1m;
Database altered.
SQL>
SQL>
SQL> col segment_name for a30
SQL> select segment_name,sum(blocks)*8/1024 from dba_extents
2 where tablespace_name = 'TBS' group by segment_name;
SEGMENT_NAME SUM(BLOCKS)*8/1024
------------------------------ ------------------
T1 .0625
SQL> col segment_name for a30
SQL> select segment_name,sum(blocks)*8/1024 from dba_extents
2 where tablespace_name = 'TBS' group by segment_name;
SEGMENT_NAME SUM(BLOCKS)*8/1024
------------------------------ ------------------
T1 .0625
SQL> select tablespace_name,sum(blocks)*8/1024 from dba_free_space
2 where tablespace_name = 'TBS' group by tablespace_name;
TABLESPACE_NAME SUM(BLOCKS)*8/1024
------------------------------------------------------------ ------------------
TBS .875
SQL>
RMAN> backup tablespace tbs format='/tmp/tbs%U.bak';
Starting backup at 13-MAR-14
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=00006 name=/u01/oradata/sales/tbs01
channel ORA_DISK_1: starting piece 1 at 13-MAR-14
channel ORA_DISK_1: finished piece 1 at 13-MAR-14
piece handle=/tmp/tbs0dp33jns_1_1.bak tag=TAG20140313T193732 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-MAR-14
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@laf ~]$ ls -l /tmp/tbs*
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:01 /tmp/tbs09p33hi1_1_1.bak
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:14 /tmp/tbs0ap33ia8_1_1.bak
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:29 /tmp/tbs0bp33j66_1_1.bak
-rw-r----- 1 oracle oinstall 830570496 Mar 13 19:32 /tmp/tbs0cp33jc1_1_1.bak
-rw-r----- 1 oracle oinstall 1081344 Mar 13 19:37 /tmp/tbs0dp33jns_1_1.bak
[oracle@laf ~]$