[20160112]rman会备份空块吗.txt

[20160112]rman会备份空块吗.txt

-- 论坛上有人问的问题,我认为会的,那些delete完所有数据的block会备份的:
http://www.itpub.net/thread-2050864-1-1.html
如果是从未被使用过的block，rman不会备份。但那些delete完所有数据的block，rman会备份吗？

--我以前做过测试关于truncate的情况:
http://blog.itpub.net/267265/viewspace-750049/

1.测试环境:

SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLESPACE SUGAR DATAFILE
  '/mnt/ramdisk/book/sugar01.dbf' SIZE 100M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--上面没有任何对象.

RMAN> backup tablespace sugar format '/home/oracle/backup/sugar1_%u' ;

$ ll -l /home/oracle/backup/
total 1068
-rw-r----- 1 oracle oinstall 1089536 2016-01-12 15:31:28 sugar1_16qr6omg

2.建立表在其表空间上面:

SCOTT@book> create table tt1 tablespace sugar as select rownum id,'TESTTESTTEST' name from dual connect by level<=2e5;
Table created.

SCOTT@book> select bytes,blocks from dba_segments where owner=user and segment_name='TT1';
     BYTES     BLOCKS
---------- ----------
   6291456        768

RMAN> backup tablespace sugar format '/home/oracle/backup/sugar2_%u' ;

$ ll -l /home/oracle/backup/
total 8288
-rw-r----- 1 oracle oinstall 1089536 2016-01-12 15:31:28 sugar1_16qr6omg
-rw-r----- 1 oracle oinstall 7380992 2016-01-12 15:34:36 sugar2_17qr6osc

3.删除全部后在备份看看:

SCOTT@book> delete from tt1 ;
200000 rows deleted.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> alter system checkpoint;
System altered.

SCOTT@book> select bytes,blocks from dba_segments where owner=user and segment_name='TT1';
     BYTES     BLOCKS
---------- ----------
   6291456        768

--占用空间依旧.
RMAN> backup tablespace sugar format '/home/oracle/backup/sugar3_%u' ;

$ ll -l /home/oracle/backup/
total 15508
-rw-r----- 1 oracle oinstall 1089536 2016-01-12 15:31:28 sugar1_16qr6omg
-rw-r----- 1 oracle oinstall 7380992 2016-01-12 15:34:36 sugar2_17qr6osc
-rw-r----- 1 oracle oinstall 7380992 2016-01-12 15:36:09 sugar3_18qr6ov9

--可以发现即使全部删除了数据,这些块还是会备份的.

$ strings sugar3_18qr6ov9 | grep "TESTTESTTEST" |wc
200000  200000 2799557

--以上语句也可以看出来,备份存在2e5次TESTTESTTEST字串.

4.truncate后再测试:

SCOTT@book> truncate table tt1;
Table truncated.

SCOTT@book> select bytes,blocks from dba_segments where owner=user and segment_name='TT1';
     BYTES     BLOCKS
---------- ----------
     65536          8

RMAN> backup tablespace sugar format '/home/oracle/backup/sugar4_%u' ;

$ ll -l /home/oracle/backup/
total 17088
-rw-r----- 1 oracle oinstall 1089536 2016-01-12 15:31:28 sugar1_16qr6omg
-rw-r----- 1 oracle oinstall 7380992 2016-01-12 15:34:36 sugar2_17qr6osc
-rw-r----- 1 oracle oinstall 7380992 2016-01-12 15:36:09 sugar3_18qr6ov9
-rw-r----- 1 oracle oinstall 1613824 2016-01-12 15:38:55 sugar4_19qr6p4f

--可以发现truncate的备份明显变小.

$ strings sugar4_19qr6p4f | grep "TESTTESTTEST" |wc
  18672   18672  261350

--即使这样,在备份中还是存在18672个TESTTESTTEST字符串.
--而且上面第4次备份比第1次备份 1613824-1089536=524288,多了512K.也就是说明即使我truncate表,oracle也不是备份8块(64K).而是还多备份512K数据.
--512/8=64 也就是多备份64块.

5.继续探究:

SCOTT@book> drop table tt1 purge ;
Table dropped.

