对表进行delete&shrink&move无法提高rman的备份效率

最新推荐文章于 2021-04-04 02:11:19 发布
最新推荐文章于 2021-04-04 02:11:19 发布
阅读量853 收藏
点赞数
分类专栏： Oracle Concept 文章标签： delete shrink resize Oracle rman
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/songyongbo_107/article/details/22992319
版权
结论：
首先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 ~]$


songyb
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
 基于轮廓变换的自适应阈值图像去噪算法
01-30
该方法首先对噪声图像进行Contourlet变换，得到不同尺度、不同方向上的子带系数，再根据子带系数的能量关系采用改进的Bayes Shrink自适应阈值对图像进行去噪处理。实验结果表明：这种方法提高了图像的PSNR值，增强了...
oracle10g数据库热备份,Oracle数据库热备份文件过大收缩方法
weixin_32538033的博客
04-02 267
ORA-00257: archiver error. Connect internalonly, until freed.Oracle 10g数据库物理空间管理方式与以前Oracle发生了变化，对归档日志所在的Flash_Recovery_Area空间进行了另外限制Flash_Recovery_Area空间缺省安装时比较小，只有2GB，容易用完解决过程根据数据库目前可用存储空间情况、FLASH_R...
测试truncate，delete 对rman 备份集大小的影响
clep17285的博客
03-11 116
思路： 创建5张一模一样大小的表并插入blob 字段，表b3 在表空间b3上，表b4 在表空间b4上，表b7 在表空间b4上，表b5 在表空间b5上，。b8 在表空间b8上 create table xx(id int ,i...
oracle 压缩备份比率,RMAN备份压缩你了解多少
weixin_28876083的博客
04-04 522
RMAN备份压缩你了解多少1、RMAN备份默认已经进行压缩1)不用的块压缩When employing unused block compression, RMAN skips reading, and backing up, any database blocks that are not currently allocated to some database object. This is ...
SQL DELETE 语句
Cactus
08-01 4361
SQL DELETE 语句 DELETE 语句 DELETE 语句用于删除表中的行。 语法 DELETE FROM 表名称 WHERE 列名称 = 值 Person: LastName FirstName Address City
oracle的数据清理（move命令）
08-05 527
Oracle的数据清理（move命令）       从8i开始，oracle开始提供Move的命令。我们通常使用这个命令，将一个table segment从一个tablespace移动到另一个tablespace。Move实际上是在block之间物理的copy数据，那么
泛型<编程>：类型化缓存(II)
02-04
我们勾画了一个非常类似于std::vector的模板类buffer，除了buffer没有容积概念，并且增加了一些基本函数，比如grow_noinit和shrink_nodestroy。此外，前文提到把类型特性(typetraits)作为一个提供优化的技术手段。...
shrink_zoom_bilinear.zip_bilinear shrink_zoom
07-13
这个程序是为了实现图片（包括彩色图和灰度图）的放缩功能，能满足任意指定放缩倍数或任意目标尺寸，原理是依据双线性插值。
DVD影片备份工具（DVD Shrink） 3.20 绿色汉化版
10-26
DVD影片备份工具（DVD Shrink） 3.20 绿色汉化版
shrink-string:适用于Node的微型字符串压缩模块
05-04
npm i shrink-string 用法 const { compress , decompress } = require ( 'shrink-string' ) // `compress` takes a unicode string and returns a base64 string // `decompress` takes that base64 string and ...
Oracl误删数据表的恢复方法
08-19 363
使用SCN对误删数据的恢复
数据库中的move操作
【洪波@阿里云】
05-02 2270
本文的几个内容是一直就有点迷糊的,今天清理了一遍,记录备忘.在我们的系统中,有大量这样的需求:把表goods中的数据,move到表spec_goods中. (两个表的列是相同的).一般来说,我们知道,最标准的写法是这样的:insert into spec_goods(c1,c2,c3)  select c1,c2,c3 from goods where c3=A;但我们系统中,这样的表很多,每
关于分区表的move操作（r2笔记90天)
杨建荣的学习笔记
09-09 287
关于分区表的move操作还是很值得深究的一个问题。如果分区表中含有lob字段，难度还会加大。一般来说，如果尝试对分区表进行move操作，会得到如下的错误警告。ORA-1...
delete 之后shrink table 释放表空间
a1008223的专栏
05-07 256
http://blog.csdn.net/tianlesoftware/article/details/4764254 SQL> alter table my_objects enable row movement; Table altered SQL> alter table my_objects shrink space; Table altered
code 6101 和 code 6111的解决办法
bruce
03-02 2706
<<<< code 6101 和 code 6111的解决办法 <<<< <<<< kdbchk: row locked by non-existent transaction <<<< table=0 slot=0 <<<< lockid=2...
11g RAC 时间向回调整
bruce
06-27 2701
NTP时间同步超前，需要将集群时间向后调整。 下面是调整过程的测试，调整步骤： 1、停集群 2、修改系统时间 3、同步硬件时钟 4、启动集群 Last login: Mon Mar 11 01:30:25 2013 from 196.128.1.234 [root@node1 ~]# [root@node1 ~]# [root@node1 ~]# uname -a
Oracle为什么不按照estimate_percent来分析表？
bruce
11-27 2134
DB 11.2.0.3.0 redhat 5.7 创建一个测试表： SQL> conn user1/oracle Connected. SQL> create table test1 as select * from dba_objects; Table created. SQL> col table_name for a10 SQL> select table_name, num_ro
quorum failgroup disk (1)
bruce
06-27 1902
当前状态： 当前+SYS有4块盘，normal redundancy，存放ocr&vote。 目标： 将+SYS修改为3块盘，normal redundancy，并且一个是quorum failgroup。然后冗余OCR到+DATA。 操作步骤简要： 1、从+SYS中删除2块盘 alter diskgroup sys drop regular disk 'SYS_0002','SY
关于监听密码的设置问题
bruce
08-29 1665
pre-10gR1 ======= 有两种办法设置密码，分为明文和暗文： 1.Clear text Password (i)Add PASSWORDS_ entry to your existing listener.ora file. e.g.PASSWORDS_listener1 = (p1,p2) (ii)Stop your listener, and restart it. Now p
vector<string>& GetVecStrSerial()
最新发布
03-26
It is a dynamic array that can grow or shrink as needed, and provides efficient random access to its elements. To use a vector, you first need to include the <vector> header file: ``` #include ```...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值