SCOTT@book> create table tt1 tablespace sugar as select rownum id,to_char(rownum,'000000')||'TESTAA' name from dual connect by level<=2e5;
Table created.

SCOTT@book> truncate table tt1;
Table truncated.

RMAN> backup tablespace sugar format '/home/oracle/backup/sugar5_%u' ;

$ ll -l /home/oracle/backup/su*
-rw-r----- 1 oracle oinstall 1089536 2016-01-12 15:31:28 /home/oracle/backup/sugar1_16qr6omg
-rw-r----- 1 oracle oinstall 7380992 2016-01-12 15:34:36 /home/oracle/backup/sugar2_17qr6osc
-rw-r----- 1 oracle oinstall 7380992 2016-01-12 15:36:09 /home/oracle/backup/sugar3_18qr6ov9
-rw-r----- 1 oracle oinstall 1613824 2016-01-12 15:38:55 /home/oracle/backup/sugar4_19qr6p4f
-rw-r----- 1 oracle oinstall 1613824 2016-01-12 16:24:02 /home/oracle/backup/sugar5_1fqr6rp2

$ strings sugar5_1fqr6rp2 | grep "TESTAA" |wc
  17877   17877  268097

$  strings sugar5_1bqr6qf2 | grep "TESTAAA" > aaa.txt

$ head aaa.txt
000319TESTAA,
000318TESTAA,
000317TESTAA,
000316TESTAA,
000315TESTAA,
000314TESTAA,
000313TESTAA,
000312TESTAA,
000311TESTAA,
000310TESTAA,

--如果你注意看结尾逗号,ascii 对应2c 实际上就是记录的开始.这样结尾不是逗号的就是该块的第一条记录.

$ grep -v ",$" aaa.txt
000001TESTAA
000320TESTAA
000634TESTAA
000948TESTAA
001262TESTAA
001576TESTAA
001890TESTAA
002204TESTAA
002518TESTAA
002832TESTAA
003146TESTAA
003460TESTAA
003774TESTAA
004088TESTAA
004402TESTAA
004716TESTAA
005030TESTAA
005344TESTAA
005658TESTAA
005972TESTAA
006286TESTAA
006600TESTAA
006914TESTAA
007228TESTAA
007542TESTAA
007856TESTAA
008170TESTAA
008484TESTAA
008798TESTAA
009112TESTAA
009426TESTAA
009740TESTAA
010052TESTAA
010353TESTAA
010654TESTAA
010955TESTAA
011256TESTAA
011557TESTAA
011858TESTAA
012159TESTAA
012460TESTAA
012761TESTAA
013062TESTAA
013363TESTAA
013664TESTAA
013965TESTAA
014266TESTAA
014567TESTAA
014868TESTAA
015169TESTAA
015470TESTAA
015771TESTAA
016072TESTAA
016373TESTAA
016674TESTAA
016975TESTAA
017276TESTAA
017577TESTAA

$ grep -v ",$" aaa.txt |wc
     58      58     812

--也就是读取了58块.

6.重复:
--删除表重建建立:
SCOTT@book> drop table tt1 purge ;
Table dropped.

SCOTT@book> create table tt1 tablespace sugar as select rownum id,to_char(rownum,'000000')||'TESTAA' name from dual connect by level<=2e5;
Table created.

--编辑建立如下脚本:

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) N10
      ,DBMS_ROWID.ROWID_ROW_NUMBER (ROWID) N20
  FROM tt1
WHERE id IN (1
             ,320
             ,634
             ,948
             ,1262
             ,1576
             ,1890
             ,2204
             ,2518
             ,2832
             ,3146
             ,3460
             ,3774
             ,4088
             ,4402
             ,4716
             ,5030
             ,5344
             ,5658
             ,5972
             ,6286
             ,6600
             ,6914
             ,7228
             ,7542
             ,7856
             ,8170
             ,8484
             ,8798
             ,9112
             ,9426
             ,9740
             ,10052
             ,10353
             ,10654
             ,10955
             ,11256
             ,11557
             ,11858
             ,12159
             ,12460
             ,12761
             ,13062
             ,13363
             ,13664
             ,13965
             ,14266
             ,14567
             ,14868
             ,15169
             ,15470
             ,15771
             ,16072
             ,16373
             ,16674
             ,16975
             ,17276
             ,17577) order by 1;

SCOTT@book> @ /home/oracle/backup/bbb.txt
                  N10        N20
--------------------- ----------
                  131          0
                  132          0
                  133          0
                  134          0
                  135          0
                  136          0
                  137          0
                  138          0
                  139          0
                  140          0
                  141          0
                  142          0
                  143          0
                  145          0
                  146          0
                  147          0
                  148          0
                  149          0
                  150          0
                  151          0
                  152          0
                  153          0
                  154          0
                  155          0
                  156          0
                  157          0
                  158          0
                  159          0
                  161          0
                  162          0
                  163          0
                  164          0
                  165          0
                  166          0
                  167          0
                  168          0
                  169          0
                  170          0
                  171          0
                  172          0
                  173          0
                  174          0
                  175          0
                  177          0
                  178          0
                  179          0
                  180          0
                  181          0
                  182          0
                  183          0
                  184          0
                  185          0
                  186          0
                  187          0
                  188          0
                  189          0
                  190          0
                  191          0
58 rows selected.

WITH a
     AS (    SELECT 130 + ROWNUM x
               FROM DUAL
         CONNECT BY LEVEL <= 61)
SELECT x
  FROM a
WHERE x NOT IN (SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) N10
                   --      ,DBMS_ROWID.ROWID_ROW_NUMBER (ROWID) N20
                   FROM tt1
                  WHERE id IN (1
                              ,320
                              ,634
                              ,948
                              ,1262
                              ,1576
                              ,1890
                              ,2204
                              ,2518
                              ,2832
                              ,3146
                              ,3460
                              ,3774
                              ,4088
                              ,4402
                              ,4716
                              ,5030
                              ,5344
                              ,5658
                              ,5972
                              ,6286
                              ,6600
                              ,6914
                              ,7228
                              ,7542
                              ,7856
                              ,8170
                              ,8484
                              ,8798
                              ,9112
                              ,9426
                              ,9740
                              ,10052
                              ,10353
                              ,10654
                              ,10955
                              ,11256
                              ,11557
                              ,11858
                              ,12159
                              ,12460
                              ,12761
                              ,13062
                              ,13363
                              ,13664
                              ,13965
                              ,14266
                              ,14567
                              ,14868
                              ,15169
                              ,15470
                              ,15771
                              ,16072
                              ,16373
                              ,16674
                              ,16975
                              ,17276
                              ,17577));

         X
----------
       160
       176
       144
3 rows selected.

--从块131开始,144,160,176没有读取.

alter system dump datafile 6 block 144 ;
alter system dump datafile 6 block 160 ;
alter system dump datafile 6 block 176 ;

--检查转储frmt: 0x02 chkval: 0x98b4 type: 0x20=FIRST LEVEL BITMAP BLOCK.
--也就是一级位图.

SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents  where owner=user and segment_name='TT1';
OWNER  SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT  TT1                  TABLE              SUGAR                                   0          6        128      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                   1          6        136      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                   2          6        144      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                   3          6        152      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                   4          6        160      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                   5          6        168      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                   6          6        176      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                   7          6        184      65536          8            6

SCOTT  TT1                  TABLE              SUGAR                                   8          6        192      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                   9          6        200      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                  10          6        208      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                  11          6        216      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                  12          6        224      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                  13          6        232      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                  14          6        240      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                  15          6        248      65536          8            6
SCOTT  TT1                  TABLE              SUGAR                                  16          6        256    1048576        128            6
SCOTT  TT1                  TABLE              SUGAR                                  17          6        384    1048576        128            6
SCOTT  TT1                  TABLE              SUGAR                                  18          6        512    1048576        128            6
SCOTT  TT1                  TABLE              SUGAR                                  19          6        640    1048576        128            6
SCOTT  TT1                  TABLE              SUGAR                                  20          6        768    1048576        128            6
21 rows selected.

--很明显即使你truncate表,前面的64块也是备份的.这样truncate后还多了512K,对于8k的数据块.

时间： 2024-09-18 13:13:31

[20160112]rman会备份空块吗.txt的相关文章

[20151116]rman 全备份以及增量0级备份.txt

[20151116]rman 全备份以及增量0级备份.txt --前一阵子写了一篇将as copy作为增量0级备份的帖子,今天对比全备份以及增量0级备份的不同. 1.测试环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- --------------------------------------

[20160523]rman备份与检查点2.txt

[20160523]rman备份与检查点2.txt --写过几篇关于检查点的文章,测试看看如果rman做备份时是否写检查点. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING         VERSION        BANNER ------------------- -------------- ---------------------------------------------------------------------------- x86

[20160524]rman备份与检查点4.txt

[20160524]rman备份与检查点4.txt --链接: http://blog.itpub.net/267265/viewspace-2105221/ http://blog.itpub.net/267265/viewspace-2105223/ --昨晚仔细思考,重复测试看看,使用新的控制文件是否可以恢复.感觉我的问题在于我做了catalog注册了备份文件时丢失某些信息.重新 --测试看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING      

[20160523]rman备份与检查点3.txt

[20160523]rman备份与检查点3.txt --从前面的测试可以发现: --1.通过以上信息可以确定备份文件会写检查点. --2.而且备份文件是先写检查点,再做备份. --我这里就产生1个疑问,假设某个数据文件备份时间很长,这样数据文件的某个数据块的scn可能大于文件头的CHECKPOINT_CHANGE#.当我 --们恢复使用不完全恢复到某个时间点或者SCN,应该使用那个备份文件呢?做一个测试来说明问题. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRIN

[20161118]rman备份的疑问2.txt

[20161118]rman备份的疑问2.txt --这个是我前几天做测试时遇到的疑问,不知道为什么rman 备份要修改数据块的dba地址. --我在itpub上也问了这个问题,链接http://www.itpub.net/thread-2071504-1-1.html,可惜没有人解答. --具体细节不清楚,读取数据文件然后重新编码组织数据,将kcbh.rdba_kcbh的后2位放入tailchk中,设置spare3_kcbh=0x0001, --也许为了区分备份与数据文件,当然还有别的目的,再

[20150321]索引空块的问题.txt

[20150321]索引空块的问题.txt --晚上看了: 索引空块较多造成index range scan的IO成本较高 http://www.dbaxiaoyu.com/archives/2504 --感觉有点怪怪的: SELECT /*+gather_plan_statistics ab*/ LOG.OID              OID, LOG.REGION           REGION, LOG.ACCEPT_SEQ       ACCEPT_SEQ, LOG.PROCESS_

Oracle增量备份和快速备份（块改变跟踪Block Change Tracking）

Oracle增量备份和快速备份(块改变跟踪Block Change Tracking) 下面小麦苗给出全库备份的脚本: 点击(此处)折叠或打开 [oracle@rhel6lhr ~]$ crontab -l 2 12 * * 1 /home/oracle/lhr/rman/rman_backup_full.sh [oracle@rhel6lhr ~]$ more /home/oracle/lhr/rman/rman_backup_full.sh export ORACLE_SID=orclasm

[20150811]模拟坏块处理.txt

[20150811]模拟坏块处理.txt --如果存在备份,修复坏块还是相对简单的.在11g下: select * from V$DATABASE_BLOCK_CORRUPTION; --在rman下执行: blockrecover corruption list; --如果数据块没有使用,没有分配data_object_id而出现坏块,如何恢复呢?一般采用的方法建立新对象的方法,格式化这个数据块. --具体测试如下: 1.建立测试环境: SCOTT@test> @ver1 PORT_STRIN

[20160816]11G dataguard坏块修复.txt

[20160816]11G dataguard坏块修复.txt --11GR2 不仅仅支持在备库在只读的情况下,日志应用(ACTIVE Data Guard),还提供主备库的坏块修复.自己以前也做过相关测试, --我记得上次测试的仅仅是主库数据块损坏,没有测试备库的数据块损坏.补充一些测试: 1.环境: SYS@test> @ ver1 PORT_STRING                    VERSION        BANNER ---------------------------